Diagnosing TempDB Contention in SQL Server: GAM, SGAM, PFS, and Multiple Data Files

The previous lesson’s latch pattern shows up at system scale in one very specific, very common place: tempdb’s allocation pages, under heavy use of temp tables and table variables (yes — straight back to Course 2’s temp objects material).

What’s Actually Being Contended

Every tempdb data file has special allocation-tracking pages: GAM (Global Allocation Map), SGAM (Shared GAM), and PFS (Page Free Space). Every session creating a temp table or table variable must touch these pages to claim space — under high concurrency, many sessions latch-wait on the same few physical pages.

Diagnosing It

-- High PAGELATCH waits specifically on tempdb pages is the signature
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

-- Confirm it's tempdb specifically
SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks
WHERE resource_description LIKE '2:%'; -- database_id 2 = tempdb

The Standard Fix: Multiple Equally-Sized Data Files

-- Common starting guidance: one tempdb data file per CPU core, up to ~8, all EQUAL size
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:tempdbtempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);
-- Repeat with matching sizes for tempdev3, tempdev4...

Equal size matters: SQL Server’s proportional-fill allocation favors the file with the most free space, so unequal files defeat the round-robin benefit entirely — a genuinely common mistake when adding files without matching existing sizes.