Tracer les plans d'exécution

Afin de suivre le comportement d’une application ou d’un traitement, il est utile de récupérer les plans d’exécution réels (après exécution) des requêtes exécutées.

C’est possible à l’aide des évènements étendus.

L’événement query_post_execution_plan_profile récupère les plans d’exécution réels à l’aide de l’infrastructure de profilage léger, depuis SQL Server 2017 CU14 et dans toutes les versions suivantes. Ces plans donnent le nombre de lignes et les lectures de chaque opérateur, mais pas son temps processeur ni sa durée, que seul le profilage standard mesure. Sur SQL Server 2017, démarrer la session active le profilage léger pour toute l’instance ; si vous la démarrez et l’arrêtez souvent, Microsoft conseille d’activer le trace flag 7412 au démarrage.

Cette capture a un coût. Mesuré sur SQL Server 2025, une session sur cet événement, sans action, a fait perdre 71 % de débit à une charge transactionnelle (des lectures par clé de quelques dixièmes de milliseconde), et n’a pas ralenti de façon visible une requête de 820 ms. Sur un serveur transactionnel chargé, ne laissez la session tourner que le temps du traitement à observer.

Créer la session d’évènements

Exécutez le code suivant sur votre serveur, cela créera la session sans la démarrer. Elle ne coûte rien tant qu’elle n’est pas démarrée.

CREATE EVENT SESSION [suivi_execution] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'suivi_execution',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO

Cette session, lorsqu’elle sera démarrée, récupérera les plans de toutes les exécutions. La trace sera stockée dans des fichiers binaires, dans le répertoire de journalisation du serveur, portant l’extension .xel : au maximum 5 fichiers de 100 Mo chacun. Au-delà, les plus anciens sont supprimés, et une capture longue ou chargée ne garde que la fin du traitement ; le banc cité plus haut écrivait plus de 4 Go en 20 secondes sans filtre.

Vous pouvez filtrer par base de données, ou par login, avec une version différente, comme illustré ci-dessous.

Pour filtrer sur une base de données

CREATE EVENT SESSION [suivi_execution] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([database_name]=N'PachaDataFormation'))
ADD TARGET package0.event_file(SET filename=N'suivi_execution',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO

où vous remplacez PachaDataFormation par le nom de la base de données cible. Le filtre porte sur la base de contexte de la requête : une requête lancée depuis master qui lit PachaDataFormation.Contact.Contact est rattachée à master, et cette session ne la capture pas.

Pour filtrer sur un login

CREATE EVENT SESSION [suivi_execution] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([sqlserver].[username]=N'rudi'))
ADD TARGET package0.event_file(SET filename=N'suivi_execution',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO

où vous remplacez rudi par le nom de votre login (Windows ou SQL Server, avec le nom complet, domaine compris, en cas de login Windows).

Pour filtrer sur une base de données et un login

CREATE EVENT SESSION [suivi_execution] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE ([database_name]=N'PachaDataFormation'
    AND [sqlserver].[username]=N'rudi'))
ADD TARGET package0.event_file(SET filename=N'suivi_execution',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO

Exemple de filtre pour isoler les requêtes les plus consommatrices

Il est en général inutile de récupérer tous les plans d’exécutions. Vous pouvez :

1. filtrer les bases de données système (master, tempdb, msdb), et SSISDB si vous utilisez Integration Services ;
2. filtrer sur la durée (duration exprimée en microsecondes).

L’exemple suivant utilise ces filtres pour ne conserver que les requêtes qui durent plus de 100 millisecondes.

Ces filtres réduisent le volume des fichiers ; la session coûte presque autant aux requêtes, filtrée ou non. Mesuré sur SQL Server 2025, une session filtrée sur une durée qu’aucune requête n’atteignait a coûté presque autant que la session sans filtre (70 % de débit perdu contre 71 %).

CREATE EVENT SESSION [suivi_execution] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    SET collect_database_name=(1)
    ACTION(sqlserver.sql_text)
    WHERE (
        [sqlserver].[is_system] = 0 -- on évite les sessions système
        AND database_name <> N'master'
        AND database_name <> N'msdb'
        AND database_name <> N'tempdb'
        AND database_name <> N'SSISDB'
        AND duration > 100000 -- 100 millisecondes
    ))
ADD TARGET package0.event_file(
    SET filename=N'suivi_execution',max_file_size=(100))
WITH (STARTUP_STATE=OFF);

Démarrer la session

Au bon moment, juste avant le démarrage de votre traitement, activez la session :

-- pour démarrer
ALTER EVENT SESSION [suivi_execution] ON SERVER 
STATE = START;

Arrêter la session

A la fin du traitement, arrêtez la session :

-- pour arrêter
ALTER EVENT SESSION [suivi_execution] ON SERVER 
STATE = STOP;

Récupérer les données

Il faut maintenant trouver les fichiers .xel pour me les faire parvenir.

Ils se trouvent par défaut dans le répertoire du journal d’erreur de SQL Server. Vous pouvez trouver son chemin à l’aide de la requête suivante :

SELECT SERVERPROPERTY('ErrorLogFileName')

Rendez-vous dans ce répertoire et identifiez les fichiers suivi_execution*.xel.

Zippez-les : la compression est très importante sur ce type de fichiers.

Envoyez-moi le zip !