SQL Server tempdb Contention: How to Diagnose and Fix It

tempdb is shared by every connection on the instance. When the allocation system can't keep up with the request rate, you see latch contention on specific system pages — and everything slows down at the same time, regardless of which database the user thought they were querying.

Diagnosis: prove it's tempdb contention

SELECT
    wait_type,
    wait_resource,
    blocking_session_id,
    COUNT(*) AS sessions_waiting
FROM sys.dm_exec_requests
WHERE wait_type LIKE 'PAGELATCH%'
  AND wait_resource LIKE '2:%'
GROUP BY wait_type, wait_resource, blocking_session_id
ORDER BY sessions_waiting DESC;

Notes:

• 2:1:1 = PFS page (Page Free Space tracking)
• 2:1:2 = GAM page (Global Allocation Map)
• 2:1:3 = SGAM page (Shared GAM)
• 2:X:1, 2:X:2, 2:X:3 on file X = same allocation pages, but in additional tempdb data files

If you see many sessions waiting on these specific page IDs, you have allocation contention. Other contention patterns on tempdb (object metadata, session-level temp table cache thrashing) need different treatment.

Fix 1: Enough data files

The single biggest fix. The historical rule of "one tempdb file per logical core, up to 8" is still a reasonable starting point:

• ≤ 8 logical cores: one file per core
• > 8 cores: start at 8 files, add 4 at a time if contention persists

All files equal size, equal autogrowth (and ideally pre-sized so autogrowth rarely fires). The proportional fill algorithm requires equal sizes to spread allocation evenly across files.

Fix 2: Trace flags 1117 / 1118 — already on for tempdb since SQL 2016

If you're on SQL Server 2016 or newer, the effect of TF 1117 (uniform autogrow) and TF 1118 (mixed-extent disabled) is on by default for tempdb. Don't re-enable them. On older versions, enable both.

Fix 3: Memory-optimized tempdb metadata (SQL 2019+)

If you're on SQL Server 2019+ and your contention is on system metadata pages (not the PFS/GAM/SGAM pages above), enabling memory-optimized tempdb metadata can wipe the problem out:

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
GO
-- restart required

Read the release notes; there are columnstore-on-temp-table restrictions in some service packs.

Fix 4: Instant file initialization

If autogrowth does fire, IFI lets data files grow without zero-filling. Verify the SQL Server service account has Perform Volume Maintenance Tasks in the local security policy. Confirm:

SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services;

Fix 5: Reduce tempdb pressure at the source

Not every tempdb problem is solved at the tempdb layer. Common upstream causes:

• Code spilling hash joins / sorts to disk — fix the memory grant or the query
• Heavy temp table use in tight loops — redesign with table variables (sometimes) or persistent staging tables
• RCSI / SI without enough version store cleanup — the version store lives in tempdb and can dominate it
• Cursors that materialize large intermediate result sets

Fix 6: Place tempdb on fast storage

tempdb is the hottest, most-write-heavy database on the instance. On modern hardware this means local NVMe (on-prem) or premium SSD tier (cloud VMs). Sharing a slow LUN with database log files is a recipe for stalled I/O across the whole instance.

What to measure after

The same DMV query from the top of this post. After the fix, the PAGELATCH wait counts on 2:1:1/2/3 should drop to near-zero. If they don't, you didn't fix what you thought you were fixing — keep looking.

---

Stuck with tempdb pain in production? A SQL Server health check includes full tempdb diagnostics — contention pattern, file count, sizing, version store, and trace flag posture. Free discovery call below.