sys.dm_exec_plan_attributes (Transact-SQL)
S'applique à : 
SQL Server
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.
Remarque
Certaines informations retournées via cette fonction sont mappée à la vue de compatibilité descendante sys.syscacheobjects .
Syntaxe
sys.dm_exec_plan_attributes ( plan_handle )
Arguments
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 handle de plan peut être obtenu à partir de la vue de gestion dynamique sys.dm_exec_cached_plans .
Table retournée
| Nom de la colonne | Type de données | Description |
|---|---|---|
| attribute | varchar(128) | Nom de l'attribut associé à ce plan. Le tableau situé juste en dessous de celui-ci répertorie les attributs possibles, leurs types de données et leurs descriptions. |
| valeur | sql_variant | Valeur de l'attribut associé à ce plan. |
| is_cache_key | bit | Indique si l'attribut est utilisé comme une partie de la clé de recherche en cache pour le plan. |
Dans le tableau ci-dessus, l’attribut peut avoir les valeurs suivantes :
| Attribut | Type de données | Description |
|---|---|---|
| set_options | int | Indique les valeurs d'option ayant servi à compiler le plan. |
| objectid | int | Une des clés principales servant à rechercher un objet dans le cache. Il s’agit de 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. |
| dbid | int | ID 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_execute | int | Pour les objets système stockés dans la base de données de ressources, l’ID de base de données à partir duquel le plan mis en cache est exécuté. Dans tous les autres cas, il est égal à 0. |
| user_id | int | Une 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. Ceci est la méthode privilégiée. Toute autre valeur représente l'ID de l'utilisateur soumettant la requête dans la base de données. |
| language_id | smallint | ID de la langue de la connexion qui a créé l'objet dans le cache. Pour plus d’informations, consultez sys.syslanguages (Transact-SQL). |
| date_format | smallint | Format de date de la connexion qui a créé l'objet dans le cache Pour plus d’informations, consultez SET DATEFORMAT (Transact-SQL). |
| date_first | tinyint | Valeur date first. Pour plus d’informations, consultez SET DATEFIRST (Transact-SQL). |
| compat_level | tinyint | Représente le jeu de niveau de compatibilité dans la base de données dans laquelle le contexte du plan de requête a été compilé. Le niveau de compatibilité retourné est le niveau de compatibilité du contexte de base de données actuel pour les instructions adhoc et n’est pas affecté par l’indicateur de requête QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n. Pour les instructions contenues dans une procédure stockée ou une fonction, elle correspond au niveau de compatibilité de la base de données dans laquelle la procédure stockée ou la fonction est créée. |
| statut | int | Bits d'état interne qui font partie de la clé de recherche en cache. |
| required_cursor_options | int | Options de curseur spécifiées par l'utilisateur (type de curseur par exemple). |
| acceptable_cursor_options | int | Options de curseur vers laquelle SQL Server peut implicitement se convertir 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. |
| merge_action_type | smallint | Type 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. |
| is_replication_specific | int | Représente que la session à partir de laquelle ce plan a été compilé est une session connectée à l’instance de SQL Server à l’aide d’une propriété de connexion non documentée qui permet au serveur d’identifier la session comme une session créée par les composants de réplication, afin que le comportement de certains aspects fonctionnels du serveur soit modifié en fonction de ce que ce composant de réplication attend. |
| optional_spid | smallint | La connexion session_id (spid) fait partie de la clé de cache afin de réduire le nombre de recompilations. Cela empêche les recompilations pour la réutilisation d’une session unique d’un plan impliquant des tables temporaires non liées dynamiquement. |
| optional_clr_trigger_dbid | int | Renseigné uniquement dans le cas d’un déclencheur DML CLR. ID de la base de données contenant l’entité. Pour tout autre type d’objet, retourne zéro. |
| optional_clr_trigger_objid | int | Renseigné uniquement dans le cas d’un déclencheur DML CLR. ID d’objet stocké dans sys.objects. Pour tout autre type d’objet, retourne zéro. |
| parent_plan_handle | varbinary(64) | Toujours NULL. |
| is_azure_user_plan | tinyint | 1 pour les requêtes exécutées dans une base de données Azure SQL à partir d’une session lancée par un utilisateur. 0 pour les requêtes qui ont été exécutées à partir d’une session non lancée par un utilisateur final, mais par les applications exécutées à partir de l’infrastructure Azure qui émettent des requêtes à d’autres fins de collecte de données de télémétrie ou d’exécution de tâches administratives. Les clients ne sont pas facturés pour les ressources consommées par les requêtes où is_azure_user_plan = 0. Azure SQL Database uniquement. |
| inuse_exec_context | int | Nombre de lots en cours d'exécution qui font appel au plan de requête. |
| free_exec_context | int | Nombre de contextes d'exécution en cache pour le plan de requête qui ne sont pas actuellement utilisés. |
| hits_exec_context | int | Nombre 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_context | int | Nombre 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_context | int | Nombre de contextes d'exécution ayant été supprimés en raison d'une mémoire insuffisante dans le plan en cache. |
| inuse_cursors | int | Nombre de lots en cours d'exécution contenant un ou plusieurs curseurs qui font appel au plan en cache. |
| free_cursors | int | Nombre de curseurs libres ou inactifs du plan en cache. |
| hits_cursors | int | Nombre 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_cursors | int | Nombre de fois où un curseur inactif était introuvable dans le cache. |
| removed_cursors | int | Nombre de curseurs ayant été supprimés en raison d'une mémoire insuffisante dans le plan en cache. |
| sql_handle | varbinary(64) | Handle SQL du lot. |
autorisations
Sur SQL Server, nécessite l’autorisation VIEW SERVER STATE.
Sur les objectifs de service Azure SQL Database Basic, S0 et S1, et pour les bases de données dans des pools élastiques, le compte d’administrateur du serveur ou le compte d’administrateur Microsoft Entra est requis. Sur tous les autres objectifs de service SQL Database, l’autorisation VIEW DATABASE STATE est requise dans la base de données.
Autorisations pour SQL Server 2022 (et versions plus récentes)
Nécessite l’autorisation VIEW SERVER PERFORMANCE STATE sur le serveur.
Notes
Définir les options
Les copies du même plan compilé peuvent différer uniquement par la valeur de la colonne set_options . 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 traduire la valeur retournée dans set_options aux options avec lesquelles le plan a été compilé, soustrayez les valeurs de la valeur set_options , en commençant par la plus grande valeur possible, jusqu’à atteindre 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 avec laquelle le plan a été compilé sont ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), Plan parallèle(2) et ANSI_PADDING (1).
| Option | Valeur |
|---|---|
| ANSI_PADDING | 1 |
| ParallelPlan Indique que les options de parallélisme de plan ont changé. |
2 |
| FORCEPLAN | 4 |
| CONCAT_NULL_YIELDS_NULL | 8 |
| ANSI_WARNINGS | 16 |
| ANSI_NULLS | 32 |
| QUOTED_IDENTIFIER | 64 |
| ANSI_NULL_DFLT_ON | 128 |
| ANSI_NULL_DFLT_OFF | 256 |
| 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. |
1 024 |
| ResyncQuery Indique que la requête a été soumise par des procédures stockées système internes. |
2 048 |
| ARITH_ABORT | 4096 |
| NUMERIC_ROUNDABORT | 8192 |
| DATEFIRST | 16384 |
| DATEFORMAT | 32 768 |
| LanguageID | 65536 |
| UPON Indique que l'option de base de données PARAMETERIZATION avait pour valeur FORCED lorsque le plan a été compilé. |
131 072 |
| ROWCOUNT | S’applique à : SQL Server 2012 (11.x) et versions ultérieures 262144 |
Curseurs
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 traduire la valeur retournée dans required_cursor_options et acceptable_cursor_options vers les options avec lesquelles le plan a été compilé, soustrait les valeurs de la valeur de colonne, en commençant par la plus grande valeur possible, jusqu’à atteindre 0. Chaque valeur soustraite correspond à une option de curseur utilisée dans le plan de requête.
| Option | Valeur |
|---|---|
| None | 0 |
| INSENSITIVE | 1 |
| SCROLL | 2 |
| READ ONLY | 4 |
| FOR UPDATE | 8 |
| LOCAL | 16 |
| GLOBAL | 32 |
| FORWARD_ONLY | 64 |
| KEYSET | 128 |
| DYNAMIC | 256 |
| SCROLL_LOCKS | 512 |
| OPTIMISTIC | 1 024 |
| STATIC | 2 048 |
| FAST_FORWARD | 4096 |
| IN PLACE | 8192 |
| FOR select_statement | 16384 |
Exemples
R. 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
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_cached_plans (Transact-SQL)
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)