DBA Scripts: Get Blocking Sessions

Part of the DBA-Tools Project.

---

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 actually crashing. It’s just that dozens of sessions are queued up behind one that never let go of a lock.

This post covers two complementary scripts: a summary that tells you at a glance whether there’s a real blocking problem right now and who’s causing it, and a detail view that shows the full chain of sessions involved so you know exactly what to investigate.

---

Why Blocking Sessions Matters

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

• A single slow or 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; the fix is always upstream, at the session that’s actually holding the lock
• Blocking chains can be several sessions deep, session C blocked by B, B blocked by A, so the summary output alone isn’t always enough to find the true root
• It’s one of the fastest health checks to run during an incident, because the fix 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 network teams, to rule out blocking first
• After deploying application changes that touch transaction handling, to confirm nothing regressed

---

The Script

Run the summary first, it answers the only question that matters at the start of an incident: is there blocking right now, and how bad is it?

/*
Script Name : Get-BlockingSummary
Category    : performance-troubleshooting
Purpose     : Identify the head blocker in any active blocking situation, with blocked session count and wait time.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-blocking-sessions/)
Requires    : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-blocking-sessions/
-- 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 bc.blocking_session_id = s.session_id
LEFT JOIN sys.dm_exec_requests        AS r   ON bc.blocking_session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
ORDER BY bc.blocked_session_count DESC, bc.max_wait_sec DESC;

Once the summary confirms there’s a head blocker, run the detail script to see every session involved in the chain, not just the top of it.

/*
Script Name : Get-BlockingSessions
Category    : performance-troubleshooting
Purpose     : Show sessions involved in blocking chains with wait type, timing, and current statement.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-blocking-sessions/)
Requires    : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-blocking-sessions/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    s.session_id,
    DB_NAME(s.database_id)                                          AS database_name,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    r.blocking_session_id,
    r.wait_type,
    CAST(ISNULL(r.wait_time, 0) / 1000.0 AS DECIMAL(10,1))        AS wait_sec,
    CAST(ISNULL(r.total_elapsed_time, 0) / 1000.0 AS DECIMAL(10,1)) AS elapsed_sec,
    r.cpu_time,
    r.logical_reads,
    t.text                                                          AS current_statement
FROM sys.dm_exec_sessions    AS s
LEFT  JOIN sys.dm_exec_requests AS r  ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
  AND (
      r.blocking_session_id > 0                    -- this session is being blocked
      OR EXISTS (                                  -- this session is blocking someone else
          SELECT 1 FROM sys.dm_exec_requests r2
          WHERE r2.blocking_session_id = s.session_id
      )
  )
ORDER BY ISNULL(r.wait_time, 0) DESC;

Between the two, the summary tells you who to look at first; the detail script tells you the full shape of the chain, including anything waiting several sessions deep.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the scripts:

# 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's the head blocker?
.\run.ps1 Get-BlockingSummary

# Full chain detail once you've found the head blocker
.\run.ps1 Get-BlockingSessions

# To run against a remote sql server:
.\run.ps1 Get-BlockingSummary -ServerInstance SQLSERVER01

These scripts live in the repo at:

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

---

Example Output

This is a genuine blocking scenario, deliberately created on this lab instance to capture real evidence rather than a manufactured example: one session opened a transaction, updated a row, and held it open without committing; a second session tried to update the same row and queued up behind it.

Get-BlockingSummary found the head blocker immediately (numbers move fast, this was moments before the grid above was captured):

head_blocker_session_id	blocked_session_count	max_wait_sec	head_blocker_login	open_transaction_count	head_blocker_wait_type
68	1	31	HPAI01\Peter	1	WAITFOR

Get-BlockingSessions showed the full picture, both sides of the chain, pictured above: session 68 holding the transaction open on WAITFOR, session 69 queued up behind it on LCK_M_U, the exact wait times climbing every time either script is re-run while the block is still live.

---

Understanding the Results

• head_blocker_session_id / blocking_session_id — the session everyone else is waiting for. In the example above, session 68 is the head blocker; session 69 is waiting on it.
• open_transaction_count — how many open, uncommitted transactions the head blocker has. 1 with no obvious reason to still be open (as here) is the classic “forgot to commit” pattern.
• head_blocker_wait_type — what the head blocker itself is waiting on. WAITFOR in this example is the manufactured delay standing in for a real long-running operation; in production this is more often NULL (actively running something) or another lock wait (a chain, not a single blocker).
• wait_type on the blocked session — LCK_M_U here means an update lock wait, the blocked session is trying to modify a row someone else already has locked. Different wait types (LCK_M_S, LCK_M_X) point at reads versus exclusive writes.
• current_statement — the exact statement each session is running or last ran, usually enough on its own to identify which application or job is involved.

---

Common Causes

• An application transaction that does work, then waits on something else (a network call, a UI prompt) before committing, leaving the lock held the whole time
• A long-running report or batch job that reads through FULL recovery or REPEATABLE READ isolation, holding locks far longer than a simple SELECT would
• A missing index forcing a scan that holds more locks, for longer, than an equivalent seek would
• An application bug where an exception path skips the commit/rollback entirely, leaving the transaction open until the connection times out or is killed

---

How to Fix Blocking Sessions

Identify the head blocker from the summary output. If open_transaction_count > 0 and the wait type suggests it isn’t actively doing anything useful, the transaction is likely stuck:

-- Check exactly what's locked before acting
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = <head_blocker_session_id>;

-- Kill the session if the transaction is confirmed stuck, not intentional
KILL <head_blocker_session_id>;

Before killing anything, confirm with the application owner whether the session is doing something legitimate, a long ETL load or migration can look identical to a stuck transaction. Killing a genuine long-running transaction forces a rollback, which can take as long as the transaction itself did to get there.

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, don’t hold a transaction open across a network call or user interaction
• Review isolation level, READ COMMITTED SNAPSHOT removes most reader/writer blocking entirely by giving readers a versioned copy instead of waiting on a lock

---

Best Practices

• Run the summary script first during any “the database is slow/hung” report, before assuming it’s a resource problem, blocking is one of the fastest things to rule in or out
• Keep application transactions as short as possible; never hold one open across a network round-trip you don’t control
• Watch open_transaction_count specifically, it’s the single fastest signal that a session is holding a lock it doesn’t need to

---

Related Scripts

You may also find these scripts useful:

• Blocking and Locking (hub)

• Blocking Chains

• Blocking Chains with Plan
• Contention Analysis
• Deadlock Summary
• Lock Escalation Stats
• Open Transactions

---

Frequently Asked Questions

What’s the difference between blocking and a deadlock?

Blocking is one session waiting for another to release a lock, it resolves on its own once the blocker finishes or is killed. A deadlock is two (or more) sessions each waiting on a lock the other holds, with no way for either to proceed, SQL Server detects this and kills one session automatically to break the cycle. See Deadlock Summary for the deadlock-specific script.

Is killing the head blocker always safe?

No. Confirm what the session is doing first via sys.dm_tran_locks and the current statement. A legitimate long transaction (a big migration, an overnight batch) will roll back if killed, which can take as long as the transaction already ran. Killing is the right call for a genuinely stuck or forgotten transaction, not a blanket first response.

---

Summary

Blocking is one of the most common reasons SQL Server suddenly looks unresponsive, and it’s also one of the fastest problems to diagnose once you know where to look: find the head blocker, not the sessions waiting on it. The summary script answers “is there a problem and who’s causing it” in seconds; the detail script gives you the full chain when the picture is more than one session deep.

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