Infrastructure du profilage de requêteQuery Profiling Infrastructure

S’APPLIQUE À : ouiSQL Server nonAzure SQL Database nonAzure SQL Data Warehouse nonParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Le Moteur de base de données SQL ServerSQL Server Database Engine offre la possibilité d’accéder aux informations d’exécution sur les plans d’exécution de requête.The Moteur de base de données SQL ServerSQL Server Database Engine provides the ability to access runtime information on query execution plans. 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.One of the most important actions when a performance issue occurs, is to get precise understanding on the workload that is executing and how resource usage is being driven. Pour cela, l’accès au plan d’exécution réel est important.For this, access to the actual execution plan is 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.While query completion is a prerequisite for the availability of an actual query plan, live query statistics can provide real-time insights into the query execution process as the data flows from one query plan operator to another. 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, tels que les longues requêtes et celles qui s’exécutent indéfiniment sans jamais se terminer.The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues, such as long running queries, and queries that run indefinitely and never finish.

Infrastructure de profilage des statistiques d’exécution de requête standardThe standard query execution statistics profiling infrastructure

L’infrastructure de profil de statistiques d’exécution de requête, ou profilage standard, doit être activée pour collecter des informations sur les plans d’exécution, à savoir le nombre de lignes et l’utilisation de l’UC et des E/S.The query execution statistics profile infrastructure, or standard profiling, must be enabled to collect information about execution plans, namely row count, CPU and I/O usage. Les méthodes suivantes de collecte d’informations sur les plans d’exécution pour une session cible tirent parti de l’infrastructure de profilage standard :The following methods of collecting execution plan information for a target session leverage the standard profiling infrastructure:

Notes

Cliquer sur le bouton inclure les statistiques des requêtes dynamiques dans SQL Server Management StudioSQL Server Management Studio s’appuie sur l’infrastructure de profilage standard.Clicking the button Include Live Query Statistics in SQL Server Management StudioSQL Server Management Studio leverages the standard profiling infrastructure.
Dans les versions ultérieures de SQL ServerSQL Server, si l’infrastructure de profilage légère est activée, elle est exploitée par les statistiques des requêtes dynamiques au lieu du profilage standard lorsqu’elle est affichée par le biais du moniteur d’activité ou que la DMV sys.dm_exec_query_profiles est directement interrogée.In higher versions of SQL ServerSQL Server, if the lightweight profiling infrastructure is enabled, then it is leveraged by live query statistics instead of standard profiling when viewed through Activity Monitor or directly querying the sys.dm_exec_query_profiles DMV.

Les méthodes suivantes de collecte globale d’informations sur les plans d’exécution pour toutes les sessions tirent parti de l’infrastructure de profilage standard :The following methods of collecting execution plan information globally for all sessions leverage the standard profiling infrastructure:

Lors de l’exécution d’une session d’événements étendus qui utilise l’événement query_post_execution_showplan, la vue de gestion dynamique sys.dm_exec_query_profiles est également renseignée, ce qui active les statistiques des requêtes actives pour tous les sessions, à l’aide du Moniteur d’activité ou par interrogation directe de la vue de gestion dynamique.When running an extended event session that uses the query_post_execution_showplan event, then the sys.dm_exec_query_profiles DMV is also populated, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV. Pour plus d’informations, voir Live Query Statistics.For more information, see Live Query Statistics.

Infrastructure légère de profilage des statistiques sur l’exécution des requêtesThe lightweight query execution statistics profiling infrastructure

À compter de SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 et SQL Server 2016 (13.x)SQL Server 2016 (13.x), une nouvelle infrastructure légère de profilage des statistiques sur l’exécution des requêtes (ou profilage léger), a été introduite.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), a new lightweight query execution statistics profiling infrastructure, or lightweight profiling was introduced.

Notes

Les procédures stockées compilées en mode natif ne sont pas prises en charge avec le profilage léger.Natively compiled stored procedures are not supported with lightweight profiling.

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v1Lightweight query execution statistics profiling infrastructure v1

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

À compter de SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 et SQL Server 2016 (13.x)SQL Server 2016 (13.x), la surcharge de performances liée à la collecte des informations sur les plans d’exécution a été réduite avec l’introduction du profilage léger.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), the performance overhead to collect information about execution plans was reduced with the introduction of lightweight profiling. Contrairement au profilage standard, le profilage léger ne collecte pas d’informations sur l’exécution de l’UC.Unlike standard profiling, lightweight profiling does not collect CPU runtime information. Toutefois, le profilage léger collecte toujours les informations sur le nombre de lignes et l’utilisation des E/S.However, lightweight profiling still collects row count and I/O usage information.

Un nouvel événement étendu query_thread_profile reposant sur le profilage léger a également été introduit.A new query_thread_profile extended event was also introduced that leverages lightweight profiling. 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.This extended event exposes per-operator execution statistics allowing more insight on the performance of each node and thread. Un exemple de session utilisant cet événement étendu peut être configuré comme dans l’exemple ci-dessous :A sample session using this extended event can be configured as in the below example:

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);

Notes

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.For more information on the performance overhead of query profiling, see the blog post Developers Choice: Query progress - anytime, anywhere.

Lors de l’exécution d’une session d’événements étendus qui utilise l’événement query_thread_profile, la vue de gestion dynamique sys.dm_exec_query_profiles est également renseignée à l’aide du profilage léger, ce qui active les statistiques des requêtes actives pour tous les sessions, à l’aide du Moniteur d’activité ou par interrogation directe de la vue de gestion dynamique.When running an extended event session that uses the query_thread_profile event, then the sys.dm_exec_query_profiles DMV is also populated using lightweight profiling, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV.

Infrastructure légère de profilage des statistiques sur l’exécution des requêtes v2Lightweight query execution statistics profiling infrastructure v2

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

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 inclut une version révisée du profilage léger avec une surcharge minimale.SP1 includes a revised version of lightweight profiling with minimal overhead. Vous pouvez aussi activer le profilage léger de manière globale à l’aide de l’indicateur de trace 7412 pour les versions mentionnées ci-dessus dans S’applique à.Lightweight profiling can also be enabled globally using trace flag 7412 for the versions stated above in Applies to. 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.A new DMF sys.dm_exec_query_statistics_xml is introduced to return the query execution plan for in-flight requests.

À compter de SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 et SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11, si le profilage léger n’est pas activé globalement, le nouvel argument d’indicateur de requête USE HINT QUERY_PLAN_PROFILE peut être utilisé pour activer le profilage léger au niveau de la requête, pour toute session.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11, if lightweight profiling is not enabled globally then the new USE HINT query hint argument QUERY_PLAN_PROFILE can be used to enable lightweight profiling at the query level, for any session. Quand une requête qui contient ce nouvel indicateur se termine, un nouvel événement étendu query_plan_profile est également généré. Il fournit du code XML de plan d’exécution réel semblable à l’événement étendu query_post_execution_showplan.When a query that contains this new hint finishes, a new query_plan_profile extended event is also output that provides an actual execution plan XML similar to the query_post_execution_showplan extended event.

Notes

L’événement étendu query_plan_profile s’appuie également sur le profilage léger même si l’indicateur de requête n’est pas utilisé.The query_plan_profile extended event also leverages lightweight profiling even if the query hint is not used.

Un exemple de session avec l’événement étendu query_plan_profile peut être configuré comme dans l’exemple ci-dessous :A sample session using the query_plan_profile extended event can be configured like the example below:

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 v3Lightweight query execution statistics profiling infrastructure v3

S’applique à : SQL ServerSQL Server (à compter de SQL Server 2019 - PreviewSQL Server 2019 preview)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 - PreviewSQL Server 2019 preview)

SQL Server 2019 - PreviewSQL Server 2019 preview inclut une version nouvellement révisée du profilage léger qui collecte des informations sur le nombre de lignes pour toutes les exécutions.includes a newly revised version of lightweight profiling collecting row count information for all executions. Le profilage léger est activé par défaut sur SQL Server 2019 - PreviewSQL Server 2019 preview et l’indicateur de trace 7412 n’a aucun effet.Lightweight profiling is enabled by default on SQL Server 2019 - PreviewSQL Server 2019 preview and trace flag 7412 has no effect. Le profilage léger peut être désactivé au niveau de la base de données à l’aide de la configuration étendue à la base de données LIGHTWEIGHT_QUERY_PROFILING : ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: 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.A new DMF sys.dm_exec_query_plan_stats is introduced to return the equivalent of the last known actual execution plan for most queries, and is called last query plan statistics. Les dernières statistiques de plan de requête peuvent être activées au niveau de la base de données à l’aide de la configuration étendue à la base de données LAST_QUERY_PLAN_STATS : ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.The last query plan statistics can be enabled at the database level using the LAST_QUERY_PLAN_STATS database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Un nouvel événement étendu query_post_execution_plan_profile collecte l’équivalent d’un plan d’exécution réel basé sur le profilage léger, contrairement à query_post_execution_showplan qui utilise le profilage standard.A new query_post_execution_plan_profile extended event collects the equivalent of an actual execution plan based on lightweight profiling, unlike query_post_execution_showplan which uses standard profiling. Un exemple de session avec l’événement étendu query_post_execution_plan_profile peut être configuré comme dans l’exemple ci-dessous :A sample session using the query_post_execution_plan_profile extended event can be configured like the example below:

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 standardExample 1 - Extended Event session using standard profiling

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égerExample 2 - Extended Event session using lightweight profiling

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);

Notes Remarks

Important

En raison d’un éventuel AV aléatoire pendant l’exécution d’une procédure stockée de supervision qui référence sys.dm_exec_query_statistics_xml, vous devez vérifier que le correctif KB 4078596 est installé dans SQL Server 2016 (13.x)SQL Server 2016 (13.x) et SQL Server 2017 (14.x)SQL Server 2017 (14.x).Due to a possible random AV while executing a monitoring stored procedure that references sys.dm_exec_query_statistics_xml, ensure KB 4078596 is installed in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).

À compter du profilage léger v2 et de sa faible surcharge, tout serveur qui n’est pas encore lié à l’UC peut exécuter le profilage léger de manière continue, ce qui permet aux spécialistes des bases de données d’explorer toute exécution en cours à tout moment, par exemple à l’aide du Moniteur d’activité ou en interrogeant directement sys.dm_exec_query_profiles, et d’obtenir le plan de requête avec les statistiques d’exécution.Starting with lightweight profiling v2 and its low overhead, any server that is not already CPU bound can run lightweight profiling continuously, and allow database professionals to tap into any running execution at any time, for example using Activity Monitor or directly querying sys.dm_exec_query_profiles, and get the query plan with runtime statistics.

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.For more information on the performance overhead of query profiling, see the blog post Developers Choice: Query progress - anytime, anywhere.

Notes

Les événements étendus avec profilage léger utilisent les informations du profilage standard quand l’infrastructure de celui-ci est déjà activée.Extended Events that leverage lightweight profiling will use information from standard profiling in case the standard profiling infrastructure is already enabled. 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.For example, an extended event session using query_post_execution_showplan is running, and another session using query_post_execution_plan_profile is started. La deuxième session continuera d’utiliser les informations du profilage standard.The second session will still use information from standard profiling.

 Voir aussiSee Also

Surveiller et optimiser les performances Monitor and Tune for Performance
Outils de surveillance et d’optimisation des performances Performance Monitoring and Tuning Tools
Ouvrir le Moniteur d’activité (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
Moniteur d’activité Activity Monitor
Analyse des performances à l'aide du magasin de requêtes Monitoring Performance By Using the Query Store
Superviser l’activité système à l’aide d’événements étendus Monitor System Activity Using Extended Events
sys.dm_exec_query_statistics_xml sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles sys.dm_exec_query_profiles
Indicateurs de trace Trace flags
Guide de référence des opérateurs Showplan logiques et physiques Showplan Logical and Physical Operators Reference
Plan d’exécution réel actual execution plan
Statistiques des requêtes dynamiques Live Query Statistics
Developers Choice: Query progress - anytime, anywhereDevelopers Choice: Query progress - anytime, anywhere