sys.dm_exec_cached_plans (Transact-SQL)

Article
12/19/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Retourne une ligne pour chaque plan de requête mis en cache par SQL Server pour accélérer l’exécution des requêtes. Vous pouvez faire appel à cette vue de gestion dynamique pour rechercher des plans de requête en cache, du texte de requête en cache, la quantité de mémoire occupée par les plans en cache et le nombre de réutilisations de ces plans.

Dans Azure SQL Database, les vues de gestion dynamique ne peuvent pas exposer les informations susceptibles d’avoir un impact sur le contenu de la base de données ou d’exposer des informations sur d’autres bases de données auxquelles l’utilisateur a accès. Pour éviter d’exposer ces informations, chaque ligne qui contient des données qui n’appartiennent pas au locataire connecté est filtrée. En outre, les valeurs des colonnes memory_object_address et pool_id sont filtrées ; la valeur de colonne est définie sur NULL.

Remarque

Pour appeler cela à partir d’Azure Synapse Analytics ou du système de plateforme Analytics (PDW), utilisez le nom sys.dm_pdw_nodes_exec_cached_plans. Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Nom de la colonne	Type de données	Description
bucketid	int	ID du compartiment de hachage dans lequel l'entrée est en cache. La valeur indique une plage comprise entre 0 et la taille de la table de hachage pour le type du cache. Pour les caches Plans d'objets et Plans SQL, la taille de la table de hachage peut atteindre 10 007 sur des systèmes 32 bits et 40 009 sur des systèmes 64 bits. Pour le cache Arborescences liées, la taille de la table de hachage peut faire jusqu'à 1 009 sur des systèmes 32 bits et 4 001 sur des systèmes 64 bits. Pour le cache Procédures stockées étendues, la taille de la table de hachage peut faire jusqu'à 127 sur des systèmes 32 bits et 64 bits.
refcounts	int	Nombre d'objets du cache faisant référence à cet objet. Les refcounts doivent être au moins 1 pour qu’une entrée soit dans le cache.
usecounts	int	Nombre de fois que l'objet du cache a été trouvé. Non incrémenté lorsque les requêtes paramétrables recherchent un plan dans le cache. Peut être incrémenté plusieurs fois lors de l'utilisation du plan d'exécution de requêtes.
size_in_bytes	int	Nombre d'octets mobilisés par l'objet dans le cache.
memory_object_address	varbinary(8)	Adresse mémoire de l'entrée en cache. Cette valeur peut être utilisée avec sys.dm_os_memory_objects pour obtenir la répartition de la mémoire du plan mis en cache et avec sys.dm_os_memory_cache_entries_entries pour obtenir le coût de mise en cache de l’entrée.
cacheobjtype	nvarchar(34)	Type d'objet dans le cache. Il peut s'agir de l'une des valeurs suivantes : Compiled Plan (plan compilé) Compiled Plan Stub (stub du plan compilé) Parse Tree (arborescence d'analyse) Extended Proc (procédure étendue) CLR Compiled Func (fonction compilée CLR) CLR Compiled Proc (procédure compilée CLR)
objtype	nvarchar(16)	Type d'objet. Vous trouverez ci-dessous les valeurs possibles et leurs descriptions correspondantes. Proc : Procédure stockée Préparé : instruction Préparée Adhoc : requête ad hoc. Fait référence à Transact-SQL envoyé en tant qu’événements de langage à l’aide d’osql ou sqlcmd au lieu d’appels de procédure distante. ReplProc : Replication-filter-procedure Trigger : déclencheur Affichage : Affichage Valeur par défaut : valeur par défaut UsrTab : Table utilisateur SysTab : Table système Vérifier : contrainte CHECK Règle : Règle
plan_handle	varbinary(64)	Identificateur du plan en mémoire. Cet identificateur est temporaire et il reste constant uniquement tant que le plan est dans le cache. Cette valeur peut être utilisée avec les fonctions de gestion dynamique suivantes : sys.dm_exec_sql_text sys.dm_exec_query_plan sys.dm_exec_plan_attributes
pool_id	int	ID du pool de ressources par rapport auquel cette utilisation de la mémoire de plan est prise en compte.
pdw_node_id	int	S’applique à : Azure Synapse Analytics, Analytics Platform System (PDW) Identificateur du nœud sur lequel cette distribution est activée.

Autorisations

Sur SQL Server et SQL Managed Instance, l’autorisation VIEW SERVER STATE est requise.

Sur les objectifs de service SQL Database Basic, S0 et S1, et pour les bases de données dans des pools élastiques, le compte d’administrateur du serveur, le compte d’administrateur Microsoft Entra ou l’appartenance au ##MS_ServerStateReader##rôle serveur est requis. Sur tous les autres objectifs de service SQL Database, l’autorisation VIEW DATABASE STATE sur la base de données ou l’appartenance au rôle serveur ##MS_ServerStateReader## est requise.

Autorisations pour SQL Server 2022 (et versions plus récentes)

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

Exemples

R. Retour du texte du traitement des entrées mises en cache qui sont réutilisées

L'exemple suivant retourne le texte SQL de toutes les entrées en cache ayant été utilisées plusieurs fois.

SELECT usecounts, cacheobjtype, objtype, text   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_sql_text(plan_handle)   
WHERE usecounts > 1   
ORDER BY usecounts DESC;  
GO

B. Retour des plans de requête pour tous les déclencheurs en cache

L'exemple suivant retourne les plans de requête de tous les déclencheurs en cache.

SELECT plan_handle, query_plan, objtype   
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_query_plan(plan_handle)   
WHERE objtype ='Trigger';  
GO

C. Retour des options SET ayant servi à compiler le plan

L'exemple suivant retourne les options SET ayant servi à compiler le plan. Le sql_handle plan est également retourné. L’opérateur PIVOT est utilisé pour générer les attributs et sql_handle les set_options attributs en tant que colonnes plutôt que sous forme de lignes. Pour plus d’informations sur la valeur retournée, set_optionsconsultez sys.dm_exec_plan_attributes (Transact-SQL).

SELECT plan_handle, pvt.set_options, pvt.sql_handle  
FROM (  
      SELECT plan_handle, epa.attribute, epa.value   
      FROM sys.dm_exec_cached_plans   
      OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa  
      WHERE cacheobjtype = 'Compiled Plan'  
      ) AS ecpa   
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;  
GO

D. Retour de la répartition mémoire de tous les plans compilés en cache

L'exemple suivant retourne une répartition de la mémoire utilisée par tous les plans compilés du cache.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,   
    omo.memory_object_address, type, page_size_in_bytes   
FROM sys.dm_exec_cached_plans AS ecp   
JOIN sys.dm_os_memory_objects AS omo   
    ON ecp.memory_object_address = omo.memory_object_address   
    OR ecp.memory_object_address = omo.parent_address  
WHERE cacheobjtype = 'Compiled Plan';  
GO

Voir aussi

Fonctions et vues de gestion dynamique (Transact-SQL)
Fonctions et vues de gestion dynamique relatives à l'exécution (Transact-SQL)
sys.dm_exec_query_plan (Transact-SQL)
sys.dm_exec_plan_attributes (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_os_memory_objects (Transact-SQL)
sys.dm_os_memory_cache_entries (Transact-SQL)
FROM (Transact-SQL)