Diagnosing and Resolving TempDB Allocation Page Contention (PAGELATCH_UP) in SQL Server

Recognizable Condition

High PAGELATCH_UP waits on TempDB pages 2:1:1, 2:1:2, 2:1:3 accompanied by sustained CPU usage and query slowdown indicate allocation page contention. This wait type appears when many threads try to allocate pages from the same TempDB file simultaneously.

Cause/Diagnostic Table

Factor	Typical Symptom
TempDB file count lower than logical core count	Increased PAGELATCH_UP on the first few files
Trace flag 1118 disabled	Mixed extent allocations cause hot pages
Auto‑growth set to small increments or percent	Frequent growth events add latency
Workload creates many temporary tables or table variables	Higher allocation pressure

Ordered Checks

1. Query overall wait stats: SELECT wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type = 'PAGELATCH_UP'; Look for a high wait_time_ms relative to total waits.
2. Examine TempDB file configuration: SELECT file_id, name, physical_name, size*8/1024.0 AS size_mb, growth, is_percent_growth FROM sys.master_files WHERE database_id = DB_ID('tempdb'); Note the number of rows (data files).
3. Check if trace flag 1118 is enabled: DBCC TRACESTATUS(-1); The output should list 1118 with a status of 1.
4. Capture per‑session waits to spot hotspots: SELECT session_id, wait_type, wait_time_ms FROM sys.dm_exec_session_wait_stats WHERE wait_type = 'PAGELATCH_UP' ORDER BY wait_time_ms DESC;

Fixes Tied to Findings

• If TempDB data file count < logical cores (up to a maximum of 8), add files: ALTER DATABASE tempdb ADD FILE (NAME = N'tempdev2', FILENAME = N'C:\\SQLData\\tempdb2.ndf', SIZE = 512MB, FILEGROWTH = 256MB); Repeat for each additional file, keeping size and growth uniform.
• Enable trace flag 1118 globally: add -T1118 to the SQL Server startup parameters or run DBCC TRACEON(1118, -1); (requires restart to persist).
• Set uniform auto‑growth: ensure each file uses a fixed MB increment (e.g., 256 MB) and disable percent growth.
• Consider placing TempDB on fast storage (SSD) or using a memory‑optimized filegroup if the edition supports in‑memory TempDB (SQL Server 2019+).

Escalation Criteria

• After applying changes, re‑run the wait‑stat query; if PAGELATCH_UP wait time remains >50 % of its original value, investigate application usage of temp tables/table variables.
• Look for patterns such as frequent creation/drop of #temp tables in stored procedures; consider rewriting to use table variables or permanent tables with cleanup.
• If contention persists, evaluate upgrading to a newer SQL Server version that includes improved TempDB caching (e.g., SQL Server 2022).
• In rare cases, partitioning the workload across multiple instances or using resource governor to limit concurrent TempDB consumers may be necessary.

Verification

1. Re‑query sys.dm_os_wait_stats for PAGELATCH_UP and confirm wait_time_ms has dropped by at least 50 % compared to the baseline captured before changes.
2. Run the TempDB file query again and verify the file count matches the number you configured.
3. Execute DBCC TRACESTATUS(-1) and ensure 1118 appears with a status of 1.

Limitations and Practical Tips

• Changing the TempDB file count requires a SQL Server service restart; schedule this during a maintenance window.
• Trace flag 1118 can affect workloads that rely heavily on mixed extent allocations; test the change in a non‑production environment first.
• Uniform auto‑growth values should be sized to accommodate typical growth between restarts to avoid frequent auto‑growth events.
• Monitor TempDB usage with sys.dm_db_file_space_usage to ensure no file becomes a hotspot after redistribution.