TempDB Allocation
Overview
TempDB is full is not a diagnosis. TempDB fills for three completely different reasons, each with a different owner and a different fix, and the only useful first question is which of the three.
TempDB Allocation answers it. Recorded tempdb usage, stacked into three bands over time.
| Band | What it is | What causes it |
|---|---|---|
| User Objects | Temporary tables, table variables, and anything you created. | Application and procedure code. |
| Internal Objects | Sorts, hashes, spools, cursors, and work tables the engine builds for itself. | Query plans. Almost always spills from an under-sized memory grant. |
| Version Store | Row versions kept for snapshot isolation, read committed snapshot, triggers and online index operations. | Long running transactions, mostly readers. |
The band that is growing tells you which team to talk to. User objects is a code conversation. Internal objects is a query tuning conversation. Version store is a long-transaction conversation, and usually the one nobody expects.
Where to find it
A database level report on tempdb. Expand tempdb → Historic → TempDB Allocation.
The three tempdb historic nodes only appear under tempdb, because they are the only place they mean anything.
The page title reads TempDB Allocation for <server name>.
Requirements
- A historic database configured for this instance, holding the recorded allocation.
- The collection job running. The chart draws what was recorded, and a period with no collection is drawn as zero rather than left blank.
The controls
| Control | What it does |
|---|---|
| Time Frame | 24 Hours, 48 Hours, 7 Days or 30 Days. Opens on 24 Hours. |
| Free Space: Show / Hide | Adds or removes a fourth band for the unused space in the tempdb files. Hidden by default. |
The bucket size changes with the time frame so the chart stays readable: hourly bars for 24 and 48 hours, four hourly bars for 7 days, and daily bars for 30 days. Each bar is the average across its bucket.
That averaging matters when you are chasing an incident. A version store that ballooned for twenty minutes inside a four hour bucket is averaged down to a sixth of its real height. Narrow the time frame before reading a spike, and use TempDB High Usage for the individual sessions.
The Free Space band
Off by default, because when it is on it is usually the tallest band by a wide margin and it flattens the three you came to read.
Turn it on when the question is how close did we come to filling tempdb rather than what is using tempdb. With it on the chart total is the size of the tempdb files, and the free band is the headroom.
Reading the chart
Every bucket in the window gets a bar, including buckets with no recorded data, which is why a gap in collection shows as a run of zero height bars rather than as a break in the axis.
A bar of zero height is ambiguous, and it is worth saying so plainly: it means either nothing was using tempdb then, or nothing was collected then. Check whether every band is zero for the same run of buckets. A genuine quiet period usually still has a little internal object activity.
How to read the report
- Find the tallest band and follow it over time. That is the report.
- If it is Internal Objects, the plans are spilling. Memory Grants and Spills names the queries, and Parallelism Calibration often explains why the grants are wrong.
- If it is Version Store, find the long running transaction. A version store that never comes back down means something has been open for hours, and Open Transactions and Active Queries will show it. The version store is only cleaned up behind the oldest active transaction, so one forgotten session holds all of it.
- If it is User Objects, it is code. Temporary tables that are never dropped inside long sessions, or a procedure creating a very large one. TempDB High Usage attributes it to a session and a statement.
- Check the shape against the clock. A nightly peak is a job. A peak every weekday morning is people.
Common patterns
Version store climbing all day and dropping overnight. A long running reporting transaction under snapshot isolation. The drop is when it finally ends. The fix is the transaction, not tempdb.
Internal objects spiking on a schedule. A report or an overnight job whose plan spills. The spill is the symptom of a memory grant that was too small, which is usually a statistics or a cardinality estimate problem.
User objects as a flat plateau. Temporary objects held for the life of a session, typically by an application that keeps connections open. They are only released when the session ends.
All three bands low and free space nearly all of the chart. TempDB is over-provisioned. Not a problem, and worth knowing before somebody adds more files.
Everything at zero for a run of buckets. The collection job was not running.
A sudden step in every band at once. The instance restarted. TempDB is recreated at startup, so everything begins again from nothing.
Where the data comes from
[DBHealthHistory].[dbo].[tempDBAllocation], written by the historic monitoring job from the tempdb space usage views.
Buckets are built from a full list of time slots joined outward to the recorded data, which is why every bucket in the window is present whether or not anything was collected for it.
This page writes nothing.
Related reports
| Report | Why you would go there |
|---|---|
| TempDB High Usage | The individual sessions and statements behind a peak, with the query text. |
| TempDB Use By Hour | The same history as a heat map, for finding the shape of the week. |
| TempDB Consumers | Who is using tempdb right now, and why. |
| TempDB Metadata Contention | The other tempdb problem: allocation page latching rather than space. |
| Memory Grants and Spills | The queries whose grants are too small, when internal objects is the band that grows. |
| Open Transactions | The long running transaction holding the version store. |
Frequently asked questions
Why is TempDB Allocation only under tempdb? Because the three bands describe tempdb specifically. Under any other database the question has no meaning.
Why is the Free Space band hidden by default? Because it is usually much larger than the other three and squashes them flat. Turn it on when the question is headroom.
What exactly is the version store? Row versions SQL Server keeps so that a reader can see a consistent older copy of a row. Snapshot isolation, read committed snapshot, triggers, online index rebuilds and multiple active result sets all create them.
Why does the version store not shrink when the query finishes? Because it is cleaned up behind the oldest active transaction on the instance. One long transaction anywhere holds versions for everything.
Why does my spike look smaller than it was? Because every bar is an average over its bucket. Narrow the time frame, and use TempDB High Usage for the individual sessions.
The whole chart is at zero. Nothing was collected for that window. Check that the historic collection job is running.
How is this different from TempDB Metadata Contention? This is about space. That page is about latch contention on the allocation pages, which is a completely different tempdb problem with a different fix.