sys.dm_exec_cached_plans (Transact-SQL)

發行項
03/07/2014

針對 SQL Server 快取的每個查詢計畫傳回一個資料列，加快查詢執行的速度。您可以使用這個動態管理檢視尋找快取的查詢計畫、快取的查詢文字、快取計畫所使用的記憶體，以及快取計畫的重複使用計數。

在 Windows Azure SQL 資料庫，動態管理檢視不可以公開可能會影響資料庫內含項目的資訊或公開有關使用者可存取之其他資料庫的資訊。為了避免公開此資訊，包含不屬於連接租用戶之資料的每個資料列都會被篩選出來。另外，資料行 memory_object_address 和 pool_id 的值也會被篩選出來；資料行值設定為 NULL。

適用於：SQL Server (SQL Server 2008 透過目前版本)、Windows Azure SQL 資料庫 (初始版本，透過目前版本)。

bucketid

int

這是快取項目的雜湊值區識別碼。這個值的範圍從 0 開始，一直到該快取類型的雜湊資料表大小。

若為 SQL 計畫和物件計畫快取，雜湊資料表在 32 位元系統上最大為 10007，在 64 位元系統上最大為 40009。若為 Bound Trees 快取，雜湊資料表在 32 位元系統上最大為 1009，在 64 位元系統上最大為 4001。若為擴充預存程序快取，雜湊資料表在 32 位元和 64 位元系統上最大為 127。

refcounts

int

參考這個快取物件的快取物件數目。 Refcounts 必須至少為 1，快取中才能有項目。

usecounts

int

已經查閱快取物件的次數。當參數化查詢在快取中找到計畫時，不會累加。但是，使用執行程序表時，可能會累加多次。

size_in_bytes

int

快取物件所耗用的位元組數目。

memory_object_address

varbinary(8)

快取項目的記憶體位址。這個值可以與 sys.dm_os_memory_objects 一起使用，取得快取計畫的記憶體細分，也可以與 sys.dm_os_memory_cache_entries 一起使用，取得快取項目的成本。

cacheobjtype

nvarchar(34)

快取中的物件類型。這個值可以是下列其中一個值：

編譯的計畫
已編譯計畫虛設常式
剖析樹狀結構
擴充程序
CLR 編譯的函數
CLR 編譯的程序

objtype

nvarchar(16)

物件的類型。這個值可以是下列其中一個值：

值	說明
Proc	預存程序
Prepared	準備陳述式
Adhoc	隨選查詢 1
ReplProc	複寫篩選程序
Trigger	觸發程序
View	檢視表
Default	預設
UsrTab	使用者資料表
SysTab	系統資料表
Check	CHECK 條件約束
Rule	規則

plan_handle

varbinary(64)

記憶體中計畫的識別碼。這個識別碼是暫時性的，只有當計畫留在快取時才會保留。這個值可以與下列動態管理函數一起使用：

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes

pool_id

int

計算此計畫記憶體使用量代表之資源集區的識別碼。

1 是指利用 osql 或 sqlcmd (而非遠端程序呼叫) 提交為語言事件的 Transact-SQL。

權限

需要伺服器的 VIEW SERVER STATE 權限。

範例

A.傳回重複使用之快取項目的批次文字

下列範例會針對使用超過一次的所有快取項目，傳回其 SQL 文字。

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.傳回所有快取觸發程序的查詢計畫

下列範例會傳回所有快取觸發程序的快取計畫。

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.傳回編譯計畫所用的 SET 選項

下列範例會傳回編譯計畫所用的 SET 選項。另外也會傳回計畫的 sql_handle。 PIVOT 運算子可用來將 set_options 和 sql_handle 屬性輸出為資料行，而非資料列。如需有關 set_options 傳回值的詳細資訊，請參閱＜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.傳回所有快取編譯計畫的記憶體細分

下列範例會傳回快取中所有編譯計畫所用細分的記憶體。

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