SQL Server Blocking Detection: Find Blocked Sessions

calendar Mar 1, 2026 / · 5 min read · sql server blocking performance dm exec requests dm exec sessions wait types dba scripts monitoring query tuning  ·
Share on: linkedin copy

Blocking in SQL Server occurs when one session holds a lock that another session needs, causing the second session to wait. This T-SQL script queries the dynamic management views to show all currently blocked sessions, the blocking session, wait type, wait duration, and the SQL text involved.

Purpose and Overview

When users report slow queries or application timeouts, blocking is a common cause. SQL Server does not alert you to blocking by default — you must query the DMVs to see what is happening in real time.

This script uses sys.dm_exec_requests and sys.dm_exec_sessions to find every session that is currently waiting on a lock held by another session. It also retrieves the SQL text for both the blocked and blocking sessions using sys.dm_exec_sql_text, so you can identify exactly which queries are involved.

Use this script during an active slowdown to identify the head blocker and the chain of sessions waiting behind it.

Code Breakdown

 1SELECT
 2    r.session_id                          AS blocked_session_id,
 3    r.blocking_session_id                 AS blocking_session_id,
 4    r.wait_type,
 5    r.wait_time / 1000.0                  AS wait_seconds,
 6    r.status,
 7    s.login_name                          AS blocked_login,
 8    s.host_name                           AS blocked_host,
 9    s.program_name                        AS blocked_program,
10    DB_NAME(r.database_id)                AS database_name,
11    SUBSTRING(
12        qt.text,
13        (r.statement_start_offset / 2) + 1,
14        (
15            CASE r.statement_end_offset
16                WHEN -1 THEN DATALENGTH(qt.text)
17                ELSE r.statement_end_offset
18            END - r.statement_start_offset
19        ) / 2 + 1
20    )                                     AS blocked_sql_text,
21    bs.login_name                         AS blocking_login,
22    bs.host_name                          AS blocking_host,
23    bs.program_name                       AS blocking_program,
24    SUBSTRING(
25        bqt.text,
26        1,
27        4000
28    )                                     AS blocking_sql_text
29FROM
30    sys.dm_exec_requests AS r
31    INNER JOIN sys.dm_exec_sessions AS s
32        ON r.session_id = s.session_id
33    LEFT JOIN sys.dm_exec_sessions AS bs
34        ON r.blocking_session_id = bs.session_id
35    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
36    OUTER APPLY (
37        SELECT er.sql_handle
38        FROM sys.dm_exec_requests AS er
39        WHERE er.session_id = r.blocking_session_id
40    ) AS bh
41    OUTER APPLY sys.dm_exec_sql_text(bh.sql_handle) AS bqt
42WHERE
43    r.blocking_session_id > 0
44ORDER BY
45    r.wait_time DESC;

Filtering to Blocked Sessions Only

The WHERE r.blocking_session_id > 0 clause limits results to sessions that are actively waiting on another session. A value of 0 means the session is not blocked. This filter is the core of the script — without it you would see all running requests.

Joining Sessions for Login and Host Details

sys.dm_exec_requests contains the wait and blocking information, but it does not include login name or host name. Joining to sys.dm_exec_sessions twice — once for the blocked session and once for the blocking session — adds the user and application context needed to identify who owns each session.

Retrieving SQL Text with CROSS APPLY

sys.dm_exec_sql_text is a table-valued function that accepts a sql_handle and returns the full SQL batch text. CROSS APPLY calls it for the blocked session. OUTER APPLY is used for the blocking session because a blocker may not have an active request (it could be idle with an open transaction), so a left-style apply prevents the row from being dropped.

Extracting the Exact Statement

The SUBSTRING expression uses statement_start_offset and statement_end_offset from sys.dm_exec_requests to extract the specific statement within the batch that is currently executing, rather than the entire batch. Offsets are byte-based, so dividing by 2 converts to character positions for NVARCHAR text.

Wait Seconds Column

wait_time in sys.dm_exec_requests is stored in milliseconds. Dividing by 1000.0 converts it to seconds for easier reading. Sessions that have been blocked for many seconds are high-priority candidates for investigation.

Key Benefits and Use Cases

  • Diagnose active slowdowns — run during an incident to find the root cause immediately
  • Identify the head blocker — the session with no blocking_session_id but appearing in others' blocking_session_id column is the chain leader
  • See both sides of the block — shows the SQL text of both the blocked and blocking query
  • Multi-session chains — when session A blocks B which blocks C, all rows appear so you can trace the full chain
  • Login and host visibility — helps you contact the right team or application owner

Performance Considerations

This script reads only in-memory DMVs and has negligible overhead — it is safe to run repeatedly during an incident. However, keep these points in mind:

  • DMV data is instantaneous — blocking clears the moment locks are released, so run this script quickly when the problem is active
  • Idle blockers — a session holding an open transaction while idle (e.g., a developer with an uncommitted BEGIN TRAN) will show as a blocker with no active SQL. The blocking_sql_text column will be NULL in this case
  • Read Committed Snapshot Isolation (RCSI) — if the database uses RCSI, readers do not block writers. If blocking is rare on your system, verify whether RCSI is enabled with SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME()
  • Extended Events or SQL Trace — for persistent blocking monitoring, consider an Extended Events session capturing blocked_process_report events rather than running this script manually

Practical Tips

  • Schedule with SQL Agent — create a job that runs this script every 30 seconds during business hours and logs results to a history table for post-incident analysis
  • Set a blocking threshold — use sp_configure 'blocked process threshold' to enable the blocked process report event for automated alerting
  • Combine with lock information — join to sys.dm_tran_locks to see exactly which objects and rows are locked
  • Kill the head blocker — once identified, use KILL <session_id> to release the blocking session. Always confirm with the application team before killing production sessions
  • Look for open transactions — run DBCC INPUTBUFFER(<blocking_session_id>) or query sys.dm_exec_sessions where open_transaction_count > 0 to find sessions with uncommitted work

Conclusion

Blocking is one of the most common causes of SQL Server performance problems. This script gives you immediate visibility into which sessions are blocked, how long they have been waiting, and what SQL is involved on both sides. Run it at the first sign of slowness to identify the head blocker and resolve the issue quickly.

References

Posts in this series