SQL Server Agent – Creating Performance Condition Alerts on Database Server Objects

In my previous articles we discussed the SQL Server Agent subsystem and one of its components, alerts. In this the fifth article in the series, we discover that the DBA can select among two types of alerts: SQL Server event alerts and SQL Server performance condition alerts. My next article, “Creating alerts on database server performance condition with Performance Monitor”, will describe how DBAs can implement general performance monitoring for Microsoft SQL Server with Performance Monitor (a standard tool included in the Windows NT Server operating system family).

Compared to performance monitoring with Performance Monitor, SQL Server performance condition alerts are used to monitor the internal performance
of SQL Server objects. A SQL Server performance condition alert fires upon a specific performance condition — i.e. when the specific threshold has been reached.

To set up a SQL Server performance condition alert:

  1. Start SQL Server administrative console – Enterprise Manager
  2. Make a connection to a database server, drill down (expand) SQL
    Server objects to Alerts (your_database_server_name -> Management ->
    SQL Server Agent -> Alerts
    ), and place a mouse cursor on it
  3. Select “New Alert…” in the context-dependent menu (right mouse click on the details pane)
  4. In the “New Alert Properties – (your_database_server_name)” dialog window,
    define the following options:

On the “General” bookmark:

  • Name = “Deadlock(s) detected . . .”

  • Type = “SQL Server performance condition alert.” Ensure that the
    “Enabled” option is checked

  • Object = “SQL Server:Locks”

  • Counter = “Number of Deadlocks/sec”

  • Instance = “Database”

  • Alert if counter = “becomes equal to”

  • Value = “1”

On the “Response” bookmark:

  • Click “New operator…” button

  • In the “New Operator Properties – (your_database_server_name)” dialog window:
    for the “Name” field, input DBA; for the “Net send address” field, specify the
    net send address of the newly created operator. In our case, this should be
    the domain account name for the database administrator or his/her personal
    computer NetBIOS-name

  • Test the notification functionality by pressing the “Test” button. (Your
    database administrator should now see a very strange message 🙂

  • Press the “OK” button 2 times

ATTENTION: You have just created an alert to notify you when
the number of deadlocks in any of your SQL Server databases raises above 1 per
second); however, the alert will not fire until the SQL Server Agent service has been started.

It is a best practice to start the SQL Server Agent with your database server. To do so, ensure that the “Auto-start
service when OS starts” option for services SQLServerAgent in the SQL Server Service
Manager window is checked.

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!

Latest Articles