sys.dm_exec_query_plan_stats (Transact-SQL)

文章
05/04/2023

適用於：SQL Server 2019 (15.x) Azure SQL 資料庫 Azure SQL 受控執行個體

針對先前快取的查詢計劃，傳回最後已知實際執行計畫的對等值。

語法

sys.dm_exec_query_plan_stats ( plan_handle )

引數

plan_handle

此 Token 用於唯一識別已執行的批次查詢執行計畫，以及位於計畫快取中或目前執行中的計畫。 plan_handle 為 varbinary(64)。

您可以從下列動態管理物件中取得 plan_handle：

傳回的資料表

資料行名稱	資料類型	描述
dbid	smallint	編譯對應這個計畫的 Transact-SQL 陳述式時，作用中內容資料庫的識別碼。對於隨選和準備的 SQL 陳述式而言，則為編譯陳述式的資料庫識別碼。資料行可為 Null。
objectid	int	這個查詢計畫的物件識別碼 (如預存程序或使用者自訂函數)。若為特定和準備批次，這個資料行是 Null。資料行可為 Null。
number	smallint	編號預存程序整數。例如，orders 應用程式的一組程序可以命名為 orderproc;1、orderproc;2，依此類推。若為特定和準備批次，這個資料行是 Null。資料行可為 Null。
encrypted	bit	指出對應的預存程序是否加密。 0 = 未加密 1 = 加密資料行不可為 Null。
query_plan	xml	包含以 plan_handle 指定的實際查詢執行計畫的上一個已知執行階段執行程序表表示法。顯示計畫是 XML 格式。每個包含諸如特定 Transact-SQL 陳述式、預存程序呼叫和使用者定義函式呼叫的批次，都會產生一份計畫。資料行可為 Null。

備註

這是一項選擇性功能。若要在伺服器層級啟用，請使用追蹤旗標 2451。若要在資料庫層級啟用，請使用 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的 [LAST_QUERY_PLAN_STATS] 選項。

此系統函數可在「輕量型」查詢執行統計資料分析基礎結構下運作。如需詳細資訊，請參閱查詢分析基礎結構。

sys.dm_exec_query_plan_stats 的執行程序表輸出包含下列資訊：

在快取計畫中找到的所有編譯時間資訊
執行階段資訊，例如每個運算子的實際資料列數、查詢 CPU 時間和執行時間總計、溢寫警告、實際 DOP、已使用記憶體和授權記憶體的最大值

在下列情況下，sys.dm_exec_query_plan_stats 的傳回資料表中的 query_plan 資料行會傳回「相當於實際執行計畫的」執行程序表輸出：

該計畫位於 sys.dm_exec_cached_plans。

AND
正在執行的查詢很複雜或很耗資源。

在下列情況下，sys.dm_exec_query_plan_stats 的傳回資料表中的 query_plan 資料行會傳回「簡易」¹ 執行程序表輸出：

該計畫位於 sys.dm_exec_cached_plans。

AND
查詢很簡單，通常分類為 OLTP 工作負載的一部分。

¹ 是指僅包含根節點運算子 (SELECT) 的執行程序表。

在下列情況下，sys.dm_exec_query_plan_stats 不會傳回任何輸出：

使用 plan_handle 指定的查詢計劃已從計畫快取收回。

OR
第一個位置無法快取查詢計劃。如需詳細資訊，請參閱執行計畫快取和重複使用。

注意

由於 xml 資料類型允許的巢狀層級數目有限制，因此 sys.dm_exec_query_plan 無法傳回巢狀元素等於或超過 128 個層級的查詢計劃。在舊版 SQL Server 中，這種情況會使查詢計劃無法傳回並產生錯誤 6335。在 SQL Server 2005 (9.x) Service Pack 2 和更新版本中，query_plan 資料行會傳回 NULL。

權限

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

SQL Server 2022 及更新版本的權限

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

範例

A. 查看特定快取計畫的最後已知實際查詢執行計畫

下列範例會查詢 sys.dm_exec_cached_plans 以尋找相關計畫，並從輸出中複製其 plan_handle。

SELECT * FROM sys.dm_exec_cached_plans;
GO

接下來，請使用複製的 plan_handle 與系統函數 sys.dm_exec_query_plan_stats 取得上一個已知的實際查詢執行計畫。

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);
GO

B. 查看所有快取計畫的上一個已知實際查詢執行計畫

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;
GO

C. 查看特定快取計畫和查詢文字的上一個已知實際查詢執行計畫

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';
GO

D. 查看觸發程序的快取事件

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO

分享方式：