Contención en tempdb (SQL Server)

Síntoma

Esperas PAGELATCH_UP/EX, contención en las páginas de asignación de tempdb (PFS/GAM/SGAM), lentitud general.

1. Confirme la contención

Las esperas PAGELATCH sobre páginas de sistema de tempdb (como 2:1:1) indican contención de asignación.

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. Desglose el uso de tempdb

¿Quién usa cuánto tempdb? ¿Objetos internos (spills de sort/hash), el version store u objetos de usuario?

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. Corrija la configuración de archivos

Use varios archivos de datos de IGUAL tamaño para tempdb, tantos como núcleos de CPU (hasta 8). SQL 2016+ lo recomienda durante la instalación; en versiones anteriores use los trace flags 1117/1118. Coloque los archivos en disco rápido (NVMe local).

4. Encuentre la consulta descontrolada

Las consultas con grandes spills de sort/hash hacen crecer tempdb sin control. Apunte a las que tienen memory grants insuficientes o planes malos; reduzca los spills con índices/planes.

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. Equivalentes en otras bases de datos

La misma 'contención de espacio temporal' corresponde al tablespace TEMP en Oracle (v$tempseg_usage / v$sort_usage) y a temp_files y el desbordamiento de work_mem en PostgreSQL. La solución de fondo es siempre la misma: corregir la consulta que desborda y acelerar el espacio temporal.