Blocked SQL Sessions and Blockers

Validated against: SQL Server 2005, 2008, 2008 R2.

Below is a SQL script that returns SQL sessions waiting (blocked) for one second or more, and also the associated SQL sessions that are doing the actual blocking. The script returns pretty much all the information you will ever need. If any suggestions to improve it, please be sure to let me know.

;WITH request_mode ( request_mode, [description] )
AS (
SELECT 'NULL', 'No access s granted to the resource. Serves as a placeholder.'
UNION ALL
SELECT 'Sch-S',
'Schema stability. Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.'
UNION ALL
SELECT 'Sch-M',
'Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.'
UNION ALL
SELECT 'S', 'Shared. The holding session is granted shared access to the resource.'
UNION ALL
SELECT 'U',
'Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.'
UNION ALL
SELECT 'X', 'Exclusive. The holding session is granted exclusive access to the resource.'
UNION ALL
SELECT 'IS',
'Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.'
UNION ALL
SELECT 'IU',
'Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.'
UNION ALL
SELECT 'IX',
'Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.'
UNION ALL
SELECT 'SIU',
'Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.'
UNION ALL
SELECT 'SIX',
'Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.'
UNION ALL
SELECT 'UIX',
'Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.'
UNION ALL
SELECT 'BU', 'Bulk Update. Used by bulk operations.'
UNION ALL
SELECT 'RangeS_S', 'Shared Key-Range and Shared Resource lock. Indicates serializable range scan.'
UNION ALL
SELECT 'RangeS_U', 'Shared Key-Range and Update Resource lock. Indicates serializable update scan.'
UNION ALL
SELECT 'RangeI_N',
'Insert Key-Range and Null Resource lock. Used to test ranges before inserting a new key into an index.'
UNION ALL
SELECT 'RangeI_S', 'Key-Range Conversion lock. Created by an overlap of RangeI_N and S locks.'
UNION ALL
SELECT 'RangeI_U', 'Key-Range Conversion lock created by an overlap of RangeI_N and U locks.'
UNION ALL
SELECT 'RangeI_X', 'Key-Range Conversion lock created by an overlap of RangeI_N and X locks.'
UNION ALL
SELECT 'RangeX_S', 'Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_S. locks.'
UNION ALL
SELECT 'RangeX_U', 'Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_U locks.'
UNION ALL
SELECT 'RangeX_X',
'Exclusive Key-Range and Exclusive Resource lock. This is a conversion lock used when updating a key in a range.'
)
SELECT d.[name] AS [DatabaseName], wt.[session_id] AS [Waiting SessionId],
CAST(wt.[wait_duration_ms] / 1000. AS DECIMAL(38, 1)) AS [Waiting Duration (Secs)],
CAST((wt.[wait_duration_ms] / 1000.)/60. AS DECIMAL(38, 1)) AS [Waiting Duration (Mins)],
wt.[wait_type] AS [Waiting Type], wt.[resource_description] AS [Waiting ResourceDescription],
tl.[request_mode] AS [Waiting RequestMode], rm.[description] AS [Waiting RequestModeDescription],
tl.[request_type] AS [Waiting RequestType], tl.[request_status] AS [Waiting RequestType],
(
SELECT SUBSTRING(ST.text, ( ER.statement_start_offset / 2 ) + 1,
( ( CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset ) / 2 ) + 1)
FROM sys.dm_exec_requests AS ER WITH ( NOLOCK )
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE ER.session_id = tl.request_session_id
) AS [Waiting SQL], wt.[blocking_session_id] AS [Blocking SessionId],
CASE WHEN wt.blocking_session_id > 0 THEN (
SELECT ST2.text
FROM sys.sysprocesses AS SP WITH ( NOLOCK )
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
WHERE SP.spid = wt.blocking_session_id
)
ELSE NULL
END AS [Blocking SQL],
CASE WHEN UPPER(tl.[resource_type]) = 'OBJECT'
THEN (
SELECT [name]
FROM [sys].[objects] o1 WITH ( NOLOCK )
WHERE [o1].object_id = tl.[resource_associated_entity_id]
)
ELSE (
SELECT [name]
FROM [sys].[objects] o1 WITH ( NOLOCK )
WHERE [o1].object_id = p.[OBJECT_ID]
)
END AS [Object Name], p.[index_id], i.[name] AS index_name
FROM sys.dm_os_waiting_tasks AS wt WITH ( NOLOCK )
JOIN sys.dm_tran_locks AS tl WITH ( NOLOCK )
ON wt.resource_address = tl.lock_owner_address
JOIN sys.databases d WITH ( NOLOCK)
ON tl.[resource_database_id] = d.[database_id]
LEFT JOIN [sys].[partitions] AS p WITH ( NOLOCK )
ON tl.[resource_associated_entity_id] = p.[hobt_id]
LEFT JOIN [sys].[indexes] AS i WITH ( NOLOCK )
ON p.[OBJECT_ID] = i.[OBJECT_ID]
AND p.[index_id] = i.[index_id]
LEFT JOIN request_mode rm WITH (NOLOCK)
ON tl.[request_mode] = rm.[request_mode]
WHERE wt.[blocking_session_id] IS NOT NULL
AND wt.[wait_duration_ms] >= 5000/*5 seconds or more*/;
This entry was posted in SQL 2005, SQL 2008, SQL 2008 R2 and tagged . Bookmark the permalink.