Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SP_Blitz Cache Arithmetic Overflow Computing CPU, duration, read, and write metrics #2018

Closed
ianmanton opened this issue Apr 5, 2019 · 7 comments

Comments

Projects
None yet
2 participants
@ianmanton
Copy link

commented Apr 5, 2019

Version of the script
@Version = '7.3';
@VersionDate = '20190219';

What is the current behavior?
Intermittently - (every 3 hours but presumably thus coinciding with a big recurring job (CDR ETL)) the spblitz process fails at the "Computing CPU, duration, read, and write metrics" step with Msg 8115, Level 16, State 2, Procedure sp_BlitzCache, Line 2164 [Batch Start Line 69] Arithmetic overflow error converting expression to data type money.) My read count is apparently up to 16 or 17 digits but as Money is being used (for a variable that is only ever an integer?) it's breaching the datatype size and falling over so the procedure aborts.

What is the expected behavior?
Not falling over :) Be able to process and sum values greater than the money datatype particularly if no decimal point is used?

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SS2017 CU 14, Windows Server 2016. Seemingly yes - We have only recently upgraded to the latest release, prior to that we had been running the same version for a year or so (the one before the additional columns needed to be added to the tables between releases) - but it also hasn't been running so frequently so may not have coincided with this hefty activity previously - call it luck!

ValuesAsMoneyAndNvarcharOnFailedRun

Update def up to 17dp (at least) last failure had this as the read value... 22,517,998,719,324,937 !

BrentOzar added a commit that referenced this issue Apr 23, 2019

#2018 sp_BlitzCache arith overflow
Changing MONEY on totals to BIGINT. Closes #2018.

BrentOzar added a commit that referenced this issue Apr 23, 2019

#2018 sp_BlitzCache arith overflow (#2033)
Changing MONEY on totals to BIGINT. Closes #2018.

@BrentOzar BrentOzar self-assigned this Apr 23, 2019

@BrentOzar BrentOzar added this to the 2019-04 Release milestone Apr 23, 2019

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Apr 23, 2019

Thanks for the bug report, and sorry about that.

Researching it, yeah, looks like the August 2018 release changed from BIGINT to MONEY for those totals, here:

af71aab

It's been fixed in the dev branch, and it'll be in this month's release. Thanks!

@ianmanton

This comment has been minimized.

Copy link
Author

commented Apr 24, 2019

@ianmanton

This comment has been minimized.

Copy link
Author

commented May 1, 2019

Hi @BrentOzar - just to check - do we need to change the datatype of the blitzcache table columns back to something else - have now installed the updated FRK (thanks!) but I'm now getting LOTS of Arithmetic Overflow errors on execution instead of the one from before!

Output below:

Setting up configuration variables
Now starting diagnostic analysis
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries
Finished running investigatory queries
Waiting to match @seconds parameter
Capturing second pass of wait stats, perfmon counters, file stats
Analyzing changes between first and second passes of DMVs
Analysis finished, outputting results
Determining SQL Server version.
Calling sp_BlitzCache
Checking @MinutesBack validity.
Setting @MinutesBack to a negative number
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Checking all sort orders, please be patient
Beginning all sort loop
Beginning for ALL
Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Adding SQL to collect trigger stats.
Collecting execution plan information.
Skipping analysis, going to results
Displaying analysis of plan cache.
Returning ExpertMode = 0
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type money.
Writing results to table.
Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Processing SQL Handles To Ignore
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Including only chosen SQL Handles
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Adding SQL to collect trigger stats.
Collecting execution plan information.
Skipping analysis, going to results
Displaying analysis of plan cache.
Returning ExpertMode = 0
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type money.
Writing results to table.
Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Processing SQL Handles To Ignore
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Including only chosen SQL Handles
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Adding SQL to collect trigger stats.
Collecting execution plan information.
Skipping analysis, going to results
Displaying analysis of plan cache.
Returning ExpertMode = 0
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type money.
Writing results to table.
Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Processing SQL Handles To Ignore
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Including only chosen SQL Handles
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Adding SQL to collect trigger stats.
Collecting execution plan information.
Skipping analysis, going to results
Displaying analysis of plan cache.
Returning ExpertMode = 0
Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type money.
Writing results to table.
Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Processing SQL Handles To Ignore
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Including only chosen SQL Handles
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Adding SQL to collect trigger stats.
Collecting execution plan information.
Skipping analysis, going to results
Displaying analysis of plan cache.
Returning ExpertMode = 0
Writing results to table.
Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Processing SQL Handles To Ignore
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Including only chosen SQL Handles
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Collecting execution plan information.
Skipping analysis, going to results
Displaying analysis of plan cache.
Returning ExpertMode = 0
Writing results to table.
Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Skip Analysis set to 1, hiding Summary
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Processing SQL Handles To Ignore
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Including only chosen SQL Handles
Applying chosen sort order
Getting spill information for newer versions of SQL
Adding additional info columns for newer versions of SQL
Getting memory grant information for newer versions of SQL
Getting spill information for newer versions of SQL
Adding SQL to collect trigger stats.
Collecting execution plan information.
Skipping analysis, going to results
Displaying analysis of plan cache.
Returning ExpertMode = 0
Writing results to table.
Writing results to table.
sp_BlitzCache Finished

@ianmanton

This comment has been minimized.

Copy link
Author

commented May 1, 2019

Possibly a clash here?

image

@BrentOzar

This comment has been minimized.

Copy link
Member

commented May 1, 2019

@ianmanton

This comment has been minimized.

Copy link
Author

commented May 1, 2019

@BrentOzar

This comment has been minimized.

Copy link
Member

commented May 1, 2019

OK, cool. If you have time to debug & find it, feel free to open a new issue - I'm going to leave this one closed though. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.