Taming tempdb Contention in SQL Server

Every SQL Server DBA has felt the sting: a production system suddenly grinding to a halt, PAGELATCH waits spiking, and the tempdb data files growing like weeds. You check the wait stats and see PAGEIOLATCH_EX or PAGELATCH_EX dominating, often with high wait times on allocation pages. This isn't a storage bottleneck—it's tempdb contention, a classic symptom of concurrent allocation requests fighting over the same internal structures. Left unchecked, it can bring even the most robust OLTP workloads to their knees.

Understanding tempdb Contention and Its Symptoms

Tempdb contention occurs when multiple sessions simultaneously attempt to allocate or deallocate pages in tempdb. The primary culprits are the shared global allocation map (GAM), shared global allocation map (SGAM), and page free space (PFS) pages. These pages are the internal allocation structures that SQL Server uses to track which extents and pages are free or in use. When many sessions need to create or drop temporary objects—#temp tables, table variables, or worktables for sorts and hash joins—they all queue up to modify these pages. The result is latch contention, not lock contention, which means you see PAGELATCH_EX or PAGELATCH_SH waits on specific page IDs in tempdb. Common symptoms include high PAGELATCH_* wait times, slow query performance, and tempdb data files that grow rapidly even with minimal user data.

Diagnosing PFS, GAM, and SGAM Page Waits

To confirm tempdb contention, query the sys.dm_os_waiting_tasks DMV. Look for wait_type = 'PAGELATCH_EX' or 'PAGELATCH_SH' where the resource_description contains 2:1:1 (PFS page for file 1), 2:1:2 (GAM page), or 2:1:3 (SGAM page). The pattern 2:1:X means database ID 2 (tempdb), file ID 1, page ID X. A high count of these waits indicates allocation contention.

-- Check for PAGELATCH waits on tempdb allocation pages
SELECT 
    session_id,
    wait_type,
    wait_duration_ms,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description LIKE '2:%';

You can also use sys.dm_db_file_space_usage to see how much space is allocated to user objects, internal objects, and version store. If user objects are small but internal objects are large, you likely have contention from worktables or sort operations.

Trace Flag 1118 and 1117: Legacy Solutions

Before SQL Server 2016, the go-to fix was enabling trace flags 1118 and 1117. Trace flag 1118 forces uniform extent allocation, ensuring that each object gets its own extent rather than sharing mixed extents. This reduces contention on SGAM pages because fewer objects compete for the same mixed extent. Trace flag 1117 makes all files in a filegroup grow proportionally, preventing one file from becoming a hotspot. These flags are still valid for older versions, but they are no longer the recommended approach for modern SQL Server (2016+), where uniform extent allocation is the default for tempdb and file growth behavior has improved. If you're on SQL Server 2014 or earlier, these flags remain essential.

Rule of thumb: If you're on SQL Server 2016 or later, do not enable trace flags 1118 or 1117 for tempdb—they are unnecessary and may cause other issues. Focus on proper file configuration instead.

Modern Approach: Multiple Data Files and File Sizing

The most effective solution for tempdb contention is to create multiple equally-sized data files. The general rule is to have one data file per CPU core, but cap at 8 files for most workloads (more files can cause excessive file metadata contention). Each file gets its own set of PFS, GAM, and SGAM pages, distributing the allocation load. Crucially, all files must be the same size and have the same FILEGROWTH increment. If files are different sizes, SQL Server will favor the larger file for allocations, creating a new hotspot.

-- Add multiple tempdb data files (example: 4 files, 1GB each)
ALTER DATABASE tempdb
ADD FILE (
    NAME = tempdev2,
    FILENAME = 'T:\tempdb\tempdb2.ndf',
    SIZE = 1024 MB,
    FILEGROWTH = 256 MB
);
-- Repeat for tempdev3, tempdev4...

After adding files, set the initial size to match the existing file and set AUTOGROW to the same increment for all files. Monitor sys.dm_io_virtual_file_stats to ensure even I/O distribution across files.

Using DMVs to Pinpoint Problematic Queries

Not all tempdb usage is equal. Some queries are heavy consumers, creating large worktables or many temporary objects. Use sys.dm_exec_query_stats and sys.dm_exec_sql_text to find queries with high tempdb_space_used or writes to tempdb. The sys.dm_db_task_space_usage DMV shows per-session tempdb allocation for internal objects.

-- Find sessions using the most tempdb space
SELECT 
    session_id,
    user_objects_alloc_page_count,
    internal_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC;

Cross-reference with sys.dm_exec_requests to identify the exact query text and plan. Look for operations like large sorts, hash joins, or spools that spill to tempdb. These are prime candidates for optimization.

Optimizing Temporary Objects and Query Patterns

Reduce tempdb load by optimizing how temporary objects are used. Avoid creating many small #temp tables in a loop—use table variables or derived tables instead. For large #temp tables, consider using a CREATE TABLE with explicit indexes rather than relying on implicit statistics. Minimize the use of SELECT INTO for temp tables because it generates heavy allocation activity. For queries that use sorts, ensure proper indexing on the base tables to avoid sort spills. Also, consider using OPTION (ORDER GROUP) or OPTION (HASH JOIN) hints to control plan choices that affect tempdb usage.

Monitoring and Maintaining tempdb Performance

Set up a baseline for tempdb performance using sys.dm_os_wait_stats and sys.dm_db_file_space_usage. Monitor PAGELATCH_* waits daily. If waits exceed 10-20% of total wait time, investigate. Also track tempdb file growth—if files autogrow frequently, increase initial sizes. Use ALTER DATABASE tempdb MODIFY FILE to resize files during maintenance windows. Consider enabling INSTANT FILE INITIALIZATION for tempdb data files (but not log files) to speed up growth operations. Finally, review tempdb configuration after any major schema or workload change.

The short version

Tempdb contention is caused by latch contention on allocation pages (PFS, GAM, SGAM) when many sessions create temporary objects. Diagnose it using sys.dm_os_waiting_tasks for PAGELATCH_EX waits on pages 2:1:1, 2:1:2, and 2:1:3. The modern fix is multiple, equally-sized data files (one per CPU core, max 8). Trace flags 1118/1117 are legacy solutions for older versions. Use DMVs to find heavy tempdb users and optimize queries that cause spills or excessive temporary objects. Monitor wait stats and file growth regularly to keep tempdb healthy.