Configuring A SQL Server Performance Condition Alert

Alerts are a valuable tool within SQL Server.  They can be used to easily monitor your the SQL Server environment for a variety of factors.  There are two main types of Alerts that can be configured within SQL Server.  Event Alerts, which can be used to track actions within SQL such as critical failures or failed login attempts, and Performance Condition Alerts, which are more robust and can be used to alert you to more specific performance related issues.  There are thousands of ways to configure performance condition alerts so the best way to understand them is to research all the options and have a basic understanding of what you are trying to accomplish before creating the alert.

Below I will outline the steps necessary to create and configure a performance condition alert to monitor wait statistics by  having the alert monitor the average wait time of locks on the server.  In this case I’m going to send out an alert if the average wait time exceeds 1000 milliseconds.

The first step is to create the new alert.  To do this expand the SQL Server Agent drop-down in Management Studio.  Locate and right-click the Alerts folder and select New Alert…

NewAlert_Location

This will launch the New Alert window which will allow you to configure your alert.  The first thing you will want to do is give you alert a name.  This should be something indicative of what the alert will be doing as the more alerts that get added, the more confusing it can be to locate if they are not named properly.

The Type, by default is set to SQL Server event alert.  Since we are creating a  performance condition alert you should click the drop-down and change this to display SQL Server performance condition alert.  Once this is done you will notice the window will display a new set of input boxes.  As mentioned before, there a numerous conditions that can be configured.  This will become evident once you begin browsing through the Object and Counter drop-downs.  Whether or not you will set an Instance is dependent on the type of Object you select.

The screenshot below shows the setup I used for creating an alert  to monitor wait statistics by  having the alert monitor the average wait time of locks on the server.

NewAlertWindow-General

Once you have configured the alert conditions, the next step is to tell the alert what to do once the condition is met.  This can be configured on the Response page.  Here I am setting it up to notify an operator via e-mail.  If you do not have any operators currently set up, take a look at my post on  Configuring SQL Server Agent Operators.

NewAlertWindow-Response

Once the Response page is configured click OK.  You will now see the new alert displayed in the Alerts drop-down.  By default it will be enabled once it has been created.

NewAlert_Created

As with most things done in SQL Server, you can also create and configure an alert using a SQL script.  The following script creates the same alert as using the steps outlined above and assigns an operator to receive an email when the alert is triggered.

Notice that alerts are created on the msdb system database.

The script shown below is the standard script that I run against all new production database servers to set up the most important alerts.  The script covers all alerts for severity 17 through 24 as well as 823 through 825 which are lesser known I/O error alerts.  All you will need to do is specify an Operator that is set up on your system and update the @operator_name value.  If you do not have any operators configured and are not sure how to do that, see my post on Configuring SQL Server Agent Operators.

Currently I have the @notification_method set to 1.  This will trigger an email to go out to the specified Operator.  Should you also want to configure it for Paging and Net Send you can set this value to 7.

As mentioned before, the best way to learn about all the options available to you when setting up alerts is to just dig in and create them.  Some are more relevant than others and several are really only used when you are attempting to diagnose a particular issue on your server.  Either way, they are a great tool for monitoring performance and understanding how your SQL environment is functioning.