Creating Alerts on Database Server Performance Conditions with Performance Monitor

November 18, 2002

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.

See All Articles by Columnist Alexzander Nepomnjashiy