sys.dm_exec_cached_plans (Transact-SQL)

發行項
05/04/2023

適用於：SQL Server Azure SQL Database 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。若為 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：預存程式備妥：備妥語句 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 Database [Basic]、[S0] 和 [S1] 服務目標，以及彈性集區中的資料庫，需要伺服器管理員帳戶、Azure Active Directory 管理員帳戶或##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)