Infrastructure du profilage de requête
S’applique à :
SQL ServerAzure SQL Database
Le moteur de base de données SQL Server permet d’accéder 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. 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 en direct affiche la progression globale de la requête et les statistiques d’exécution au niveau de l’opérateur, telles que le nombre de lignes produites, le temps écoulé, la progression de l’opérateur, etc. Étant donné que ces données sont disponibles en temps réel sans avoir besoin d’attendre la fin de la requête, ces statistiques d’exécution sont extrêmement utiles pour le débogage des problèmes de performances des requêtes, tels que les requêtes longues 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 :
Note
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 des statistiques de requête dynamiques 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 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 de profilage des statistiques d’exécution de requêtes légères
À compter de SQL Server 2014 (12.x) SP2 et SQL Server 2016 (13.x), une nouvelle infrastructure de profilage des statistiques d’exécution de requêtes légères 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 (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 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);
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 é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 à 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 et 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 n’importe quelle 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.
Note
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 Azure SQL Database incluent une nouvelle version 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 sur SQL Server 2019 (15.x) et Azure SQL Database. À compter de SQL Server 2019 (15.x), 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 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 sur l’utilisation de l’infrastructure de profilage des requêtes
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 | - |
Remarques
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 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.
Note
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.
Note
Sur SQL Server 2017 (14.x), le profilage léger est désactivé par défaut, mais est activé lorsqu’une trace XEvent s’appuie sur query_post_execution_plan_profile est démarrée, puis désactivée à nouveau lorsque la trace est arrêtée. Par conséquent, si les traces Xevent basées sur query_post_execution_plan_profile des traces sont fréquemment démarrées et arrêtées sur une instance SQL Server 2017 (14.x), il est fortement recommandé d’activer le profilage léger au niveau global avec traceflag 7412 pour éviter la surcharge répétée d’activation/désactivation.
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
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour