tempdb contention (SQL Server)

Symptom

PAGELATCH_UP/EX waits, contention on tempdb allocation pages (PFS/GAM/SGAM), general slowness.

1. Confirm the contention

PAGELATCH waits on tempdb system pages (such as 2:1:1) indicate allocation contention.

SQL Server

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:%'
ORDER BY wait_duration_ms DESC;

2. Break down tempdb usage

Who is using how much tempdb? Internal objects (sort/hash spills), the version store, or user objects?

SQL Server

SELECT * FROM sys.dm_db_file_space_usage;
SELECT TOP 10 session_id, user_objects_alloc_page_count, internal_objects_alloc_page_count
FROM sys.dm_db_task_space_usage ORDER BY internal_objects_alloc_page_count DESC;

3. Fix the file configuration

Use multiple EQUALLY sized data files for tempdb, equal to the number of CPU cores (up to 8). SQL 2016+ recommends this at setup; on older versions use trace flags 1117/1118. Put the files on fast disk (local NVMe).

4. Find the runaway query

Queries with large sort/hash spills blow up tempdb. Target those with insufficient memory grants or bad plans; reduce spills with indexes/plans.

SQL Server

SELECT TOP 10 t.text, mg.requested_memory_kb, mg.granted_memory_kb
FROM sys.dm_exec_query_memory_grants mg CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
ORDER BY mg.requested_memory_kb DESC;

5. Analogues in other databases

The same 'temporary space contention' is the TEMP tablespace in Oracle (v$tempseg_usage / v$sort_usage) and temp_files and work_mem overflow in PostgreSQL. The root fix is always the same: fix the spilling query and speed up the temporary space.