Creating Alerts on Database Server Performance Conditions with Performance Monitor

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

  • 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

Alexzander Nepomnjashiy
Alexzander Nepomnjashiy
I am a Microsoft SQL Server Database Designer for Neo-Systems North-West - a security services, consulting, and training company. I have over eight years of experience in the IT field. I am currently working on several projects which involve the deployment of Microsoft Windows NT Server/Microsoft SQL Server within an enterprise business/financial environment. My typical role in these projects includes extending and improving our clients' corporate ERP systems to manage retail sales data, predict market changes and calculate trends for future market situations (DSS, OLAP). Also among my responsibilities are the design and administration of Microsoft SQL Server 7.0/2000 databases. I am available to work on a contract basis for the following types of projects: - Technical authoring, including books, articles, and white papers; - Network and systems design and analysis; - Database and software development and analysis; - Short-term consulting projects. I hope you find these articles useful. If you have any ideas for future articles (in a field of Microsoft SQL Server databases design, administration, performance optimization), or if you have anything to say about the ones below, please do not hesitate to contact me! Feel free to forward these articles to all interested associates. Thank You!

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles