Milena Petrovic

SQL Server alerts in troubleshooting performance issues: How to create and use SQL Server alerts

March 15, 2014 by
In the first part of this series, we presented the alerting feature available via SQL Server Agent. In this sequel, we will show how to provide all prerequisites, create alerts, and adequate notifications

Before creating a SQL Server alert, make sure that Database Mail and SQL Server Agent properties are properly configured

Configure Database Mail

  1. Expand the Management node in SQL Server Management Studio
  2. Right-click Database Mail and select Send Test Database mail
    If you receive the message that there are no Database mail profiles, you have to create an email profile first

  3. When prompted to launch the configuration wizard, click Yes. Another option is to right-click Database Mail and select Configure Database Mail
  4. Create a database mail account using your mail server parameters and credentials

    Database mail configuration wizard - manage an existing account dialog

  5. Create a database mail profile and add the mail account created in the previous step

    Database mail configuration wizard - manage an existing profile dialog

Configure SQL Server Agent

Another very important step, often skipped when creating an alert, is setting SQL Server Agent properties:

  1. Right-click SQL Server Agent in Object Explorer and select Properties
  2. Select the Alert System tab
  3. Select the Enable mail profile option
  4. Make sure that Mail system is Database Mail
  5. Select Mail profile from the drop-down list
  6. Make sure that Include body of e-mail in notification message is selected

    SQL Server agent properties dialog

  7. The fail-safe operator is a backup operator that will be notified if sending notifications to all other ‘regular’ operators fails. If you want to use this option, select the Enable fail-safe operator option and select the operator from the list
  8. Click OK
  9. Restart SQL Server Agent, as changes might not be applied automatically

If the mail profile is not enabled for SQL Server Agent, you will be able to create SQL Server alerts and set email notifications successfully. There will be no error messages, nor warnings, yet emails will not be sent. To find out why the email notifications are not sent, you will have to check the SQL Server error logs, they will clearly indicate that the problem is that Database Mail is not enabled for SQL Server Agent notifications

Dialog showing SQL Server error logs

Creating a SQL Server alert

Alerts can be created using SQL Server Management Studio or T-SQL. When creating an alert, you have to specify its name, the event or threshold value that fires the alert, and the action executed when the alert is fired

  1. Make sure that SQL Server Agent is running. If not, right click SQL Server Agent in SQL Server Management Studio Object Explorer and select Start. Consider setting SQL Server Agent to start up automatically
  2. Right-click the Alerts node in the SQL Server Agent and select New Alert
  3. Enter the alert name and select SQL Server performance condition as the alert type from the drop-down menu. Other two available options are SQL Server event and WMI event
  4. In the Performance condition alert definition, specify the object name, for example Buffer Manager. Other available objects are Access Methods, Availability Replica, Buffer Node, Catalog Metadata, Databases, General Statistics, Latches, Locks, and many more

    These object types can be obtained by executing

  5. The list of available counters shows all Buffer Memory counters. Again, you can see them when querying the sys. dm_os_performance_counters view, or in Performance Monitor. In this example, we’ll select Page Life Expectancy
  6. The instance name is the database name, Total if the SQL Server alert is applied to all databases, counter specific information, or blank. This is the information returned in the instance_name column by the sys. dm_os_performance_counters view
  7. The Alert if counter field can have falls below, becomes equal to, or rises above values

    As the normal values for Page Life Expectancy are above 300 seconds (5 minutes), we’ll select falls under and enter 300 in the Value field

    Configuring the SQL Server alert based on Page Life Expectancy counter

    Note that the counters are queried periodically and that there might be a slight delay before the alert is triggered

    Here is an example for setting Memory Grants Pending. Its value represents the total number of SQL Server processes waiting to be granted workspace in the memory. The threshold value is zero. If any processes are waiting for the memory workspace, they are queued and the Memory Grants Pending value is higher. The alert will be fired if the counter rises above 0

    Dialog showing the Memory Grants Pending set up

  8. In the Response tab, specify what event type you want to trigger by the alarm. Available options are to execute a SQL Server job, and to send a notification to an operator. You can select both for a single SQL Server alert. To be able to create a response, you must be a member of the sysadmin role
  9. To execute a job, select the Execute job option, and an existing job from the drop-down list. If the job doesn’t exist, click the New Job button and create a new job
  10. To send a notification, select Notify operators

    Dialog showing the Execute job option

  11. If the operator list is empty, select New Operator and fill in the records

    Dialog showing New operator set up

  12. In the Notifications tab, select E-mail for the alert type

    Another way to add a notification is using the sp_add_notification stored procedure

    The @notification_method parameter can be 1,2, or 4 for emails, pager, and net send, respectively. Only members of the sysadmin SQL Server role can execute the sp_add_notification stored procedure

  13. Click OK and the new Operator will appear in the Operator list of the Alert Properties dialog
  14. Open the Options tab in the Alert Properties dialog
  15. Select to include the error message into emails sent and add additional text that will provide more details about the situation that triggered the alert

    Alert Properties - include the error message dialog

  16. Set the delay between responses. The default is 0 minutes and 0 seconds. Keep in mind that sending frequent email notifications until the issue is fixed and the condition doesn’t trigger the alarm might lead to sending too many emails, block your email client and affect the complete system performance
  17. Open the History tab, to see how many times the SQL Server alert has occurred, the last occurrence, and the last response to the alert. To reset the number of captured alerts and other parameters shown in this tab, check the Reset count check box

    History tab - see how many times the SQL Server alert has occurred

The same alert can be created using T-SQL and the dbo.sp_add_alert stored procedure. Note that it has to be executed against the msdb database by a member of a sysadmin role

When an alert is fired on the FUJITSU\SQL2012 instance, you’ll get an email with the SQL Server Alert System: ‘PLE_Alert’ occurred on \\FUJITSU\SQL2012 subject and the following text in the email body:

DATE/TIME: 2/19/2014 1:08:41 PM

DESCRIPTION: The SQL Server performance counter ‘s’ (instance ‘s’) of object ‘s’ is now below the threshold of s (the current value is s).

COMMENT: Page Life Expectancy has fallen under 300 seconds

JOB RUN: (None)

Disabling an alert
To stop the alert from being fired, but still keep it as inactive, disable it. You can easily enable it and reuse later

  1. Expand the SQL Server Agent node in SQL Server Management Studio Object Explorer
  2. Select the alert you want to disable and right-click it
  3. Select Disable

The same can be done using T-SQL. Only members of the sysadmin SQL server role can execute the sp_update_alert stored procedure

When set to 0, the @enabled parameter disables the alarm. To enable the alarm, use same code except for @enabled = 1

Note that all T-SQL commands given in this article, unless qualified as msdn. dbo.<procedure_name> have to be executed against the msdb database. Otherwise, you’ll get the error message that the procedure cannot be found

As shown, the Alert feature in SQL Server Agent is very useful for automating performance issue notifications. It enables creating SQL Server alerts for every available SQL Server performance counter. For each counter, you can set a custom threshold value and specify whether the expected values should be higher or lower. However, it is not possible to set two values for a counter in the same alert. For example, if the counter value is lower than the value A – fire an alarm with a notification about a medium severity issue, and if the counter value falls below the value B – fire a critical alarm

Email notifications can be seamlessly sent to a number of operators, or to a mailing list. Also, sending pager and net send notifications are available, but will be depreciated in the future SQL Server versions

Setting multiple alarms is not very user friendly, as you have to create a single alarm for each counter you want to monitor, therefore go through a number of steps. The feature is supported only in Enterprise and Business Intelligence SQL Server editions

Milena Petrovic