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

SQL Server performance monitoring and alerting

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

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

Postby EdCarden » Fri Apr 13, 2012 6:11 pm

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?
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm

Postby peter.peart » Mon Apr 16, 2012 10:15 am

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
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Re:

Postby EdCarden » Tue Apr 17, 2012 7:11 pm

peter.peart wrote: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

Thanks for the info. I am a frequent visitor of the website, in fact its where I heard about SQL MOniotr. Still, I'm suprised to read that these values came from that sites users. Oh well.
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests