SQL Server – Capturing Blocking Information (Enhanced SQL Script)

When the database that I’m handling was experiencing excessive blockings, below is the script I use too see what are being blocked and which stored procedure is the blocker. Blocking​​ is when one connection needs access to a piece of data and has to wait for another connection’s lock to clear.

  SELECT  blocking.session_id AS blocking_session_id,
         blocked.session_id AS blocked_session_id ,
         waitstats.wait_type AS blocking_resource ,
         waitstats.wait_duration_ms ,
         waitstats.resource_description ,
         DB_NAME(tl.resource_database_id) AS DatabaseName,
         blocked_cache.text AS blocked_text ,
         blocking_cache.text AS blocking_text
       FROM 
         sys.dm_exec_connections AS blocking
         INNER JOIN sys.dm_exec_requests blocked
         ON blocking.session_id = blocked.blocking_session_id
         CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
         blocked_cache
         CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
         blocking_cache
         INNER JOIN sys.dm_os_waiting_tasks waitstats
         ON waitstats.session_id = blocked.session_id
         INNER JOIN sys.dm_tran_locks tl
         ON tl.lock_owner_address = waitstats.resource_address
WHERE waitstats.wait_duration_ms >= 2000

The above query is okay but we were able to enhanced the script by adding columns to see what objects that are being blocked. Below is the enhanced version of the script.

 ;WITH T1 AS (
         SELECT blocking.session_id AS blocking_session_id ,
                blocked.session_id AS blocked_session_id ,
                waitstats.wait_type AS blocking_resource ,
                waitstats.wait_duration_ms ,
                waitstats.resource_description ,
                DB_NAME(tl.resource_database_id) AS DatabaseName,
                blocked_cache.text AS blocked_text ,
                blocking_cache.text AS blocking_text
        FROM 
                sys.dm_exec_connections AS blocking
                INNER JOIN sys.dm_exec_requests blocked
                ON blocking.session_id = blocked.blocking_session_id
                CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
                blocked_cache
                CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
                blocking_cache
	            INNER JOIN sys.dm_os_waiting_tasks waitstats
                ON waitstats.session_id = blocked.session_id
                INNER JOIN sys.dm_tran_locks tl
                ON tl.lock_owner_address = waitstats.resource_address
		WHERE waitstats.wait_duration_ms >= 2000
), T2A AS (     
       SELECT blocking_session_id, resource_description,
       CHARINDEX('hobtid', resource_description) AS StartPos,
             SUBSTRING(resource_description, CHARINDEX('hobtid', resource_description), LEN(resource_description)) AS StartText
      FROM T1
), T2B AS (
      SELECT blocking_session_id, resource_description, StartPos, StartText, SUBSTRING(StartText, 0, CHARINDEX(' ', StartText)) AS hotbid_text
      FROM T2A
), T2C AS (
       SELECT blocking_session_id, resource_description, CAST(SUBSTRING(hotbid_text, CHARINDEX('=', hotbid_text)+1, LEN(hotbid_text)) AS BIGINT) AS hobt_id
       FROM T2B
), T2D AS (
       SELECT T2C.blocking_session_id, T2C.resource_description, OBJECT_SCHEMA_NAME(object_id) AS schema_name, object_name(object_id) as object_name, object_id, partition_id, index_id, partition_number, p.hobt_id, rows
       FROM sys.partitions p INNER JOIN T2C ON p.hobt_id = T2C.hobt_id
)
SELECT GETDATE() AS DateTimeCaptured, T1.blocking_session_id, T1.blocked_session_id, T1.blocking_resource, T1.wait_duration_ms, T1.resource_description, T1.DatabaseName, T1.blocking_text, T1.blocked_text, 
T2D.schema_name + '.' + T2D.object_name AS blocked_object_name, T2D.object_id AS blocked_object_id, T2D.index_id as blocked_index_id, i.name AS blocked_index_name, i.type_desc AS blocked_index_type
FROM T1 INNER JOIN T2D ON T1.blocking_session_id = T2D.blocking_session_id AND T1.resource_description = T2D.resource_description
INNER JOIN sys.indexes i ON i.object_id = T2D.object_id AND i.index_id = T2D.index_id

Cheers!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter