Suppose you want to or
need to know anytime your SQL Server is experiencing blocking where queries are
being blocked and it may or may not escalate to a deadlock situation. Chances
are you might be using a comprehensive tool to get performance alerts. But I
don't always have access to such tools at the clients.
So here I have a SQL
Server Agent alert to get automatic email when SQL Server detects queries being
blocked.
There are basically two
key alert settings I am using:
1.
The minimum threshold
value for sending the email alert is 4. That means if more than 4 processes are
involved in blocking an email is sent out immediately.
2.
The alert email shows
what the current number of process being blocked is (I highlighted it in yellow
in below alert). That way we can assess how quickly or slowly the blocking is
getting worse.
After that first email
alert, if the situation does not resolve itself, it will keep sending the email
every 15 minutes with the updated count of processes being blocked.
So if we do not get a
second email alert in next 15 minutes, it would mean it resolved itself and, no
manual intervention is needed.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Alert on SQL Server SPIDs being blocked',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=900, -- This
value is in seconds so 900 seconds = every 15 minutes
@include_event_description_in=1,
@notification_message=N'Pleae contact your DBA team
at DBA-Team@YourOrganization.org for further assistance.',
@category_name=N'[Uncategorized]',
@performance_condition=N'General Statistics|Processes
blocked||>|3',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'App_Team', @notification_method = 1
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'DBA', @notification_method = 1
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'Pager Email', @notification_method = 1
GO
GO