DESCRIPTION
Shows the number of blocking sessions.
ALERT DEFINITION
To create this alert, click on 'Alerts' > 'Manage Alerts' tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert similar to below.
Alert Name: Count Blocked Sessions On
Execution Interval: 10 MInutes
SQL Statement:
SELECT CHAR(13) + CHAR(10)
+ 'Count Blocked Session: ' + CONVERT(VARCHAR, COUNT(*)) + CHAR(13) + CHAR(10)
, COUNT(*)
FROM sys.dm_exec_requests r
LEFT JOIN sys.databases d ON (d.database_id = r.database_id)
JOIN sys.dm_exec_sessions s ON (s.session_id = r.session_id)
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE blocking_session_id <> 0
Execute Against: Monitored Database
Units: N/A
High Threshold: Min 15, Max empty