March 8, 2011 at 1:47 am
I'm looking for info on how to set up alerts, what kind of alerts to set up, and notification issues. There seems to be few eyeballs in the SQL 2008 Admin subforum so I'm reposting with more questions here.
One problem I found is that in setting up buffer cache hit ratio, it only allows integers for the condition value, and 1 seems to represent 100%. So how to monitor for when BCHR fall below 95%, which needs to be entered as a decimal value .95?
Another is setting exclusion time windows. For example, I don't need to receive alerts during our backup windows. How can I set an alert to be ignored during the backup window?
Also some general questions:
Which SQL Server event alerts should be monitored? Should every SQL instance be set up to monitor all severity levels?
Which is the minimum group of SQL Server performance conditons to monitor and alert thresholds?
Are there any WMI event alerts to monitor and alert? Any example to be found on how to set these up?
Thanks for any advice!
March 8, 2011 at 5:34 am
For the buffer cache hit ratio, set it to 95 if you want to know when it hits below that number (although, if you ask me, that's a very high number and you're likely to get false positives).
In general, set up the alerts that you think you need to know about. Primarily I look error conditions, maxed cpu, filled disk space, that sort of thing. If you gather too much stuff through alerts, you'll cut down the signal and introduce noise, effectively making your alerts useless.
I've never set up a WMI alert, so i'm not sure about that.
"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
March 8, 2011 at 5:50 am
We monitor and send alerts on errors with Severity level 17 and higher, plus 823/824 IO Failure and 825 and 832. In addition to Buffer Cache hit ratio, another one that comes to mind is low page life expectency (<300).
Thanks, Craig
March 8, 2011 at 6:04 am
the BCHR isn't working for the reason i mentioned. it won't let me enter decimal values, only integers. i entered 50 (for 50%) but it's warning for .97 (97%). i tried to enter it as 50/100, which it accepts, but it still gives an alert for 97%.
DATE/TIME:3/8/2011 2:02:26 PM
DESCRIPTION:The SQL Server performance counter 'Buffer cache hit ratio' (instance 'N/A') of object 'SQLServer:Buffer Manager' is now below the threshold of 50.00 (the current value is 0.97).
COMMENT:(None)
JOB RUN:(None)
I also tried entering with the % character, which it accepts, but don't get correct results in the alert email (and it alerts for all values ie. at 97%)
DATE/TIME:3/8/2011 2:14:34 PM
DESCRIPTION:(None)
COMMENT:(None)
JOB RUN:(None)
March 8, 2011 at 8:45 am
OLDCHAPPY (3/8/2011)
the BCHR isn't working for the reason i mentioned. it won't let me enter decimal values, only integers. i entered 50 (for 50%) but it's warning for .97 (97%). i tried to enter it as 50/100, which it accepts, but it still gives an alert for 97%.DATE/TIME:3/8/2011 2:02:26 PM
DESCRIPTION:The SQL Server performance counter 'Buffer cache hit ratio' (instance 'N/A') of object 'SQLServer:Buffer Manager' is now below the threshold of 50.00 (the current value is 0.97).
COMMENT:(None)
JOB RUN:(None)
I also tried entering with the % character, which it accepts, but don't get correct results in the alert email (and it alerts for all values ie. at 97%)
DATE/TIME:3/8/2011 2:14:34 PM
DESCRIPTION:(None)
COMMENT:(None)
JOB RUN:(None)
Try 0.50 or whatever value you want.
Again, I wouldn't suggest alerting on this. It's too easy to get momentary spikes that don't really mean anything. this value being below so, 80% on on OLTP system has to be sustained below 80% before it's a real indication of a problem.
"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
March 8, 2011 at 8:51 am
for the 3rd time...IT WON'T ACCEPT DECIMAL VALUES. :crazy:
and 95% is simply an example value. it's just an easy value to make the server hit to test alerts.
March 8, 2011 at 9:14 am
OLDCHAPPY (3/8/2011)
for the 3rd time...IT WON'T ACCEPT DECIMAL VALUES. :crazy:and 95% is simply an example value. it's just an easy value to make the server hit to test alerts.
Not to argue with you, but I put in a decimal value, 0.50, and it accepted it just fine. If I didn't put the zero in front, it gave me an error, but as long as there was a zero, it worked.
"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
March 9, 2011 at 12:55 am
Grant Fritchey (3/8/2011)
Not to argue with you, but I put in a decimal value, 0.50, and it accepted it just fine. If I didn't put the zero in front, it gave me an error, but as long as there was a zero, it worked.
that wouldn't be argumentative at all....rather helpful! thanks. :satisfied:
never thought to put a zero in front of the decimal...:crazy:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply