Alert: Long running query and top 10 expensive queries logic

SQL Server performance monitoring and alerting

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

Alert: Long running query and top 10 expensive queries logic

Postby JoonaHook » Fri Jan 11, 2013 3:35 pm

Hi,
I've done some testing with this alert and the "top 10 expensive queries"
and it seems that it doesn't catch all long running/expensive queries and I was wondering what is the functioning logic how much and how often this checkup occurs?

I have configured long running query alert from low 20s upto high 130s and tested queries which ran for 45s sometimes it catched the query and more often not. Also I think it should eventually appear on "top 10 expensive queries" list if I would select last hour and avg/execution if I know that I have run this query only couple of times in the whole system but this doesn't seem to be the case. Do I have something misconfigured or I have misunderstood exactly how much these statistics/alerts catch running queries?

BR
Joona Hook
JoonaHook
 
Posts: 7
Joined: Wed Nov 09, 2011 9:50 am

Postby Chris Spencer » Fri Jan 11, 2013 4:47 pm

Hi Joona

Not all queries make it to the Top 10 Expensive Queries table. The queries are filtered using the following criteria:

The Query must have been executed in the last hour

Execution count > zero
AND one of the following conditions must be true
    • > 500 ms cumulative CPU time
    • > 50 executions
    • > 5,000 cumulative logical reads
    • > 50 cumulative logical writes
    • > 50 cumulative physical reads
    • > 5 seconds cumulative execution time


I hope this helps?

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Re:

Postby JoonaHook » Thu Jan 24, 2013 6:21 am

Hi,
Ok that cleared questions about top 10 but what about alerts it seems that not nearly all queries which would qualify for an alert gets caught. Does the alert checkup run in interval of some kind?

Regards,
Joona

Chris Spencer wrote:Hi Joona

Not all queries make it to the Top 10 Expensive Queries table. The queries are filtered using the following criteria:

The Query must have been executed in the last hour

Execution count > zero
AND one of the following conditions must be true
    • > 500 ms cumulative CPU time
    • > 50 executions
    • > 5,000 cumulative logical reads
    • > 50 cumulative logical writes
    • > 50 cumulative physical reads
    • > 5 seconds cumulative execution time

I hope this helps?

Regards
Chris
JoonaHook
 
Posts: 7
Joined: Wed Nov 09, 2011 9:50 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests