Arithmetic overflow error converting expression to data type money.

[viswaratha12]

We are seeing below error periodically happen since we upgraded to SQL Server 2017.

Warning: The join order has been enforced because a local join hint is used. Warning: Null value is eliminated by an aggregate or other SET operation.

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

Warning: Null value is eliminated by an aggregate or other SET operation.

[amachanic]

Sorry for the late response. Do you know which field is causing this? The money data type supports very large numbers, so I'd like to understand what we're dealing with here.

[DavidSchanzer]

Hi Adam, I'm getting this too and it's the [reads] column that is overflowing.

On one of my very busy SQL instances, this executes without error: EXEC sp_WhoIsActive @output_column_list = '[session_id], [physical_io], [physical_reads], [writes], [tempdb_allocations], [tempdb_current], [CPU], [context_switches], [used_memory], [physical_io_delta], [reads_delta], [physical_reads_delta], [writes_delta], [tempdb_allocations_delta], [tempdb_current_delta], [CPU_delta], [context_switches_delta], [used_memory_delta], [tasks], [tran_start_time], [open_tran_count], [blocking_session_id], [blocked_session_count], [percent_complete], [host_name], [login_name], [database_name], [start_time], [login_time], [program_name]'

whereas this (same with '[reads]' added): EXEC sp_WhoIsActive @output_column_list = '[session_id], [physical_io], [physical_reads], [writes], [tempdb_allocations], [tempdb_current], [CPU], [context_switches], [used_memory], [physical_io_delta], [reads_delta], [physical_reads_delta], [writes_delta], [tempdb_allocations_delta], [tempdb_current_delta], [CPU_delta], [context_switches_delta], [used_memory_delta], [tasks], [tran_start_time], [open_tran_count], [blocking_session_id], [blocked_session_count], [percent_complete], [host_name], [login_name], [database_name], [start_time], [login_time], [program_name], [reads]'

returns:

Warning: The join order has been enforced because a local join hint is used. Warning: Null value is eliminated by an aggregate or other SET operation. Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type money. Warning: Null value is eliminated by an aggregate or other SET operation.

I tried doing a global replace of "money" to "decimal(38,4)" but then I got the error:

Warning: The join order has been enforced because a local join hint is used. Warning: Null value is eliminated by an aggregate or other SET operation. Msg 8115, Level 16, State 5, Line 2 Arithmetic overflow error converting numeric to data type varchar. Warning: Null value is eliminated by an aggregate or other SET operation.

Any help you can give would be very much appreciated Adam!

[DavidSchanzer]

Here is the [reads] value that is causing the money type to overflow, if that's helpful: 4433793837069541014

I got this value by hacking the code to do a global replace of: LEFT(CONVERT(CHAR(22), CONVERT(MONEY, column), 1), 19)) to: LEFT(CONVERT(CHAR(42), CONVERT(DECIMAL(38,4), column), 1), 39))

The output wasn't pretty but it enabled me to retrieve the value.

[amachanic]

Wow, @DavidSchanzer, that's a heck of a lot of reads. I'm thinking I should make some sort of "you've gone into ridiculous land" constant, e.g. 999999999999999.99 or something, and we'll just send that back if it's in this sort of category.

[amachanic]

Either of these look nice?

select LEFT(CONVERT(CHAR(22), CONVERT(MONEY, 99999999999999), 1), 19) select LEFT(CONVERT(CHAR(22), CONVERT(MONEY, 919919919919919), 1), 19)

[amachanic]

[DavidSchanzer]

They both look nice!

[amachanic]

Can you tell me more about this process doing 4 quintillion reads? I'm curious as to how you got there - wondering if you're hitting a bug in SQL Server or something. I just ran a quick test on this end and was able to hit 100 million reads a minute using a tight loop. Assuming there's some faster processor out there than my laptop, that can do e.g. 200 million, and assuming that you had 120 of them running full time 24x7 and doing nothing else, then according to my math it would take 351 years to hit that number you sent over...

[DavidSchanzer]

It's running: DBCC CHECKDB ([dbname]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY where the database is 2.5 TB.

[DavidSchanzer]

It's been running for almost 3 days now.

[amachanic]

Wow. That's some serious consistency checking.

[DavidSchanzer]

Indeed. Eventually finished after 4 days and 8 hours.

What are your thoughts about how best to modify sp_WhoIsActive so that it doesn't throw an error in this circumstance?

[amachanic]

The plan is to define one of those constants above as the "ridiculous_data_threshold", then check for it at output time, and if the data exceeds it, use the constant instead. If you want to get it into a PR, go for it. Otherwise I'll do it when I have time/inclination.

[DavidSchanzer]

Thanks Adam.

[erikdarlingdata]

If I'm getting this right, in the @format_output section, we'd do something like this for each section that could potentially have a "big" value:

CASE @format_output
    WHEN 1 THEN 'CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io))) OVER() 
                   - LEN(CONVERT(VARCHAR, physical_io))) 
                   + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CASE WHEN physical_io > 922337203685476 THEN 922337203685476 ELSE physical_io END), 1), 19)) AS '
    WHEN 2 THEN 'CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CASE WHEN physical_io > 922337203685476 THEN 922337203685476 ELSE physical_io END), 1), 19)) AS '
    ELSE ''
END + 'physical_io, ' +

I formatted it a little to make it more compact, but you get the idea.