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