TempDB Use by Hour by Day
Overview
The TempDB Use by Hour by Day report shows when tempdb fills up.
TempDB is shared by every database on the instance, and its size is decided by its worst moment, not its average. One hour a week that spills a large sort to disk sets the size tempdb has to be for the whole week. A heat map is the right shape for that question, because it makes the worst moment a visible cell rather than a number buried in an average.
Hours run down, days across, one cell per hour, darker for heavier use.
Where to find it
An instance-level historic report – tempdb is instance-wide, so there is no per-database version. Right-click the server in the tree and open it from the historic reports. The page title reads TempDB use by Hour by Day (historic) for <server name>.
Reading the heat map
Each cell is one hour of one day; darker means heavier tempdb use.
The ramp is monotone in perceptual lightness, so 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 and always clears 4.5:1 contrast.
Dark mode inverts the ramp’s anchor. Colour-blind mode uses a single blue hue across the same lightness steps.
The darkest cell is the one that matters
On most heat maps the pattern is the finding. On this one the peak is the finding. TempDB has to be sized for its maximum, so the single darkest cell in the range is the number that governs your configuration – the rest of the map is context for it.
No data is not zero
An hour where nothing was recorded is drawn differently from an hour of no tempdb use. A collection gap that read as an idle hour would understate exactly the peak this report exists to find.
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 at the ends.
Widen this one. For a report whose purpose is finding the peak, a longer window is strictly better – a monthly job that dwarfs everything else is invisible in a seven-day view. Six weeks is the widest available.
The range is shared with the other by-hour-by-day reports.
Requirements
A historic report reading tempDBUsage 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 collection is periodic, a spike shorter than the sampling interval can be missed entirely. This report finds sustained pressure reliably and brief spikes only sometimes – so a pale map is good evidence but not proof that nothing ever spilled.
How to read the report
- Find the darkest cell in the widest range available. That is the number tempdb has to be sized for.
- Ask what runs then. A single dominant peak almost always has one job behind it.
- Look for a recurring band. Regular heavy use is usually a nightly or weekly process, and is often reducible.
- Compare against Long Running by Hour by Day. Heavy tempdb use and slow queries at the same hour usually means spills – sorts or hashes that did not fit in memory.
- Check that the peak is not growing week over week across the range.
Common patterns
One dark cell far darker than everything else. A single job, and the reason tempdb is the size it is. Worth identifying – it is often one query that could be rewritten or given an index.
A dark band during the maintenance window. Index rebuilds use tempdb heavily when SORT_IN_TEMPDB is on. Expected, and worth sizing for.
Heavy tempdb use during business hours. Sorts, hashes and spools from user queries. Read with Long Running by Hour by Day – if they line up, memory pressure is causing spills.
Steadily darkening over the weeks. Growing data volumes pushing more operations out of memory. A capacity signal.
Version-store growth on an instance using snapshot isolation. A long-running transaction can hold the version store open far past its own runtime, which shows as tempdb use continuing after the work appears finished.
Pale everywhere. Good, but not proof – a spike shorter than the collection interval can be missed.
Related reports
| Report | Why you would go there |
|---|---|
| Files for Database | The actual tempdb files, their sizes and growth settings. |
| Long Running by Hour by Day | Whether tempdb pressure and slow queries coincide. |
| Disk Space | Whether the volume holding tempdb has room for the peak. |
| Waits | Whether tempdb allocation contention shows in the instance waits. |
| CPU by Hour by Day | What else the instance was doing at the peak. |
Frequently asked questions
Why is there no per-database version? TempDB is shared by the whole instance. Attributing its use to one database is not something the history records.
Should I size tempdb for the peak or the average? The peak. TempDB that cannot handle its worst hour fails in that hour, and autogrowth during a spill is itself slow.
Why does the map look empty when I know something spilled? Collection is periodic. A spike shorter than the sampling interval can pass between samples.
What does a blank cell mean? Nothing was recorded – a collection gap, not an idle hour.
Why widen the range on this report in particular? Because the finding is the maximum, and a monthly job is invisible in a weekly window.