sys.dm_exec_cached_plans (Transact-SQL)

Artikel
12/19/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Gibt eine Zeile für jeden Abfrageplan zurück, der von SQL Server für eine schnellere Abfrageausführung zwischengespeichert wird. In dieser dynamischen Verwaltungssicht können Sie zwischengespeicherte Abfragepläne, zwischengespeicherten Abfragetext, den von zwischengespeicherten Plänen verwendeten Arbeitsspeicher und die Anzahl der Wiederverwendungen für zwischengespeicherte Pläne suchen.

In Azure SQL-Datenbank können dynamische Verwaltungsansichten keine Informationen verfügbar machen, die sich auf das Eindämmen von Datenbanken auswirken oder Informationen zu anderen Datenbanken verfügbar machen, auf die der Benutzer zugreifen kann. Um diese Informationen nicht verfügbar zu machen, wird jede Zeile mit Daten, die nicht zum verbundenen Mandanten gehören, herausgefiltert. Darüber hinaus werden die Werte in den Spalten memory_object_address und pool_id gefiltert. Der Spaltenwert wird auf NULL festgelegt.

Hinweis

Um dies von Azure Synapse Analytics oder Analytics Platform System (PDW) aufzurufen, verwenden Sie den Namen sys.dm_pdw_nodes_exec_cached_plans. Diese Syntax wird vom serverlosen SQL-Pool in Azure Synapse Analytics nicht unterstützt.

Spaltenname	Datentyp	Beschreibung
bucketid	int	ID des Hashbuckets, in dem der Eintrag gespeichert ist. Der Wert gibt einen Bereich von 0 bis zur Hashtabellengröße für den Typ des Caches an. Für die SQL-Plan- und Objektplancaches kann die Hashtabellengröße in 32-Bit-Systemen bis zu 10007 und in 64-Bit-Systemen bis zu 40009 betragen. Für den Cache für gebundene Strukturen kann die Hashtabellengröße in 32-Bit-Systemen bis zu 1009 und in 64-Bit-Systemen bis zu 4001 betragen. Für den Cache für erweiterte gespeicherte Prozeduren kann die Hashtabellengröße in 32-Bit-Systemen und 64-Bit-Systemen bis zu 127 betragen.
refcounts	int	Anzahl der Cacheobjekte, die auf dieses Cacheobjekt verweisen. Refcounts müssen mindestens 1 sein, damit sich ein Eintrag im Cache befindet.
usecounts	int	Häufigkeit, mit der das Cacheobjekt nachgeschlagen wurde. Nicht inkrementiert, wenn parametrisierte Abfragen einen Plan im Cache suchen. Kann bei Verwenden von Showplan mehrmals inkrementiert werden.
size_in_bytes	int	Anzahl von Bytes, die vom Cacheobjekt belegt werden.
memory_object_address	varbinary(8)	Speicheradresse des zwischengespeicherten Eintrags. Dieser Wert kann mit sys.dm_os_memory_objects verwendet werden, um die Speicheraufschlüsselung des zwischengespeicherten Plans und mit sys.dm_os_memory_cache_entries abzurufen_entries um die Kosten für die Zwischenspeicherung des Eintrags zu erhalten.
cacheobjtype	nvarchar(34)	Typ des Objekts im Cache. Der Wert kann in folgenden Formen vorliegen: Kompilierter Plan Stub des kompilierten Plans Analysestruktur Erweiterte Prozedur Kompilierte CLR-Funktion Kompilierte CLR-Prozedur
objtype	nvarchar(16)	Typ des Objekts. Nachfolgend sind die möglichen Werte und die entsprechenden Beschreibungen aufgeführt. Proc: Gespeicherte Prozedur Vorbereitet: Vorbereitete Anweisung Adhoc: Ad-hoc-Abfrage. Bezieht sich auf Transact-SQL, das als Sprachereignisse übermittelt wird, indem osql oder sqlcmd anstelle von Remoteprozeduraufrufen verwendet wird. ReplProc: Replikationsfilter-Prozedur Auslöser: Auslöser Ansicht: Ansicht Standard: Standard UsrTab: Benutzertabelle SysTab: Systemtabelle Check: CHECK-Einschränkung Regel: Regel
plan_handle	varbinary(64)	Bezeichner für den speicherinternen Plan. Dieser Bezeichner ist vorübergehend und bleibt nur für die Dauer der Speicherung des Plans im Cache konstant. Dieser Wert kann mit den folgenden dynamischen Verwaltungsfunktionen verwendet werden: sys.dm_exec_sql_text sys.dm_exec_query_plan sys.dm_exec_plan_attributes
pool_id	int	Die ID des Ressourcenpools, auf die sich diese Planspeicherauslastung bezieht.
pdw_node_id	int	Gilt für: Azure Synapse Analytics, Analytics Platform System (PDW) Der Bezeichner für den Knoten, auf dem sich diese Verteilung befindet.

Berechtigungen

Für SQL Server und SQL Managed Instance ist die VIEW SERVER STATE-Berechtigung erforderlich.

Für SQL-Datenbank Standard-, S0- und S1-Dienstziele sowie für Datenbanken in elastischen Pools ist das Serveradministratorkonto, das Microsoft Entra-Administratorkonto oder die Mitgliedschaft in der ##MS_ServerStateReader##Serverrolle erforderlich. Für alle anderen SQL-Datenbank-Dienstziele ist entweder die VIEW DATABASE STATE-Berechtigung für die Datenbank oder die Mitgliedschaft in der ##MS_ServerStateReader##-Serverrolle erforderlich.

Berechtigungen für SQL Server 2022 und höher

Erfordert die VIEW SERVER PERFORMANCE STATE-Berechtigung auf dem Server.

Beispiele

.A Zurückgeben des Batchtexts von zwischengespeicherten Einträgen, die wiederverwendet werden

Im folgenden Beispiel wird der SQL-Text aller zwischengespeicherten Einträge zurückgegeben, die mehr als einmal verwendet wurden.

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. Zurückgeben von Abfrageplänen für alle zwischengespeicherten Trigger

Im folgenden Beispiel werden die Abfragepläne für alle zwischengespeicherten Trigger zurückgegeben.

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. Zurückgeben der SET-Optionen, mit denen der Plan kompiliert wurde

Im folgenden Beispiel werden die SET-Optionen zurückgegeben, mit denen der Plan kompiliert wurde. Der sql_handle Plan wird ebenfalls zurückgegeben. Der PIVOT-Operator wird verwendet, um die set_options Und-Attribute sql_handle als Spalten und nicht als Zeilen auszugeben. Weitere Informationen zum zurückgegebenen Wert finden Sie unter sys.dm_exec_plan_attributes (Transact-SQL).For more information about the value returned in set_options, see 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: Zurückgeben der Arbeitsspeicheraufteilung aller zwischengespeicherter kompilierter Pläne

Im folgenden Beispiel wird die Aufteilung des Arbeitsspeichers zurückgegeben, der von allen kompilierten Plänen im Zwischenspeicher verwendet wird.

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

Weitere Informationen

Dynamische Verwaltungssichten und Funktionen (Transact-SQL)
Dynamische Verwaltungssichten und -funktionen im Zusammenhang mit der Ausführung (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)