Runbook: tempdb contention

Symptom: bursts of PAGELATCH_UP/PAGELATCH_EX waits on pages like 2:1:1 (PFS), 2:1:3 (SGAM) — many sessions stuck, CPU low.

Confirm

SELECT session_id, wait_type, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%';

Resource 2:<file>:<page> = tempdb. Page 1/3/2 = PFS/SGAM/GAM allocation pages.

Fix (in order)

1. Multiple tempdb data files — equal size, equal growth; start with 8 files or #cores, whichever is smaller. On SQL 2016+ setup does this, but audit inherited instances.
2. Ensure trace-flag-equivalent behavior is on (2016+: built-in uniform extent allocation + autogrow all files).
3. Reduce tempdb churn: temp table storms in hot code paths, spills (fix the memory grants), version store pressure (long transactions under RCSI/SI).
4. Memory-optimized tempdb metadata (2019+) for extreme metadata contention: ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Don'ts

• Don't add 32 files "to be safe" — diminishing returns and slower file growth management.
• Don't shrink tempdb during business hours to "fix" it; it will regrow and fragment.