How do you use cloud databases? Take the survey.
Long-running query alerts being regularly sent, but can't find queries
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!
0 · Share on Twitter