sys.dm_exec_query_plan_stats (Transact-SQL)

s’applique à : SQL Server 2019 et versions ultérieures Azure SQL Database Data Warehouse parallèles Azure Synapse Analytics

Retourne l’équivalent du dernier plan d’exécution réel connu pour un plan de requête précédemment mis en cache.

Syntaxe

sys.dm_exec_query_plan_stats(plan_handle)  

Arguments

plan_handle
Jeton qui identifie de façon unique un plan d’exécution de requête pour un lot exécuté et dont le plan réside dans le cache du plan, ou qui est en cours d’exécution. plan_handle est de type varbinary (64).

Le plan_handle peut être obtenu à partir des objets de gestion dynamique suivants :

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 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.
chiffrées 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 dernière représentation Showplan connue du plan d’exécution de requêtes réel spécifiée 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.

Notes

Il est nécessaire d'accepter cette fonctionnalité. Pour activer au niveau du serveur, utilisez l' indicateur de trace 2451. Pour activer au niveau de la base de données, utilisez l’option LAST_QUERY_PLAN_STATS dans ALTER DATABASE scoped CONFIGURATION (Transact-SQL).

Cette fonction système fonctionne sous l’infrastructure de profilage des statistiques d’exécution de requête légère . Pour plus d’informations, consultez Infrastructure du profilage de requête.

La sortie Showplan de sys.dm_exec_query_plan_stats contient les informations suivantes :

  • Toutes les informations de compilation trouvées dans le plan mis en cache
  • Informations d’exécution telles que le nombre réel de lignes par opérateur, le temps processeur et la durée d’exécution de la requête totale, les avertissements de débordement, le DOP réel, la mémoire maximale utilisée et la mémoire allouée

Dans les conditions suivantes, une sortie Showplan équivalente à un plan d’exécution réel est retournée dans la colonne query_plan de la table retournée pour sys.dm_exec_query_plan_stats :

  • Le plan se trouve dans sys.dm_exec_cached_plans.
    AND
  • La requête en cours d’exécution est complexe ou consommatrice de ressources.

Dans les conditions suivantes, une sortie Showplan simplifiée 1 est retournée dans la colonne query_plan de la table retournée pour sys.dm_exec_query_plan_stats :

  • Le plan se trouve dans sys.dm_exec_cached_plans.
    AND
  • La requête est assez simple, généralement classée dans le cadre d’une charge de travail OLTP.

1 fait référence à une Showplan qui ne contient que l’opérateur de nœud racine (Select).

Dans les conditions suivantes, aucune sortie n’est retournée à partir de sys.dm_exec_query_plan_stats :

Notes

En raison d’une limitation du nombre de niveaux imbriqués autorisés dans le type de données XML , sys.dm_exec_query_plan ne peut pas retourner des plans de requête qui remplissent ou dépassent 128 niveaux d’éléments imbriqués. Dans les versions antérieures de SQL Server , cette condition a empêché le plan de requête de retourner et génère l' erreur 6335. Dans SQL Server 2005 (9.x) Service Pack 2 et versions ultérieures, la query_plan colonne retourne la valeur null.

Autorisations

Nécessite l'autorisation VIEW SERVER STATE sur le serveur.

Exemples

R. Examen du dernier plan d’exécution de requête réel connu pour un plan mis en cache spécifique

L’exemple suivant interroge sys.dm_exec_cached_plans pour rechercher le plan intéressant et copier son plan_handle à partir de la sortie.

SELECT * FROM sys.dm_exec_cached_plans;  
GO  

Ensuite, pour obtenir le dernier plan d’exécution de requête réel connu, utilisez l' plan_handle sys.dm_exec_query_plan_stats copié avec la fonction système.

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);  
GO  

B. Examen du dernier plan d’exécution de requête réel connu pour tous les plans mis en cache

SELECT *   
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;  
GO  

C. Examen du dernier plan d’exécution de requête réel connu pour un plan et un texte de requête spécifiques mis en cache

SELECT *   
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';  
GO  

D. Examiner les événements mis en cache pour le déclencheur

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO

Voir aussi

Indicateurs de trace
Fonctions et vues de gestion dynamique (Transact-SQL)
Vues de gestion dynamique liées à l’exécution ()Transact-SQL