sys.dm_exec_query_plan (Transact-SQL)

 

CETTE RUBRIQUE S’APPLIQUE À :ouiSQL Server (à partir de la version 2008)nonAzure SQL DatabasenonAzure SQL Data WarehousenonParallel Data Warehouse

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 pour le plan d’exécution est publié et disponible à l’adresse ce site Web Microsoft. Vous le trouverez également dans le répertoire d'installation de SQL Server.

Topic link icon Conventions de la syntaxe Transact-SQL

  
sys.dm_exec_query_plan ( plan_handle )  

plan_handle
Identifie de façon univoque un plan de requête pour un traitement en cache ou en cours d'exécution.

plan_handle est varbinary(64). plan_handle peut être obtenu à partir d’objets de gestion dynamique suivants :

Sys.dm_exec_cached_plans

Sys.dm_exec_query_stats

Sys.dm_exec_requests

Nom de colonneType de donnéesDescription
dbidsmallintID de la base de données de contexte qui était en fonction lorsque l'instruction Transact-SQL correspondant à ce plan a été compilée. Pour les instructions SQL ad hoc et préparées, l'ID de la base de données où les instructions ont été compilées.

Colonne acceptant la valeur NULL.
ObjectIDintID de l'objet (par exemple, procédure stockée ou fonction définie par l'utilisateur) pour ce plan de requête. Pour les traitements ad hoc et préparées, cette colonne est null.

Colonne acceptant la valeur NULL.
nombresmallintEntier servant à la numérotation des procédures stockées. Par exemple, un groupe de procédures pour le commandes application peut être nommée orderproc ;&1;, orderproc ;&2;, et ainsi de suite. Pour les traitements ad hoc et préparées, cette colonne est null.

Colonne acceptant la valeur NULL.
chiffrébitsIndique si la procédure stockée correspondante est chiffrée.

0 = Non chiffrée.

1 = Chiffrée.

Colonne n'acceptant pas la valeur NULL.
query_planxmlContient la représentation sous forme de plan d’exécution lors de la compilation du plan de l’exécution de requête est spécifié avec plan_handle. Le plan d'exécution de requêtes est au format XML. Un plan est généré pour chaque traitement contenant par exemple des instructions Transact-SQL ad hoc, des appels de procédures stockées et des appels de fonctions définies par l'utilisateur.

Colonne acceptant la valeur NULL.

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

  • Si le plan de requête est spécifié à l’aide de plan_handle a été supprimé du cache de plan, le query_plan colonne 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 descripteur de plan est capturé et lorsqu’il a été utilisé avec sys.dm_exec_query_plan.

  • Certaines instructions Transact-SQL ne sont pas mises en mémoire 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. Impossible de récupérer à l’aide de plans de requête XML pour de telles instructions sys.dm_exec_query_plan , sauf si le lot en cours d’exécution, car ils n’existent pas dans le cache.

  • Si un Transact-SQL lot ou une procédure stockée contient un appel à une fonction définie par l’utilisateur ou un appel de code SQL dynamique, par exemple à l’aide de EXEC (chaîne), le compilés Showplan XML pour la fonction définie par l’utilisateur n’est pas incluse dans le tableau retourné par sys.dm_exec_query_plan pour la procédure stockée ou le lot. Vous devez procéder à un appel séparé à sys.dm_exec_query_plan pour le handle de plan correspond à la fonction définie par l’utilisateur.

Lorsqu’une requête ad hoc utilise un paramétrage simple ou forcé, les query_plan colonne contiendra uniquement le texte de l’instruction et pas le plan de requête. 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 le sql colonne de la sys.syscacheobjects vue ou la colonne de texte de la sys.dm_exec_sql_text vue de gestion dynamique.

En raison d’une limitation du nombre de niveaux imbriqués autorisés dans les xml type de données, sys.dm_exec_query_plan ne peut pas retourner des plans de requête qui respectent ou dépassent 128 niveaux d’éléments imbriqués. Dans les versions antérieures de SQL Server, cette condition empêchait les retours par le plan de requête et générait l'erreur 6335. Dans SQL Server 2005 Service Pack 2 et versions ultérieures, le query_plan colonne renvoie la valeur NULL. Vous pouvez utiliser la sys.dm_exec_text_query_plan (Transact-SQL) fonction de gestion dynamique pour retourner la sortie du plan de requête au format texte.

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

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

Pour afficher les plans de requête XML, exécutez les requêtes suivantes dans l’éditeur de requête de SQL Server Management Studio, puis cliquez sur ShowPlanXML dans les query_plan colonne de la table retournée par sys.dm_exec_query_plan. Le plan d'exécution de requêtes XML s'affiche dans le volet de résumé de Management Studio. Pour enregistrer le Showplan XML dans un fichier, cliquez sur ShowPlanXML dans les query_plan colonne, cliquez sur enregistrer les résultats sous, nommez le fichier au format nom_fichier>.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 divers types de lots Transact-SQL, par exemple les procédures stockées, les fonctions définies par l'utilisateur et les lots appropriés, sont mis en cache dans une zone de la mémoire appelée le cache de 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 descripteur avec la sys.dm_exec_query_plan vue de gestion dynamique pour récupérer le plan d’exécution en tant que Transact-SQL requête ou le lot.

Si une requête ou un traitement 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 traitement 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.

System_CAPS_ICON_note.jpg Remarque


Pour exécuter cet exemple, remplacez les valeurs de session_id et plan_handle avec des valeurs spécifiques à votre serveur.

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  

Le tableau retourné par sys.dm_exec_requests indique que le descripteur de plan pour la requête ou le lot à exécution lente est 0x06000100A27E7C1FA821B10600, que vous pouvez spécifier que le plan_handle argument avec sys.dm_exec_query_plan pour 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 se trouve dans le query_plan colonne 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 de 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 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 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  

Vues de gestion dynamique et fonctions (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)
Référence des opérateurs logiques et physiques Showplan
Sys.dm_exec_text_query_plan (Transact-SQL)

Ajouts de la communauté

AJOUTER
Afficher: