Capturer les requêtes lentes SQL Server avec Extended Events

Une requête lente intermittente devient difficile à diagnostiquer une fois disparue. Un plan provenant d'une autre exécution ne dit pas forcément si le temps venait du processeur, des lectures ou des attentes. Une session Extended Events ciblée conserve des éléments utiles sans enregistrer tous les détails du serveur.

La question choisie est précise: quelles requêtes terminées dans une base durent au moins une seconde? Elle limite la collecte et sélectionne des cas à approfondir. L'exemple vise une instance SQL Server classique; les sessions cloud au niveau base et leurs cibles demandent une autre configuration.

Définir la capture avant de démarrer

Vérifiez les métadonnées sur l'instance réelle au lieu de deviner une unité.

SELECT object_name, name, type_name, description
FROM sys.dm_xe_object_columns
WHERE object_name IN (N'rpc_completed', N'sql_batch_completed')
  AND name IN (N'duration', N'cpu_time');

Pour les événements de fin RPC et batch utilisés ici, duration est exprimée en microsecondes: 1 000 000 représente une seconde. Tous les champs temporels de tous les événements ne suivent pas nécessairement cette unité. Préservez-la lors d'un export.

La session filtre la base d'identifiant 5. Remplacez-le par DB_ID de la base voulue, et le chemin Windows par un dossier existant accessible en écriture au compte de service SQL Server. Choisissez un nom de session libre.

-- SQL Server instance example: replace database_id 5 and the file path.
CREATE EVENT SESSION AxialSlowRequestsDemo ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
)
ADD TARGET package0.event_file (
    SET filename = N'D:\XEvents\AxialSlowRequests.xel',
        max_file_size = (20), max_rollover_files = (4)
)
WITH (MAX_MEMORY = 4096 KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 5 SECONDS,
      STARTUP_STATE = OFF);
ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = START;

Les RPC couvrent notamment les appels de procédure envoyés par un pilote; les événements batch couvrent les batches SQL. Collecter les deux évite une lacune selon le mode d'envoi de l'application. Cela ne rend pas leurs mesures interchangeables et ne permet pas d'additionner toutes les durées sans considérer leur portée.

La taille et la rotation des fichiers sont bornées. D'anciens fichiers peuvent disparaître lors de la rotation: il s'agit d'une fenêtre de diagnostic, pas d'une archive permanente. STARTUP_STATE OFF empêche cette investigation temporaire de reprendre automatiquement après redémarrage.

ALLOW_SINGLE_EVENT_LOSS accepte une perte possible d'événements. La capture ne doit donc pas être présentée comme un audit exact. Contrôlez santé de session et pertes lorsque la complétude compte.

Lire les indices nécessaires au test suivant

Inspectez les événements avec le même chemin.

SELECT object_name, CAST(event_data AS xml) AS EventXml
FROM sys.fn_xe_file_target_read_file(
    N'D:\XEvents\AxialSlowRequests*.xel', NULL, NULL, NULL
);

Le XML contient horodatage, champs propres à l'événement et actions sélectionnées. Interprétez les horaires de façon cohérente, généralement en UTC, pour les rapprocher des journaux applicatifs. Lisez durée, CPU, lectures logiques et texte pertinent selon le schéma de chaque événement.

Une longue durée avec peu de CPU invite à examiner attentes, blocages, stockage ou autres délais; elle n'identifie pas une cause unique. Beaucoup de lectures logiques peuvent orienter vers un accès excessif. Une forte consommation CPU peut justifier l'analyse du plan et des expressions. Rapprochez le cas du plan et des paramètres réellement utilisés.

Les événements terminés n'expliquent pas une requête encore bloquée sans fin. Pour un incident actif, inspectez séparément requêtes courantes et attentes. Le réseau côté client et le rendu de l'interface ne font pas non plus partie de la durée SQL capturée.

Le texte SQL peut contenir des littéraux sensibles. Limitez accès aux fichiers, actions collectées et conservation. Examinez le contenu avant de diffuser des fichiers bruts dans un fil de support largement accessible.

Arrêter et préserver la conclusion

Après reproduction ou expiration du budget, arrêtez puis supprimez la session temporaire.

ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = STOP;
DROP EVENT SESSION AxialSlowRequestsDemo ON SERVER;

Supprimer sa définition ne supprime pas les fichiers. Conservez les éléments pertinents puis appliquez la politique de conservation. Une analyse future ne doit pas inclure involontairement de vieux fichiers à cause d'un motif trop large.

Ne commencez pas par tous les événements d'instruction et tous les plans de l'instance. Élargissez uniquement pour répondre à une question encore ouverte, en mesurant l'impact. Un filtre apparemment étroit peut capter des milliers de requêtes pendant un incident.

Enregistrez base, période, seuil, types d'événements, pertes et hypothèse. Faites ensuite une modification ciblée ou collectez la prochaine mesure nécessaire. Extended Events est utile lorsqu'il relie une vraie requête lente à une explication testable, pas simplement lorsqu'il produit un fichier supplémentaire.

Références techniques: Microsoft Learn: Extended Events quick start · Microsoft Learn: CREATE EVENT SESSION · Microsoft Learn: Read event files.