sys.dm_exec_plan_attributes (Transact-SQL)
Gilt für: 
SQL Server
Gibt eine Zeile pro Planattribut für den vom Planhandle angegebenen Plan zurück. Mit dieser Tabellenwertfunktion können Sie Details zu einem bestimmten Plan abrufen, z. B. die Cacheschlüsselwerte oder die Anzahl der aktuellen, gleichzeitigen Ausführungen des Plans.
Hinweis
Einige der informationen, die über diese Funktion zurückgegeben werden, sind der Abwärtskompatibilitätsansicht "sys.syscacheobjects " zugeordnet.
Syntax
sys.dm_exec_plan_attributes ( plan_handle )
Argumente
plan_handle
Führt eine eindeutige Identifizierung eines Abfrageplans für einen ausgeführten Batch aus, dessen Plan sich im Plancache befindet. plan_handle ist varbinary(64) Der Planhandle kann aus der sys.dm_exec_cached_plans dynamischen Verwaltungsansicht abgerufen werden.
Zurückgegebene Tabelle
| Spaltenname | Datentyp | Beschreibung |
|---|---|---|
| Attribut | varchar(128) | Name des Attributs, das diesem Plan zugeordnet ist. Die Tabelle unmittelbar darunter listet die möglichen Attribute, deren Datentypen und beschreibungen auf. |
| value | sql_variant | Wert des Attributs, das diesem Plan zugeordnet ist. |
| is_cache_key | bit | Gibt an, ob das Attribut als Teil des Cachesuchschlüssels für den Plan verwendet wird. |
Aus der obigen Tabelle kann das Attribut die folgenden Werte haben:
| Attribut | Datentyp | Beschreibung |
|---|---|---|
| set_options | int | Gibt die Optionswerte an, mit denen der Plan kompiliert wurde. |
| objectid | int | Einer der Hauptschlüssel zur Suche nach einem Objekt im Cache. Dies ist die Objekt-ID, die in sys.objects für Datenbankobjekte (Prozeduren, Ansichten, Trigger usw.) gespeichert ist. Für Pläne vom Typ "Adhoc" oder "Prepared" ist dies ein interner Hash des Batchtexts. |
| dbid | int | Die ID der Datenbank, welche die Entität enthält, auf die der Plan verweist. Für Ad-hoc-Pläne oder vorbereitete Pläne ist dies die Datenbank-ID, von der der Batch ausgeführt wird. |
| dbid_execute | int | Bei Systemobjekten, die in der Ressourcendatenbank gespeichert sind, wird die Datenbank-ID, aus der der zwischengespeicherte Plan ausgeführt wird. In allen anderen Fällen ist der Wert gleich 0. |
| user_id | int | Mit dem Wert -2 wird angegeben, dass der abgesendete Batch nicht von der impliziten Namensauflösung abhängt und von verschiedenen Benutzern gemeinsam verwendet werden kann. Dies ist die bevorzugte Methode. Jeder andere Wert stellt den Benutzernamen des Benutzers dar, der die Abfrage in der Datenbank absendet. |
| language_id | smallint | ID der Sprache der Verbindung, die das Cacheobjekt erstellt hat. Weitere Informationen finden Sie unter sys.syslanguages (Transact-SQL).For more information, see sys.syslanguages (Transact-SQL). |
| date_format | smallint | Datumsformat der Verbindung, die das Cacheobjekt erstellt hat. Weitere Informationen finden Sie unter SET DATEFORMAT (Transact-SQL). |
| date_first | tinyint | Erster Datumswert. Weitere Informationen finden Sie unter SET DATEFIRST (Transact-SQL). |
| compat_level | tinyint | Stellt die in der Datenbank festgelegte Kompatibilitätsstufe dar, in deren Kontext der Abfrageplan kompiliert wurde. Die zurückgegebene Kompatibilitätsstufe ist die Kompatibilitätsstufe des aktuellen Datenbankkontexts für Adhoc-Anweisungen und ist vom Abfragehinweis QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n nicht betroffen. Für Anweisungen, die in einer gespeicherten Prozedur oder Funktion enthalten sind, entspricht sie der Kompatibilitätsebene der Datenbank, in der die gespeicherte Prozedur oder Funktion erstellt wird. |
| status | int | Interne Statusbits, die Teil des Cachesuchschlüssels sind. |
| required_cursor_options | int | Vom Benutzer angegebene Cursoroptionen, z. B. der Cursortyp. |
| acceptable_cursor_options | int | Cursoroptionen, in die SQL Server implizit konvertiert werden kann, um die Ausführung der Anweisung zu unterstützen. Beispielsweise kann der Benutzer einen dynamischen Cursor angeben, doch kann dieser Cursortyp vom Abfrageoptimierer in einen statischen Cursor konvertiert werden. |
| merge_action_type | smallint | Der Typ des Triggerausführungsplans, der als Ergebnis einer MERGE-Anweisung verwendet wird. 0 gibt einen Nicht-Triggerplan an, einen Triggerplan, der nicht als Ergebnis einer MERGE-Anweisung ausgeführt wird, oder einen Triggerplan, der als Ergebnis einer MERGE-Anweisung ausgeführt wird, die nur eine DELETE-Aktion angibt. 1 gibt einen INSERT-Triggerplan an, der als Ergebnis einer MERGE-Anweisung ausgeführt wird. 2 gibt einen UPDATE-Triggerplan an, der als Ergebnis einer MERGE-Anweisung ausgeführt wird. 3 gibt einen DELETE-Triggerplan an, der als Ergebnis einer MERGE-Anweisung ausgeführt wird, die eine entsprechende INSERT- oder UPDATE-Aktion enthält. Bei geschachtelten Triggern, die durch kaskadierende Aktionen ausgeführt werden, ist dieser Wert die Aktion der MERGE-Anweisung, durch die das Kaskadieren verursacht wurde. |
| is_replication_specific | int | Stellt dar, dass die Sitzung, aus der dieser Plan kompiliert wurde, eine Sitzung ist, die mit der Instanz von SQL Server verbunden ist, indem eine nicht dokumentierte Verbindungseigenschaft verwendet wird, die es dem Server ermöglicht, die Sitzung als eine von Replikationskomponenten erstellte Sitzung zu identifizieren, sodass das Verhalten bestimmter funktionaler Aspekte des Servers entsprechend der erwarteten Replikationskomponente geändert wird. |
| optional_spid | smallint | Die Verbindung session_id (spid) wird Teil des Cacheschlüssels, um die Anzahl der neu kompilierten Elemente zu reduzieren. Dadurch wird verhindert, dass eine einzelne Sitzung einen Plan wiederverwenden kann, der nicht dynamisch gebundene temporäre Tabellen umfasst. |
| optional_clr_trigger_dbid | int | Wird nur bei einem CLR-DML-Trigger aufgefüllt. Die ID der Datenbank, die die Entität enthält. Gibt für einen anderen Objekttyp null zurück. |
| optional_clr_trigger_objid | int | Wird nur bei einem CLR-DML-Trigger aufgefüllt. Die in sys.objects gespeicherte Objekt-ID. Gibt für einen anderen Objekttyp null zurück. |
| parent_plan_handle | varbinary(64) | Immer NULL, |
| is_azure_user_plan | tinyint | 1 für Abfragen, die in einer Azure SQL-Datenbank von einer Sitzung ausgeführt werden, die von einem Benutzer initiiert wurde. 0 für Abfragen, die von einer Sitzung ausgeführt wurden, die nicht von einem Endbenutzer initiiert wurde, sondern von Anwendungen, die aus der Azure-Infrastruktur ausgeführt werden, die Abfragen zu anderen Zwecken ausstellen, um Telemetrie zu sammeln oder administrative Aufgaben auszuführen. Kunden werden für Ressourcen, die von Abfragen verbraucht werden, nicht in Rechnung gestellt, wobei is_azure_user_plan = 0. nur Azure SQL-Datenbank. |
| inuse_exec_context | int | Die Anzahl der derzeit ausgeführten Batches, die den Abfrageplan verwenden. |
| free_exec_context | int | Die Anzahl der zwischengespeicherten Ausführungskontexte für den Abfrageplan, die derzeit nicht verwendet werden. |
| hits_exec_context | int | Die Anzahl der Vorgänge, bei denen der Ausführungskontext aus dem Plancache abgerufen und wiederverwendet wurde, wodurch der Aufwand zum erneuten Kompilieren der SQL-Anweisung eingespart wird. Der Wert ist ein aggregierter Wert für alle bisherigen Batchausführungen. |
| misses_exec_context | int | Die Anzahl der Vorgänge, bei denen ein Ausführungskontext im Plancache nicht gefunden wurde, was zum Erstellen eines neuen Ausführungskontexts für die Batchausführung führt. |
| removed_exec_context | int | Die Anzahl der Ausführungskontexte, die aufgrund ungenügenden Arbeitsspeichers für den zwischengespeicherten Plan entfernt wurden. |
| inuse_cursors | int | Die Anzahl der derzeit ausgeführten Batches, die einen oder mehrere Cursor enthalten, die den zwischengespeicherten Plan verwenden. |
| free_cursors | int | Die Anzahl der im Leerlauf befindlichen oder freien Cursor für den zwischengespeicherten Plan. |
| hits_cursors | int | Die Anzahl der Vorgänge, bei denen ein inaktiver Cursor aus dem zwischengespeicherten Plan abgerufen und wiederverwendet wurde. Der Wert ist ein aggregierter Wert für alle bisherigen Batchausführungen. |
| misses_cursors | int | Die Anzahl der Vorgänge, bei denen im Cache kein inaktiver Cursor gefunden werden konnte. |
| removed_cursors | int | Die Anzahl der Cursor, die aufgrund ungenügenden Arbeitsspeichers für den zwischengespeicherten Plan entfernt wurden. |
| sql_handle | varbinary(64) | Das SQL-Handle für den Batch. |
Berechtigungen
Auf SQL Server ist die VIEW SERVER STATE-Berechtigung erforderlich.
Für Azure SQL-Datenbank Standard-, S0- und S1-Dienstziele sowie für Datenbanken in elastischen Pools ist das Serveradministratorkonto oder das Microsoft Entra-Administratorkonto erforderlich. Für alle anderen SQL-Datenbank Dienstziele ist die VIEW DATABASE STATE Berechtigung in der Datenbank erforderlich.
Berechtigungen für SQL Server 2022 und höher
Erfordert die VIEW SERVER PERFORMANCE STATE-Berechtigung auf dem Server.
Hinweise
SET-Optionen
Kopien desselben kompilierten Plans können sich nur von dem Wert in der Spalte set_options unterscheiden. Dies weist darauf hin, dass verschiedene Verbindungen für die gleiche Abfrage unterschiedliche Sätze von SET-Optionen verwenden. Die Verwendung unterschiedlicher Sätze von Optionen ist meist unerwünscht, da dies zusätzliche Kompilierungen, einen geringeren Anteil von Wiederverwendungen von Plänen sowie, da im Cache mehrere Pläne vorhanden sind, eine Vergrößerung des Plancaches verursachen kann.
Auswerten von SET-Optionen
Um den in set_options zurückgegebenen Wert in die Optionen zu übersetzen, mit denen der Plan kompiliert wurde, subtrahieren Sie die Werte vom set_options Wert, beginnend mit dem größten möglichen Wert, bis Sie 0 erreicht haben. Jeder subtrahierte Wert entspricht einer Option, die im Abfrageplan verwendet wurde. Wenn der Wert in set_options beispielsweise 251 ist, sind die Optionen, mit denen der Plan kompiliert wurde, ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), ParallelPlan(2) und ANSI_PADDING (1).
| Option | Wert |
|---|---|
| ANSI_PADDING | 1 |
| ParallelPlan Gibt an, dass sich die Optionen für die Planparallelität geändert haben. |
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 Gibt an, dass im Plan keine Arbeitstabelle verwendet wird, um einen FOR BROWSE-Vorgang zu implementieren. |
512 |
| TriggerOneRow Gibt an, dass der Plan Optimierungen einzelner Zeilen für Deltatabellen von AFTER-Triggern umfasst. |
1024 |
| ResyncQuery Gibt an, dass die Abfrage von internen gespeicherten Systemprozeduren übermittelt wurde. |
2048 |
| ARITH_ABORT | 4096 |
| NUMERIC_ROUNDABORT | 8192 |
| DATEFIRST | 16384 |
| DATEFORMAT | 32768 |
| LanguageID | 65536 |
| UPON Gibt an, dass die Datenbankoption PARAMETERIZATION beim Kompilieren des Plans auf FORCED festgelegt wurde. |
131072 |
| ROWCOUNT | Gilt für: SQL Server 2012 (11.x) und höher 262144 |
Cursor
Inaktive Cursor werden in einem kompilierten Plan zwischengespeichert, sodass der zum Speichern des Cursors verwendete Arbeitsspeicher von gleichzeitigen Benutzern des Cursors wiederverwendet werden kann. Angenommen, dass ein Cursor von einem Batch deklariert und verwendet wird, ohne dass seine Zuordnung aufgehoben wird. Wenn zwei Benutzer denselben Batch ausführen, sind zwei aktive Cursor vorhanden. Sobald die Zuordnung der Cursor aufgehoben ist (möglicherweise in unterschiedlichen Batches), wird der Arbeitsspeicher zum Speichern des Cursors zwischengespeichert und nicht freigegeben. Der Liste der inaktiven Cursor wird im kompilierten Plan beibehalten. Bei der nächsten Ausführung des Batches durch einen Benutzer wird der zwischengespeicherte Arbeitsspeicher für den Cursor wiederverwendet und als aktiver Cursor ordnungsgemäß initialisiert.
Auswerten von Cursoroptionen
Um den in required_cursor_options zurückgegebenen Wert und acceptable_cursor_options in die Optionen zu übersetzen, mit denen der Plan kompiliert wurde, subtrahieren Sie die Werte vom Spaltenwert, beginnend mit dem größten möglichen Wert, bis Sie 0 erreicht haben. Jeder subtrahierte Wert entspricht einer Cursoroption, die im Abfrageplan verwendet wurde.
| Option | Wert |
|---|---|
| Keine | 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 | 1024 |
| STATIC | 2048 |
| FAST_FORWARD | 4096 |
| IN PLACE | 8192 |
| FOR select_statement | 16384 |
Beispiele
A. Zurückgeben der Attribute für einen bestimmten Plan
Im folgenden Beispiel werden alle Planattribute für einen angegebenen Plan zurückgegeben. Die dynamische Verwaltungssicht sys.dm_exec_cached_plans wird zuerst abgefragt, um das Planhandle für den angegebenen Plan abzurufen. In der zweiten Abfrage ersetzen Sie <plan_handle> durch einen Planhandlewert aus der ersten Abfrage.
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. Zurückgeben der SET-Optionen für kompilierte Pläne und des SQL-Handles für zwischengespeicherte Pläne
Im folgenden Beispiel wird ein Wert zurückgegeben, der die Optionen darstellt, mit denen die einzelnen Pläne kompiliert wurden. Außerdem wird das SQL-Handle für alle zwischengespeicherten Pläne zurückgegeben.
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
Weitere Informationen
Dynamische Verwaltungssichten und Funktionen (Transact-SQL)
Dynamische Verwaltungssichten und -funktionen im Zusammenhang mit der Ausführung (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)