DBAtlas · Database operations atlas

Find the blocking (lock) chain

SQL ServerSession

Command

SELECT r.session_id            AS waiting_spid,
       r.blocking_session_id  AS blocking_spid,
       r.wait_type,
       r.wait_time / 1000.0   AS wait_sec,
       r.status, r.command,
       DB_NAME(r.database_id) AS db,
       t.text                 AS waiting_query
FROM   sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE  r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- To find the head (root) blocker of the chain, look for the SPID whose
-- own blocking_session_id is not set (it is not blocked itself).

What it does

Lists waiting sessions and the sessions blocking them, with wait time and the query they run; makes the lock chain visible.

When to use it

To find the source of lock contention on "the application hangs / times out" complaints.

Caution

Confirm with the business owner before killing the root blocker (the SPID that is not blocked by anyone but blocks others). Query only; no risk.

Open in DBAtlas →

Related