Blocking by Hour by Day
Overview
The Blocking by Hour by Day report answers when contention happens.
Blocking is almost never constant. It clusters – around a nightly job, a month-end run, the first hour of the working day, the moment a report and a batch load collide. A count of blocked sessions over a month tells you there is a problem; this page tells you when to go looking, which is the half that leads to a fix.
Hours run down the side, days across the top, and each cell is one hour. Darker means more blocking was recorded in that hour.
Click any cell to see the blocking that was happening then.
Where to find it
An instance-level historic report. Right-click the server in the tree and open it from the historic reports. The page title reads Blocking by Hour by Day (historic) for <server name>.
Reading the heat map
Each cell is one hour of one day, and darker means more blocking was recorded.
The ramp is monotone in perceptual lightness, so the magnitude reads from lightness alone – in greyscale, on a projector, or under any form of colour vision deficiency. Cell text picks black or white per fill so it always clears a 4.5:1 contrast ratio.
Dark mode flips the ramp’s anchor so hot still advances off the surface. Colour-blind mode collapses it to a single blue hue at the same lightness steps, which is safe under every form of colour vision deficiency by construction.
No data is not zero
A slot where nothing was recorded is drawn differently from an hour that recorded no blocking. For this report the distinction matters more than most: an unmonitored hour looks exactly as peaceful as a genuinely clean one, and on a blocking report that is the difference between “we are fine” and “we were not watching”.
Clicking through
Click any cell to open Blocking History for that hour. The page title reads Blocking History for <instance> at <time>.
A dark cell is a question. The hour view is the answer, and it answers it as incidents rather than as rows.
Why that matters
The single most useful fact about an hour of blocking is whether it was one long storm or a dozen short ones, and until this rebuild that fact was destroyed before it reached the screen: one row survived per distinct blocking query text, so a statement that blocked eleven times in the hour and one that blocked once came out identical.
The two shapes
Storm, the default. One lane per database, time running left to right across the hour. Captures next to each other in the same database merge into one band, so sixteen minutes of continuous blocking draws as a sixteen minute bar rather than thirty two identical rows.
- Height is how many sessions were stuck.
- Fill is how long the worst of them waited.
Offenders. The same captures grouped by the query at the head of the chain and ranked by the blocked time it caused, split by the database its victims were in.
Storm answers “when”. Offenders answers “what do I fix”, and Offenders is the one that goes into a ticket.
Colour is a single hue at three steps and always carries how long the victims waited, never identity. Anything else would mean learning a legend before the picture said anything.
The toolbar
| Button | What it does |
|---|---|
Show More |
Adds a week to the range. |
Show Less |
Removes a week. |
One to six weeks, in one-week steps. The buttons hide themselves at the ends of the range.
The range is shared with every other by-hour-by-day report – CPU, Long Running, TempDB, Deadlocks, Disk Latency and I/O all move with it. That is what keeps two of these pages comparable when you read them side by side, which for blocking is the point: contention that lines up with a CPU peak is a different problem from contention that does not.
Requirements
This is a historic report reading blockingOverTime from the DBHealthHistory repository, populated by the collection service. Without collection configured there is nothing to draw.
It works when the monitored server is unavailable, because it reads history rather than the live instance.
How to read the report
- Look for clusters, not cells. One blocked session is normal. A recurring block of dark cells is a pattern with a cause.
- Check whether the cluster lines up with a job. Overlay it mentally against the nightly maintenance window and the business day.
- Compare against CPU by Hour by Day. Blocking with high CPU is usually contention under load; blocking with low CPU is usually a long transaction holding locks while doing nothing.
- Click the worst cell and read the chain.
- Widen to six weeks before deciding a pattern is monthly rather than coincidental.
Common patterns
A dark band in the first working hour. Everybody arrives at once against a cold cache. Often self-resolving, sometimes an indexing problem worth fixing.
Blocking during the maintenance window. Index rebuilds or CHECKDB against live work. Usually means the window has grown into the working day, or a job that used to finish no longer does.
A dark column once a month. Month-end processing. Predictable, which makes it schedulable.
Blocking with no matching CPU. The classic idle-transaction case – a session holding locks while doing nothing. Blocking Tree shows this live.
Blocking at exactly the same minute of every hour. Something on an hourly schedule. Look at Agent jobs first.
An empty row across every day. The collector was not running. Not a quiet hour.
Related reports
| Report | Why you would go there |
|---|---|
| Blocking Tree | What is blocking right now, and who to kill. |
| CPU by Hour by Day | Whether contention and load peak together. |
| Deadlocks by Hour | Contention that resolved itself the hard way. |
| Long Running by Hour by Day | Whether the blocked hours are also the slow ones. |
| Deadlock History | What keeps colliding with what. |
Frequently asked questions
How is this different from Blocking Tree? Blocking Tree is live – who is stuck behind whom right now. This is the history of when blocking happened, which is what tells you where to look.
What does a blank cell mean? Nothing was recorded for that hour. It is drawn differently from an hour that recorded no blocking, because an unmonitored hour is not a clean one.
Why does the range change on the other heat maps too? It is one shared setting, so all the by-hour-by-day reports stay comparable.
Can I see the actual blocked sessions? Yes – click the cell.
Why is there no grid under the chart? The heat map is the whole report. The detail lives one click down, in the hour view.