SQL Server TempDB Performance Problems

TempDB is different from every other database on a SQL Server instance in one important way: it is shared by everything. Every database, every session, every workload on the instance competes for TempDB resources simultaneously. When TempDB has a problem, the effect is not isolated to one application or one database — it affects everything running on the server at the same time.

This makes TempDB problems worth understanding specifically, because the symptoms can appear to be many different problems — seemingly unrelated queries slowing down together, broad performance degradation that doesn't trace back to a single query — when the root cause is a single shared resource.

What TempDB Is Used For

TempDB handles a wider range of SQL Server operations than most teams realise:

• Temporary tables (#temp and ##global temp tables) created by queries and stored procedures
• Table variables — despite being declared differently from temp tables, they are stored in TempDB
• Internal work tables for sort operations, hash joins, hash aggregates, and spool operators that exceed their memory allocation
• The version store — when Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation is enabled, SQL Server maintains old row versions in TempDB so that readers can see a consistent snapshot without holding locks
• Online index rebuild operations, which use TempDB during the build phase
• MARS (Multiple Active Result Sets) and certain cursor operations

A workload that creates many temporary tables, runs complex aggregation queries, or uses RCSI on a database with long-running transactions will place significant demand on TempDB.

Allocation Page Contention

Historically, the most common TempDB performance problem on busy servers is allocation page contention. SQL Server uses specific pages within each database file to track which extents (groups of 8 pages) are allocated or free: the GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), and PFS (Page Free Space) pages. In TempDB, pages 2, 3, and 4 of each data file serve these purposes.

When many sessions create temporary tables or allocate space simultaneously, they all need to update these allocation pages. With a single TempDB data file, every allocation operation contends for the same pages. The symptom in wait statistics is PAGELATCH_EX or PAGELATCH_SH waits, specifically on database_id 2 (TempDB), on pages 2, 3, or 4.

The standard fix is to add multiple equally-sized TempDB data files. When multiple files are present and of equal size, SQL Server uses proportional fill to spread allocations across all files, distributing the contention across multiple sets of allocation pages. The standard recommendation is one data file per logical CPU core, up to eight files. Beyond eight, the benefit is typically marginal.

Critically: all TempDB data files must be the same size with the same autogrowth settings. If one file is larger than the others, SQL Server will use it more heavily due to proportional fill, defeating the purpose of having multiple files.

SQL Server 2016 and later creates 8 TempDB data files by default on new installations. Servers upgraded from older versions retain whatever TempDB configuration was in place before the upgrade. If your instance was originally installed on SQL Server 2012 or 2014, it likely has only one TempDB data file.

Version Store Growth

When RCSI is enabled on a database, every row that is modified is written in both its new form and its previous form. The previous form is stored in the TempDB version store, where it remains until no active transaction needs to read it.

The version store grows when there are long-running transactions that started before recent modifications. A transaction that began an hour ago and is still active requires SQL Server to retain all row versions generated in that hour, because the transaction might read any of them. If the transaction commits or rolls back, the version store can be cleaned up.

Monitor version store size using sys.dm_db_file_space_usage for TempDB, specifically the version_store_reserved_page_count column. If this is large and growing, look for long-running transactions using sys.dm_tran_active_snapshot_database_transactions. The transaction with the earliest snapshot sequence number is the one preventing version store cleanup.

Spills to Disk

When SQL Server plans a sort or hash operation, it estimates how much memory it will need based on the expected number of rows. If the actual row count is higher than estimated — due to stale statistics or bad cardinality estimates — the operation runs out of its memory allocation and spills the intermediate data to TempDB.

Spills are visible in execution plans as warning icons on Sort or Hash Match operators. They show the number of times the operation spilled and the number of pages written to disk. A spill to disk is significantly slower than the same operation in memory.

The underlying cause of spills is almost always a cardinality estimation problem — SQL Server underestimated the number of rows. The fix is to address the statistics or query structure that is producing the wrong estimate, not to adjust TempDB. Updating statistics on the tables involved in the query is the first step. If the problem persists, the query structure or parameters may need attention.

Excessive Temporary Object Creation

Application code that creates and drops temporary tables in a tight loop generates substantial TempDB activity. Each creation and drop is a metadata operation. Under high concurrency, this creates allocation page contention even if each individual temp table is small.

Table variables have different caching behaviour from temporary tables. Small, single-use temporary result sets may be better served by table variables in some cases — though table variables have their own trade-offs: they don't have statistics, which can cause poor query plans when used with large amounts of data, and their row count estimates are always 1 for the query optimiser's purposes unless the database compatibility level is 150+ with table variable deferred compilation enabled.

In practice, the choice between temporary tables and table variables should be made based on how the object is used, not as a general performance rule.

Monitoring TempDB

Three DMVs are useful for ongoing TempDB monitoring:

• sys.dm_db_file_space_usage (when run in the context of TempDB): shows total space used, version store space, internal object space, and user object space in TempDB
• sys.dm_db_session_space_usage: shows TempDB space used per session — useful for identifying sessions consuming disproportionate TempDB resources
• sys.dm_db_task_space_usage: similar to session space usage but per active task, useful for identifying specific queries rather than sessions

What to Do

1. Check TempDB data file count. Query sys.master_files where database_id = 2 and type = 0. If there is only one data file, adding more is the first step — especially if wait statistics show PAGELATCH_EX waits on TempDB.
2. Check that all data files are equal size. Unequal files cause disproportionate use of the largest file.
3. Check wait statistics for PAGELATCH_EX and PAGELATCH_SH specifically on TempDB pages (database_id 2, page numbers 2, 3, or 4).
4. Check version store size if RCSI is enabled, and identify any long-running transactions causing it to grow.
5. Check execution plans of slow queries for spill warnings on Sort and Hash Match operators.
6. Identify sessions consuming large TempDB allocations using sys.dm_db_session_space_usage.