sys.dm_exec_query_plan （Transact-SQL）

文章
07/05/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

傳回計劃句柄所指定批次的 XML 格式 Showplan。計劃句柄指定的計劃可以快取或目前正在執行。

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

語法

sys.dm_exec_query_plan(plan_handle)

引數

plan_handle
這是標記，可唯一識別已執行之批次的查詢執行計劃，且其計劃位於計劃快取中，或目前正在執行中。 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。

備註

在下列情況下，傳回數據表的 query_plan 數據行不會傳回任何 Showplan 輸出，sys.dm_exec_query_plan：

如果使用 plan_handle 指定的查詢計劃已從計劃快取收回，則傳回數據表query_plan數據行為 null。例如，如果擷取計劃句柄與與sys.dm_exec_query_plan搭配使用時，可能會發生此狀況。
某些 Transact-SQL 語句不會快取，例如大量作業語句或包含大小大於 8 KB 的字串常值語句。除非批次目前正在執行，否則無法使用 sys.dm_exec_query_plan 擷取這類語句的 XML Showplans，因為它們不存在於快取中。
如果 Transact-SQL 批次或預存程式包含對使用者定義函數的呼叫或動態 SQL 的呼叫，例如使用 EXEC （string），則使用者定義函數的 已編譯 XML Showplan 不會包含在批次或預存程式所傳回的數據表中sys.dm_exec_query_plan 。相反地，您必須針對對應至 使用者定義函式的計劃句柄，對sys.dm_exec_query_plan 進行個別呼叫。

當臨機操作查詢使用簡單或強制參數化時， query_plan 數據行只會包含語句文字，而不是實際的查詢計劃。若要傳回查詢計劃，請針對已備妥參數化查詢的計劃句柄呼叫 sys.dm_exec_query_plan 。您可以參考 sys.syscacheobjects 檢視的 sql 數據行，或sys.dm_exec_sql_text動態管理檢視的文字數據行，來判斷查詢是否已參數化。

注意

由於 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 權限。

範例

下列範例示範如何使用 sys.dm_exec_query_plan 動態管理檢視。

若要檢視 XML 執行程式表，請在 SQL Server Management Studio 的查詢編輯器中執行下列查詢，然後按兩下 sys.dm_exec_query_plan 所傳回之數據表的 query_plan 數據行中的 ShowPlanXML。 XML Showplan 會顯示在 Management Studio 摘要窗格中。若要將 XML Showplan 儲存至檔案，請在 [query_plan] 數據行中以滑鼠右鍵按兩下 [ShowPlanXML]，按兩下 [另存結果為]，將檔案命名為 file_name.sqlplan> 格式<;例如 MyXMLShowplan.sqlplan。

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

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

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

注意

若要執行此範例，請將 session_id 和 plan_handle 的值取代為伺服器特定的值。

首先，使用 sp_who 預存程式擷取執行查詢或批次之進程的伺服器進程識別碼（SPID）：

USE master;  
GO  
exec sp_who;  
GO

傳 sp_who 回的結果集表示 SPID 為 54。您可以使用 SPID 搭配 sys.dm_exec_requests 動態管理檢視，使用下列查詢來擷取計劃句柄：

USE master;  
GO  
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 資料行中。

USE master;  
GO  
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 資料行中。

USE master;  
GO  
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 傳回之數據表的數據行中。

USE master;  
GO  
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

另請參閱

動態管理檢視和函數 (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
執行程序邏輯和實體運算子參考
 sys.dm_exec_text_query_plan （Transact-SQL）

分享方式：