SQL SERVER – FIX: Arithmetic Overflow Error in Object Execution Statistics Report in Management Studio

Many times, there are unforeseen conditions and few developers can’t predict which writing the code of a product. This is the reason we have bugs in the products and we all have job to do. One of my clients was not able to run one of the inbuilt reports which come with SQL Server Management Studio. The report name was Object Execution Statistics. As per them, this report was running fine until last week and it had suddenly stopped working. They wanted some idea this as it was not a show stopper for them. When they launch the report, they see below error

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

If you have not seen already, there was a series of blogs which I have written about SSMS Reports. Here is the blog about the report which I am talking about. SQL SERVER – SSMS: Top Object and Batch Execution Statistics Reports

Object Execution Statistics report is part of Database-Level Report. This report provides detailed historical execution data for all currently cached plans for objects within the Database. This execution data is aggregated over the time during which the plan has been in the cache.

Using the profiler, it was easy to find the query which runs in the background while launching the report. As expected, I could see the error by running query directly in SSMS.

SQL SERVER - FIX: Arithmetic Overflow Error in Object Execution Statistics Report in Management Studio ssms-rep-err-01-800x662

Before considering the query, let’s spend few seconds to understand the error we are facing.

Arithmetic overflow error converting an expression to data type int.

Which means, there is a value being passed to a column which is greater than the Integer Datatype [2,147,483,647]

Keep in mind that, we will get the same error by running the same query from management studio query window also. The query is so big that I am not going to paste here. We can copy the query from profiler and paste in SSMS. Now, if we need to identify all the columns which have the datatype defined as INT. Below were the identified columns.

declare @dbid int; 
declare @sql_handle_convert_table table(row_id int identity 
, t_SPRank int
, t_execution_count int
, t_plan_generation_num int

I changed the datatype for all of them to BIGINT and re-ran the query in SSMS query windows and it executed successfully. Now, which is the column causing the issue?

declare @dbid bigint; 
declare @sql_handle_convert_table table(row_id bigint identity 
, t_SPRank bigint
, t_execution_count bigint
, t_plan_generation_num bigint

We further isolated the issue by changing the datatype back to INT and found the column to be t_execution_count. This column – execution_count column is coming from DMV sys.dm_exec_query_stats. Then I ran the below query to check the top execution_count from DMV — sys.dm_exec_query_stats

SELECT TOP 5 execution_count
FROM sys.dm_exec_query_stats
ORDER BY execution_count DESC

execution_count
—————————-
35622915365 << more than the max of an integer.
858124017
48468962
48468962
30130034

When we try to convert this number to INT we get the same exact error what we get in the report.

SELECT CONVERT (INT, 35622915365)

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

This confirms that the column value of execution_count in the DMV – sys.dm_exec_query_stats has a value which his higher than INT Datatype.

WORKAROUND/SOLUTION

This is a clear bug in the SSMS report. I checked the latest version today and it is not fixed. Till Microsoft fixes it, the only way to resolve this problem is to run FREEPROCCACHE for this Query_Handle which is having a high count. So, we got the Query Handle we ran the query,

dbcc freeproccache(Query_handle)

Then after, the report executed fine without any issues.

Have you found any such issues with Standard Reports?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , , ,
Previous Post
The Evolution of the DBA – Challenges, Changes and Upcoming Trends
Next Post
SQL SERVER – FIX: Error: 5511 – FILESTREAM’s file system log record under log folder is corrupted

Related Posts

5 Comments. Leave new

  • I’m very much inspired when I’ve visited your blog. Your blog is really informative. Hope you will continue with the new article.

    Reply
  • Yes, we experienced the same issue in SQL Server 11.0.5058. We had 9 queries with more than 2.1 billion [execution_count] value, with the highest being over 14 billion.

    Reply
  • Andrew Robinson
    October 17, 2018 1:57 pm

    How did you get the query handle?

    Reply
  • I got the query handle by adding to Pinal Dave’s SELECT TOP 5 query above to be:

    SELECT TOP 5 sql_handle, execution_count FROM sys.dm_exec_query_stats ORDER BY execution_count DESC

    To get an idea of what query or SP was causing this, I ran this command (substitute the query handle passed into fn_get_sql() with your own of course):

    SELECT DB_NAME(dbid) AS dbName, OBJECT_NAME(objectID) AS objectName, *
    FROM sys.fn_get_sql(0x030008009D699817404543012BA6000001000000000000000000000000000000000000000000000000000000)

    I can then clear out the query with too many executions per Pinal Dave’s final query:

    dbcc freeproccache(0x030008009D699817404543012BA6000001000000000000000000000000000000000000000000000000000000)

    Reply

Leave a Reply

Menu