Everything You Need to Know about Head Blockers

Sometimes SQL Server becomes very busy, to the point that nothing moves and everything is stuck. This is usually when people starting to shout and say nasty words.

In many cases, it happens because there is a long blocking chain. It means that some task is waiting for something (disk, memory, external process, etc.), let's call it Resource A, while holding a lock on some other resource, let's call it Resource B. Other tasks that need access to Resource B are blocked. Maybe one of these tasks is already holding a lock on Resource C, so another task might be blocked behind that resource, and so on.

If the first task has to wait for a long time, then the blocking chain might become very long, and everything becomes stuck. When this happens, you want to identify that first task as quickly as possible, understand what it's doing and what it's waiting for, and release the blocking chain. That first task is called the Head Blocker.

You can release the blocking chain by either resolving the wait on which the head blocker is waiting and let it finish and released its locks. You can also simply kill the head blocker, although this can be risky, because it might need to roll back a lot of work, and during that time the blocking chain will not be released.

Recently I encountered such a use case, and I needed a good script to identify the head blocker. There are plenty of scripts out there to identify the head blocker, but I couldn't find a single one that gave me all the information I needed.

Some scripts output the entire blocking chain, which can include thousands of rows. In most cases, this data is useless. I'm only interested in the head blocker and some aggregated data about the size of the blocking chain.

Some scripts give you the head blocker but fail to include all the information about it, such as the query text and the wait type.

Some scripts identify only one head blocker, but sometimes there are multiple blocking chains at the same time. I would like to see all of them, and by comparing the aggregated data (e.g. the blocking chain length), I can take a decision on which blocking chain and head blocker I should concentrate my efforts.

You've probably guessed by now where this is going. I wrote my own version of Head Blockers script, and I put in in our Madeira Toolbox on GitHub. This script identifies all the head blockers that exist now in your instance along with a lot of useful troubleshooting information about the task each one of them is currently running.

The script also produces some aggregated data about each blocking chain: the chain length, the number of blocked tasks, and the number of blocked sessions.

You can find the script here.