Infrastructure du profilage de requête

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Base de données SQL dans Microsoft Fabric

Le moteur de base de données SQL Server fournit l’accès aux informations d’exécution sur les plans d’exécution des requêtes. L’une des actions les plus importantes en cas de problème de performances consiste à bien comprendre la nature de la charge de travail en cours d’exécution et la façon dont est pilotée l’utilisation des ressources. Par conséquent, l’accès au plan d’exécution réel est important.

Bien que la complétion de requête soit un prérequis à la disponibilité d’un plan de requête réel, les statistiques des requêtes actives peuvent fournir des insights en temps réel concernant le processus d’exécution de requête à mesure que les données transitent d’un opérateur de plan de requête vers un autre. Le plan de requête active affiche la progression globale de la requête ainsi que des statistiques d’exécution de niveau opérateur telles que le nombre de lignes produites, le temps écoulé, la progression de l’opérateur, etc. Vous pouvez accéder à ces données en temps réel sans avoir à attendre l’exécution de la requête ; ces statistiques d’exécution se révèlent donc extrêmement utiles pour résoudre les problèmes de performances de requêtes, tel que les requêtes de longue durée et les requêtes qui s'exécutent indéfiniment et ne se terminent jamais.

Infrastructure de profilage des statistiques d’exécution de requête standard

L’infrastructure de profil de statistiques d’exécution de requête ou le profilage standard doit être activée pour collecter des informations sur les plans d’exécution, à savoir le nombre de lignes, le processeur et l’utilisation des E/S. Les méthodes suivantes de collecte des informations de plan d’exécution pour une session cible utilisent l’infrastructure de profilage standard :

Note

La sélection du bouton Inclure des statistiques de requête dynamique dans SQL Server Management Studio utilise l’infrastructure de profilage standard. Dans les versions ultérieures de SQL Server, si l’infrastructure de profilage légère est activée, elle est utilisée par les statistiques de requête en direct au lieu du profilage standard lorsqu’elle est affichée par le biais du Moniteur d’activité ou interroge directement le DMV sys.dm_exec_query_profiles .

Les méthodes suivantes de collecte des informations de plan d’exécution globalement pour toutes les sessions utilisent l’infrastructure de profilage standard :

Événement query_post_execution_showplan étendu. Pour activer les événements étendus, consultez Surveiller l’activité système à l’aide d’événements étendus.
L’événement de trace Showplan XML dans Trace SQL et SQL Server Profiler. Pour plus d’informations sur cet événement de trace, consultez Classe d’événements Showplan XML.

Lors de l’exécution d’une session d’événements étendue qui utilise l’événement query_post_execution_showplan , la sys.dm_exec_query_profiles DMV est également remplie, ce qui active les statistiques de requête en direct pour toutes les sessions, à l’aide du Moniteur d’activité ou de l’interrogation directe de la vue dynamique. Pour plus d’informations, voir Live Query Statistics.

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes

À compter de SQL Server 2014 (12.x) SP2 et SQL Server 2016 (13.x), une nouvelle infrastructure de profilage léger des statistiques d’exécution de requêtes ou un profilage léger a été introduit.

Note

Les procédures stockées compilées en mode natif ne sont pas prises en charge avec le profilage léger.

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v1

S’applique à : SQL Server 2014 (12.x) SP2 à SQL Server 2016 (13.x).

À compter de SQL Server 2014 (12.x) SP2 et SQL Server 2016 (13.x), la surcharge de performances pour collecter des informations sur les plans d’exécution a été réduite avec l’introduction du profilage léger. Contrairement au profilage standard, le profilage léger ne collecte pas les informations d’exécution du processeur. Toutefois, le profilage léger collecte toujours les informations sur le nombre de lignes et l’utilisation des E/S.

Un nouvel query_thread_profile événement étendu a également été introduit qui utilise le profilage léger. Cet événement étendu expose les statistiques d’exécution par opérateur, ce qui permet de bénéficier d’insights supplémentaires sur les performances de chaque nœud et chaque thread. Un exemple de session utilisant cet événement étendu peut être configuré comme dans l’exemple suivant :

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile
    (
    ACTION (sqlos.scheduler_id,
            sqlserver.database_id,
            sqlserver.is_system,
            sqlserver.plan_handle,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.server_instance_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text)
    )
ADD TARGET package0.ring_buffer (SET max_memory = (25600))
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Note

Pour plus d’informations sur la surcharge de performances liée au profilage de requête, consultez le billet de blog Developers Choice: Query progress - anytime, anywhere.

Lors de l’exécution d’une session d’événements étendue qui utilise l’événement query_thread_profile , la sys.dm_exec_query_profiles DMV est également remplie à l’aide d’un profilage léger, ce qui active les statistiques de requête en direct pour toutes les sessions, à l’aide du Moniteur d’activité ou de l’interrogation directe de la vue dynamique.

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v2

S’applique à : SQL Server 2016 (13.x) SP1 à SQL Server 2017 (14.x).

SQL Server 2016 (13.x) SP1 inclut une version révisée du profilage léger avec une surcharge minimale. Le profilage léger peut également être activé globalement à l’aide de l’indicateur de trace 7412 pour les versions indiquées précédemment dans S’applique. Une nouvelle fonction de gestion dynamique sys.dm_exec_query_statistics_xml a été introduite afin de retourner le plan d’exécution de requête pour les requêtes en cours.

À compter de SQL Server 2016 (13.x) SP2 CU3 et SQL Server 2017 (14.x) CU11, si le profilage léger n’est pas activé globalement, le nouvel argument QUERY_PLAN_PROFILE peut être utilisé pour activer le profilage léger au niveau de la requête, pour n’importe quelle session. Lorsqu’une requête contenant ce nouvel indicateur se termine, un nouvel query_plan_profile événement étendu est également généré qui fournit un xml de plan d’exécution réel similaire à l’événement query_post_execution_showplan étendu.

Note

L’événement query_plan_profile étendu utilise également le profilage léger même si l’indicateur de requête n’est pas utilisé.

Vous pouvez configurer un exemple de session à l’aide de l’événement query_plan_profile étendu comme dans l’exemple suivant :

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile
    (
    ACTION (sqlos.scheduler_id,
            sqlserver.database_id,
            sqlserver.is_system,
            sqlserver.plan_handle,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.server_instance_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text)
    )
ADD TARGET package0.ring_buffer (SET max_memory = (25600))
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v3

S’applique à : SQL Server 2019 (15.x) et versions ultérieures, et Azure SQL Database

SQL Server 2019 (15.x) et la base de données Azure SQL incluent une version nouvellement révisée du profilage léger qui collecte des informations sur le nombre de lignes pour toutes les exécutions. Le profilage léger est activé par défaut dans SQL Server 2019 (15.x) et base de données Azure SQL. Dans SQL Server 2019 (15.x) et versions ultérieures, l’indicateur de trace 7412 n’a aucun effet. Le profilage léger peut être désactivé au niveau de la base de données à l’aide de la LIGHTWEIGHT_QUERY_PROFILING la base de données : ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Une nouvelle DMF sys.dm_exec_query_plan_stats est introduite pour retourner l’équivalent du dernier plan d’exécution réel connu pour la plupart des requêtes. Elle s’appelle Dernières statistiques de plan de requête. Les dernières statistiques du plan de requête peuvent être activées au niveau de la base de données à l’aide de la LAST_QUERY_PLAN_STATS la base de données : ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Un nouvel query_post_execution_plan_profile événement étendu collecte l’équivalent d’un plan d’exécution réel basé sur un profilage léger, contrairement query_post_execution_showplanà celui qui utilise le profilage standard. SQL Server 2017 (14.x) propose également cet événement à partir de CU14. Vous pouvez configurer un exemple de session à l’aide de l’événement query_post_execution_plan_profile étendu comme dans l’exemple suivant :

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile
    (
    ACTION (sqlos.scheduler_id,
            sqlserver.database_id,
            sqlserver.is_system,
            sqlserver.plan_handle,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.server_instance_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text)
    )
ADD TARGET package0.ring_buffer (SET max_memory = (25600))
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Exemple 1 - Session d’événements étendus utilisant le profilage standard

CREATE EVENT SESSION [QueryPlanOld] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan
    (
    ACTION (sqlos.task_time,
            sqlserver.database_id,
            sqlserver.database_name,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.sql_text)
    )
ADD TARGET package0.event_file
    (
    SET filename = N'C:\Temp\QueryPlanStd.xel'
    )
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Exemple 2 - Session d’événements étendus utilisant le profilage léger

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile
    (
    ACTION (sqlos.task_time,
            sqlserver.database_id,
            sqlserver.database_name,
            sqlserver.query_hash_signed,
            sqlserver.query_plan_hash_signed,
            sqlserver.sql_text)
    )
ADD TARGET package0.event_file
    (
    SET filename = N'C:\Temp\QueryPlanLWP.xel'
    )
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);

Conseils d’utilisation de l’infrastructure de profilage de requête

La table suivante récapitule les actions pour activer le profilage standard ou le profilage léger, à la fois globalement (au niveau du serveur) ou dans une seule session. Inclut également la version la plus ancienne pour laquelle l’action est disponible.

Scope	Profilage standard	Profilage léger
Global	Session d’événements étendus avec le `query_post_execution_showplan` XE ; À compter de SQL Server 2012 (11.x)	Indicateur de trace 7412 ; À compter de SQL Server 2016 (13.x) SP1
Global	Trace SQL et SQL Server Profiler avec l’événement de `Showplan XML` trace	Session d’événements étendus avec le `query_thread_profile` XE ; À compter de SQL Server 2014 (12.x) SP2
Global	N/A	Session d’événements étendus avec le `query_post_execution_plan_profile` XE ; À compter de SQL Server 2017 (14.x) CU14 et SQL Server 2019 (15.x)
Session	Utilisez `SET STATISTICS XML ON`.	Utilisez l’indicateur de `QUERY_PLAN_PROFILE` requête avec une session d’événements étendus avec le `query_plan_profile` XE ; À compter de SQL Server 2016 (13.x) SP2 CU3 et SQL Server 2017 (14.x) CU11
Session	Utilisez `SET STATISTICS PROFILE ON`.	N/A
Session	Sélectionnez le bouton Statistiques des requêtes actives dans SSMS ; À compter de SQL Server 2014 (12.x) SP2	N/A

Remarks

Important

En raison d’une violation d’accès aléatoire possible lors de l’exécution d’une procédure stockée de surveillance qui référence sys.dm_exec_query_statistics_xml, vérifiez que la base de connaissances KB 4078596 est installée dans SQL Server 2016 (13.x) et SQL Server 2017 (14.x).

À compter du profilage léger v2 et de sa faible surcharge, tout serveur qui n’est pas déjà lié au processeur peut exécuter le profilage léger en continu et permettre aux professionnels de la base de données de tirer parti de n’importe quelle exécution en cours d’exécution à tout moment, par exemple à l’aide du Moniteur d’activité ou de sys.dm_exec_query_profilesl’interrogation directe, et d’obtenir le plan de requête avec des statistiques d’exécution.

Pour plus d’informations sur la surcharge de performances liée au profilage de requête, consultez le billet de blog Developers Choice: Query progress - anytime, anywhere.

Les événements étendus qui utilisent des informations de profilage léger utilisent des informations de profilage standard, si l’infrastructure de profilage standard est déjà activée. Par exemple, une session d’événements étendus utilisant query_post_execution_showplan est exécutée, et une autre session utilisant query_post_execution_plan_profile est démarrée. La deuxième session utilise toujours les informations du profilage standard.

Note

Sur SQL Server 2017 (14.x), le profilage léger est désactivé par défaut, mais est activé lorsqu’une trace d’événement étendue est query_post_execution_plan_profile démarrée, puis désactivée à nouveau lorsque la trace est arrêtée. Par conséquent, si les traces d'événements étendues basées sur query_post_execution_plan_profile sont fréquemment démarrées et arrêtées sur une instance de SQL Server 2017 (14.x), vous devez activer le profilage léger à l'échelle globale avec l'indicateur de trace 7412 pour éviter la surcharge répétée d'activation et de désactivation.

Rétroaction

Cette page vous a-t-elle été utile ?

Last updated on 2025-11-18

Partager via

Infrastructure du profilage de requête

Infrastructure de profilage des statistiques d’exécution de requête standard

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v1

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v2

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v3

Exemple 1 - Session d’événements étendus utilisant le profilage standard

Exemple 2 - Session d’événements étendus utilisant le profilage léger

Conseils d’utilisation de l’infrastructure de profilage de requête

Remarks

Contenu connexe

Rétroaction

Ressources additionnelles