sys.dm_exec_cached_plans (Transact-SQL)

Artikel
06/18/2013

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.

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 muss mindestens 1 betragen, wenn der Eintrag im Cache vorhanden sein soll.

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 Speicheraufteilung des zwischengespeicherten Plans abzurufen, und mit sys.dm_os_memory_cache_entries, um die Kosten für das Zwischenspeichern des Eintrags abzurufen.

cacheobjtype

nvarchar(34)

Typ des Objekts im Cache. Die folgenden Werte sind möglich:

Kompilierter Plan
Stub des kompilierten Plans
Analysestruktur
Erweiterte Prozedur
Kompilierte CLR-Funktion
Kompilierte CLR-Prozedur

objtype

nvarchar(16)

Typ des Objekts. Die folgenden Werte sind möglich:

Wert	Beschreibung
Proc	Gespeicherte Prozedur
Vorbereitet	Vorbereitete Anweisung
Ad-hoc	Ad-hoc-Abfrage1
Replikationsprozedur	Replikationsfilterprozedur
Trigger	Trigger
Sicht	Sicht
Standardwert	Standardwert
UsrTab	Benutzertabelle
SysTab	Systemtabelle
Check	CHECK-Einschränkung
Rule	Rule

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.

1 Gilt für Transact-SQL-Abfragen, die als Sprachereignisse von osql oder sqlcmd aus anstatt als Remoteprozeduraufrufe übermittelt werden.

Berechtigungen

Erfordert die VIEW SERVER 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. Das sql_handle für den Plan wird ebenfalls zurückgegeben. Der PIVOT-Operator wird verwendet, um die Attribute set_options und sql_handle als Spalten und nicht als Zeilen auszugeben. Weitere Informationen zum in set_options zurückgegebenen Wert finden Sie unter 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, pages_allocated_count, 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