DBA Scripts: Get Blocking Summary

When Everything Looks Busy But Nothing Is Moving

Blocking is one of the most common causes of a SQL Server that suddenly looks frozen. Applications start timing out, users report that everything has stopped responding, and the server itself looks fine: CPU is low, disk queues are calm, nothing is crashing. Dozens of sessions are simply queued behind one that never let go of a lock.

One script, one question, and it is the question an incident actually turns on: is there blocking right now, and who is causing it. Everything else can wait until you have that answer. When the answer turns out to be a chain rather than a single session, Blocking Chains picks the story up, walking the chain recursively and recovering the statement even from a blocker that is asleep rather than running.

Why Blocking Matters

SQL Server uses locking to enforce transaction isolation, and most of the time that is invisible: a session holds a lock for a few milliseconds and moves on. The trouble starts when one session holds a lock far longer than expected, through an open transaction that never commits, a long-running batch, or an application that forgot to close a connection, and everything else that wants the same data queues up behind it.

• A single forgotten transaction can block dozens of sessions within seconds, turning one problem into what looks like a full outage
• The sessions doing the waiting are innocent. Killing them does nothing, because the fix is always upstream at the session holding the lock
• Chains can run several sessions deep, C behind B behind A, so the head blocker is not always the session you first land on
• It is one of the fastest checks to run during an incident, because the answer is usually obvious the moment you see who the head blocker is

When to Run This Script

• The moment users or an application report SQL Server hanging or timing out
• Routine SQL Server health checks
• Before escalating a slowness incident to infrastructure or networks, to rule blocking in or out first
• After deploying application changes that touch transaction handling

The Script

One row per head blocker, ordered by how much damage each is doing.

/*
Script Name : Get-BlockingSummary
Category    : performance-troubleshooting
Purpose     : Head blockers with context — who is blocking, how many sessions, and what they are running.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-blocking-sessions/)
Requires    : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

WITH blocked_counts AS (
    SELECT
        r.blocking_session_id,
        COUNT(*) AS blocked_session_count,
        MAX(r.wait_time) / 1000 AS max_wait_sec,
        SUM(r.wait_time) / 1000 AS total_wait_sec
    FROM sys.dm_exec_requests AS r
    WHERE r.blocking_session_id <> 0
    GROUP BY r.blocking_session_id
)
SELECT
    bc.blocking_session_id AS head_blocker_session_id,
    bc.blocked_session_count,
    bc.max_wait_sec,
    bc.total_wait_sec,
    s.login_name AS head_blocker_login,
    s.host_name AS head_blocker_host,
    s.program_name AS head_blocker_program,
    DB_NAME(s.database_id) AS head_blocker_database,
    s.open_transaction_count,
    r.wait_type AS head_blocker_wait_type,
    CAST(ISNULL(r.wait_time, 0) / 1000.0 AS DECIMAL(10,1)) AS head_blocker_wait_sec,
    SUBSTRING(ISNULL(qt.text, ''), 1, 500) AS head_blocker_statement
FROM blocked_counts AS bc
JOIN sys.dm_exec_sessions AS s ON s.session_id = bc.blocking_session_id
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = bc.blocking_session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = bc.blocking_session_id
                                           AND r.session_id IS NULL
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS qt
ORDER BY bc.blocked_session_count DESC, bc.max_wait_sec DESC;

For most incidents that is the whole job: it names the session, and you go and deal with it.

How To Run From The Repo

Clone DBA Tools, initialize and run it:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Is there blocking right now, and who is the head blocker:
.\run.ps1 Get-BlockingSummary

This script lives in the repo at:

• sql/performance/blocking-locking/Get-BlockingSummary.sql
• powershell/wrappers/performance/blocking-locking/Get-BlockingSummary.ps1

Example Output

Real blocking, created on the lab: one session held an uncommitted UPDATE on a row, and two more queued behind it.

Two rows, and only the first is worth acting on. It is the real head: WAITFOR, one open transaction, holding a lock while doing nothing at all. The second is listed as a blocker too, but its own wait type is LCK_M_X, which means it is blocked as well.

That is a chain caught in the act. Both sessions are blocking somebody, only one of them is the cause, and the difference between them is that single wait type column. Deal with the row on WAITFOR and the other resolves itself.

Understanding the Results

Seven columns decide what you do next. The rest are context for whoever you have to call.

Common Causes

• An application transaction that does its work then waits on something else, a network call or a user prompt, before committing, holding the lock the whole time
• A long-running report or batch reading under an isolation level that holds locks far longer than a simple SELECT would
• A missing index forcing a scan that takes more locks, for longer, than the equivalent seek
• An application bug where an exception path skips the commit or rollback, leaving the transaction open until the connection is killed or times out

How to Fix Blocking

Identify the head blocker from the summary. If open_transaction_count is above zero and the wait type suggests it is not doing anything useful, the transaction is probably stuck. Confirm what it is before ending it: read the statement text, and check sys.dm_tran_locks for the resources actually held.

Killing a genuine long-running transaction forces a rollback that can take as long as the transaction took to get there, and the locks stay held throughout. That is the right call for a forgotten transaction, not a first response. Troubleshoot SQL Server Blocking walks the whole decision.

For blocking that keeps recurring rather than a one-off, the durable fixes are:

• Add the missing index, so the query holds locks for less time in the first place
• Shorten the application transaction. Commit as soon as the work is logically done, and never hold one open across a network round trip you do not control
• Review the isolation level. READ COMMITTED SNAPSHOT removes most reader and writer blocking by giving readers a versioned copy instead of a lock to wait on, and the locking and row versioning guide covers the trade-offs

Best Practices

• Run the summary first on any report that the database is slow or hung, before assuming a resource problem. Blocking is among the fastest things to rule in or out
• Run it twice before acting, so you know whether the queue is draining or growing
• Keep application transactions short, and never hold one open across a round trip you do not control
• Watch open_transaction_count specifically. It is the single fastest signal that a session is holding a lock it no longer needs

Frequently Asked Questions

Related Scripts

You may also find these scripts useful:

• Blocking and Locking (hub)
• Troubleshoot SQL Server Blocking, Step by Step, the walkthrough this script sits inside
• Blocking Chains, when the chain runs more than two sessions deep
• Open Transactions, for the forgotten commit this script keeps pointing at
• Deadlock Summary, for the case where SQL Server already picked a victim
• Lock Escalation, Contention Analysis, and Blocking Chains with Plan, when blocking keeps coming back
• Log and Filter sp_who2 Results, the same problem from sp_who2 when that is all you have
• How to Check Blocking SPIDs in SQL Server
• DBA Scripts: The Complete Guide, the map across every script on this site

Summary

Blocking is one of the most common reasons SQL Server suddenly looks unresponsive, and one of the fastest to diagnose once you know where to look: find the head blocker, not the sessions waiting on it. This answers whether there is a problem and who is causing it in seconds, which is all you need in order to act.

Run the summary as a first response any time something looks hung. It takes seconds and either rules blocking in or out immediately.