“The database is slow” and “the database is blocked” look identical from the outside, timeouts, a frozen application, users complaining, and the fix is completely different depending on which one it actually is. Four scripts on this site cover the full picture: finding the head blocker, tracing a multi-level chain, reading a deadlock’s actual graph after SQL Server has already resolved it, and finding the open transaction that’s the real root cause behind all three.
This post is the map. It groups them by the question each answers and lays out the order that actually makes sense during a real incident, not picked at random under pressure.
Why Blocking and Locking Matters
- 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 whichever session is actually holding the lock
- Blocking chains can run several sessions deep, session C blocked by B blocked by A, so a summary view alone isn’t always enough to find the true root
- A deadlock is SQL Server resolving a blocking situation it can’t otherwise escape, by killing one side automatically, the same underlying access pattern usually caused both, and will keep causing them until it’s actually fixed
Start Here
The Scripts, Grouped by Question
Is there blocking right now, and who’s causing it?
- Blocking Sessions, the fast first-response check, a head-blocker summary plus a full blocking-chain detail view in one pass
Is this a single blocker, or a multi-level chain?
- Blocking Chains, traces every active blocking chain from head blocker down to the last waiting session in one recursive query, so a chain three levels deep is visible immediately instead of pieced together session by session
Did SQL Server already resolve this by killing a session?
- Deadlock Summary, reads recent deadlock events straight from the
system_healthExtended Events session every instance already runs, no extra tracing needed, and returns the full deadlock graph for each one
What’s actually holding a transaction open?
- Open Transactions, lists every currently open transaction with its age, owning session, and last SQL run, the direct next step whenever a head blocker turns out to be an idle session sitting on a forgotten transaction
How They Fit Together
The order that actually makes sense during a live incident:
Best Practices Across the Series
- Always identify the head blocker before killing anything, killing a blocked session does nothing, the fix is upstream
- Confirm what a long-running transaction is actually doing (via
sys.dm_tran_locksand its current statement) before deciding to kill it, a legitimate long operation will roll back if killed, which can take as long as the operation itself - Treat a recurring deadlock on the same two statements as a design problem, access order or missing indexes, not something to just keep retrying past
- Access tables in a consistent order across all code paths where possible, it’s the single most reliable way to prevent both blocking pileups and deadlocks by design
Frequently Asked Questions
What is the difference between blocking and a deadlock?
Blocking is one session waiting for another to release something, and it resolves once the first finishes. A deadlock is a cycle that cannot resolve, so SQL Server picks a victim and kills it. Blocking is a delay; a deadlock is an error someone has already seen.
Everything looks blocked. How do I find the actual cause?
Follow the chain to its head. A long blocking list is usually one session at the root with dozens queued behind it, so the only session worth investigating is the one blocking but not blocked.
Is an open transaction always a bug?
Not always, but a long-running one nearly is. It holds locks and stops the transaction log truncating, so a forgotten open transaction shows up as both a blocking problem and a disk space problem, which is why the two get investigated together.
See Also
This pillar is part of DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
Blocking and locking incidents all come down to the same question asked at different depths: who’s actually holding the lock, how deep does the chain go, and did SQL Server already have to intervene. These four scripts answer that at every depth, from a ten-second first check to a full recursive chain trace to reading back a deadlock graph after the fact.
Start with Blocking Sessions any time something looks hung, it’s the fastest way to confirm or rule out blocking before going any further.
Leave a Reply