Performance · SQL Server · Troubleshooting

TempDB Under Pressure: Diagnosing Contention Without Guesswork

3 min read

TempDB is shared by every database on a SQL Server instance, which makes it a frequent meeting point for unrelated symptoms. Adding files may solve one class of allocation contention, but it will not repair a spilling query, uncontrolled version-store growth or slow storage. The safe starting point is classification.

[company_article_image src=”articles/tempdb-contention.svg” alt=”TempDB contention diagnostic model connecting workloads, internal pressure and evidence” caption=”Different TempDB pressure paths require different evidence and corrective actions.”]

Identify what TempDB is doing

TempDB supports temporary tables, table variables, worktables, sorts, hashes, row versioning, online operations and internal engine tasks. Establish which consumers grew during the incident instead of treating total file size as the cause.

SELECT SUM(user_object_reserved_page_count) * 8.0 / 1024 AS user_objects_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS internal_objects_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS version_store_mb,
       SUM(unallocated_extent_page_count) * 8.0 / 1024 AS free_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

Read the wait resource, not only the wait name

PAGELATCH_* means an in-memory page latch, not physical disk latency. Inspect the waited page resource. Repeated pressure on allocation pages can indicate allocation contention; other page IDs may point to metadata or a specific object. PAGEIOLATCH_*, by contrast, involves data-file I/O and needs file-latency evidence.

Find session-level consumers

SELECT s.session_id,
       (s.user_objects_alloc_page_count - s.user_objects_dealloc_page_count) * 8.0 / 1024 AS user_mb,
       (s.internal_objects_alloc_page_count - s.internal_objects_dealloc_page_count) * 8.0 / 1024 AS internal_mb
FROM sys.dm_db_session_space_usage AS s
ORDER BY internal_mb DESC, user_mb DESC;

Correlate large consumers with active requests, execution plans and memory grants. Sort and hash spills often indicate inaccurate cardinality estimates, insufficient grants or excessive concurrency. Increasing TempDB capacity may protect the incident window, but query evidence should drive the permanent correction.

Check version-store retention

RCSI, snapshot isolation and some engine features retain row versions. A long-running transaction can prevent cleanup and make growth appear unrelated to the originating workload. Track active snapshot transactions and identify the transaction age before disabling an isolation model that applications depend on.

Change configuration deliberately

  • Use equally sized data files with equal growth settings.
  • Pre-size for the measured workload and leave filesystem headroom.
  • Use sensible fixed growth increments rather than tiny percentage growth.
  • Add files gradually when allocation contention is proven; more files are not automatically better.
  • Measure data and log file latency separately.

Prove the result

Repeat the same workload window and compare latch waits, spill count, file latency, growth events and application response time. TempDB tuning is complete only when the original user-visible symptom improves without moving pressure somewhere else.

Related Articles