Diagnosing and Resolving TempDB Contention in SQL Server

Recognizable Condition

TempDB contention typically manifests as sustained high wait times for PAGEIOLATCH_UP (allocation pages), PAGEIOLATCH_EX (data pages), and WRITELOG. These symptoms are often accompanied by frequent auto-growth events recorded in the SQL Server error log and elevated I/O latency (Avg. Disk sec/Read/Write) on the drives hosting TempDB files.

Cause and Diagnostic Overview

Symptom	Likely Cause
PAGELATCH_UP on TempDB pages 2-3	Hot allocation pages due to high concurrency in temp object creation
PAGEIOLATCH_EX on TempDB user tables	Query spills (hash or sort) writing large intermediate results to disk
High WRITELOG waits	TempDB log bottleneck, often due to slow storage or undersized log files
Frequent auto-growth events	Undersized initial file size or insufficient number of data files

Ordered Diagnostic Checks

1. Analyze Wait Statistics: Identify if TempDB waits are dominating the system.

   -- Run as sysadmin or with VIEW SERVER STATE permission
   SELECT wait_type, waiting_tasks_count, wait_time_ms 
   FROM sys.dm_os_wait_stats 
   WHERE wait_type LIKE '%PAGELATCH%' 
      OR wait_type LIKE '%PAGEIOLATCH%' 
      OR wait_type = 'WRITELOG';

2. Review File Space Usage: Check for version store pressure or unallocated space.

   -- Run in the context of the master database
   SELECT database_id, file_id, 
          (unallocated_extent_page_count*8)/1024.0 AS unallocated_mb, 
          (version_store_reserved_page_count*8)/1024.0 AS version_store_mb 
   FROM sys.dm_db_file_space_usage 
   WHERE database_id = DB_ID('tempdb');

3. Identify Active TempDB Sessions: Pinpoint queries causing the load.

   -- Requires VIEW SERVER STATE and VIEW DATABASE STATE
   SELECT s.session_id, s.login_name, r.command, r.wait_type, t.text AS query_text 
   FROM sys.dm_exec_sessions s 
   JOIN sys.dm_exec_requests r ON s.session_id = r.session_id 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t 
   WHERE s.is_user_process = 1 AND s.database_id = DB_ID('tempdb');

4. Monitor Disk I/O: Use Performance Monitor (PerfMon) to check PhysicalDisk\Avg. Disk sec/Read and Write. Latency > 10ms typically indicates a storage bottleneck.
5. Scan Error Logs: Search for autogrow events.

   EXEC xp_readerrorlog 0, 1, N'autogrow';

Remediation Tied to Findings

PAGELATCH_UP on Allocation Pages

When contention occurs on allocation pages, increase the number of TempDB data files to distribute the load. The general recommendation is one file per logical processor up to 8 files.

1. Check core count: SELECT cpu_count FROM sys.dm_os_sys_info;
2. Add files to match core count (up to 8) and ensure they are of equal size.

   -- Example: Adding a third data file on drive D:
   ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'D:\TempDB\tempdb3.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);

3. Rollback: To revert, remove the added file using ALTER DATABASE tempdb REMOVE FILE tempdev3; and restart the service.

PAGEIOLATCH_EX (Query Spills)

If data page waits dominate, the issue is likely inefficient queries spilling to disk.

• Review execution plans for Hash Match or Sort operators with a warning icon indicating a spill to TempDB.
• Update statistics or add missing indexes to improve cardinality estimates, reducing the memory grant requirement.
• If the workload is legitimate, move TempDB to faster storage (e.g., NVMe SSDs).

High WRITELOG Waits

Log contention requires optimizing the transaction log throughput.

• Ensure the TempDB log file (templog.ldf) is on a dedicated high-speed disk.
• Verify if trace flag 1118 is enabled (default in SQL Server 2016+) to reduce allocation contention. Check with DBCC TRACESTATUS(1118);.

Version Store Pressure

Long-running transactions prevent the cleanup of the version store, bloating TempDB.

• Identify long-running transactions using sys.dm_tran_active_transactions.
• Terminate unnecessary orphaned sessions using the KILL [session_id] command (requires sysadmin permissions).

Verification and Limitations

To verify the fix, re-run the wait statistics query. A successful remediation should show a significant reduction in the dominant wait type. Confirm file distribution with:

SELECT file_id, name, size*8/1024.0 AS size_mb FROM sys.master_files WHERE database_id = DB_ID('tempdb');

Limitations: Changing the number of TempDB files requires a SQL Server service restart. Avoid shrinking TempDB files under heavy load, as this can cause fragmentation and increase contention.

Escalation Criteria

Escalate to a senior DBA or infrastructure team if:

• Disk latency remains > 20ms after moving TempDB to the fastest available storage.
• Auto-growth occurs multiple times per hour despite sizing files to peak demand.
• Version store growth persists despite terminating long-running transactions, suggesting a fundamental application design flaw.