Diagnostiquer la contention et l'utilisation d'espace de tempdb

Commande

-- 1) Current tempdb page latch waits (2:1:1 PFS, 2:1:2 GAM, 2:1:3 SGAM):
SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms, wt.resource_description
FROM   sys.dm_os_waiting_tasks wt
WHERE  wt.wait_type LIKE N'PAGELATCH[_]%'
  AND  wt.resource_description LIKE N'2:%';

-- 2) Sessions using the most tempdb (MB):
SELECT TOP (20) su.session_id, s.login_name, s.program_name,
       (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 / 1024         AS user_obj_mb,
       (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 / 1024 AS internal_obj_mb
FROM   sys.dm_db_session_space_usage su
  JOIN sys.dm_exec_sessions s ON s.session_id = su.session_id
WHERE  s.is_user_process = 1
ORDER BY su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count
       - su.user_objects_dealloc_page_count - su.internal_objects_dealloc_page_count DESC;

-- 3) Overall breakdown and version store:
SELECT SUM(unallocated_extent_page_count) * 8 / 1024       AS free_mb,
       SUM(user_object_reserved_page_count) * 8 / 1024     AS user_obj_mb,
       SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_obj_mb,
       SUM(version_store_reserved_page_count) * 8 / 1024   AS version_store_mb
FROM   tempdb.sys.dm_db_file_space_usage;

SELECT DB_NAME(database_id) AS db, reserved_page_count * 8 / 1024 AS version_store_mb
FROM   sys.dm_tran_version_store_space_usage
ORDER BY reserved_page_count DESC;

-- 4) File count / equal sizing and memory-optimized metadata:
SELECT name, type_desc, size * 8 / 1024 AS size_mb, growth * 8 / 1024 AS growth_mb, is_percent_growth
FROM   tempdb.sys.database_files;
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS tempdb_metadata_memopt;

Ce que fait la commande

Affiche les attentes PAGELATCH sur les pages d'allocation de tempdb (PFS/GAM/SGAM) et les tables système, les sessions qui utilisent le plus tempdb (tables temporaires vs. débordements sort/hash), la base qui gonfle le version store, et la configuration des fichiers.

Quand l'utiliser

Quand les attentes PAGELATCH_UP/EX augmentent sous de fortes charges de tables temporaires, quand tempdb se remplit (1105/9002), ou quand le version store grossit.

Attention

Les pages 2:1:1, 2:1:2, 2:1:3 et les pages PFS aux multiples de 8088 signifient une contention d'allocation : la solution est plusieurs fichiers de données de taille égale avec une croissance égale (un par cœur jusqu'à 8, puis par ajouts de 4). Pour la contention sur les pages des tables système, 2019+ propose les métadonnées tempdb optimisées en mémoire (redémarrage nécessaire). Si le version store grossit, trouvez la plus ancienne transaction snapshot ouverte ; réduire tempdb n'est pas une solution. Requête en lecture seule ; sûre.