sys.dm_exec_query_plan (Transact-SQL)

適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Microsoft Fabric 中的 SQL 資料庫

動態管理函式會 sys.dm_exec_query_plan 回傳計畫以XML格式呈現,針對計畫句柄指定的批次。 計劃句柄指定的計劃可以快取或目前正在執行。

Showplan 的 XML 架構已發行,並在此Microsoft網站上提供。 它也可在安裝 SQL Server 的目錄中取得。

Transact-SQL 語法慣例

Syntax

sys.dm_exec_query_plan(plan_handle)

引數

plan_handle

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

plan_handle可以從下列動態管理物件取得 :

傳回的資料表

欄位名稱 資料類型 Description
dbid smallint 編譯對應這個計畫的 Transact-SQL 陳述式時,作用中內容資料庫的識別碼。 對於隨選和準備的 SQL 陳述式而言,則為編譯陳述式的資料庫識別碼。

資料行可為 Null。
objectid int 這個查詢計畫的物件識別碼 (如預存程序或使用者自訂函數)。 對於臨時批次和預備批次,此欄位為 NULL。

資料行可為 Null。
number smallint 編號預存程序整數。 例如,一組用於 命令 申請的程序可能會命名 orderproc;1為 、 orderproc;2,等等。 對於臨時批次和預備批次,此欄位為 NULL。

資料行可為 Null。
encrypted 位元 指出對應的預存程序是否加密。

0 = 未加密

1 = 加密

數據行不可為 Null。
query_plan xml 包含以 為 plan_handle的查詢執行計畫的編譯時 Showplan 表示。 Showplan 以 XML 格式表示。 每個包含諸如特定 Transact-SQL 陳述式、預存程序呼叫和使用者定義函式呼叫的批次,都會產生一份計劃。

資料行可為 Null。

備註

在下列情況下,與query_plan相關的結果表中的sys.dm_exec_query_plan欄位不會傳回任何 Showplan 輸出:

  • 若以 為 plan_handle 指定的查詢計畫已被從計畫快取中驅逐,則 query_plan 回傳資料表的欄位為 NULL。 例如,若計畫柄被擷取與使用之間存在時間延遲,則可能發生此條件 sys.dm_exec_query_plan

  • 某些 Transact-SQL 語句不會快取,例如大量作業語句或包含大小大於 8 KB 的字串常值語句。 除非批次目前正在執行,否則無法透過使用 sys.dm_exec_query_plan 來取得此類語句的 XML Showplan,因為這些 Showplans 不存在於快取中。

  • 若 Transact-SQL 批次或儲存程序包含對使用者定義函式的呼叫或動態 SQL,例如使用 EXEC(字串),則該使用者定義函式的編譯後 XML Showplan 不會包含在該批次或儲存程序回傳 sys.dm_exec_query_plan 的資料表中。 相反地,你必須對應使用者定義函式的計畫 handle 另行呼叫 sys.dm_exec_query_plan 。

當臨時查詢使用簡單或強制參數化時,欄位 query_plan 只會包含語句文字,而非實際的查詢計畫。 要回傳查詢計畫,請呼叫 sys.dm_exec_query_plan 已準備好參數化查詢的計畫句柄。 你可以透過參考 sqlsys.syscacheobjects 檢視的欄位或 sys.dm_exec_sql_text 動態管理檢視的文字欄位來判斷查詢是否被參數化。

Note

由於 XML 資料型別中巢狀層級數量有限, sys.dm_exec_query_plan 無法回傳符合或超過 128 層巢狀元素的查詢計畫。 在舊版 SQL Server 中,這種情況會使查詢計劃無法傳回並產生錯誤 6335。 在 SQL Server 2005 (9.x) Service Pack 2 和之後的版本中,query_plan 欄會傳回 NULL。
您可以使用 sys.dm_exec_text_query_plan (Transact-SQL) 動態管理函式,以文字格式傳回查詢計劃的輸出。

許可

要執行 sys.dm_exec_query_plan,使用者必須是 系統管理員 固定伺服器角色的成員,或擁有 VIEW SERVER STATE 伺服器權限。

SQL Server 2022 及更新版本的權限

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

Examples

以下範例展示了如何使用 sys.dm_exec_query_plan 動態管理視圖。

要查看 XML Showplans,請在 SQL Server Management Studio 的 查詢編輯器 執行以下查詢,然後在管理工作室摘要窗格中 sys.dm_exec_query_plan XML Showplan 顯示回傳的表格欄位中選擇 ShowPlanXMLquery_plan。 若要將 XML Showplan 儲存為檔案,請在欄位query_plan中右鍵點擊 ShowPlanXML,選擇另存結果為 Save Results,並以格式<file name>.sqlplan命名檔案;例如。 MyXMLShowplan.sqlplan

A. 擷取慢速執行 Transact-SQL 查詢或批次的快取查詢計劃

各種 Transact-SQL 批次類型的查詢計劃,例如臨機操作批次、預存程式和使用者定義函式,會在稱為計劃快取的記憶體區域中快取。 每個快取的查詢計劃都是由稱為計劃句柄的唯一標識碼來識別。 你可以用動態管理視圖指定這個計畫手柄 sys.dm_exec_query_plan ,以取得特定 Transact-SQL 查詢或批次的執行計畫。

如果 Transact-SQL 查詢或批次在特定與 SQL Server 的連線上長時間執行,請擷取該查詢或批次的執行計劃,以探索造成延遲的原因。 下列範例示範如何擷取慢速執行查詢或批次的 XML Showplan。

Note

要執行這個範例,請將 和 session_idplan_handle 的值替換成你伺服器專屬的值。

首先,透過儲存程序取得執行查詢或批次 sp_who 的程序的會話 ID(SPID):

exec sp_who;  
GO  

回傳的 sp_who 結果集表示會話 ID 為 54。 你可以利用動態 sys.dm_exec_requests 管理視圖中的會話 ID 來取得計畫句柄,並透過以下查詢:

SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

回傳的 sys.dm_exec_requests 表格顯示慢速查詢或批次的計畫代號為 0x06000100A27E7C1FA821B10600,你可以指定為 plan_handle 參數 sys.dm_exec_query_plan ,以以下方式取得執行計畫的 XML 格式。 慢速查詢或批次的執行計畫以 XML 格式呈現,包含在由 sys.dm_exec_query_plan返回的表格欄位中query_plan。

SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B. 從計劃快取擷取每個查詢計劃

若要擷取位於計劃快取中所有查詢計劃的快照集,請藉由查詢 sys.dm_exec_cached_plans 動態管理檢視,擷取快取中所有查詢計劃的計劃句柄。 計劃句柄會儲存在 plan_handle 的數據行中 sys.dm_exec_cached_plans。 然後使用 CROSS APPLY 運算子,將計劃句柄傳遞至 , sys.dm_exec_query_plan 如下所示。 目前在計畫快取中的每項計畫之 XML 顯示計畫輸出,都是在傳回的資料表之 query_plan 資料行中。

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C. 擷取伺服器從計劃快取收集查詢統計數據的每個查詢計劃

若要擷取伺服器收集目前位於計劃快取中之統計數據的所有查詢計劃快照集,請藉由查詢 sys.dm_exec_query_stats 動態管理檢視來擷取快取中這些計劃的計劃句柄。 計劃句柄會儲存在 plan_handle 的數據行中 sys.dm_exec_query_stats。 然後使用 CROSS APPLY 運算子,將計劃句柄傳遞至 , sys.dm_exec_query_plan 如下所示。 伺服器目前在計劃快取中收集統計數據之每個計劃的 XML Showplan 輸出位於 query_plan 傳回之數據表的數據行中。

SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. 依平均 CPU 時間擷取前五個查詢的相關信息

下列範例會傳回前五個查詢的計劃和平均CPU時間。

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
   plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO