Part of the DBA-Tools Project.
“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
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:
- Check for blocking first with Blocking Sessions, it answers “is there a problem and who’s causing it” in seconds and either rules blocking in or out immediately
- If the picture looks more than one level deep, follow up with Blocking Chains to see the full chain rather than just the head blocker and the session complaining
- If the head blocker is idle (a
sleepingsession still holding a transaction), pivot to Open Transactions to see exactly what it’s holding and how long it’s been open - After the fact, or during a recurring pattern, check Deadlock Summary, since deadlocks aren’t always reported by name, the same underlying access pattern that causes blocking often causes deadlocks too
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
See Also
This pillar is one of ten in 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