Contention sur tempdb (SQL Server)

Symptôme

Attentes PAGELATCH_UP/EX, contention sur les pages d'allocation de tempdb (PFS/GAM/SGAM), lenteur générale.

1. Confirmez la contention

Des attentes PAGELATCH sur les pages système de tempdb (comme 2:1:1) indiquent une contention d'allocation.

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. Détaillez l'utilisation de tempdb

Qui utilise combien de tempdb ? Des objets internes (débordements de sort/hash), le version store ou des objets utilisateur ?

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. Corrigez la configuration des fichiers

Utilisez pour tempdb plusieurs fichiers de données de taille ÉGALE, en nombre égal aux cœurs CPU (jusqu'à 8). SQL 2016+ le recommande à l'installation ; sur les versions plus anciennes, utilisez les trace flags 1117/1118. Placez les fichiers sur un disque rapide (NVMe local).

4. Trouvez la requête incontrôlée

Les requêtes avec de gros débordements de sort/hash font exploser tempdb. Ciblez celles dont le memory grant est insuffisant ou le plan mauvais ; réduisez les débordements par les index/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. Équivalents dans les autres bases

La même « contention sur l'espace temporaire » correspond au tablespace TEMP dans Oracle (v$tempseg_usage / v$sort_usage) et aux temp_files et débordements de work_mem dans PostgreSQL. Le correctif de fond est toujours le même : corriger la requête qui déborde et accélérer l'espace temporaire.