Pour le dépannage d’une requête lente ou d’une session applicative spécifique, les statistiques d’attente au niveau instance dans sys.dm_os_wait_stats sont trop grossières : elles agrègent les événements d’attente de tous les processus depuis le dernier redémarrage. Réinitialiser la vue avec DBCC SQLPERF est également problématique sur les instances occupées où des centaines d’autres sessions génèrent du bruit.

Le framework des événements étendus de SQL Server 2008 résout cela élégamment : vous pouvez créer une session d’événements légère et filtrée qui collecte les statistiques d’attente pour un seul SPID, avec un impact négligeable sur le reste de l’instance.

La procédure : étape par étape

Le flux est : créer la session XE filtrée sur votre SPID cible → la démarrer → exécuter votre requête ou attendre que la session accumule des données → l’arrêter → lire et analyser le fichier collecté.

Prérequis : le compte exécutant cette procédure nécessite la permission ALTER ANY EVENT SESSION. Les données collectées sont écrites sur disque — surveillez l’utilisation de l’espace du dossier de sortie pendant les longues fenêtres de collecte.

-- 1) Supprimer la session si elle existe déjà
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'WaitMon')
    DROP EVENT SESSION WaitMon ON SERVER
GO

-- 2) Créer la session filtrée
--    Remplacer 79 par le session_id réel à surveiller
CREATE EVENT SESSION WaitMon ON SERVER
ADD EVENT sqlos.wait_info
    (WHERE sqlserver.session_id = 79)
ADD TARGET package0.asynchronous_file_target
    (SET FILENAME     = N'S:	empEE_WaitMonStats.xel',
         METADATAFILE = N'S:	empEE_WaitMonStats.xem')
WITH (max_dispatch_latency = 1 SECONDS)
GO

-- 3) Démarrer la collecte
ALTER EVENT SESSION WaitMon ON SERVER STATE = START
GO

-- 4) >>> Exécutez votre requête ou attendez que la session se déroule <<<

-- 5) Arrêter la collecte
ALTER EVENT SESSION WaitMon ON SERVER STATE = STOP
GO

Analyser les résultats

Après l'arrêt de la session, chargez les données collectées dans une table temporaire et agrégez par type d'attente. Les types d'attente les plus fréquents ou les plus longs pointent vers les goulots d'étranglement : I/O disque (PAGEIOLATCH), réseau (ASYNC_NETWORK_IO), verrous (LCK_M_*) ou CPU (SOS_SCHEDULER_YIELD).