Tracer les plans d'exécution

Comment tracer les plans d’exécution actuels des requêtes.

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

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

Les plans d’exécution actuels peuvent être récupérés sans impact notable sur les performances à l’aide de l’infrastructure de profilage léger sur SQL Server 2017 à partir du CU21, et sur SQL Server 2019.

Créer la session d’évènements

Exécutez le code suivant sur votre serveur, cela créera la session sans la démarrer. Il n’y a donc aucun impact pour l’instant.

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 tous les plans d’exécution. La trace sera stockée dans des fichiers binaires, dans le répertoire de journalisation du serveur, dans au maximum 5 fichiers de 100 Mo chacun, portant l’extension .xel.

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'nom_de_la_base_de_donnees'))
ADD TARGET package0.event_file(SET filename=N'suivi_execution',max_file_size=(100))
WITH (STARTUP_STATE=OFF)
GO

où vous remplacez bien sûr nom_de_la_base_de_donnees par le nom de la base de données cible.

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 bien sûr 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'nom_de_la_base_de_donnees')
    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 suivante utilse ces filtres pour ne conserver que les requêtes qui durent plus de 100 millisecondes.

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].[session_id] > 50 -- on évite les sessions systèmes
        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 va être très importante.

Envoyez-moi le zip !

[Besoin de services avec SQL Server ? Contactez-moi]