How can we help you today? How can we help you today?

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&nbsp;</div><div>&nbsp; &nbsp; c.session_id, c.connect_time, s.login_name, s.login_time</div><div>FROM&nbsp;</div><div>&nbsp; &nbsp; sys.dm_exec_sessions AS s</div><div>&nbsp; &nbsp; INNER JOIN sys.dm_exec_connections AS c&nbsp;</div><div>&nbsp; &nbsp; &nbsp; &nbsp; ON c.session_id = s.session_id</div><div>ORDER BY&nbsp;</div><div>&nbsp; &nbsp; 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!
Severn
0

Comments

2 comments

Sort by
  • Alex B
    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
    Alex B
    0
  • Severn
    Hi @Alex B  - thanks so much for your reply. I am on 12.1.2.23581 so will update and see if it fixes the issue. 
    Severn
    0

Add comment

Please sign in to leave a comment.