Partager via

  • Article

sys.dm_exec_query_plan (Transact-SQL)

Retourne le Showplan 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 Showplan est publié et disponible sur ce site Web de Microsoft. Vous le trouverez également dans le répertoire d'installation de SQL Server 2008.

Icône Lien de rubriqueConventions de la syntaxe de Transact-SQL

Syntaxe

sys.dm_exec_query_plan ( plan_handle )

Arguments

Table renvoyé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 traitements ad hoc et préparés, cette colonne est null.

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 traitements ad hoc et préparés, cette colonne est null.

Colonne acceptant la valeur NULL.

number

smallint

Entier servant à la numérotation des procédures stockées. Par exemple, un groupe de procédures pour l'application orders peuvent être appelées orderproc;1, orderproc;2, etc. Pour les traitements ad hoc et préparés, cette colonne est null.

Colonne acceptant la valeur NULL.

encrypted

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

xml

Contient la représentation du plan d'exécution (Showplan) à la compilation du plan d'exécution de requête qui est spécifié à l'aide de l'argument plan_handle. Le plan d'exécution 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.

Notes

Aucune sortie du plan d'exécution n'est retournée dans la colonne query_plan de la table retournée pour l'objet pour sys.dm_exec_query_plan dans les conditions suivantes :

  • Si le plan de requête spécifié au moyen de l'argument plan_handle a été supprimé de la mémoire cache des plans, la valeur de la colonne query_plan de la table retournée est null. Ceci peut se produire par exemple s'il existe un délai entre le moment où le descripteur de plan est capturé et le moment de son utilisation 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. Il est impossible de récupérer pour de telles instructions des plans d'exécution XML à l'aide de sys.dm_exec_query_plan sauf si le traitement est en cours d'exécution car elles n'existent pas dans la mémoire cache.

  • Si une procédure stockée ou un traitement Transact-SQL contient un appel à une fonction définie par l'utilisateur ou un appel à du code SQL dynamique, par exemple via la commande EXEC (string), 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_query_plan pour la procédure stockée ou le traitement. Vous devez donc procéder à un appel distinct à sys.dm_exec_query_plan pour le descripteur de plan 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_query_plan pour le descripteur de plan de la requête paramétrée préparée. Vous pouvez déterminer si la requête a été paramétrée par le référencement de la colonne sql de la vue sys.syscacheobjects ou le texte de colonne de la vue de gestion dynamique sys.dm_exec_sql_text. Pour plus d'informations sur le paramétrage, consultez Paramétrage simple et Paramétrage forcé.

En raison d'une limite dans le nombre autorisé des niveaux imbriqués dans le type de données xml, 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. 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, la colonne query_plan retourne NULL. Vous pouvez faire appel à la fonction de gestion dynamique sys.dm_exec_text_query_plan (Transact-SQL) pour retourner la sortie du plan de requête au format texte.

Autorisations

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

Exemples

Les exemples suivants montrent l'utilisation de la vue de gestion dynamique sys.dm_exec_query_plan.

Pour afficher des plans d'exécution XML, exécutez les requêtes suivantes dans l'éditeur de requêtes de SQL Server Management Studio, puis cliquez sur ShowPlanXML dans la colonne query_plan de la table renvoyée par l'objet sys.dm_exec_query_plan. Le plan d'exécution XML s'affiche dans le volet de résumé de Management Studio. Pour enregistrer le plan d'exécution XML dans un fichier, cliquez avec le bouton droit de la souris sur ShowPlanXML dans la colonne query_plan, sélectionnez Enregistrer les résultats sous, puis nommez le fichier selon le 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 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. Il est possible d'utiliser ce descripteur 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 traitement Transact-SQL particulier.

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

Notes

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

L'ensemble 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 qui est renvoyée par sys.dm_exec_requests indique que le descripteur de plan pour la requête ou le traitement à exécution lente est 0x06000100A27E7C1FA821B10600. En spécifiant cette valeur pour l'argument plan_handle avec sys.dm_exec_query_plan, vous pouvez 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 traitement à exécution lente se trouve dans la colonne query_plan de la table renvoyée par sys.dm_exec_query_plan.

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

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

Pour récupérer une capture instantanée 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 XML pour chaque plan actuellement dans la mémoire cache des plans se trouve dans la colonne query_plan de la table renvoyé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 une capture instantanée 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 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 renvoyé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