sys.dm_exec_cached_plans (Transact-SQL)

文章
07/05/2024

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

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

在 Azure SQL Database 中，動態管理檢視不可以公開可能會影響資料庫內含項目的資訊，或公開有關使用者可存取之其他資料庫的資訊。為了避免公開這項資訊，會篩選出包含不屬於已連線租使用者之數據的每個數據列。此外，會篩選數據行memory_object_address和pool_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：臨機操作查詢。是指使用 osql 或 sqlcmd 提交為語言事件的 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) 此散發節點的標識碼。

權限

在 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)

分享方式：