Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 18, 2002

Creating Alerts on Database Server Performance Conditions with Performance Monitor

By Alexzander Nepomnjashiy

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM