sys.dm_exec_query_plan (Transact-SQL)

適用于: SQL Server Azure SQL 資料庫Azure SQL 受控執行個體

針對計畫控制代碼指定的批次,以 XML 格式傳回顯示計畫。 計畫控制代碼指定的計畫可以是快取或目前正在執行的計畫。

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

Transact-SQL 語法慣例

語法

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;1orderproc;2,依此類推。 若為特定和準備批次,這個資料行是 Null

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

0 = 未加密

1 = 加密

資料行不可為 Null。
query_plan xml 包含使用 plan_handle指定的查詢執行計畫的編譯時間 Showplan 標記法。 顯示計畫是 XML 格式。 每個批次都會產生一個方案,例如臨機操作 Transact-SQL 語句、預存程式調用和使用者定義函式呼叫。

資料行可為 Null。

備註

在下列狀況之下,sys.dm_exec_query_plan 傳回之資料表的 query_plan 資料行不會傳回任何顯示計畫輸出:

  • 如果使用 plan_handle 指定的查詢計劃已從計畫快取收回,則傳回資料表 的query_plan 資料行為 null。 例如,如果從擷取計畫控制代碼到 sys.dm_exec_query_plan 使用計畫控制代碼之間,延遲了一段時間,就可能出現這個情況。

  • 某些 Transact-SQL 語句不會快取,例如大量作業語句或包含大小大於 8 KB 的字串常值語句。 您無法利用 sys.dm_exec_query_plan 來擷取這些陳述式的 XML 顯示計畫,因為它們不在快取中,除非批次正在執行。

  • 如果 Transact-SQL 批次或預存套裝程式含使用者定義函數的呼叫或動態 SQL 的呼叫,例如使用 EXEC (字串) ,則使用者定義函數的編譯 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 伺服器的許可權。

範例

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

若要檢視 XML Showplans,請在 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 顯示計畫。

注意

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

首先,請利用 sp_who 預存程序來擷取正在執行查詢或批次之處理序的伺服器處理序識別碼 (SPID):

USE master;  
GO  
exec sp_who;  
GO  

sp_who 傳回的結果集指出 SPID 是 54。 您可以搭配 sys.dm_exec_requests 動態管理檢視來使用 SPID,以利用下列查詢來擷取計畫控制代碼:

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_handlesys.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_handlesys.dm_exec_query_stats 資料行中。 之後,請依照下列方式,利用 CROSS APPLY 運算子,將計畫控制代碼傳給 sys.dm_exec_query_plan。 伺服器已收集了目前在計畫快取中的統計資料之各項計畫之 XML 顯示計畫輸出,都是在傳回的資料表之 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)