cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Alert - Blocked Sessions - From Instance

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

Labels (1)
Comments

The notification text doesn't match the alert.

Mark,

I know you just had a case for this issue. I think this alert was created before some edits to the quick poll where we are filtering out more items. When you say the notification text doesn't match what do you mean there?

I know the alert as drafted my not match the blockers tab in DPA. Also please review the new edits I just published to this alert. Does this look more like what you land on in your case?

The notification text says "The following Ignite monitors are currently down and not collecting data."  That has nothing to do with blocking. It was obviously cut and pasted from a different alert. Perhaps something like "These are sessions being blocked:" would be more appropriate.


Ideally, the query text would closely align with the quick poll so that they are reporting the same thing, just in different ways. My issue had to do with getting blocking alerts and the DPA screens telling me that there was no blocking, because these queries were different.

Been wanting to implement an alert like this. Thanks!

Thanks.  Great code.  I tweaked this to add in sys.dm_exec_sql_text  if you want to see the text of the session that is blocking:

SELECT 'SPID ' + convert(varchar,blocking_session_id) + ' - ' + t.text +

      ' -  has been blocking ' + CONVERT(varchar,session_id) +

       ' for ' + CONVERT(varchar,wait_time/1000) + ' seconds.' "Message"

     , t.text

     , 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

OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t

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

has there been any updates to the product to address the need to have the related SQL text and SPIDS in the alert? mandevil

This has been on our radar for a while now - to give attention to alerts. It is certainly on our roadmap, but I can't give specifics on if/when it will be done. If/when we do get to it though, would you be interested in taking part in our beta program to review the changes and provide feedback?

Version history
Revision #:
1 of 1
Last update:
‎12-23-2013 12:18 PM
Updated by: