分享方式:


sys.dm_exec_cached_plans (Transact-SQL)

適用於:SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

針對 SQL Server 快取的每個查詢計劃,傳回一個數據列,以加快查詢執行速度。 您可以使用此動態管理檢視來尋找快取的查詢計劃、快取的查詢文字、快取計劃所擷取的記憶體數量,以及快取計劃的重複使用計數。

在 Azure SQL Database 中,動態管理檢視不可以公開可能會影響資料庫內含項目的資訊,或公開有關使用者可存取之其他資料庫的資訊。 為了避免公開這項資訊,會篩選出包含不屬於已連線租使用者之數據的每個數據列。此外,會篩選數據行memory_object_addresspool_id中的值;數據行值會設定為 NULL。

注意

若要從 Azure Synapse Analytics 或 Analytics Platform System (PDW) 呼叫此專案,請使用 名稱 sys.dm_pdw_nodes_exec_cached_plans。 Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。

資料行名稱 資料類型 描述
bucketid int 快取專案所在的哈希值區標識碼。 值表示範圍從 0 到快取類型的哈希表大小。

針對 SQL 方案和物件計劃快取,哈希表大小在 32 位系統上最多可達 10007,64 位系統上最多可有 40009 個。 對於系結樹狀結構快取,哈希表大小在32位系統上最多可達1009,而64位系統上最多可有4001個。 針對擴充預存程式快取,哈希表大小在32位和64位系統上最多可達127。
refcounts int 參考此快取物件的快取物件數目。 在快取中,必須至少有1個專案的Refcount
usecounts int 快取物件的查閱次數。 參數化查詢在快取中尋找計劃時,不會遞增。 使用 showplan 時,可以遞增多次。
size_in_bytes int 快取物件所耗用的位元組數目。
memory_object_address varbinary(8) 快取項目的記憶體位址。 這個值可以與 sys.dm_os_memory_objects 搭配使用,以取得快取計劃的記憶體明細,以及搭配 sys.dm_os_memory_cache_entries_entries 以取得快取專案的成本。
cacheobjtype nvarchar(34) 快取中的物件類型。 此值可以是下列其中一項:

已編譯的計劃

已編譯的計劃存根

剖析樹狀結構

擴充程式

CLR 編譯的 Func

CLR 編譯的 Proc
objtype nvarchar(16) 物件的類型。 以下是可能的值及其對應的描述。

Proc:預存程式
已備妥:備妥的語句
Adhoc:臨機操作查詢。 是指使用 osqlsqlcmd 提交為語言事件的 Transact-SQL,而不是做為遠端過程調用。
ReplProc:Replication-filter-procedure
觸發程式:觸發程式
檢視:檢視
預設值:預設值
UsrTab:用戶數據表
SysTab:系統數據表
檢查:CHECK 條件約束
規則:規則
plan_handle varbinary(64) 記憶體中計畫的識別碼。 這個識別碼是暫時性的,只有當計畫留在快取時才會保留。 此值可與下列動態管理功能搭配使用:

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes
pool_id int 為此計劃記憶體使用量所考慮的資源集區標識碼。
pdw_node_id int 適用於:Azure Synapse Analytics、Analytics Platform System (PDW)

此散發節點的標識碼。

1

權限

在 SQL Server 和 SQL 受控執行個體上,需要 VIEW SERVER STATE 權限。

在 SQL 資料庫 基本、S0 和 S1 服務目標上,以及彈性集區中的資料庫,需要伺服器管理員帳戶、Microsoft Entra 系統管理員帳戶,或伺服器角色的成員##MS_ServerStateReader##資格。 在所有其他 SQL Database 服務目標上,需要資料庫的 VIEW DATABASE STATE 權限或 ##MS_ServerStateReader## 伺服器角色的成員資格。

SQL Server 2022 及更新版本的權限

需要伺服器上的 VIEW SERVER PERFORMANCE 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 運算子是用來將 和 sql_handle 屬性輸出set_options為數據行,而不是數據列。 如需 中 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, 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  

另請參閱

動態管理檢視和函數 (Transact-SQL)
執行相關的動態管理檢視和函數 (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)