TempDB Consumers
Overview
There are already five tempdb reports in this application: two heat maps, an allocation view, a high usage view, a files advisor and the metadata contention page. All of them answer how much or when.
None of them answered the question somebody actually has during an incident, which is who, right now, and why.
Where to find it
Right-click an instance in the tree, TempDB Consumers.
The toolbar switches between Composition, By session and Version store, and the refresh can be paused. Nothing is polled until the page is opened.
Requirements
| Requirement | Why |
|---|---|
VIEW SERVER STATE |
Every source here is server scoped. |
| SQL Server 2016 or newer for the version store size | sys.dm_tran_version_store_space_usage arrived then. Before it, the file space counters carry the figure alone. |
| SQL Server 2019 or newer for the ADR readings | The persistent version store statistics and the ADR flag arrived then. |
The four things tempdb holds
A single percentage full tells nobody which of these to chase, and they have completely different causes and completely different fixes.
| Part | What it is | Where the fix is |
|---|---|---|
| User objects | Temp tables and table variables. | Attributable to a session, and usually code. |
| Internal objects | Sorts, hashes and spools that did not fit in their memory grant. | The same event Memory Grants and Spills counts as a spill, seen from the other side. |
| Version store | Row versions for RCSI, snapshot isolation, triggers and online index builds. | The transaction pinning them. This is the one that fills tempdb overnight while nobody is looking. |
| Free | What is left, and the trajectory towards it. |
The version store finding is a session, not a size
For the version store the finding is almost never “the version store is large”. It is the oldest transaction preventing cleanup.
Version cleanup is strictly ordered: no version newer than the oldest open snapshot transaction can be discarded, no matter how long ago it was written. One session, usually idle, usually an application connection that opened a snapshot transaction and went to lunch, is holding every version behind it.
Naming that session with its login, host, program and how long it has been open is the entire value of this page during an incident. It is the headline when a snapshot transaction has been open more than ten minutes, ahead of whatever is currently measuring largest, because it is the thing about to cause the incident rather than the thing already visible.
Where the session is sleeping, the page says so: an idle connection with an open transaction is the classic shape, and it is the detail that turns “the version store is growing” into “kill session 84”.
Reading the chart
The bars are the current sample. The strip underneath is one stacked band per sample taken while the page has been open, so the composition can be watched changing.
The strip is this session’s samples only. Nothing here survives closing the page. That is stated in the footer rather than implied, because a strip that looks like history and is not would be worse than no strip.
The trajectory
Full in about 40 minutes at the rate of the last 18 samples.
Measured from the first sample to the last, not from the last two. A single pair of ten second readings on a busy instance is mostly noise and would produce a different answer on every refresh.
When usage is flat or falling the page says “not filling over the samples taken so far” rather than printing a date in the next century.
Reading the grid
| Column | What it is |
|---|---|
| Holding | Which of the four parts this session is holding. |
| Session | The session id. |
| Login, host and application | Who it is, which matters more than the id when deciding to kill it. |
| Current | What this session’s running tasks hold right now. |
| Session total | What the connection has allocated over its whole life. |
| Open for | How long its transaction has been open. |
| What it is doing | The statement, or the fact that there is not one. |
Current and Session total are never added together. Session space usage is cumulative for the life of the connection, so a pooled connection accumulates a large lifetime number that is not currently allocated anywhere. Adding the two produces a figure that is not a quantity of anything.
Accelerated Database Recovery changes the answer completely
With ADR on, row versions live in a persistent version store inside the user database rather than in tempdb. A tempdb version store reading near zero on a 2019 or 2022 instance is therefore expected rather than reassuring, and the growth has moved somewhere the tempdb reports do not look.
The page detects ADR, says so in the banner, and reports the size of the persistent version store where it can read it. This is the one thing that makes a clean looking tempdb page misleading, and it is the reason the banner exists.
The line that keeps this page safe
The version store size comes from sys.dm_tran_version_store_space_usage and the file space counters. It never comes from sys.dm_tran_version_store.
That view returns one row per version record. On a busy server with a version store of any size, scanning it is genuinely dangerous, and it is the single most common way a well meant tempdb script makes an incident worse. This report does not read it under any circumstances.
Right-click actions in the grid
| Action | What it does |
|---|---|
| Copy session details | Session id, login, host, program and how long it has been open. |
| Go to Memory Grants and Spills | On an internal objects row, where the spill came from. |
| Go to TempDB Metadata Contention | The other half of a tempdb complaint. |
| Copy the query behind this report | The whole batch, to run and adapt yourself. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.dm_db_file_space_usage |
The four way split of tempdb, and free space. |
sys.dm_db_session_space_usage |
Lifetime allocation per session. |
sys.dm_db_task_space_usage |
Current allocation by running tasks. |
sys.dm_tran_version_store_space_usage |
The version store size, cheaply, per database. |
sys.dm_tran_active_snapshot_database_transactions |
The open snapshot transactions, and which is oldest. |
sys.dm_tran_persistent_version_store_stats |
The ADR persistent version store, on 2019 and newer. |
sys.databases |
RCSI, snapshot isolation and ADR flags. |
sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_exec_sql_text |
Who the session is and what it is running. |
Messages you may see
Session N has held a snapshot transaction for 47 minutes. Version store cleanup cannot pass it. The headline finding. Everything newer than that transaction is pinned regardless of size.
At the rate of the samples taken so far, tempdb is full in about 25 minutes. Shown when the trajectory is under four hours, which is the window in which it is worth acting.
N databases have Accelerated Database Recovery on. Shown when the tempdb version store is nearly empty, because that reading is expected rather than reassuring on an ADR instance.
sys.dm_tran_version_store_space_usage does not exist on this build. Before SQL Server 2016. The version store figure comes from the file space counters only.
No session is holding a snapshot transaction. Version store view. Nothing is pinning cleanup.
Sampling. The trajectory needs a second reading. The first sample is drawn; the slope needs the next one.
Related reports
| Report | Why you would go there |
|---|---|
| TempDB Metadata Contention | The other half of a tempdb complaint: the allocation pages being latched. |
| Open Transactions | On a version store row it is usually the whole answer. |
| TempDB by Hour | Whether this is a spike or the shape of every day. |
| What is Active | What the named session is part of. |
| Memory Grants and Spills | Where the internal objects came from. |
Frequently asked questions
Why is the version store nearly empty on my 2019 instance? Probably Accelerated Database Recovery. Versions moved into the user database and out of tempdb. The banner says so when it detects it, and reports the persistent version store size where it can.
Why is Session total so much bigger than Current? Because it covers the whole life of the connection. A pooled connection that ran a large sort an hour ago still carries that number even though it holds nothing now. The two columns are deliberately kept apart.
The strip is empty when I open the page. Why? Because it holds only the samples taken since the page opened. The first bar appears immediately; the strip needs a second sample.
Can I make the page sample faster? No, and deliberately. Every sample is a read of the file space counters and the session tables, and the trajectory is measured across the whole sample set rather than the last pair, so sampling faster would add cost without improving the answer.
Is it safe to run this during an incident? Yes. It never touches sys.dm_tran_version_store, which is the view that makes a bad tempdb situation worse. Everything it reads is an aggregate or a session list.
Why does killing the oldest snapshot session not free space immediately? Cleanup runs on its own schedule once the transaction is gone. The space is releasable at that point rather than released at that moment.