SQL Monitor Alerts - LRQs (Long Running Query) Alerts Config

[EdCarden]

The default settings for the LOW, MEDIUM & HIGH Level alerts for LRQ alerts are:

LOW: Raised when a query takes 60 seconds or longer to run to completion

MEDIUM: Raised when A query takes longer than 120 seconds to run to completion

HIGH: Raised when a query takes 130 seconds or more to run to completion.

I'm curious as to whether anyone else thinks these defaults should probably be adjusted and if so then to what value?


I know we can change these to whatever we like but in general what do DBA's see as being an adequate amount of time before a LWO level LRQ alert should be raised and how long it should be before MEDIUM and HIGH levels are raised for the same LRQ.

I ask because we have a lot and I mean a LOT of LRQ alerts on a daily basis. We have so many that our SQL Monitor Repository DB has grown to 130GB after 9 months of usage. Considering we monitor a single SQL Server instance that has effectively 1 database I'd say that it seems like we are experiencing an abnormally large number of LRQ alerts.

Anyone else seeing a large number of LRQ alerts? How about Repository DB Growth that far exceeds the average you can expect as outlined in the products Requirements page?

[peter.peart]

Thanks for your post. From our research (asking DBA's) these are the sort of timeframes that we have been told.

Feel free to indulge in your own research though, however you might be better off posting on SQL Server Central asking for some advice, as you will get a lot more traffic there. There's a link below for you:

http://www.sqlservercentral.com/forums

On the repository growth front, this is really going to depend on how much data your're keeping, how many alerts are being generated, whether you're keeping SQL Trace data or not etc.

HTH!

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569

[EdCarden]