Why is SQL Server alerting important?

To be aware of what’s going on with your system and how SQL Server performs, monitoring is necessary. However, just monitoring might not be enough. Being notified about a performance issue whenever a specific SQL Server counter meets a threshold value, or a specific event occurs, boosts monitoring usability. These notifications enable DBAs to be aware of every important system performance change as soon as it happens, and therefore they can act immediately to fix the issue and prevent further problems

Alerts fired when a specific metric reaches the pre-defined threshold allows DBAs to work on other tasks and be sure that no potentially threatening SQL Server issue will be unnoticed. With automatic alerts in SQL Server, there is no need to sit by the monitor, watch the graphs and numbers, or run reports every 5 minutes. A properly set SQL Server alerting will notify you about all important issues

There are two monitoring features available in SQL Server Management Studio – SQL Server Activity Monitor and Data Collection. Activity Monitor provides no flexibility for specifying the monitored metrics and for creating reports. Data Collection provides creating custom collection sets where you can specify the metrics you want to audit, as well as using custom reports, but these custom collections and reports require coding. Another native resource for monitoring SQL Server counters is the sys. dm_os_performance_counters dynamic management view. Neither of the listed features provides specifying a threshold value and creating alerts when a specific condition is met

The SQL Server alerting feature

The alerting feature in SQL Server is brought by Alerts in SQL Server Agent. It provides the capability to set thresholds and create alerts for the SQL Server counters, send messages to a number of operators, launches a built-in or custom application, or execute specific tasks

General recommendations for using SQL Server Alerts:

• Alerting can increase processing load, so be careful when configuring it on resource intensive servers
• Alerting can increase network traffic, especially when alerting for multiple SQL Server instances is managed on a single SQL Server instance
• Make sure you have all prerequisites properly configured (Database mail and SQL Server Agent properties) before creating an alert, as you will be able to create alerts without any error messages even when the necessary options are not available

Alert types

Alerts can be created for SQL Server events, SQL Server performance conditions, and WMI events

An alert created for a specific SQL Server event can be triggered when a specific error occurs (defined by the error number), any error of a specific severity occurs (defined by the severity level), or the message logged by the event contains a specific text. For example, an alert can be fired if the event message contains a specific database object or user name. In this article, we’ll focus on alerts triggered by a specific SQL Server performance condition

The counters available are the counters available in the sys. dm_os_performance_counters dynamic management view, the object and counter names used in SQL Server alerting dialogs are the same

Some of the alerts are defined for the SQL Server instance, while others can be defined on all or specific databases only. For example, the memory counters alerts are created on an instance, while database counter alerts can be created on a database level, so you can set an alert to be fired only if the Production database data file size reaches 1 GB. If any other database data file reaches this threshold, no alerts will be fired

When the alert is fired, an existing or newly created SQL Server job can be executed. This provides a wide range of actions that can automate steps needed to mitigate or fix the event that fired the alert. Besides executing a SQL Server job, an alert can send a notification at the same time. The available notifications are emails, net send and pager messages. Note that the latter two will be removed from the future versions of SQL Server. If you want to send an email, Database Mail has to be configured. It’s highly recommended to test this feature first. If you configure an alert without an existing Database Mail profile, you will not be notified about it and therefore unaware that emails will not be sent

The benefits of using SQL Server Alerts are that DBAs are seamlessly up to date with all important and critical events on the SQL Server instance. The advantages of SQL Server Alerts are that alerts can be easily made for multiple counters – a custom combination of counters and values is easy to create and modify, and no coding is required. The feature is available in SQL Server Management Studio, so no third party tools are necessary, executing any SQL Server Agent job is possible, which enables fixing common error conditions automatically, without any manual work

In case of notification frequency improperly set, there might come to increased network traffic and significantly slow down the system response. Also, sending too many email notifications can clog your email. The server load can also be increased

Although the feature provides a good solution for being alerted when a SQL Server counter reaches a specific value, the alerts are limited to the counters available in SQL Server. System performance counters are not supported. Another disadvantage is a single point of failure. If alerting on multiple SQL Server instances is managed from a single point and SQL Server Agent, SQL Server instance, or the whole server fail for any reason, alerts will not be fired for any of the managed SQL Server instances

In the next part of this series, we will give recommendations and detailed steps for creating alerts