sys.dm_exec_query_plan (Transact-SQL)sys.dm_exec_query_plan (Transact-SQL)

CETTE RUBRIQUE S’APPLIQUE À :ouiSQL Server (à partir de la version 2008)nonAzure SQL DatabasenonAzure SQL Data Warehouse nonParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Retourne le plan d'exécution de requêtes au format XML pour le traitement spécifié par le descripteur de plan.Returns the Showplan in XML format for the batch specified by the plan handle. Le plan spécifié par le descripteur de plan peut être en cache ou en cours d'exécution.The plan specified by the plan handle can either be cached or currently executing.

Le schéma XML pour le Showplan est publié et disponible sur ce site Web Microsoft.The XML schema for the Showplan is published and available at this Microsoft Web site. Vous le trouverez également dans le répertoire d'installation de SQL ServerSQL Server.It is also available in the directory where SQL ServerSQL Server is installed.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax


sys.dm_exec_query_plan ( plan_handle )  

ArgumentsArguments

plan_handleplan_handle
Identifie de façon univoque un plan de requête pour un traitement en cache ou en cours d'exécution.Uniquely identifies a query plan for a batch that is cached or is currently executing.

plan_handle est varbinary(64).plan_handle is varbinary(64). plan_handle peut être obtenu à partir des objets de gestion dynamique suivants :plan_handle can be obtained from the following dynamic management objects:

sys.dm_exec_cached_planssys.dm_exec_cached_plans

sys.dm_exec_query_statssys.dm_exec_query_stats

sys.dm_exec_requestssys.dm_exec_requests

Table retournéeTable Returned

Nom de colonneColumn name Type de donnéesData type DescriptionDescription
dbiddbid smallintsmallint ID de la base de données de contexte qui était en fonction lorsque l'instruction Transact-SQLTransact-SQL correspondant à ce plan a été compilée.ID of the context database that was in effect when the Transact-SQLTransact-SQL statement corresponding to this plan was compiled. 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.For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

Colonne acceptant la valeur NULL.Column is nullable.
ObjectIDobjectid intint ID de l'objet (par exemple, procédure stockée ou fonction définie par l'utilisateur) pour ce plan de requête.ID of the object (for example, stored procedure or user-defined function) for this query plan. Pour les traitements ad hoc et préparées, cette colonne est null.For ad hoc and prepared batches, this column is null.

Colonne acceptant la valeur NULL.Column is nullable.
nombrenumber smallintsmallint Entier servant à la numérotation des procédures stockées.Numbered stored procedure integer. Par exemple, un groupe de procédures pour le commandes application peut-être être appelée orderproc ; 1, orderproc ; 2, et ainsi de suite.For example, a group of procedures for the orders application may be named orderproc;1, orderproc;2, and so on. Pour les traitements ad hoc et préparées, cette colonne est null.For ad hoc and prepared batches, this column is null.

Colonne acceptant la valeur NULL.Column is nullable.
chiffréencrypted bitbit Indique si la procédure stockée correspondante est chiffrée.Indicates whether the corresponding stored procedure is encrypted.

0 = Non chiffrée.0 = not encrypted

1 = Chiffrée.1 = encrypted

Colonne n'acceptant pas la valeur NULL.Column is not nullable.
query_planquery_plan xmlxml Contient 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.Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. Le plan d'exécution de requêtes est au format XML.The Showplan is in XML format. Un plan est généré pour chaque traitement contenant par exemple des instructions Transact-SQLTransact-SQL ad hoc, des appels de procédures stockées et des appels de fonctions définies par l'utilisateur.One plan is generated for each batch that contains, for example ad hoc Transact-SQLTransact-SQL statements, stored procedure calls, and user-defined function calls.

Colonne acceptant la valeur NULL.Column is nullable.

NotesRemarks

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:Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan:

  • Si le plan de requête est spécifié à l’aide de plan_handle a été supprimé du cache du plan, le query_plan colonne de la table retournée est null.If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. Par exemple, ceci 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.For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.

  • Certaines instructions Transact-SQLTransact-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.Some Transact-SQLTransact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. Impossible de récupérer à l’aide de plans d’exécution 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.XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

  • Si un Transact-SQLTransact-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 via la commande EXEC (chaîne), le compilé Showplan XML pour la fonction définie par l’utilisateur n’est pas incluse dans la table retournée par sys.dm_exec_query_plan du lot ou une procédure stockée.If a Transact-SQLTransact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Vous devez procéder à un appel séparé à sys.dm_exec_query_plan pour le descripteur de plan qui correspond à la fonction définie par l’utilisateur.Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.

    Lorsqu’une requête ad hoc utilise un paramétrage simple ou forcé, le query_plan colonne contiendra uniquement le texte de l’instruction et pas le plan de requête.When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. Pour retourner le plan de requête, appelez sys.dm_exec_query_plan pour le descripteur de plan de la requête paramétrable préparée.To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. Vous pouvez déterminer si la requête a été paramétrée en référençant la sql colonne de la sys.syscacheobjects vue ou la colonne de texte de la sys.dm_exec_sql_text vue de gestion dynamique.You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.

    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 correspondent ou sont supérieurs à 128 niveaux d’éléments imbriqués.Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. Dans les versions antérieures de SQL ServerSQL Server, cette condition empêchait les retours par le plan de requête et générait l'erreur 6335.In earlier versions of SQL ServerSQL Server, this condition prevented the query plan from returning and generates error 6335. Dans SQL Server 2005SQL Server 2005 Service Pack 2 et versions ultérieures, le query_plan colonne renvoie la valeur NULL.In SQL Server 2005SQL Server 2005 Service Pack 2 and later versions, the query_plan column returns 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.You can use the sys.dm_exec_text_query_plan (Transact-SQL) dynamic management function to return the output of the query plan in text format.

PermissionsPermissions

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.To execute sys.dm_exec_query_plan, a user must be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission on the server.

ExemplesExamples

Les exemples suivants montrent comment utiliser le sys.dm_exec_query_plan vue de gestion dynamique.The following examples show how to use the sys.dm_exec_query_plan dynamic management view.

Pour afficher les plans de requête XML, exécutez les requêtes suivantes dans l’éditeur de requête de SQL Server Management StudioSQL Server Management Studio, puis cliquez sur ShowPlanXML dans les query_plan colonne de la table retournée par sys.dm_exec_query_plan.To view the XML Showplans, execute the following queries in the Query Editor of SQL Server Management StudioSQL Server Management Studio, then click ShowPlanXML in the query_plan column of the table returned by sys.dm_exec_query_plan. Le plan d'exécution de requêtes XML s'affiche dans le volet de résumé de Management StudioManagement Studio.The XML Showplan displays in the Management StudioManagement Studio summary pane. Pour enregistrer le Showplan XML dans un fichier, avec le bouton droit 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.To save the XML Showplan to a file, right-click ShowPlanXML in the query_plan column, click Save Results As, name the file in the format <file_name>.sqlplan; for example, MyXMLShowplan.sqlplan.

A.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 lenteRetrieve the cached query plan for a slow-running Transact-SQL query or batch

Les plans de requête pour divers types de lots Transact-SQLTransact-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.Query plans for various types of Transact-SQLTransact-SQL batches, such as ad hoc batches, stored procedures, and user-defined functions, are cached in an area of memory called the plan cache. Chaque plan de requête mis dans cette mémoire cache est différencié par un identificateur unique appelé descripteur de plan.Each cached query plan is identified by a unique identifier called a plan handle. 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 pour un particulier Transact-SQLTransact-SQL requête ou le lot.You can specify this plan handle with the sys.dm_exec_query_plan dynamic management view to retrieve the execution plan for a particular Transact-SQLTransact-SQL query or batch.

Si une requête ou un traitement Transact-SQLTransact-SQL s'exécute longtemps sur une connexion à SQL ServerSQL 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.If a Transact-SQLTransact-SQL query or batch runs a long time on a particular connection to SQL ServerSQL Server, retrieve the execution plan for that query or batch to discover what is causing the delay. 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.The following example shows how to retrieve the XML Showplan for a slow-running query or batch.

Note

Pour exécuter cet exemple, remplacez les valeurs de session_id et plan_handle avec des valeurs spécifiques à votre serveur.To run this example, replace the values for session_id and plan_handle with values specific to your server.

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.First, retrieve the server process ID (SPID) for the process that is executing the query or batch by using the sp_who stored procedure:

USE master;  
GO  
exec sp_who;  
GO  

Le jeu de résultats renvoyé par sp_who indique que le SPID est 54.The result set that is returned by sp_who indicates that the SPID is 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 :You can use the SPID with the sys.dm_exec_requests dynamic management view to retrieve the plan handle by using the following query:

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 spécifiez en tant 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.The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600, which you can specify as the plan_handle argument with sys.dm_exec_query_plan to retrieve the execution plan in XML format as follows. Le plan d’exécution au format XML pour la requête ou le lot à exécution lente est contenu dans le query_plan colonne de la table retournée par sys.dm_exec_query_plan.The execution plan in XML format for the slow-running query or batch is contained in the query_plan column of the table returned by sys.dm_exec_query_plan.

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

B.B. Récupération de chaque plan de requête du cache du planRetrieve every query plan from the plan cache

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.To retrieve a snapshot of all query plans residing in the plan cache, retrieve the plan handles of all query plans in the cache by querying the sys.dm_exec_cached_plans dynamic management view. Les descripteurs de plan sont stockés dans la colonne plan_handle de sys.dm_exec_cached_plans.The plan handles are stored in the plan_handle column of sys.dm_exec_cached_plans. Utilisez ensuite l'opérateur CROSS APPLY pour transmettre les descripteurs à sys.dm_exec_query_plan comme suit.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. 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.The XML Showplan output for each plan currently in the plan cache is in the query_plan column of the table that is returned.

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

C.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êteRetrieve every query plan for which the server has gathered query statistics from the plan cache

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.To retrieve a snapshot of all query plans for which the server has gathered statistics that currently reside in the plan cache, retrieve the plan handles of these plans in the cache by querying the sys.dm_exec_query_stats dynamic management view. Les descripteurs de plan sont stockés dans la colonne plan_handle de sys.dm_exec_query_stats.The plan handles are stored in the plan_handle column of sys.dm_exec_query_stats. Utilisez ensuite l'opérateur CROSS APPLY pour transmettre les descripteurs à sys.dm_exec_query_plan comme suit.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. 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.The XML Showplan output for each plan for which the server has gathered statistics currently in the plan cache is in the query_plan column of the table that is returned.

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

D.D. Récupération d'informations sur les cinq premières requêtes d'après le temps processeur moyenRetrieve information about the top five queries by average CPU time

L'exemple suivant retourne les plans et le temps processeur moyen pour les cinq premières requêtes.The following example returns the plans and average CPU time for the top five queries.

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 aussiSee Also

Fonctions et vues de gestion dynamique (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
Sys.dm_exec_query_stats ( Transact-SQL ) sys.dm_exec_query_stats (Transact-SQL)
Sys.dm_exec_requests ( Transact-SQL ) sys.dm_exec_requests (Transact-SQL)
sp_who ( Transact-SQL ) sp_who (Transact-SQL)
Guide de référence des opérateurs Showplan logiques et physiques Showplan Logical and Physical Operators Reference
Sys.dm_exec_text_query_plan ( Transact-SQL )sys.dm_exec_text_query_plan (Transact-SQL)