sys.dm_exec_query_plan_stats (Transact-SQL)

  • Article

S’applique à : SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed Instance

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 qui a été exécuté et son plan réside dans le cache du plan ou qui 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 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.

La colonne n’est pas nullable.
query_plan xml Contient la dernière représentation showplan du runtime connu du plan d’exécution de requête réel spécifié 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.

Remarques

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êtes légères . Pour plus d’informations, consultez Infrastructure du profilage de requête.

La sortie sys.dm_exec_query_plan_stats showplan 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, la durée totale du processeur de requête et le temps d’exécution, les avertissements de déversement, la mémoire réelle DOP, la mémoire maximale utilisée et la mémoire accordée

Dans les conditions suivantes, une sortie showplan équivalente à un plan d’exécution réel est retournée dans la query_plan colonne 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 showplan simplifiée1 est retournée dans la query_plan colonne 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 à 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 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, consultez Mise en cache et réutilisation du plan d’exécution.

Note

Une limitation du nombre de niveaux imbriqués autorisés dans le type de données xml signifie que sys.dm_exec_query_plan vous ne pouvez 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 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 NULL.

Autorisations

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

Autorisations pour SQL Server 2022 et versions ultérieures

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

Examples

A. Examinez le 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 le plan intéressant et le copie 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 la copie avec plan_handle la fonction sys.dm_exec_query_plan_statssystème.

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