sys.dm_exec_text_query_plan (Transact-SQL)sys.dm_exec_text_query_plan (Transact-SQL)

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

Retourne le plan d'exécution de requêtes au format texte pour un lot Transact-SQLTransact-SQL ou pour une instruction spécifique dans le lot.Returns the Showplan in text format for a Transact-SQLTransact-SQL batch or for a specific statement within the batch. Le plan de requête spécifié par le descripteur de plan peut être mis en cache ou en cours d’exécution.The query plan specified by the plan handle can either be cached or currently executing. Cette fonction table est similaire à sys.dm_exec_query_plan ( Transact-SQL ) , mais présente les différences suivantes :This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences:

  • La sortie du plan de requête est au format texte.The output of the query plan is returned in text format.

  • La taille de cette sortie n'est pas limitée.The output of the query plan is not limited in size.

  • Les instructions individuelles dans le traitement peuvent être spécifiées.Individual statements within the batch can be specified.

S’applique à: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 jusqu’à la version actuelle), Azure SQL DatabaseAzure SQL Database.Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through current version), Azure SQL DatabaseAzure SQL Database.

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

SyntaxeSyntax

sys.dm_exec_text_query_plan   
(   
    plan_handle   
    , { statement_start_offset | 0 | DEFAULT }  
        , { statement_end_offset | -1 | DEFAULT }  
)  

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

Le descripteur de plan peut être obtenu à partir des objets de gestion dynamiques suivants :The plan handle can be obtained from the following dynamic management objects:

statement_start_offset | 0 | PAR DÉFAUTstatement_start_offset | 0 | DEFAULT
Indique, en octets, la position de début de la requête que la ligne décrit dans le texte de son traitement ou de son objet persistant.Indicates, in bytes, the starting position of the query that the row describes within the text of its batch or persisted object. statement_start_offset est int. La valeur 0 indique le début du traitement.statement_start_offset is int. A value of 0 indicates the beginning of the batch. La valeur par défaut est 0 :The default value is 0.

Le décalage de début de l'instruction peut être obtenu à partir des objets de gestion dynamiques suivants :The statement start offset can be obtained from the following dynamic management objects:

statement_end_offset | -1 | PAR DÉFAUTstatement_end_offset | -1 | DEFAULT
Indique, en octets, la position de fin de la requête que la ligne décrit dans le texte de son traitement ou de son objet persistant.Indicates, in bytes, the ending position of the query that the row describes within the text of its batch or persisted object.

statement_start_offset est int.statement_start_offset is int.

La valeur -1 indique la fin du traitement.A value of -1 indicates the end of the batch. La valeur par défaut est -1.The default value is -1.

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 nvarchar(max)nvarchar(max) 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 Showplan est au format texte.The Showplan is in text 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.

Notes Remarks

Dans les conditions suivantes, aucune sortie Showplan est retournée dans le plan colonne de la table retournée pour sys.dm_exec_text_query_plan:Under the following conditions, no Showplan output is returned in the plan column of the returned table for sys.dm_exec_text_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_text_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_text_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. Plans d’exécution pour de telles instructions ne peuvent pas être récupérées à l’aide de sys.dm_exec_text_query_plan car ils n’existent pas dans le cache.Showplans for such statements cannot be retrieved by using sys.dm_exec_text_query_plan 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_text_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_text_query_plan for the batch or stored procedure. Vous devez procéder à un appel séparé à sys.dm_exec_text_query_plan pour le plan_handle qui correspond à la fonction définie par l’utilisateur.Instead, you must make a separate call to sys.dm_exec_text_query_plan for the plan_handle that corresponds to the user-defined function.

Lorsqu’une requête ad hoc utilise simple ou paramétrage 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_text_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_text_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.

AutorisationsPermissions

Pour exécuter sys.dm_exec_text_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_text_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

A.A. Récupération du plan de requête mis en mémoire cache pour un lot ou une requête Transact-SQL à exécution lenteRetrieving the cached query plan for a slow-running Transact-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 pour une requête ou un traitement s'exécutant lentement.The following example shows how to retrieve the 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.The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600. L'exemple suivant retourne le plan de requête pour le descripteur de plan spécifié et utilise les valeurs par défaut 0 et -1 pour retourner toutes les instructions dans la requête ou le lot.The following example returns the query plan for the specified plan handle and uses the default values 0 and -1 to return all statements in the query or batch.

USE master;  
GO  
SELECT query_plan   
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);  
GO  

B.B. Récupération de chaque plan de requête à partir de la mémoire cache des plansRetrieving 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_text_query_plan comme suit.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_text_query_plan as follows. Le plan d’exécution de sortie pour chaque plan actuellement dans le cache du plan se trouve dans le query_plan colonne de la table qui est retournée.The 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 AS cp   
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);  
GO  

C.C. La récupération de chaque plan de requête pour lequel le serveur a regroupé des statistiques de requête du cache du planRetrieving 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_text_query_plan comme suit.Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_text_query_plan as follows. La sortie du plan d'exécution de requêtes pour chaque plan est dans la colonne query_plan de la table retournée.The Showplan output for each plan is in the query_plan column of the table that is returned.

USE master;  
GO  
SELECT * FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);  
GO  

D.D. Récupérer des informations sur les cinq premières requêtes par temps processeur moyenRetrieving information about the top five queries by average CPU time

L'exemple suivant retourne les plans de requête et le temps processeur moyen pour les cinq premières requêtes.The following example returns the query plans and average CPU time for the top five queries. Le sys.dm_exec_text_query_plan fonction spécifie les valeurs par défaut 0 et -1 pour retourner toutes les instructions dans le lot dans le plan de requête.The sys.dm_exec_text_query_plan function specifies the default values 0 and -1 to return all statements in the batch in the query plan.

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_text_query_plan(qs.plan_handle, 0, -1)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

Voir aussiSee Also

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