sys.dm_exec_query_plan_stats (Transact-SQL)

Article
07/15/2024

S’applique à : SQL Server 2019 (15.x) Base de données Azure SQL Azure SQL Managed Instance

Renvoie 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

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 :

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 est 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 est 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 ne pouvant pas accepter la valeur null.
query_plan	xml	Contient la dernière représentation Showplan d’exécution connue du plan d’exécution de la requête qui est 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 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

Il est nécessaire d'accepter cette fonctionnalité. Pour activer au niveau du serveur, utilisez Trace Flag 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êtes légères. 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 au moment de la 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 total de la requête CPU et le temps d’exécution, les avertissements de déversement, 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 exécutée est complexe ou consommatrice de ressources.

Dans les conditions suivantes, une sortie simplifiée de ¹ Showplan 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.

¹ Fait référence à un Showplan qui contient uniquement 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 :

Le plan de requête spécifié à l’aide de plan_handle a été supprimé du cache du plan.

OU
Le plan de requête n’était pas mis en cache en premier lieu. Pour plus d’informations, voir Mise en cache et réutilisation du plan d’exécution.

Remarque

Une limitation du nombre de niveaux imbriqués autorisés dans le type de données xml signifie que sys.dm_exec_query_plan ne peut pas retourner les plans de requête qui répondent ou dépassent 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 (9.x) Service Pack 2 et versions ultérieures, la colonne query_plan retourne NULL.

autorisations

Nécessite 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. Examinez le dernier plan d’exécution de requête réel connu pour un plan mis en cache spécifique

L’exemple suivant illustre l’interrogation de sys.dm_exec_cached_plans pour rechercher le lot souhaité et copier son plan_handle dans 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 la copie avec plan_handle la fonction système sys.dm_exec_query_plan_stats.

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

B. Examinez le 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. Examinez le dernier plan d’exécution de requête réel connu pour un plan mis en cache et un texte de requête spécifiques

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

Partage via