sys.dm_exec_text_query_plan (Transact-SQL)

Article
07/05/2024

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

Retourne le plan d'exécution de requêtes au format texte pour un lot Transact-SQL ou pour une instruction spécifique dans le lot. Le plan de requête spécifié par le handle de plan peut être mis en cache ou en cours d’exécution. Cette fonction table est similaire à sys.dm_exec_query_plan (Transact-SQL), mais présente les différences suivantes :

La sortie du plan de requête est au format texte.
La taille de cette sortie n'est pas limitée.
Les instructions individuelles dans le traitement peuvent être spécifiées.

S’applique à : SQL Server (SQL Server 2008 (10.0.x) et versions ultérieures), Azure SQL Database.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

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 :

statement_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. statement_start_offset est int. Une valeur 0 indique le début du traitement. La valeur par défaut est 0.

Le décalage de début de l'instruction peut être obtenu à partir des objets de gestion dynamiques suivants :

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

statement_start_offset est int.

La valeur -1 indique la fin du traitement. La valeur par défaut est -1.

Table retournée

Nom de la colonne	Type de données	Description
dbid	smallint	ID 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.
objectid	int	ID de l'objet (par exemple, procédure stockée ou fonction définie par l'utilisateur) pour ce plan de requête. Pour les lots ad hoc et préparés, cette colonne a la valeur Null. Colonne acceptant la valeur NULL.
numéro	smallint	Entier servant à la numérotation des procédures stockées. Par exemple, un groupe de procédures pour l’application de commandes peut être nommé orderproc ; 1, orderproc ; 2, et ainsi de suite. Pour les lots ad hoc et préparés, cette colonne a la valeur Null. Colonne acceptant la valeur NULL.
chiffré	bit	Indique 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_plan	nvarchar(max)	Contient la représentation du plan d'exécution des requêtes au moment de la compilation du plan d'exécution de requêtes qui est spécifié à l'aide de l'argument plan_handle. Le Showplan est au format texte. Un plan est généré pour chaque lot 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.

Notes

Dans les conditions suivantes, aucune sortie showplan n’est retournée dans la colonne de plan de la table retournée pour sys.dm_exec_text_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_text_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 d’affichage de ces instructions ne peuvent pas être récupérés à l’aide de sys.dm_exec_text_query_plan , car ils n’existent pas dans le cache.
Si une procédure stockée ou un lot Transact-SQL contient un appel à une fonction définie par l'utilisateur ou un appel à SQL dynamique, par exemple via EXEC (chaîne), le plan d'exécution XML compilé pour cette fonction définie par l’utilisateur n'est pas inclus dans la table retournée par sys.dm_exec_text_query_plan pour la procédure stockée ou le lot. Vous devez donc procéder à un appel distinct à sys.dm_exec_query_plan pour plan_handle correspondant à 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, pas le plan de requête réel. Pour retourner le plan de requête, appelez sys.dm_exec_text_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_text_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

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

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 pour une requête ou un traitement s'exécutant lentement.

Remarque

Pour exploiter cet exemple, remplacez les valeurs de session_id et plan_handle par 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

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

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

B. Récupération de chaque plan de requête à partir de la mémoire cache des plans

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_text_query_plan comme suit. La sortie du plan d'exécution de requêtes pour chaque plan actuellement dans le cache du plan 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_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);  
GO

C. Récupération dans le cache du plan 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_text_query_plan comme suit. La sortie du plan d'exécution de requêtes pour chaque plan est 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_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);  
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 de requête et le temps processeur moyen pour les cinq premières requêtes. La fonction sys.dm_exec_text_query_plan spécifie les valeurs par défaut 0 et -1 pour renvoyer toutes les instructions du lot dans le plan de requête.

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 aussi

sys.dm_exec_query_plan (Transact-SQL)

Partager via