TempDB Contention - Diagnosis & Resolution

TempDB contention manifests as PAGELATCH_UP waits on specific system pages that manage space allocation. The root cause lies in SQL Server's allocation bitmap architecture. PFS pages track free space availability every 8,088 pages, while GAM and SGAM pages track extent allocation every 64,000 pages. In high-concurrency scenarios, threads queue to modify these shared metadata pages.

The contention hotspots are predictable. PFS page 2:1:1 (database_id 2 = tempdb, file 1, page 1) becomes a severe bottleneck because it tracks the first 8,088 pages where most small object allocations occur. Similarly, GAM page 2:1:2 and SGAM page 2:1:3 serialize extent allocations. SQL Server's scheduler threads compete for exclusive latches on these pages to allocate space for temporary objects.

SQL Server 2016 introduced multiple TempDB data files by default during installation, significantly reducing contention by distributing allocation load. However, pre-2016 installations often run single TempDB files. SQL Server 2019 and later versions improved TempDB metadata caching and allocation algorithms. SQL Server 2022 added tempdb metadata memory-optimization for certain workloads.

Mixed extent allocations worsen contention because SQL Server must update both GAM and SGAM pages for each allocation. Once objects reach 8 pages, they switch to uniform extents, reducing metadata updates. The lock manager escalation to table locks can temporarily mask the underlying allocation contention.

-- Identify TempDB allocation contention waits
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'PAGELATCH%' 
    AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;

-- Show current TempDB allocation page waits
SELECT 
    session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    resource_description,
    CASE 
        WHEN resource_description LIKE '2:1:1' THEN 'PFS Page'
        WHEN resource_description LIKE '2:1:2' THEN 'GAM Page' 
        WHEN resource_description LIKE '2:1:3' THEN 'SGAM Page'
        ELSE 'Other'
    END AS page_type
FROM sys.dm_os_waiting_tasks 
WHERE wait_type LIKE 'PAGELATCH%'
    AND resource_description LIKE '2:%'
ORDER BY wait_duration_ms DESC;

-- Analyze TempDB file usage and allocation patterns
SELECT 
    f.name AS file_name,
    f.physical_name,
    f.size * 8 / 1024 AS size_mb,
    FILEPROPERTY(f.name, 'SpaceUsed') * 8 / 1024 AS used_mb,
    f.growth,
    f.is_percent_growth
FROM sys.master_files f
INNER JOIN sys.databases d ON f.database_id = d.database_id
WHERE d.name = 'tempdb' AND f.type = 0
ORDER BY f.file_id;

-- Monitor TempDB allocation unit contention in real-time
SELECT 
    r.session_id,
    r.request_id, 
    r.blocking_session_id,
    w.wait_type,
    w.wait_duration_ms,
    w.resource_description,
    t.text AS sql_text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE w.wait_type LIKE 'PAGELATCH%'
    AND w.resource_description LIKE '2:%'
    AND r.session_id > 50;

-- Check TempDB version store and user object usage
SELECT 
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb, 
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
FROM sys.dm_db_task_space_usage;

Add TempDB data files for allocation distribution This script calculates the optimal number of TempDB data files based on logical CPU count and adds them with proper sizing and placement.

-- Calculate recommended TempDB file count (up to 8 files or logical CPU count)
DECLARE @logical_cpus INT = (SELECT cpu_count FROM sys.dm_os_sys_info);
DECLARE @target_files INT = CASE WHEN @logical_cpus <= 8 THEN @logical_cpus ELSE 8 END;
DECLARE @current_files INT = (SELECT COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0);
DECLARE @files_to_add INT = @target_files - @current_files;
DECLARE @file_size_mb INT = 1024; -- Adjust based on your workload
DECLARE @file_growth_mb INT = 256;
DECLARE @data_path NVARCHAR(260);

-- Get TempDB data file path
SELECT @data_path = LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)))
FROM sys.master_files 
WHERE database_id = 2 AND file_id = 1;

DECLARE @i INT = @current_files + 1;
DECLARE @sql NVARCHAR(MAX);

WHILE @i <= @target_files AND @files_to_add > 0
BEGIN
    SET @sql = N'ALTER DATABASE tempdb ADD FILE (
        NAME = ''tempdev' + CAST(@i AS NVARCHAR(10)) + ''',
        FILENAME = ''' + @data_path + '\tempdb' + CAST(@i AS NVARCHAR(10)) + '.ndf'',
        SIZE = ' + CAST(@file_size_mb AS NVARCHAR(10)) + 'MB,
        FILEGROWTH = ' + CAST(@file_growth_mb AS NVARCHAR(10)) + 'MB
    );';
    
    PRINT @sql;
    -- EXEC sp_executesql @sql; -- Uncomment to execute
    
    SET @i += 1;
    SET @files_to_add -= 1;
END;

PRINT 'Restart SQL Server for changes to take effect optimally';

Resize existing TempDB files to equal sizes Unequal file sizes cause the proportional fill algorithm to direct disproportionate allocations to the larger files (files with more free space receive more writes), recreating allocation contention on those files.

-- Resize all TempDB data files to the same size
DECLARE @target_size_mb INT = 2048; -- Set your target size
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
    'ALTER DATABASE tempdb MODIFY FILE (NAME = ''' + name + ''', SIZE = ' + 
    CAST(@target_size_mb AS VARCHAR(10)) + 'MB);' + CHAR(13)
FROM sys.master_files 
WHERE database_id = 2 AND type = 0
ORDER BY file_id;

PRINT @sql;
-- EXEC sp_executesql @sql; -- Uncomment to execute
PRINT 'Files will resize immediately. Monitor disk space during operation.';

Enable trace flag 1118 for SQL Server versions before 2016 This prevents mixed extent allocations in TempDB, reducing GAM/SGAM page contention.

-- Enable trace flag 1118 globally (SQL Server 2014 and earlier)
-- This trace flag is enabled by default in SQL Server 2016+
IF SERVERPROPERTY('ProductMajorVersion') < 13
BEGIN
    DBCC TRACEON(1118, -1);
    PRINT 'Trace flag 1118 enabled globally';
    PRINT 'Add -T1118 to SQL Server startup parameters for persistence';
END
ELSE
BEGIN
    PRINT 'Trace flag 1118 is default behavior in SQL Server 2016+';
END

Configure TempDB with multiple data files during SQL Server installation or immediately after. Use one data file per logical CPU core up to 8 files. Beyond 8 files, add more only if monitoring shows continued allocation contention. Size all TempDB data files equally and place them on separate physical drives when possible.

Set initial TempDB file sizes large enough to handle peak workloads without autogrowth events. Configure fixed growth increments (not percentages) to maintain equal file sizes. Monitor PAGELATCH_UP waits specifically on database_id 2 pages through automated alerting.

Optimize application code to minimize temporary object creation. Replace table variables with temp tables for datasets over 100 rows. Use appropriate indexing on temp tables to reduce sorting operations. Consider memory-optimized table variables in SQL Server 2014+ for high-frequency, small temporary datasets.

Implement connection pooling to reduce session overhead and temporary object churn. Archive old data to reduce version store pressure in TempDB. Schedule maintenance operations during off-peak hours to avoid competing with application workloads for TempDB resources.

Place TempDB files on high-performance storage with adequate IOPS capacity. NVMe SSDs significantly improve allocation page latch response times. Avoid placing TempDB on the same drives as user databases or transaction logs.