Alert: Long running query and top 10 expensive queries logic

[JoonaHook]

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

[Chris Spencer]

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

[JoonaHook]

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