TEMPDB High Use

Overview

TempDB Allocation says tempdb filled and which of the three kinds of usage did it. TempDB High Usage says who.

One row per recorded high usage session over the last fourteen days, ranked by how many gigabytes it had allocated, carrying the login, the machine and the statement it was running.

The statement is the point of the page. Every other tempdb page in this product describes a quantity. This one hands you the query to go and fix.

It is a historic record, not a live one. The problem happened, it was recorded, and this page is where you go afterwards. Nothing here requires the offending session to still be connected, which is exactly what makes it useful the morning after.

The TempDB High Usage report
The heaviest tempdb sessions of the last fourteen days, with the statement each was running.

Where to find it

Two routes.

Route Scope
tempdb → Historic → TempDB High Usage This instance.
Right-click the server → Instance Level Reports → TempDB High Usage The same page, reached from the instance.

The page title reads TempDB High Usage for <server name>.

Requirements

  • A historic database configured for this instance. Without one the page says so and stops.
  • The collection job running, and running often enough to have caught the session. A session that allocated 40 GB for ninety seconds between two collection passes was never recorded.

When nothing was recorded, the page says There is no significant TempDB usage for this SQL Server. That is a good answer, with the caveat in the previous paragraph attached to it.

Reading the grid

Column What it is
Log Time When the collection saw this. Not when the session started.
Session ID The SPID at the time. It has almost certainly been reused since.
Allocation GB How much tempdb space that session had allocated when it was seen.
Host The machine the session came from.
Login Name The login it connected as.
Query The statement it was running, up to 8100 characters.

Sorted by Allocation GB, largest first, across the whole fourteen day window rather than by date. The first row is the worst thing that happened in a fortnight, not the most recent.

Double-click a row to open the full statement in a window you can read and copy. The grid column is one line and the statements on this page are rarely one line.

Up to 1000 rows are returned.

The tempdb high usage grid
Ranked by allocation across the whole window, so the top row is the worst offender of the fortnight.

Log Time is not the start time

The row is a snapshot, not a summary. It says this session had this much allocated at this moment. Three consequences.

  • A long running offender appears several times, once per collection pass that caught it, with the allocation growing across the rows. That progression is useful: it is the growth rate.
  • The Allocation GB is not the peak unless a pass happened to land on the peak. It is a sample.
  • Session ID is only meaningful with the log time beside it. SPIDs are reused constantly, and the same number twelve hours apart is a different session.

How to read the report

  1. Read the top rows. They are the largest allocations of the fortnight, which is what you came for.
  2. Group by Query in your head. The same statement appearing repeatedly is a systematic problem; one huge row from one login once is an incident.
  3. Read Host and Login together. A named user from a workstation is a person running something ad hoc. A service account from an application server is the application doing it every day.
  4. Line the log times up against TempDB Allocation. That page says which of user objects, internal objects and version store grew; this one says which session. The two together give you the whole answer.
  5. Double-click and read the statement. A large sort or hash with no supporting index, a SELECT into a temporary table with no WHERE, or a cursor over a large set covers most of what turns up here.

Common patterns

One statement, many rows, allocation climbing across them. A single query building a very large intermediate result. The plan is spilling or the temporary table is genuinely that big. Memory Grants and Spills tells you which.

A reporting login at the top every day. Reporting against the transactional database. The fix is usually elsewhere, but this is the evidence.

Rows from an application server with an ORM-shaped statement. Generated SQL with no useful predicate. Worth taking to whoever owns the application with the exact statement in hand.

A single enormous row from a personal login, once. Somebody ran something ad hoc. Worth a conversation rather than a project.

An empty page while TempDB Allocation clearly shows tempdb filling. The usage is not concentrated in a few sessions, or the collection passes are missing it. Version store growth in particular is often not attributable to a single busy session, because it is held by a transaction that may be doing nothing at the time.

Where the data comes from

  • [DBHealthHistory].[dbo].[tempDBUsage], written by the historic monitoring job.

The window is the last fourteen days, capped at 1000 rows, ordered by allocation.

This page writes nothing.

Report Why you would go there
TempDB Allocation Which kind of tempdb usage grew: user objects, internal objects or the version store.
TempDB Use By Hour The shape of the week, as a heat map.
TempDB Consumers Who is using tempdb right now, rather than who did.
TempDB Metadata Contention The other tempdb problem: latch contention on the allocation pages.
Memory Grants and Spills The queries whose grants are too small, which is where internal object usage comes from.
Open Transactions The long running transaction behind version store growth.

Frequently asked questions

The page says there is no significant tempdb usage. Nothing was recorded above the collection’s threshold in the last fourteen days. That is usually good, and it can also mean a short spike happened between two collection passes.

Why does the same session ID appear more than once? Either the same session was caught by several collection passes, or the SPID was reused by a different session later. Read the log time alongside it.

Is Allocation GB the peak for that session? No. It is what was allocated when the collection saw it. A long running session shows a series of samples, and the peak may have been between two of them.

Can I kill the session from here? No, and it would not help: this is history, and that session ended long ago. TempDB Consumers and Sessions are the live pages.

Why does the query text stop part way through? It is stored up to 8100 characters. A very long batch is truncated.

Why is the page ranked by size rather than by date? Because the question is what was the worst thing that happened, not what happened last.

How is this different from TempDB Consumers? That page is live: who is using tempdb at this moment. This page is the record of who did, over the last fortnight.