Red Gate forums :: View topic - Alert: Long running query and top 10 expensive queries logic
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Alert: Long running query and top 10 expensive queries logic

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
JoonaHook



Joined: 09 Nov 2011
Posts: 6

PostPosted: Fri Jan 11, 2013 3:35 pm    Post subject: Alert: Long running query and top 10 expensive queries logic Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Fri Jan 11, 2013 4:47 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
JoonaHook



Joined: 09 Nov 2011
Posts: 6

PostPosted: Thu Jan 24, 2013 6:21 am    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group