Partage via

Infrastructure du profilage de requête

  • Article

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 :

Remarque

Le fait de cliquer sur le bouton Inclure les statistiques de requête en direct dans SQL Server Management Studio tire parti de 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 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.

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 :

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.

Remarque

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

Remarque

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

Remarque

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

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.

Étendue Profilage standard Profilage léger
Global Session XEvent 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 trace Showplan XML; commençant par SQL Server 2000 Session XEvent avec le query_thread_profile XE ; À compter de SQL Server 2014 (12.x) SP2
Global - Session XEvent avec le query_post_execution_plan_profile XE ; À compter de SQL Server 2017 (14.x) CU14 et SQL Server 2019 (15.x)
Session Utiliser SET STATISTICS XML ON; commençant par SQL Server 2000 Utilisez l’indicateur de QUERY_PLAN_PROFILE requête avec une session XEvent avec le query_plan_profile XE ; À compter de SQL Server 2016 (13.x) SP2 CU3 et SQL Server 2017 (14.x) CU11
Session Utiliser SET STATISTICS PROFILE ON; commençant par SQL Server 2000 -
Session Cliquez sur le bouton Statistiques des requêtes actives dans SSMS ; À compter de SQL Server 2014 (12.x) SP2 -

Notes

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

Remarque

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. 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 continuera d’utiliser les informations du profilage standard.

Remarque

Sur SQL Server 2017 (14.x), le profilage léger est désactivé par défaut, mais il est activé quand une trace XEvent basée sur query_post_execution_plan_profile est démarrée, puis est de nouveau désactivée quand la trace est arrêtée. Par conséquent, si les traces XEvent basés sur query_post_execution_plan_profile sont souvent démarrés et arrêtés sur une instance de SQL Server 2017 (14.x), il est vivement recommandé d’activer le profilage léger au niveau global avec l’indicateur de trace 7412 pour éviter la surcharge due à des activations/désactivations répétées.

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