Blocking Queries
Overview
Blocking is normal. A blocking chain that is still there when you look at it twice is not.
The Blocking Queries report answers the question you actually have during an incident: which sessions are stuck, which one is at the top of the pile, and what is each of them running. It reads the instance once, walks the blocking relationships recursively, and prints every session involved as a row.
It is the flat view of blocking. Blocking Tree is the picture. This page is the list you can sort, copy out, and act on, and it is the one with the kill actions on it.
Where to find it
An instance level report. Right-click the server → Instance Level Reports → Blocking Queries.
It is one of the recommended entries, so it also appears in the report launcher on the Server Overview.
The page title reads Blocking Queries for <server name>.
Requirements
- SQL Server 2008 or newer. On SQL Server 2005 the page returns a single row reading This report requires SQL 2008 or newer rather than an error.
VIEW SERVER STATEon the instance, for the statement text.ALTER ANY CONNECTION, only if you intend to use the kill actions. Everything else on the page is read only.
Reading the grid
| Column | What it is |
|---|---|
| SPID | The session this row is about. |
| Blocked | The session that is blocking this one, or 0 when this row is the head blocker. |
| Blocking Chain | The path from the head blocker down to this session, written as 58->72->91. The head blocker itself reads Blocker on a red background. |
| Level | How deep this session sits. 0 is the head blocker, 1 is blocked directly by it, 2 is blocked by something at level 1, and so on. |
| Blocking SPID | The head blocker for this whole chain, not the immediate one. That is in Blocked. |
| Num Blocked | How many sessions are in this chain, counted per head blocker, so every row in one chain carries the same number. |
| Query Being Blocked | The statement this session is running, indented by level so the shape of the chain survives in a text column. |
The two SPID columns are not the same question, and mixing them up is the usual mistake. Blocked is who is standing directly in front of you. Blocking SPID is who is at the front of the whole queue. On a two level chain they are identical, which is exactly why it is worth checking the Level column before assuming they always are.
Rows are ordered by chain, then by depth. Every session belonging to one head blocker is together, top down, so reading the grid from the top reads each chain from its cause to its symptoms.
An empty report is the good answer
Nothing blocking means no rows. That is the normal state of a healthy instance, and it is not a failure of the report. Blocking that matters is usually blocking that persists, so a page that comes back empty twice in a row is a real answer.
Double-click a row
Double-clicking opens the Connection Advisor for that SPID: who is connected, from what application and host, what they are running, and what they are waiting on. It is the fastest way to find out whether the head blocker is a person, a job or an application before deciding what to do about it.
Right-click actions
| Item | What it does |
|---|---|
| Copy all session IDs | Puts every SPID on the page on the clipboard, one per line. Nothing is executed. |
| Kill selected sessions | KILL for the rows you have selected, after a confirmation. |
| Kill all blocking and blocked session IDs | KILL for every SPID on the page, after a confirmation. |
Read the second and third rows of that table twice. KILL rolls a transaction back, and the rollback can take longer than letting the statement finish. Killing the whole page kills the victims as well as the cause, which is almost never what you want. The narrow action is Kill selected sessions against the Level 0 row, and even that is a decision rather than a reflex.
The page refreshes itself after a kill so you can see what the chain looks like now.
How to read the report
- Find Level 0. That is the row causing everything below it. Its Blocking Chain cell reads Blocker in red.
- Read Num Blocked. One session holding up thirty is a different problem from one holding up one.
- Read the head blocker’s query. If the cell is empty or reads
[Plan Not Available], the session is idle inside an open transaction, which is a different problem again and belongs on Open Transactions. - Refresh. Blocking that has cleared by the second read did not need you.
- Decide before killing. Double-click first, find out who it is.
Common patterns
A head blocker with no query text. An application opened a transaction and went away, usually because of a missing COMMIT on an error path or a user sitting on an open edit screen. Open Transactions names the transaction and how long it has been there.
One head blocker, one victim, gone on refresh. Ordinary contention. Nothing to do.
A deep chain, three or four levels. Almost always one slow statement at the top and a queue behind it. Fix the top; the rest disperses.
The same head blocker query text every time you look. A recurring statement rather than an unlucky one. CPU by Query and Blocking by Hour by Day turn that into a pattern rather than an anecdote.
Blocking with PAGELATCH waits underneath it. Not lock blocking at all. TempDB Metadata Contention and Index Contention are the pages for allocation and last page insert hotspots.
Where the data comes from
master..sysprocessesfor the session list and theblockedcolumn.sys.dm_exec_sql_textfor the statement behind eachsql_handle.- A recursive common table expression joins the two into chains and counts the sessions per head blocker.
Nothing is stored by this page. It is a live read, and the numbers are true for the instant the query ran.
The anchor of the recursion is deliberately DISTINCT. Without it a session blocking three others enters the recursion three times, every level below multiplies by that fan-out, and Num Blocked reports twelve for a chain of four. The client side tree in Blocking Tree had the same fault and was fixed the same way, which is why the two pages now agree with each other.
Related reports
| Report | Why you would go there |
|---|---|
| Blocking Tree | The same chains drawn as a picture, sized by how many sessions are trapped underneath each band. |
| Blocking by Hour by Day | Whether this is a moment or a pattern, from recorded history. |
| Open Transactions | The head blocker that is idle rather than busy. |
| Sessions | Everything connected, not just the sessions in a chain. |
| What is Active | The wider view of what the instance is doing right now. |
| Deadlock History | Blocking that resolved itself by the engine picking a victim. |
Frequently asked questions
Why is the report empty? Because nothing is blocking. That is the answer, not a failure.
Why does the same SPID appear more than once? It should not. Each session appears once per chain it belongs to, and a session belongs to one chain. If you are seeing a repeat, you are most likely reading Blocked as though it were Blocking SPID.
Why does Num Blocked show the same value on every row of a chain? Because it counts the chain, not the row. It is a property of the head blocker, repeated so you can see it without scrolling to the top.
Why is the query text missing for some sessions? Because there is no cached statement for that handle, which usually means the session is sleeping inside an open transaction rather than executing anything.
Should I use Kill all blocking and blocked session IDs? Rarely. It kills the victims along with the cause, and every one of those rollbacks costs something. Kill the head blocker and let the queue drain.
How is this different from Blocking Tree? Same data, different shape. This is a sortable, copyable grid with the kill actions. Blocking Tree is the picture that makes a deep chain obvious at a glance.
Why does it read sysprocesses rather than the newer DMVs? Because sysprocesses carries the session list and the blocked column in one place, back to every version this report supports, and the statement text is joined on from sys.dm_exec_sql_text separately.