Blocking isn’t a bug, it’s SQL Server enforcing correctness: one session holds a lock, another wants a conflicting one, and it has to wait. Most blocking resolves in milliseconds and nobody notices. The problem is blocking that doesn’t resolve fast, a query queued behind a session that’s holding a lock far longer than it should, and an application that looks “frozen” from the user’s side. This is the diagnostic path from “something’s slow” to the actual session and query causing it.
If sessions have already deadlocked (one got killed with error 1205), that’s a different problem, see Reading and Fixing a SQL Server Deadlock instead. This post is for blocking that’s ongoing right now, not one that already resolved itself via the deadlock monitor.
Step 1: Find the Head Blocker
Blocking chains can be several sessions deep, session C waiting on B waiting on A, but only A (the head blocker) is actually the root cause. Fixing or killing B or C does nothing; the whole chain is stuck on A.
Run Get-BlockingSessions first for a quick read of who’s blocking whom right now. For a chain three or more sessions deep, Get-BlockingChains walks the full tree and identifies the actual head blocker explicitly, rather than making you trace blocking_session_id by hand.
Step 2: Read What the Head Blocker Is Actually Doing
Once you have the head blocker’s session ID, find out what it’s running and how long it’s been running it:
SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time,
r.total_elapsed_time, t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = @HeadBlockerSessionId;
If the head blocker shows status = 'sleeping', that’s the classic case: a transaction was opened, a lock was taken, and then the application went and did something else (a network round trip, waiting on user input, a slow downstream call) without committing or rolling back. The lock is held the entire time the session sits idle. Get Open Transactions is built specifically to find these: transactions that have been open far longer than any single statement should reasonably take.
If it’s actively running (status = 'running' or 'runnable'), the blocker itself is just slow, and the real question becomes why: a missing index, an expensive scan, a bad plan. That’s a performance-tuning problem wearing a blocking symptom, not a locking problem.
Step 3: Decide, Wait or Kill
If the head blocker is genuinely mid-work and close to finishing, waiting is often the right call, killing it can force a lengthy rollback that makes the outage worse, not better. If it’s sitting idle with an open transaction (the sleeping case above) with no sign of resuming, that transaction needs to end. KILL <session_id> ends it, but always with a real understanding of the blast radius: what that transaction was doing, and what rolling it back will cost in recovery time on a large transaction. Never kill a session on a guess.
Step 4: Understand Why It Happened
Once resolved, look at the actual cause rather than moving on:
- An open transaction left idle almost always traces back to application code that opens a transaction, then does something slow or fallible before committing, network calls, external API requests, user-interaction waits. The fix is application-side: shrink the transaction to only the actual database work, do everything else outside it.
- A single slow query holding locks longer than it should is a performance problem. Check its plan, check for missing indexes with Get Missing Indexes, and check Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan for the execution plan behind the blocking chain, not just the fact that it blocked.
- Lock escalation turning row-level blocking into table-level blocking. A statement that touches enough rows in one go escalates to a table lock, which then blocks everything else on that table, not just the rows it actually needed. Batch large operations into smaller chunks to stay under the escalation threshold.
- A maintenance job running during business hours. Index rebuilds, large archival deletes, and statistics updates all take locks. If blocking clusters around a predictable time of day, check the job schedule before assuming it’s application traffic.
Best Practices
- Keep transactions as short as the actual atomic unit of work requires; nothing outside the transaction that doesn’t need to be inside it.
- Never leave a transaction open across a network round trip, a UI wait, or an external API call. If the application needs to pause, commit or roll back first.
- Watch for a recurring blocking pattern at the same time of day; that’s almost always a scheduled job, not random user traffic.
- Confirm the fix. After changing application logic or adding an index, re-run Get Blocking Sessions under the same load pattern that caused the original incident, not just “it feels faster now.”
Related Scripts
- Get Blocking Sessions, the first script to run
- Get Blocking Chains, for chains three or more sessions deep
- Get Open Transactions, for the idle-transaction case specifically
- Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan
- Get Missing Indexes
- Reading and Fixing a SQL Server Deadlock, if the session has already been killed with error 1205 rather than still blocking
- Performance and Troubleshooting (area)
Leave a Reply