Find a current blocking connection
Here's a handy SQL query to help you identify and troubleshoot blocking in your SQL Server environment. This script combines information from several dynamic management views (DMVs) to give you a clear picture of what's waiting, what's blocking it, and the SQL statements involved.
SELECT t1.resource_type AS [Lock Type],
DB_NAME(resource_database_id) AS [Database],
t1.resource_associated_entity_id AS [Blocking Object],
t1.request_mode AS [Lock Request], -- The lock being requested
t1.request_session_id AS [Waiter SPID],
t2.wait_duration_ms AS [Wait Time], -- How long the session has been waiting
(
SELECT [text]
FROM sys.dm_exec_requests AS r WITH (NOLOCK) -- Get the SQL for the waiting session's batch
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
WHERE r.session_id = t1.request_session_id
) AS [Waiter Batch],
(
SELECT SUBSTRING(qt.[text], r.statement_start_offset / 2, (
CASE
WHEN r.statement_end_offset = - 1
THEN LEN(CONVERT(NVARCHAR(max), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2)
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id
) AS [Waiter Statement], -- The specific statement being blocked
t2.blocking_session_id AS [Blocker SPID], -- The SPID of the session causing the block
(
SELECT [text]
FROM sys.sysprocesses AS p -- Get the SQL for the blocking session's batch
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
WHERE p.spid = t2.blocking_session_id
) AS [Blocker Batch]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK) ON t1.lock_owner_address = t2.resource_address
OPTION (RECOMPILE);
This query joins sys.dm_tran_locks (information about current lock requests) with sys.dm_os_waiting_tasks (information about tasks that are waiting on something) to link waiting sessions to their blockers. It then uses sys.dm_exec_requests and sys.sysprocesses to retrieve the actual SQL text for both the waiting and blocking sessions, helping you pinpoint the exact statements causing issues.
For more detailed information on the DMVs used in this query, you can refer to the official Microsoft documentation:
