What is Stored in TempDB? – Interview Question of the Week #271

Question: What is stored in TempDB? Temporary user objects, work objects created by the engine, and versioning data. A table inventory shows only part of that activity.

A client asked me what was in their TempDB right now. My first query listed tables and their allocated space. It’s a useful starting point, provided we don’t mistake it for everything SQL Server is using TempDB for.

SELECT tb.name AS [Temporary Object Name],
       SUM(CASE WHEN ps.index_id IN (0,1)
                THEN ps.row_count ELSE 0 END) AS [Approximate rows],
       SUM(ps.used_page_count) * 8 AS [Used space (KB)],
       SUM(ps.reserved_page_count) * 8 AS [Reserved space (KB)]
FROM tempdb.sys.tables AS tb
JOIN tempdb.sys.dm_db_partition_stats AS ps
  ON ps.object_id = tb.object_id
GROUP BY tb.object_id, tb.name
ORDER BY tb.name;

This version groups partitions and indexes into one row per table. It counts rows from the heap or clustered index so a nonclustered index doesn’t count the same table rows again. The space totals include the table’s indexes. DMV row counts are approximate.

What the table list doesn’t show

TempDB also holds worktables and workfiles for operations such as sorting, hashing and spooling. Row versions can consume space too. Those uses don’t all appear as named tables in sys.tables.

SELECT SUM(user_object_reserved_page_count) * 8 AS user_objects_KB,
       SUM(internal_object_reserved_page_count) * 8 AS internal_objects_KB,
       SUM(version_store_reserved_page_count) * 8 AS version_store_KB,
       SUM(unallocated_extent_page_count) * 8 AS unallocated_KB
FROM tempdb.sys.dm_db_file_space_usage;

The second query summarizes broader data-file allocation categories. For a large internal-object allocation, look at requests, session/task space usage and execution plans. For version-store growth, investigate the transaction and versioning workload. A busy shared database changes while you inspect it.

Don’t drop someone else’s temporary object because it looks large. Identify its owner and purpose first. SQL Server recreates TempDB when the instance starts, so it is not a place to keep data that must survive a restart.

Related performance investigations include parameter sniffing, local variables, OPTIMIZE FOR UNKNOWN, database-scoped parameter sniffing configuration, OPTION (RECOMPILE) and the recompilation summary. Those techniques solve specific plan problems; none is a general TempDB cleanup command.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.