Long-Rynning Query False alerts

rick@syncbakrick@syncbak Posts: 14 Bronze 2
edited May 27, 2015 7:47AM in SQL Monitor Previous Versions
I am seeing alerts on long running queries on a specific stored procedure showing Query durations that appear to be many multiples of the duration I am seeing in traces. The traces consistently show the procedure taking 10-13 seconds, while the alerts frequently show durations of around 50 seconds, but sometimes into the thousands of seconds. Given the traces (and that my teammates are not at my desk with torches & pitchforks), I am convinced that these long-running query alerts are bogus.


The symptoms/behavior seem to be similar to those observed int this thread - https://forums.red-gate.com/viewtopic.php?t=11322#p43645:

Postby jschoen » Wed Nov 03, 2010 9:56 am
When I activated SQL Response to our server i got loads of long running query alerts. After experimenting I found out that during a single logon onto the sql server, I did a 30 sec query, and within a minute again a 37 query, I got an alert for 1.07 long running query.
So the monitor actually does a calculation between the 'Login time' and 'Last Batch' time. Which in our case isn't representive as the query running time, because the software often doesn't close it's connection with the database. This results in false alerts. Don't think these alerts are usefull for our environment ?

Thanks a lot!
Jeroen

Comments

  • Hi Rick

    Thank you for your post.

    I have logged a support ticket for you and I will email you shortly.

    Many Thanks
    Wendy Mitchell
    Product Support Engineer
  • As suspected, we use "Last batch time" to identify new queries, without checking if the plan handle has changed. So technically the alert is "long-running batch".

    It's difficult to make everyone happy with these kinds of alerts, because the atomicity of operations is a business consideration invisible to a SQL Server specific monitoring product. Our assumption therefore is that batches are a unit of operation. Sorry if that doesn't work in your case. I'd recommend excluding the affected queries from the LRQ alert, and monitor the average duration of the sproc with a Custom Metric that queries dm_exec_query_stats.

    Hope that helps
    Daniel Rothig
    Product Manager for SQL Monitor
Sign In or Register to comment.