sys.dm_exec_plan_attributes (Transact-SQL)

 

CETTE RUBRIQUE S’APPLIQUE À :ouiSQL Server (à partir de la version 2008)nonAzure SQL DatabasenonAzure SQL Data WarehousenonParallel Data Warehouse

Retourne une ligne par attribut de plan pour le plan spécifié par le handle de plan. Cette fonction à valeur de table vous permet d'obtenir des informations sur un plan particulier, tel que les valeurs clé de cache ou le nombre d'exécutions simultanées en cours du plan.

System_CAPS_ICON_note.jpg Remarque


Certaines des informations retournées par cette fonction mappe à la sys.syscacheobjects vue de compatibilité descendante.

  
sys.dm_exec_plan_attributes ( plan_handle )  

plan_handle
Identifie de façon unique un plan de requête pour un lot exécuté et dont le plan réside dans le cache du plan. plan_handle est varbinary(64). Le descripteur de plan peut être obtenu à partir de la sys.dm_exec_cached_plans vue de gestion dynamique.

Nom de colonneType de donnéesDescription
attributvarchar (128)Nom de l'attribut associé à ce plan. Le tableau immédiatement sous celle-ci répertorie les attributs possibles, leurs types de données et leurs descriptions.
valeursql_variantValeur de l'attribut associé à ce plan.
is_cache_keybitsIndique si l'attribut est utilisé comme une partie de la clé de recherche en cache pour le plan.
AttributType de donnéesDescription
set_optionsintIndique les valeurs d'option ayant servi à compiler le plan.
objectidintUne des clés principales servant à rechercher un objet dans le cache. C’est l’ID d’objet stocké dans sys.objects pour les objets de base de données (procédures, vues, déclencheurs, etc.). Pour des plans de type « Adhoc » ou « Prepared », il s'agit d'un hachage interne du texte du lot.
dbidintID de la base de données contenant l'entité à laquelle le plan fait référence.

Pour des plans ad hoc ou préparés, il s'agit de l'ID de la base de données à partir duquel est exécuté le lot.
dbid_executeintPour les objets système stockés dans le ressources de base de données, l’ID de base de données à partir duquel est exécuté le plan mis en cache. Dans tous les autres cas, il est égal à 0.
user_idintUne valeur de -2 indique que le lot soumis ne dépend pas de la résolution implicite des noms et peut être partagé entre différents utilisateurs. Cette méthode est recommandée. Toute autre valeur représente l'ID de l'utilisateur soumettant la requête dans la base de données.
language_idsmallintID de la langue de la connexion qui a créé l'objet dans le cache. Pour plus d’informations, consultez sys.syslanguages (Transact-SQL).
date_formatsmallintFormat de date de la connexion qui a créé l'objet dans le cache Pour plus d’informations, consultez SET DATEFORMAT (Transact-SQL).
date_firsttinyintValeur date first. Pour plus d’informations, consultez SET DATEFIRST (Transact-SQL).
statusintBits d'état interne qui font partie de la clé de recherche en cache.
required_cursor_optionsintOptions de curseur spécifiées par l'utilisateur (type de curseur par exemple).
acceptable_cursor_optionsintOptions de curseur dans lesquelles SQL Server peut convertir implicitement afin de prendre en charge l'exécution de l'instruction. Par exemple, l'utilisateur peut spécifier un curseur dynamique, mais l'optimiseur de requête peut convertir ce type de curseur en curseur statique.
inuse_exec_contextintNombre de lots en cours d'exécution qui font appel au plan de requête.
free_exec_contextintNombre de contextes d'exécution en cache pour le plan de requête qui ne sont pas actuellement utilisés.
hits_exec_contextintNombre d'obtention et de réutilisation du contexte d'exécution à partir du cache du plan évitant ainsi de recompiler l'instruction SQL. La valeur est une agrégation de toutes les exécutions de lot jusqu'à présent.
misses_exec_contextintNombre de fois un contexte d'exécution était introuvable dans le cache de plan entraînant la création d'un nouveau contexte d'exécution pour l'exécution du lot.
removed_exec_contextintNombre de contextes d'exécution ayant été supprimés en raison d'une mémoire insuffisante dans le plan en cache.
inuse_cursorsintNombre de lots en cours d'exécution contenant un ou plusieurs curseurs qui font appel au plan en cache.
free_cursorsintNombre de curseurs libres ou inactifs du plan en cache.
hits_cursorsintNombre d'obtention et de réutilisation d'un curseur inactif à partir du plan en cache. La valeur est une agrégation de toutes les exécutions de lot jusqu'à présent.
misses_cursorsintNombre de fois où un curseur inactif était introuvable dans le cache.
removed_cursorsintNombre de curseurs ayant été supprimés en raison d'une mémoire insuffisante dans le plan en cache.
sql_handlevarbinary(64)Handle SQL du lot.
merge_action_typesmallintType du plan d'exécution du déclencheur utilisé à la suite d'une instruction MERGE.

0 indique un plan de non-déclencheur, un plan de déclencheur qui ne s'exécute pas à la suite d'une instruction MERGE ou un plan de déclencheur qui s'exécute à la suite d'une instruction MERGE qui spécifie uniquement une action DELETE.

1 indique un plan de déclencheur INSERT qui s'exécute à la suite d'une instruction MERGE.

2 indique un plan de déclencheur UPDATE qui s'exécute à la suite d'une instruction MERGE.

3 indique un plan de déclencheur DELETE qui s'exécute à la suite d'une instruction MERGE contenant une action INSERT ou UPDATE correspondante.

Pour les déclencheurs imbriqués exécutés par des actions en cascade, cette valeur correspond à l'action de l'instruction MERGE qui a provoqué la cascade.

Sur SQL Server nécessite l’autorisation VIEW SERVER STATE sur le serveur.

Sur Base de données SQL niveaux Premium requiert l’autorisation VIEW DATABASE STATE dans la base de données. Sur Base de données SQL base niveaux Standard et nécessitent le Base de données SQL compte d’administrateur.

Copies du même plan compilé peuvent différer uniquement par la valeur de la set_options colonne. Cela signifie que des connexions différentes font appel à différents jeux d'options SET pour la même requête. L'utilisation de différents jeux d'option n'est pas souhaitable généralement car elle est source de complications supplémentaires, d'une réutilisation insuffisante du plan et d'une augmentation du cache du plan en raison de la présence de plusieurs copies dans le cache.

Évaluation des options définies

Pour convertir la valeur retournée dans set_options dans les options ayant servi à compiler le plan, soustraire les valeurs de la set_options valeur, en commençant par la plus grande valeur possible, jusqu'à ce que vous atteigniez 0. Chaque valeur soustraite correspond à une option utilisée dans le plan de requête. Par exemple, si la valeur de set_options est 251, les options que le plan a été compilé avec ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), Plan(2) parallèles et ANSI_PADDING (1).

OptionValeur
ANSI_PADDING1
Plan en parallèle2
FORCEPLAN4
CONCAT_NULL_YIELDS_NULL8
ANSI_WARNINGS16
ANSI_NULLS32
QUOTED_IDENTIFIER64
ANSI_NULL_DFLT_ON128
ANSI_NULL_DFLT_OFF256
NoBrowseTable

Indique que le plan n'utilise pas une table de travail pour implémenter une opération FOR BROWSE.
512
TriggerOneRow

Indique que le plan contient une optimisation de ligne unique pour les tables delta de déclencheur AFTER.
1024
ResyncQuery

Indique que la requête a été soumise par des procédures stockées système internes.
2048
ARITH_ABORT4096
NUMERIC_ROUNDABORT8192
DATEFIRST16384
DATEFORMAT32768
LanguageID65536
UPON

Indique que l'option de base de données PARAMETERIZATION avait pour valeur FORCED lorsque le plan a été compilé.
131072
ROWCOUNTS’applique à : SQL Server 2012 àSQL Server 2016

262144

Les curseurs inactifs sont mis en cache dans un plan compilé pour que la mémoire utilisée pour stocker le curseur soit réutilisée par des utilisateurs simultanés des curseurs. Par exemple, supposez qu'un lot déclare et utilise un curseur sans le désallouer. Si deux utilisateurs exécutent le même lot, il y aura deux curseurs actifs. Une fois les curseurs désalloués (éventuellement dans des lots différents), la mémoire utilisée pour stocker le curseur est mise en cache et n'est pas libérée. Cette liste des curseurs inactifs est conservée dans le plan compilé. À la prochaine exécution du lot par un utilisateur, la mémoire de curseur en cache est réutilisée et initialisée correctement comme curseur actif.

Évaluation des options de curseur

Pour convertir la valeur retournée dans required_cursor_options et acceptable_cursor_options dans les options ayant servi à compiler le plan, soustraire les valeurs de la valeur de colonne, en commençant par la plus grande valeur possible, jusqu'à ce que vous atteigniez 0. Chaque valeur soustraite correspond à une option de curseur utilisée dans le plan de requête.

OptionValeur
Aucune0
INSENSITIVE1
SCROLL2
READ ONLY4
FOR UPDATE8
LOCAL16
GLOBAL32
FORWARD_ONLY64
KEYSET128
DYNAMIC256
SCROLL_LOCKS512
OPTIMISTIC1024
STATIC2048
FAST_FORWARD4096
IN PLACE8192
POUR select_statement16384

A. Retour des attributs pour un plan spécifique

L'exemple suivant retourne tous les attributs de plan pour un plan spécifié. Dans la première requête, la vue de gestion dynamique sys.dm_exec_cached_plans est interrogée pour obtenir le descripteur de plan du plan spécifié. Dans la deuxième requête, remplacez <plan_handle> par une valeur de descripteur de plan issue de la première requête.

SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype   
FROM sys.dm_exec_cached_plans;  
GO  
SELECT attribute, value, is_cache_key  
FROM sys.dm_exec_plan_attributes(<plan_handle>);  
GO  

B. Retour des options SET pour les plans compilés et du handle SQL pour les plans en cache

L'exemple suivant retourne une valeur représentant les options ayant servi à compiler chaque plan. Il retourne également le handle SQL de tous les plans mis en cache.

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  

Vues de gestion dynamique et fonctions (Transact-SQL)
Fonctions (Transact-SQL) et les vues de gestion dynamique liées à l’exécution
Sys.dm_exec_cached_plans (Transact-SQL)
Sys.Databases (Transact-SQL)
Sys.Objects (Transact-SQL)

Ajouts de la communauté

AJOUTER
Afficher: