sys.dm_exec_query_plan (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

La sys.dm_exec_query_plan fonction de gestion dynamique renvoie le plan de présentation au format XML pour le lot spécifié par le handle du plan. Le plan spécifié par le descripteur de plan peut être en cache ou en cours d'exécution.

Le schéma XML du plan d’exécution est publié et disponible sur ce site Web Microsoft. Il est également disponible dans le répertoire où SQL Server est installé.

Conventions de la syntaxe Transact-SQL

Syntax

sys.dm_exec_query_plan(plan_handle)

Arguments

plan_handle

Un jeton qui identifie de façon univoque un plan d’exécution de requête pour un lot exécuté ; ce plan réside dans la mémoire cache des plans ou est en cours d’exécution. plan_handle est varbinary(64).

Vous plan_handle pouvez obtenir les objets de gestion dynamique suivants :

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

Table retournée

Remarques

Dans les conditions suivantes, aucune sortie Showplan n'est retournée dans la query_plan colonne de la table retournée pour sys.dm_exec_query_plan:

• Si le plan de requête spécifié par using plan_handle a été évacué du cache du plan, la query_plan colonne de la table retournée est NULL. Par exemple, cette condition peut survenir s’il y a un délai entre la capture de la poignée de plan et celle de son utilisation avec sys.dm_exec_query_plan

• Certaines instructions Transact-SQL ne sont pas mises en cache, par exemple les instructions d'opérations en bloc ou les instructions contenant des littéraux de chaîne dont la taille est supérieure à 8 Ko. Les plans XML pour ces instructions ne peuvent pas être récupérés en utilisant sys.dm_exec_query_plan à moins que le lot ne soit actuellement en cours d’exécution car elles n’existent pas dans le cache.

• Si une procédure Transact-SQL batch ou stockée contient un appel à une fonction définie par l’utilisateur ou un appel à SQL dynamique, par exemple en utilisant EXEC (chaîne), le Showplan XML compilé pour la fonction définie par l’utilisateur n’est pas inclus dans la table retournée par sys.dm_exec_query_plan pour la procédure batch ou stockée. À la place, vous devez effectuer un appel séparé à sys.dm_exec_query_plan pour le handle du plan correspondant à la fonction définie par l’utilisateur.

Lorsqu’une requête ad hoc utilise une paramétrisation simple ou forcée, la query_plan colonne ne contiendra que le texte de l’instruction et non le plan de requête réel. Pour retourner le plan de requête, appelez sys.dm_exec_query_plan le handle plan de la requête paramétrée préparée. Vous pouvez déterminer si la requête a été paramétrée en référant la sql colonne de la vue sys.syscacheobjects ou la colonne texte de la vue de gestion dynamique sys.dm_exec_sql_text .

Permissions

Pour exécuter sys.dm_exec_query_plan, un utilisateur doit être membre du rôle serveur fixe sysadmin ou avoir la VIEW SERVER STATE permission sur le serveur.

Autorisations pour SQL Server 2022 et versions ultérieures

Nécessite l'autorisation VIEW SERVER PERFORMANCE STATE sur le serveur.

Examples

Les exemples suivants montrent comment utiliser la sys.dm_exec_query_plan vue de gestion dynamique.

Pour visualiser les plans XML de présentation, exécutez les requêtes suivantes dans la Éditeur de requête de SQL Server Management Studio, puis sélectionnez ShowPlanXML dans la query_plan colonne du tableau retourné par sys.dm_exec_query_plan Le plan de présentation XML affiché dans le volet résumé du Management Studio. Pour enregistrer le plan de présentation XML dans un fichier, cliquez droit sur ShowPlanXML dans la query_plan colonne, sélectionnez Enregistrer les résultats comme, nommer le fichier dans le format <file name>.sqlplan; par exemple, MyXMLShowplan.sqlplan.

A. Récupération du plan de requête mis en mémoire cache pour un traitement ou une requête Transact-SQL à exécution lente

Les plans de requête pour différents types de lots Transact-SQL, tels que les lots ad hoc, les procédures stockées et les fonctions définies par l’utilisateur, sont mis en cache dans une zone de mémoire appelée cache du plan. Chaque plan de requête mis dans cette mémoire cache est différencié par un identificateur unique appelé descripteur de plan. Vous pouvez spécifier ce handle de plan avec la sys.dm_exec_query_plan vue de gestion dynamique pour récupérer le plan d’exécution pour une requête ou un lot de Transact-SQL particulier.

Si une requête ou un lot Transact-SQL s'exécute longtemps sur une connexion à SQL Server spécifique, récupérez le plan d'exécution de cette requête ou de ce lot pour trouver la raison de ce retard. L'exemple suivant montre la récupération du plan d'exécution de requêtes XML pour une requête ou un traitement s'exécutant lentement.

D’abord, récupérez l’ID de session (SPID) du processus qui exécute la requête ou le lot en utilisant la sp_who procédure stockée :

exec sp_who;  
GO  

L’ensemble de résultats retourné par sp_who indique que l’identifiant de session est 54. Vous pouvez utiliser l’ID de session avec la sys.dm_exec_requests vue de gestion dynamique pour récupérer le handle de plan en utilisant la requête suivante :

SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

Le tableau renvoyé par sys.dm_exec_requests indique que le handle plan pour la requête ou le lot à cours lent est 0x06000100A27E7C1FA821B10600, que vous pouvez spécifier comme plan_handle argument pour sys.dm_exec_query_plan récupérer le plan d’exécution au format XML comme suit. Le plan d’exécution au format XML pour la requête ou le lot à exécution lente est contenu dans la query_plan colonne de la table retournée par sys.dm_exec_query_plan.

SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B. Récupérer chaque plan de requête à partir du cache du plan

Pour récupérer un instantané de tous les plans de requête résidant dans la mémoire cache des plans, procurez-vous les descripteurs de tous les plans de requête dans la mémoire cache via une requête dans la vue de gestion dynamique sys.dm_exec_cached_plans. Les descripteurs de plan sont stockés dans la colonne plan_handle de sys.dm_exec_cached_plans. Utilisez ensuite l'opérateur CROSS APPLY pour transmettre les descripteurs à sys.dm_exec_query_plan comme suit. La sortie du plan d'exécution de requêtes XML pour chaque plan actuellement dans la mémoire cache des plans se trouve dans la colonne query_plan de la table retournée.

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C. Récupération dans la mémoire cache des plans de chaque plan de requête pour lequel le serveur a regroupé des statistiques de requête

Pour récupérer un instantané de tous les plans de requête pour lesquels le serveur a regroupé des statistiques actuellement dans la mémoire cache des plans, procurez-vous les descripteurs de ces plans dans la mémoire cache via une requête formulée dans la vue de gestion dynamique sys.dm_exec_query_stats. Les descripteurs de plan sont stockés dans la colonne plan_handle de sys.dm_exec_query_stats. Utilisez ensuite l'opérateur CROSS APPLY pour transmettre les descripteurs à sys.dm_exec_query_plan comme suit. La sortie du plan d'exécution de requêtes XML pour chaque plan pour lequel le serveur a regroupé des statistiques actuellement dans la mémoire cache des plans se trouve dans la colonne query_plan de la table retournée.

SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. Récupération d'informations sur les cinq premières requêtes d'après le temps processeur moyen

L'exemple suivant retourne les plans et le temps processeur moyen pour les cinq premières requêtes.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
   plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

Contenu connexe

• Vues et fonctions de gestion dynamique du système
• sys.dm_exec_cached_plans (Transact-SQL)
• sys.dm_exec_query_stats (Transact-SQL)
• sys.dm_exec_requests (Transact-SQL)
• sys.sp_who (Transact-SQL)
• Référence des opérateurs de plan d'exécution logique et physique
• sys.dm_exec_text_query_plan (Transact-SQL)