Description
This alert will run against the monitored instance and retrieve the time each session has been blocked ("Seconds Blocked" column). It will also give some details about the blocking session in the "Message" column and this can be customized with anything else from sys.dm_exec_requests DMV.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Blocked Sessions
Execution Interval: <= 10 minutes down to 1 minute
Notification Text: These are sessions being blocked.
SQL Statement:
select 'SPID ' + convert(varchar,blocking_session_id) +
' has been blocking ' + CONVERT(varchar,session_id) +
' for ' + CONVERT(varchar,wait_time/1000) + ' seconds.' "Message", wait_time/1000 "Seconds Blocked"
FROM master..sysprocesses AS s WITH(NOLOCK)
LEFT OUTER JOIN sys.dm_exec_requests r WITH(NOLOCK) ON r.session_id = s.spid
where blocking_session_id<>0
AND command <> 'AWAITING COMMAND'
AND command NOT LIKE '%BACKUP%'
AND command NOT LIKE '%RESTORE%'
AND command NOT LIKE 'FG MONITOR%'
AND last_wait_type NOT IN ('BROKER_EVENTHANDLER', 'CLR_MANUAL_EVENT', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'WAITFOR')
AND spid>50
AND spid<>@@SPID
order by blocking_session_id
Execute Against: Monitored Instance
Units: Blocked Time (secs)
High Threshold: Min 30, Max empty
Medium Threshold: Min 15, Max 30
