sp_PressureDetector - Arithmetic overflow
daves127 opened this issue · 11 comments
Version of the script
@Version = '4.13',
@version_date = '20240101';
What is the current behavior?
-- results start to flash, then leave just error msg behind:
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
-- with debug option, similar at end...
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
Completion time: 2024-02-26T13:38:29.6194139-06:00
If the current behavior is a bug, please provide the steps to reproduce.
execute sp-pressuredetector. same with sp_pressuredetector @debug=1
What is the expected behavior?
get results like we always used to (ie: last week & prior)
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
sql2016, sp3+ (13.0.7029.3). Windows Server 2012 R2 * it worked on this version setup last week.*
@daves127 when you run it with debug on, which query causes the problem?
assume its the one right above the error.. if so..
SELECT
der.session_id,
database_name =
DB_NAME(der.database_id),
[dd hh:mm:ss.mss] =
RIGHT
(
'00' +
CONVERT
(
varchar(10),
DATEDIFF
(
DAY,
der.start_time,
SYSDATETIME()
)
),
2
) +
' ' +
CONVERT
(
varchar(20),
DATEADD
(
MILLISECOND,
CASE
WHEN
DATEDIFF
(
DAY,
der.start_time,
SYSDATETIME()
) >= 24
THEN
DATEDIFF
(
SECOND,
der.start_time,
SYSDATETIME()
) * 1000.
ELSE
DATEDIFF
(
MILLISECOND,
der.start_time,
SYSDATETIME()
)
END,
'19000101'
),
14
),
query_text =
(
SELECT
[processing-instruction(query)] =
SUBSTRING
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
dest.text COLLATE Latin1_General_BIN2,
NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),NCHAR(0),N''),
(der.statement_start_offset / 2) + 1,
(
(
CASE
der.statement_end_offset
WHEN -1
THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
END
- der.statement_start_offset
) / 2
) + 1
)
FOR XML PATH(''),
TYPE
),
deqp.query_plan,
live_query_plan =
deqs.query_plan,
statement_start_offset =
(der.statement_start_offset / 2) + 1,
statement_end_offset =
(
(
CASE der.statement_end_offset
WHEN -1
THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
END
- der.statement_start_offset
) / 2
) + 1,
der.plan_handle,
der.status,
der.blocking_session_id,
der.wait_type,
wait_time_ms = der.wait_time,
der.wait_resource,
cpu_time_ms = der.cpu_time,
total_elapsed_time_ms = der.total_elapsed_time,
der.reads,
der.writes,
der.logical_reads,
granted_query_memory_gb =
CONVERT(decimal(38, 2), (der.granted_query_memory / 128. / 1024.)),
transaction_isolation_level =
CASE
WHEN der.transaction_isolation_level = 0
THEN 'Unspecified'
WHEN der.transaction_isolation_level = 1
THEN 'Read Uncommitted'
WHEN der.transaction_isolation_level = 2
AND EXISTS
(
SELECT
1/0
FROM sys.dm_tran_active_snapshot_database_transactions AS trn
WHERE der.session_id = trn.session_id
AND trn.is_snapshot = 0
)
THEN 'Read Committed Snapshot Isolation'
WHEN der.transaction_isolation_level = 2
AND NOT EXISTS
(
SELECT
1/0
FROM sys.dm_tran_active_snapshot_database_transactions AS trn
WHERE der.session_id = trn.session_id
AND trn.is_snapshot = 0
)
THEN 'Read Committed'
WHEN der.transaction_isolation_level = 3
THEN 'Repeatable Read'
WHEN der.transaction_isolation_level = 4
THEN 'Serializable'
WHEN der.transaction_isolation_level = 5
THEN 'Snapshot'
ELSE '???'
END,
der.dop,
der.parallel_worker_count
FROM sys.dm_exec_requests AS der
OUTER APPLY sys.dm_exec_sql_text(der.plan_handle) AS dest
OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp
OUTER APPLY sys.dm_exec_query_statistics_xml(der.plan_handle) AS deqs
WHERE der.session_id <> @@SPID
AND der.session_id >= 50
AND dest.text LIKE N'_%'
ORDER BY
der.cpu_time DESC,
der.parallel_worker_count DESC
OPTION(MAXDOP 1, RECOMPILE);
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
@daves127 okay great, when you run that query on your server, where is the error actually happening? This doesn't repro for me, so I need you to let me know some basic stuff here.
When I run above code.. I only get this as output... not sure how else to answer your question..
... AND dest.text LIKE N'_%'
ORDER BY
der.cpu_time DESC,
der.parallel_worker_count DESC
OPTION(MAXDOP 1, RECOMPILE);
Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
Completion time: 2024-02-26T13:48:12.1048047-06:00
@daves127 right, you're going to have to go through and quote parts of the select list out to see which one causes the problem.
got it... will advise
repasting... with only section removed to allow it to succeed...
/*
+
CONVERT
(
varchar(20),
DATEADD
(
MILLISECOND,
CASE
WHEN
DATEDIFF
(
DAY,
der.start_time,
SYSDATETIME()
) >= 24
THEN
DATEDIFF
(
SECOND,
der.start_time,
SYSDATETIME()
) * 1000.
ELSE
DATEDIFF
(
MILLISECOND,
der.start_time,
SYSDATETIME()
)
END,
'19000101'
),
14
)
*/
@daves127 okay, great, progress! If you have sp_WhoIsActive installed, can you run it and take a screenshot of just the duration column (dd hh:mm:ss.mss)? I'm trying to figure out how long you have stuff running for that might be causing this. If there's something that's been running way over 24 days, I'll have to add another section in the case expression to handle that.
.. has monitor tool & quarterly system reboot...
53 12:42:57.000
@daves127 can you check very carefully that you're using the newest version of the script, and that it's not installed in multiple databases where calling from different contexts might be causing some confusion? I looked at the query you provided in the @debug output, and compared it to the current version, and it looks quite different.
Yours:
CONVERT
(
varchar(20),
DATEADD
(
MILLISECOND,
CASE
WHEN
DATEDIFF
(
DAY,
der.start_time,
SYSDATETIME()
) >= 24
THEN
DATEDIFF
(
SECOND,
der.start_time,
SYSDATETIME()
) * 1000.
ELSE
DATEDIFF
(
MILLISECOND,
der.start_time,
SYSDATETIME()
)
END,
'19000101'
),
14
),
Current:
CONVERT
(
varchar(20),
CASE
WHEN
DATEDIFF
(
DAY,
der.start_time,
SYSDATETIME()
) >= 24
THEN
DATEADD
(
SECOND,
DATEDIFF
(
SECOND,
der.start_time,
SYSDATETIME()
),
''19000101''
)
ELSE
DATEADD
(
MILLISECOND,
DATEDIFF
(
MILLISECOND,
der.start_time,
SYSDATETIME()
),
''19000101''
)
END,
14
),
Thanks!
'version matters'. oye. thx & sorry for the exercise. too many open tabs, too many server connections. too busy sawing to realize blade is dull.