CPU by Hour by Day
Overview
The CPU by Hour by Day report draws every recorded CPU sample as a heat map – hours down the side, days across the top – so the shape of a week is visible without reading a single number.
A line chart of CPU over a month is a wall of spikes. The same data laid out by hour and day turns into a pattern: the nightly maintenance window becomes a dark band at 02:00, the trading day becomes a dark block between 09:00 and 17:00, and the Tuesday that was not like the other Tuesdays becomes a single dark column you can point at.
Click any cell to drill into that hour and see what was actually running.
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 CPU by Hour by Day (historic) for <server name>.
Reading the heat map
Each cell is one hour of one day. Darker means busier.
The colour ramp is monotone in perceptual lightness – every step is meaningfully darker than the one before it – which means the magnitude is readable from lightness alone: in greyscale, on a projector, or with any form of colour vision deficiency.
This is the part that changed. The previous ramp got lighter between 0% and 39% before it started darkening, so the low end of the scale read as noise rather than as an ordered scale.
Text on each cell picks black or white automatically per fill, so the number on a cell always clears a 4.5:1 contrast ratio against its background. Nothing is hard-coded, so no cell ends up with dark text on a dark fill at the hot end of the scale.
In dark mode the ramp inverts its anchor – deep ember through to bright ember – so “hot” still advances off the surface instead of sinking into it.
In colour-blind mode the ramp becomes a single blue hue with the same monotone lightness. A single hue is safe under every form of colour vision deficiency by construction, which the previous blue-and-orange ramp was not – it still relied on hue to carry part of the scale.
No data is not zero
A slot where nothing was ever recorded is drawn differently from a slot that recorded zero CPU. They are genuinely different facts – one means the collector was not running, the other means the server was idle – and a heat map that painted both as the coolest colour would be telling you the server was quiet when it was actually unmonitored.
An empty band across every day at the same hour usually means the collection service was stopped, not that the server rests at that time.
Clicking through
Click any cell to open CPU Load for Hour, which draws every sample the collector took during that hour. The page title reads CPU Load (historic) for <instance> on <date and hour>.
This is the intended way to use the page: find the shape, click the anomaly.
Whose CPU it was
The drilldown stacks two bands and leaves the rest as headroom:
- The bottom band is SQL Server.
- The band above it is everything else on the machine.
- The space above that is headroom.
That split is the reason to open it. An hour where SQL is at 40% on a box sitting at 95% is a completely different finding from an hour where SQL is at 95%, and one of those is not a SQL Server problem at all. Both numbers were always collected; until this rebuild only 100 - IdleCpu was ever drawn, and the split lived in a tooltip.
Whether the reading is unusual
The Compare shape puts the same clock hour from the previous days behind today’s line. A 90% spike at 1 PM is either an incident or a batch window, and nothing else in the product answers which.
Where the collector was not looking
CPU is only recorded while the service is running. A stretch with no samples is drawn as hatching rather than as a line between the two readings either side of it, because a straight line across a gap is an invention.
What the hour was spent on
The Queries shape ranks the queries the active query collector caught running during the hour, worst CPU first, and the grid underneath it carries one row per query rather than one row per capture. Right click a query for its text or its execution plan. The plan is the one that was stored while the query was running, so it is still there for an hour that ended weeks ago, long after the plan cache has forgotten it.
CPU on that view is what each run had spent by the time the collector sampled it, counted once per session rather than once per capture. A statement caught six times contributes one reading, not six.
The button only appears where there is something behind it: the active query collector has to be installed, and the repository being read has to be the instance on screen. On a central repository the captures belong to the repository’s own server, and the page says so rather than showing you another server’s queries.
The toolbar
| Button | What it does |
|---|---|
Show More |
Adds a week to the range. |
Show Less |
Removes a week. |
The range runs from one week to six weeks, in one-week steps, and the buttons hide themselves at each end rather than doing nothing when clicked.
The range is shared across every by-hour-by-day report. Setting CPU to four weeks sets Blocking, Long Running, TempDB, Deadlocks, Disk Latency and I/O to four weeks as well. That is deliberate – comparing two of these pages is only meaningful when they cover the same window – but it does mean changing it here changes it everywhere.
Whole weeks keep the day-of-week columns aligned, which is what makes “every Monday” visible as a pattern.
Requirements
This is a historic report. It reads the CPU table in the DBHealthHistory repository, populated by the Database Health Monitor collection service. Without collection configured for the instance, there is nothing to draw.
Because it reads history rather than the live instance, it works when the monitored server is unavailable.
How to read the report
- Look for bands before cells. A horizontal band is a time of day; a vertical band is a day. Both are patterns. A single dark cell is an event.
- Find the shape of a normal day first, so you know what an abnormal one looks like.
- Compare the same hour across days. The columns are aligned by day of week for exactly this.
- Click the darkest cell that surprises you. The hour view names the queries.
- Widen the range if a pattern looks like a coincidence. Two occurrences is not a pattern; six weeks will tell you.
- Treat empty slots as a collection question, not as idle time.
Common patterns
A dark band at the same hour every night. Maintenance – backups, index rebuilds, CHECKDB. Expected, but worth confirming it still finishes before the working day.
A dark block during business hours, cool outside them. Normal for a transactional system. The useful question is whether the block is getting darker month over month.
One dark column. A single bad day. Click into it – this is what the drill-through exists for.
Dark cells creeping earlier and later either side of the working day. Load is growing at the edges. Often the earliest visible sign of a capacity problem.
A checkerboard with no structure. Either genuinely variable load, or the range is too short to show a pattern. Try six weeks.
An empty row across every day. The collector was not running at that hour. Not idle time.
Related reports
| Report | Why you would go there |
|---|---|
| CPU History for Hour | What the click opens – the queries behind a cell. |
| CPU by Database | Which database is spending the CPU. |
| CPU by Query | Which statements. |
| Long Running by Hour by Day | Whether the busy hours are also the slow ones. |
| Blocking by Hour by Day | Whether CPU and contention peak together. |
Frequently asked questions
Why a heat map instead of a line chart? Because CPU has a weekly rhythm, and a line chart hides it. Hours down and days across put recurring patterns on top of each other where they can be compared.
What does a blank cell mean? Nothing was recorded for that hour – the collector was not running. It is drawn differently from a recorded zero on purpose.
Why did the colours change? The old ramp got lighter before it got darker, so the bottom of the scale was unreadable. The current ramp is monotone in lightness end to end, which also makes it work in greyscale and under colour vision deficiency.
Why does changing the number of weeks here change it on the other heat maps? The range is one shared setting, so the by-hour-by-day reports always cover the same window and stay comparable.
Why only six weeks? Past that, the columns are too narrow to read or click. For longer-term trends, the other historic reports carry the same data at a coarser grain.
Can I see what caused a dark cell? Yes – click it. That is what the drill-through is for.