In my “SQL Server Agent: Creating an Alert for ‘Could Not Allocate Space’ 1105 Errors” article, we discussed the procedure of creating a “SQL Server event alert.” In addition to the event alert, a second type of alert, the performance condition alert, exists to monitor SQL Server performance.
SQL Server performance condition alerts help the DBA monitor performance for internal SQL Server objects (for example, Log Cache Hit
Ratio/sec or Page Splits/sec). SQL Server performance condition alerts fire upon specific performance conditions (when specific thresholds have been reached). In this article we discuss the use of Performance Monitor (a standard software tool included in the Windows NT Server operating system family) and the process of general performance monitoring for Microsoft SQL Server.
The Performance Monitor can provide the DBA with a “wake up” call when one of the counters specified crosses a certain threshold. Using alerts in this way is a proactive way to watch for trouble. By instructing Performance Monitor to send a message and log an event once a counter starts creeping into an unacceptable range, the database and/or system administrator can catch problems before they occur. For example, you can set an alert to monitor a particular server and inform you know when its CPU usage rises above X percent or its free disk space dips below Y percent.
To set up a Performance Monitor alert (on Microsoft Windows NT Server 4.0):
Start Performance Monitor (typically: Start – Programs – Administrative
Tools (Common) – Performance Monitor);
On the View menu, click Alert, then click the File menu and select New
Alert Settings. These actions put Performance Monitor in alert mode and
clear out any old alert information.
On the Edit menu, click Add to Alert. You will see the “Add to Alert” dialog
box.
Specify the SQL Server name in the “Computer” field, object, instance, and
counter you want to include in the list of alerts. Under “Alert If”, click
Over or Under, depending on which condition you want to signal, and type
the threshold value. For example, if we select “% Processor Time”, click
Over and type 90 to indicate an alert should be sent when the CPU usage
creeps over 90 percent. When you are done, click Add.
If you want to run a program whenever the alert is triggered, under “Run
Program on Alert” field, type the name of the program. If you want the
program to run whenever the alert is triggered, click Every Time. If you
want it to run only on the first alert, click First Time.
Repeat the above steps for each counter you want to add to the list of alerts.
When you are finished, click Done.
Save your alert settings by clicking “Save Alert Settings As” on the File
menu. Specify the folder and filename that you want to use, and click Save.
Performance Monitor alert settings files have a .PMA file extension. Note
that only the alert settings are saved, not the data itself.
On the Options menu, click Alert. In the “Alert Options” dialog box, select
the options describing the behavior of all alerts in the alerts list. If
you want Performance Monitor to immediately switch to alert mode when an
alert is triggered, select Switch to Alert View option.
If you want to log alert events so that you can later review them using
Event Viewer, select Log Event in the Application Log option.
If you want a pop-up message to appear when alerts occur, select Send network
message option. In “Net Name” field, type the name of the user account
that should receive the message.
To specify automatic alert monitoring, click Periodic Update and type the
number of seconds between samples. Don’t make the interval too short; you’ll
get more messages than you can handle when things start to go wrong. I recommend starting
with a setting of 180 seconds (three minutes). If you want to monitor these
conditions manually, click Manual Update. To update the alert log contents,
click Options, then Update Now.
