LRQ alerted and actual query duration time discrepancies

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

LRQ alerted and actual query duration time discrepancies

Postby olafdraeger » Fri Dec 06, 2013 11:41 am

hi,

we are still relatively new to SQLMon so I'm wondering if I may be missing something about those reported query duration times.
when I have configured a low alert to be raised at 10 seconds query duration, SQLMon does create that alert and sends out the respective email with a query duration time in it. this reported time however has nothing to do with the actual query duration itself. is there a way to see the actual query duration in the alert?

as an example, I ran a query in SSMS which ran for 60+ seconds. the low alert time for LRQ is set to 10 seconds. SQLMon raises the alert as expected and reports:
Query duration: 14.98 sec
the medium alert was configured to be raised at 120 seconds, so as this threshold was not reached, no further alerts where raised.

so the question again is how can I see in SQLMon what the actual query time was?

thanks,
olaf
olafdraeger
 
Posts: 1
Joined: Fri Dec 06, 2013 11:19 am

Postby chriskelly » Mon Dec 09, 2013 11:22 am

SQL Monitor periodically checks for running SQL Processes. This check occurs approximately every 15 seconds as part of the wider polling schedule which has been designed to provide the maximum useful information for the minimum impact.

Also, SQL Monitor has to detect the same process twice in succession before it has a duration for it. So in the case of your example, the query was detected for 14.98 seconds of its duration and raised the Low alert. As the query did not continue through the medium threshold the alert was not updated.

The only way to get a more accurate query time from SQL Monitor is to change the polling configuration so that the checks run more frequently. This is not recommended though as if these checks run too often then they can have a negative impact on the SQL Server instance that you are monitoring.
Chris Kelly
Technical Support Engineer
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 1 guest