How to get notifications on SQL Server performance issues

While Performance Monitor can be used for monitoring performance parameters of SQL Server, it doesn’t provide an option to get notifications when certain performance metrics values breach specified thresholds. The main reason why this is important is to be able to identify SQL Server performance issues as soon as possible, and react before they affect too many users. In addition, notifications on potential SQL Server performance issues could help avoid more serious problems by forecasting when they are likely to occur in order to avoid them. Therefore, one of the primary tasks for the database administrator is to establish a system that will ensure that he/she is going to be notified in case when certain performance metrics values are beyond the expected/desired range

SQL Server itself has native functionality that allows for notifications when a performance exception occurs. Using the SQL Server Agent subsystem, or more precisely SQL Server Agent alerting component, a database administrator can establish a system that will allow him to get notifications for SQL Server performance issues he/she wants to be notified about

The first step in creating a notification system for SQL Server performance issues is to create a SQL Server Agent operator. This is an often overlooked step in situations when an alert is created for the first time, but this is important as SQL Server Agent requires proper setup of the operator(s) to ensure sending an e-mail notification when alert occurs

An operator can be created using SQL Server Management Studio or directly via T-SQL.

To create a new operator using SSMS, the following must be completed:

1. Open SSMS and expand the SQL Server Agent node

2. Using context menu of the Operators tree node, open the New Operator dialog

   

3. When the New Operator dialog shows up, enter the name of the operator and specify the E-mail name (email address) and if needed pager e-mail name in the Notification options section of the dialog

Quick tip: Net send option will not be functional on computers that don’t have a live network connection. This is valid even in the situation where both, the source and destination of the net send are located on the same computer. In addition, Net send will not work if the Messenger service on SQL Server is not started on a machine that is used for sending and on machine used for receiving of the Net send message. Microsoft discontinued the Messenger service starting with Windows Server 2008 and after taking in consideration general deficiencies in the Net send feature, using email primarily (and pager notifications optionally) is much more reliable option

After the operator is created, the next step would be to create performance alerts:

1. In the context menu of the Alerts node, select New Alert

   

2. Enter the alert name (any name of user preference)

3. Chose SQL Server performance condition alert from the type drop down menu

4. In the Performance condition alert definition section determine the performance object and counter for which alert should be triggered

   

5. In Alert if counter select the condition and set the desired threshold value for the specified counter

   

6. Now go to the Response tab and check the Notify operators checkbox. Check the E-mail checkbox and optionally the Pager check box

   

7. The Options tab allows user to use Include alert error text in option within email body and/or pager notification text. If any custom message has to be delivered with each notification sent to email or pager, enter the message in the Additional notification message to send text box

   

The same steps have to be repeated for each and every performance metric for which the notification is required

As it can be seen, the Performance monitor actually ensures basic ability for notifying the user when a user defined threshold value is reached, but there are a few drawbacks that have to be mentioned:

1. Not all counters are supported for alerting, and even some crucial counters are missing
2. The alerting system is very basic and doesn’t allow any conditions to be imposed, which mean that alert will be triggered on every threshold breach even for some usual spikes that are common and expected for some counters, which can easily produce way too excessive alerting and huge number of messages in the destination inbox, which can even lead to blocking the mail server from receiving new mail

3. No options exist to execute actions automatically when alert occurs

4. No ability exists for aggregate email on alerts for specific period of time etc.

5. No options exist for alerting schedules, which can cause excessive alerting during SQL Server maintenance periods

6. Hard to maintain in cases when the SQL Server environment is prone to changes

These drawbacks individually may not be showstoppers, but combined often preclude Performance Monitor from being a viable solution for many DBAs.

Useful resources:

• Enhancing SQL Agent Notifications
• Event Notifications
• Manage Events