Blocking Tree

Overview

The Blocking Tree report shows who is stuck behind whom on this instance right now.

It is drawn as an icicle: each band is as tall as the number of sessions trapped underneath it. That single rule makes the head blocker obvious – the session causing the most damage is the widest band at the top, and you can see the size of the problem before reading a single session id.

The report has two shapes sharing one query. Tree answers who do I kill. Contention regroups the same waiters by the lock they are queued on and answers what is everyone fighting over – a question the tree structurally cannot answer, because one hot table fought over by three unrelated chains draws as three separate roots.

What this replaces

This report replaces the database-level Blocking Queries page, retired on 2026-08-05. That page had four problems, and all four were structural rather than cosmetic:

  • It drew a tree as a pie, and charted only the depth-zero rows – so the hierarchy the report was named for never reached the chart at all.
  • It encoded depth by prefixing the query text with dots, one group per level. The indentation lived inside a sortable column, so sorting the grid destroyed the hierarchy silently.
  • Its blocked count was over-counted. The anchor emitted one row per victim rather than per blocker, so a blocker of three entered the recursion three times and every level multiplied by that fan-out. A one-to-three tree reported twelve – and that number was the pie slice size.
  • It read master..sysprocesses with no database filter while titling itself “Blocking queries for <database>”. It was instance-wide data wearing a database label.

Promoting the page to the instance is what fixes the last one honestly. Blocking chains cross databases, and a chain rooted in another database is the thing you most need to see, not the thing to filter out.

The Blocking Tree report
The whole report. The icicle above, every session in the chain below.
The blocking icicle
Band height is the number of sessions trapped underneath. The head blocker is the widest band at the top.

Where to find it

An instance-level report. Right-click the server in the tree and open Instance Reports → Blocking Tree.

There is also an instance XML report called Blocking Queries – a flat grid over the same idea. It is deliberately still there and is a different page. Its copy of the query got the same counting fix, so the two do not disagree about how many sessions are blocked.

Reading the icicle

Each session is a band. Band height is the number of sessions trapped underneath it, so the visual weight of a band is exactly its cost.

Sessions come in three roles, named on the legend as:

Legend What it means
root blocker (red) Head of a chain. Not waiting on anything – it is the cause.
blocked and blocking (dark blue) Blocked, and blocking somebody else in turn.
blocked (light blue) Blocked, and blocking nobody.

A fourth shade marks a longer wait, so the session that has been stuck longest stands out from its siblings.

The root blocker is the session to act on. Everything below it is a consequence. Killing a middle link frees only what is under that link and leaves the actual cause running.

Inside a band, a dashed slot is the blocker’s own place in the count – it is one of the sessions in the chain, not just the container for them.

Above the chart, a summary line gives the shape of the incident at a glance: how many root blockers, how many sessions blocked, the deepest chain, and the longest wait.

Wide chains roll their tail siblings into a single band rather than drawing hairlines, and a blocking cycle is drawn as a marked node rather than failing the page.

Clicking a band finds that session in the grid. Double-clicking opens the Connection Advisor for it.

The Contention shape

The same waiters, regrouped by the resource they are queued on. Use it when the tree shows several unrelated roots – that usually means one hot object rather than several independent problems, and the tree cannot show you that.

Reading the grid

The blocking grid
Every session in every chain. Depth is its own column, so sorting cannot destroy it.
Column What it is
SPID The session.
Blocked By The session blocking it.
Depth How deep in the chain. A real column, so sorting the grid no longer destroys the hierarchy.
Victims How many sessions are trapped underneath this one.
Wait How long it has been waiting.
Wait Type What kind of wait.
Resource The lock resource it is queued on.
Status Session status.
Open Tran Open transaction count. A blocker with open transactions and no active request is the classic idle-in-transaction case.
Login · Host · Program Who and what.
Database Which database – worth reading, because chains cross databases.
Query The statement text.

The toolbar

Group Buttons
Shape Tree · Contention
Refresh · Pause

The page auto-refreshes on its own timer with a visible countdown, defaulting to 15 seconds. Pause stops it while you read a chain – worth using, because a blocking picture that redraws underneath you is hard to act on.

The refresh runs on a private timer rather than the main navigation timer, so each firing does not push an entry into the go-back history or the popular-reports tracking.

Right-click actions

Item What it does
Kill Session <spid> Ends that session. Asks first.
Copy Query Text to Clipboard The statement that session is running.
Copy Blocking Chain as Text to Clipboard The whole chain, with a KILL line per member annotated by role.

Copy Blocking Chain is the one to reach for during an incident. It gives you the entire picture as text – including which session is the root – in a form you can paste into a ticket or a chat window.

Requirements

This is a live report. It reads the instance as it is now, and there is nothing to see unless something is blocking at the moment you look.

An empty page is a healthy answer.

Sessions blocked by SQL Server itself

Some waits are not on another user session. SQL Server reports these as negative blocker ids:

Id Meaning
-2 An orphaned distributed transaction.
-3 A deferred recovery transaction.
-4 A latch, where the owning session could not be determined.

The old report dropped the whole chain underneath these. They are shown here, because a chain rooted in an orphaned DTC transaction is a real incident with a real fix, and it used to be invisible.

How the tree is built

The recursion happens in the application, not in T-SQL. That choice does three things:

  • It retires the over-count for good. The counts roll up once over a deduplicated tree, so inflating them is not a bug that can come back.
  • It removes the MAXRECURSION 100 ceiling, which used to turn a deep chain into error 530 and no report at all.
  • It lets a blocking cycle be drawn as a marked node instead of failing the whole query.

How to read the report

  1. Find the widest band at the top. That is the head blocker and the whole problem.
  2. Read its Open Tran and Status. An idle session holding an open transaction is the most common cause and the easiest to confirm.
  3. Look at its query text before killing anything. What it was doing decides whether killing it is safe.
  4. Check how long the leaves have been waiting. That is what your users are feeling.
  5. Switch to Contention if there are several roots. Unrelated chains on one resource is a different problem than several unrelated problems.
  6. Copy the chain before you act. After the kill, the evidence is gone.

Common patterns

One root, many leaves, root is idle with an open transaction. An application began a transaction and never committed – usually an error path that skipped its rollback, or a client that went away. Killing the root clears everything.

A deep chain, each level blocking one more. Serialised access to the same rows. The root is still the answer, but the depth suggests a design worth looking at afterwards.

Several roots, all on the same resource in Contention view. One hot object. Killing sessions will not fix it – this is an indexing or access-pattern problem.

A root in a different database than the one you were investigating. Exactly the case the old database-scoped page could not show. This is why the report is instance-level now.

A chain under a negative blocker id. See above. Not a user session – an orphaned DTC transaction, deferred recovery, or a latch.

Report Why you would go there
Sessions The full picture on any session in the chain.
Deadlocks by Database Blocking that resolved itself the hard way.
Waits Whether lock waits dominate the instance generally.
Deadlock History Whether this contention has been turning into deadlocks.

Frequently asked questions

Which session should I kill? The root – the head of the chain. Everything under it is a consequence, and killing a link leaves the cause running.

Why is the page empty? Nothing is blocking right now. That is the healthy answer.

Why does the report show other databases? Because blocking chains cross database boundaries. A chain rooted elsewhere is the thing you most need to see.

Why did the blocked count change from the old report? The old count was inflated by a fan-out in the recursion – a one-to-three tree reported twelve. The number here is correct.

Why is a session shown with a negative blocker? It is blocked by SQL Server itself rather than another session. See the table above.

Can I stop it refreshing while I read? Yes – Pause. The countdown shows when the next refresh would land.

What does the marked node mean? A blocking cycle. It is drawn rather than allowed to break the page.