sys.dm_exec_cached_plans (Transact-SQL)

發行項
03/03/2023

適用于：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。若為 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：預存程式備妥：備妥的語句臨機操作：臨機操作查詢。指的是使用 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基本、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)