LRQ alerted and actual query duration time discrepancies

olafdraeger

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

chriskelly

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.