sys.dm_exec_query_plan (Transact-SQL)

S’applique à : SQL ServerAzure SQL Database Azure SQL Managed Instance

Retourne le plan d'exécution de requêtes au format XML pour le traitement spécifié par le descripteur de 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

Syntaxe

sys.dm_exec_query_plan(plan_handle)  

Arguments

plan_handle
Est 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).

Le plan_handle peut être obtenu à partir des objets de gestion dynamiques 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

Notes

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

• Si le plan de requête spécifié au moyen de l'argument plan_handle a été supprimé du cache dus plan, la colonne query_plan de la table retournée est Null. Par exemple, cette condition peut se produire s’il existe un délai entre le moment où le handle de plan a été capturé et lorsqu’il a été utilisé 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 showplans XML pour ces instructions ne peuvent pas être récupérés à l’aide de sys.dm_exec_query_plan , sauf si le lot est en cours d’exécution, car il n’existe pas dans le cache.

• Si un lot Transact-SQL ou une procédure stockée contient un appel à une fonction définie par l’utilisateur ou à un appel à SQL dynamique, par exemple à l’aide de 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 le lot ou la procédure stockée. Au lieu de cela, vous devez effectuer un appel distinct à sys.dm_exec_query_plan pour le handle de plan qui correspond à la fonction définie par l’utilisateur.

Lorsqu’une requête ad hoc utilise un paramétrage simple ou forcé, la colonne query_plan contient uniquement 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 pour le handle de plan de la requête paramétrable préparée. Vous pouvez déterminer si la requête a été paramétrée en référençant la colonne sql de la vue sys.syscacheobjects ou la colonne texte de la vue de gestion dynamique sys.dm_exec_sql_text.

autorisations

Pour exécuter sys.dm_exec_query_plan, un utilisateur doit être membre du rôle serveur fixe sysadmin ou disposer de l’autorisation VIEW SERVER STATE sur le serveur.

Autorisations pour SQL Server 2022 (et versions plus récentes)

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

Exemples

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

Pour afficher les plans d’affichage XML, exécutez les requêtes suivantes dans le Éditeur de requête de SQL Server Management Studio, puis cliquez sur ShowPlanXML dans la colonne query_plan de la table retournée par sys.dm_exec_query_plan. Le plan d’affichage XML s’affiche dans le volet résumé de Management Studio. Pour enregistrer le plan d’affichage XML dans un fichier, cliquez avec le bouton droit sur ShowPlanXML dans la colonne query_plan, cliquez sur Enregistrer les résultats sous, nommez le fichier au 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 vue de gestion dynamique sys.dm_exec_query_plan pour récupérer le plan d’exécution d’une requête Ou d’un lot 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.

Récupérez tout d'abord à l'aide de la procédure stockée sp_who l'ID de processus serveur (SPID) pour le processus exécutant la requête ou le traitement.

USE master;  
GO  
exec sp_who;  
GO  

Le jeu de résultats renvoyé par sp_who indique que le SPID est 54. Utilisez cet identificateur avec la vue de gestion dynamique sys.dm_exec_requests pour récupérer le descripteur de plan via la requête suivante :

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

La table retournée par sys.dm_exec_requests indique que le handle de plan pour la requête ou le lot en cours d’exécution lente est 0x06000100A27E7C1FA821B10600, que vous pouvez spécifier comme argument plan_handle avec sys.dm_exec_query_plan lequel 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 en cours d’exécution lent est contenu dans la colonne query_plan de la table retournée par sys.dm_exec_query_plan.

USE master;  
GO  
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.

USE master;  
GO  
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.

USE master;  
GO  
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  

Voir aussi

Fonctions et vues de gestion dynamique (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
Guide de référence des opérateurs Showplan logiques et physiques
sys.dm_exec_text_query_plan (Transact-SQL)