August 31, 2009 at 5:40 am
Hi all,
I've got about 50 SQL Database servers I'm managing that range from 2000 sp4 to 2008 sp1. There are a few problem children out there as with all servers. We have automated alerts set up, and one or two of the Database are sporadically tossing out blocking errors at 3 am, when I get into the office all is good on the servers. It seems to be occuring during the maintinace window, and of course we strive for 0 blocking, but what is the acceptable limit for the alert.
Right now I have the alert set to fire off if blocking occurs for more than 1 minute. A couple of my SA's are asking if we should increase that time from 1 minute to 5 minutes, since the normal problems at night are resolved by the time I get in for the morning, and they do not have a 3rd shift dba, and despite having my cell number they do not call me at night (kind of a mixed blessing on that one) unless there is a VERY SEVERE issue.
So I wanted to ask all of you, what is an acceptable time limit for blocking alerts?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
August 31, 2009 at 6:06 am
brad
same problem for me , but as per the googling i have seen that u can upgrade to 1 min to 5 min and chek , if it is not occuring any problems
August 31, 2009 at 6:14 am
It depends on your system, the business requirements, the types of transactions... There's no one right answer. I manage systems that if I saw blocks going past 10 seconds I'd be concerned, and I have systems where a block could exist for more than 5 minutes and no one would notice. Find the setting that's right for your system, but don't base it on convenience (or not entirely on convenience).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 31, 2009 at 6:27 am
agreed. I've been talking with my SA's about that. They manage a 3rd party software that manages the alerts for all the servers, and they have only 1 general policy for the SQL servers. I'm trying to get them to create multiple based off the business use of the server.
No black and white solution, but I'm still curious what everybody else is doing as well 🙂
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
August 31, 2009 at 6:31 am
We've got different thresholds for different servers. Personally, I'd say yours are too high if it's for an OLTP system. 60 seconds is a really long time (or should be) for most transactions. If it's a reporting system, I'm not sure I'd sweat it at all. In fact, mark the db read only and then you don't have to sweat it at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply