Infrastructure du profilage de requête

S’applique à : SQL Server Azure SQL Database

Le moteur de base de données SQL Server offre la possibilité d’accéder aux informations d’exécution sur les plans d’exécution de requête. 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. Pour cela, 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 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. 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 :

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Statistiques des requêtes dynamiques

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 :

• L’événement étendu query_post_execution_showplan. Pour activer les événements étendus, consultez Monitor System Activity Using Extended Events.
• 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 é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. 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.

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

S’applique à : SQL Server (SQL Server 2014 (12.x) SP2 through 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 d’informations sur l’exécution de l’UC. Toutefois, le profilage léger collecte toujours les informations sur le nombre de lignes et l’utilisation des E/S.

Un nouvel événement étendu query_thread_profile reposant sur le profilage léger a également été introduit. 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 ci-dessous :

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

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.

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

S’applique à : SQL Server (SQL Server 2016 (13.x) SP1 through 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. 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 à. 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 and 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. 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.

Un exemple de session avec l’événement étendu query_plan_profile peut être configuré comme dans l’exemple ci-dessous :

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 (à compter de SQL Server 2019 (15.x)) 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. À compter de SQL Server 2019 (15.x), l’indicateur de trace 7412 n’a pas d’effet. 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;.

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

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. SQL Server 2017 (14.x) propose également cet événement à partir de CU14. Un exemple de session avec l’événement étendu query_post_execution_plan_profile peut être configuré comme dans l’exemple ci-dessous :

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.

Notes

À 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.

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.

Voir aussi

Surveillance et réglage des performances
Outils de surveillance et de réglage des performances
Ouvrir le Moniteur d’activité (SQL Server Management Studio)
Moniteur d’activité
Analyse des performances à l’aide du magasin de requêtes
Superviser l’activité système à l’aide d’événements étendus
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Indicateurs de trace
Guide de référence des opérateurs Showplan logiques et physiques
Plan d’exécution réel
Statistiques des requêtes dynamiques