Today, I am going to talk about setting up performance alerts so that you can notified when a server has issues. Basically, there are two types of performance alerts, SQL Server and WMI.
I am going to use the sp_add_category system procedure to define a peformance category.
|
1 2 3 4 5 6 7 8 9 10 |
-- Add a custom category for alerts EXEC msdb.dbo.sp_add_category @class=N'ALERT', @type=N'NONE', @name=N'[Alert By Performance]' ; GO -- Show Alert categoryes EXEC msdb.dbo.sp_help_category @class=N'ALERT' GO |
The sp_add_alert stored procedure is used to create the alert and the sp_add_notification stored procedure is used to associate the alert with an operator. This process is used below to show examples of both SQL and WMI performance alerts.
The first type of performance alert is based upon SQL Server objects and counters that the database tracks. To set up the alert, you need to specify the object, counter, database instance, and condition to be meet for the alert to be triggered. The alert below notifies the operator when the log file on the adventure works database exceeds 50%. It will send out a email with a description of the event to the operator.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Peformance Alert - Log file half full EXEC msdb.dbo.sp_add_alert @name=N'Alert Full Log File (AW)', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @category_name=N'[Alert By Performance]', @performance_condition=N'SQLServer:Databases|Percent Log Used|AdventureWorks2008R2|>|50', @job_id=N'00000000-0000-0000-0000-000000000000' GO -- Attach the operator to the alerts EXEC msdb.dbo.sp_add_notification @alert_name = 'Alert Full Log File (AW)', @operator_name = N'John Miner', @notification_method = 1 ; GO |
The second type of performance alert is based upon WMI queries which came out with windows scripting in the year 2000. The example below looks for overall CPU usage every 60 seconds and triggers the alert if it is greater than 75%.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Performance Alert - WMI Query - Processor > 75% EXEC msdb.dbo.sp_add_alert @name=N'Alert High CPU % (WMI)', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @category_name=N'[Alert By Performance]', @wmi_namespace=N'\\.\ROOT\CIMV2', @wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA ''Win32_Processor'' AND TargetInstance.LoadPercentage > 75', @job_id=N'00000000-0000-0000-0000-000000000000'; GO -- Attach the operator to the alerts EXEC msdb.dbo.sp_add_notification @alert_name = 'Alert High CPU % (WMI)', @operator_name = N'John Miner', @notification_method = 1 ; GO |
To remove the previous alert, calls to the sp_delete_notification and sp_delete_alert procedures need to be executed. The operator to alert mapping is stored in the msdb.dbo.sysnotifications table.
|
1 2 3 4 5 6 7 8 9 |
-- Remove the operator 2 alert relationship EXEC msdb.dbo.sp_delete_notification @alert_name = 'Alert High CPU % (WMI)', @operator_name = N'John Miner'; GO -- Remove the alert EXEC msdb.dbo.sp_delete_alert @alert_name = 'Alert High CPU % (WMI)'; GO |
Something I forgot last time to mentions is that standard error messages can be retrieved from the msdb.sys.messages view. These messages can be added to by the end user using the standard sp_addmessage and sp_drop_message system procedures. The sp_altermessage procedure actual logs the message as if it was raised by code. The code snipet below demonstrates these techniques.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Add a new user defined message EXEC msdb.dbo.sp_addmessage @msgnum = 60007, @severity = 16 , @msgtext = N'A prior operation check has failed during processing.', @lang = 'us_english', @with_log = 'FALSE'; GO -- Place the error in the windows application log EXEC msdb.dbo.sp_altermessage 60007, 'WITH_LOG', 'true'; -- Drop user defined message EXEC sp_dropmessage @msgnum = 60007, @lang = 'us_english'; GO |
In conclusion, SQL Server out of the box gives you the tools to create alerts for both standard errors and performance issues. It is up to you to use these tools to build your own custom performance monitoring strategy. Next time, I will introduce two test scripts that can be used to test two alerts we previously defined.
Really like the modern look. I loved the information. Thank you for a quality article.
But wanna comment on few general things, The website design is perfect, the articles is real good : D.