Long-running query alerts being regularly sent, but can't find queries
Severn
Posts: 8 Bronze 1
At the same time every 3 hours (e.g. 0203, 0503, 0803) on one of our Azure Hyperscale SQL databases there are approx 1000 long-running query alerts generated for a simple insert query that runs many times a minute and generally takes under 200ms.
Here is a screenshot of the alert
In between these alerts have tried running sp_who2, sp_whoisactive and the following query on the same database
<div>SELECT </div><div> c.session_id, c.connect_time, s.login_name, s.login_time</div><div>FROM </div><div> sys.dm_exec_sessions AS s</div><div> INNER JOIN sys.dm_exec_connections AS c </div><div> ON c.session_id = s.session_id</div><div>ORDER BY </div><div> s.login_time</div>
However none of the above show any active sessions greater than a few seconds in duration, nonetheless a couple of hours later we get more alerts. Also not sure if this is a valid test but checked sys.dm_exec_query_stats for the insert query and it's max_elapsed_time since the plan was created a month ago was only 36 seconds.
Sorry if I have missing something but wondered if someone could help me with these questions
- what do the 2 fields Process login time and Query start time represent in the screenshot
- why do I not see long running queries in the few hours between the alerts
- could you suggest a T-SQL query I could run to track down any of these long running sessions that are triggering the alerts
Many thanks in advance!
Tagged:
Best Answer
-
Alex B
Posts: 1,157 Diamond 4
Hi @Severn,
What version are you currently using? We have seen this occur where the query start time was being sampled as some time before the process login time on Azure entities and the long-running query was raised as the time difference between the query start time and the login time and this should have been addressed as of 12.1.16.
Normally the query shouldn't be able to start before the process logged in, which is why this is confusing, but it seemed there was some process in Azure that could cause our sampler to go awry. The query isn't actually running long and that is why you are not seeing it before the alert (because it is based on the times sampled rather than it actually running).
If you are on SQL Monitor 12.1.16 or later and are still seeing this then please do let us know otherwise please update and confirm if that also fixes the issue for you.
Kind regards,
Alex
Answers