sys.dm_exec_query_plan

更新： 2006 年 12 月 12 日

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

Microsoft 網站發行及提供顯示計劃的 XML 結構描述。您也可以在安裝 SQL Server 2005 的目錄中找到此檔案，其位置如下：

\\Program Files\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

Transact-SQL 語法慣例

語法

sys.dm_exec_query_plan ( plan_handle )

引數

• plan_handle
  用來唯一識別批次的查詢計劃，該批次可能已快取或正在執行。

  plan_handle 是 varbinary(64)。您可以從下列動態管理物件取得 plan_handle：

  sys.dm_exec_cached_plans

  sys.dm_exec_query_stats

  sys.dm_exec_requests

傳回的資料表

資料行名稱	資料類型	描述
dbid	smallint	當編譯對應於這個計劃的 Transact-SQL 陳述式時，作用中內容資料庫的識別碼。若為特定和準備批次，這個資料行是 Null。 資料行可為 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。

備註

在下列狀況之下，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 (string)，批次或預存程序的 sys.dm_exec_query_plan 所傳回的資料表，就不會包括使用者自訂函數之已編譯的 XML 顯示計劃。相反地，您必須針對使用者自訂函數所對應的計劃控制代碼，來個別呼叫 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 2005 中，這可避免查詢計劃傳回和產生錯誤 6335。在 Service Pack 2 中，query_plan 資料行會傳回 NULL。您可以使用 sys.dm_exec_text_query_plan 動態管理函數，以文字格式傳回查詢計劃的輸出。

權限

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

範例

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

若要檢視 XML 顯示計劃，請利用 SQL Server Management Studio 的查詢編輯器來執行下列查詢，之後，在 sys.dm_exec_query_plan 傳回的資料表中，按一下 query_plan 資料行中的 [ShowPlanXML]。此時 Management Studio 摘要窗格會顯示 XML 顯示計劃。若要將 XML 顯示計劃儲存到檔案中，請以滑鼠右鍵按一下 query_plan 資料行中的 [ShowPlanXML]，按一下 [儲存結果]，再依照 <file_name>.sqlplan 格式來指定檔案名稱，例如 MyXMLShowplan.sqlplan。

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

在 SQL Server 2005 中，各類型 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，您可以依照下列方式，利用 sys.dm_exec_query_plan 將它指定成 plan_handle 引數來擷取 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 動態管理檢視，來擷取快取中所有查詢計劃的計劃控制代碼。計劃控制代碼會儲存在 sys.dm_exec_cached_plans 的 plan_handle 資料行中。之後，請依照下列方式，利用 CROSS APPLY 運算子，將計劃控制代碼傳給 sys.dm_exec_query_plan。目前在計劃快取中的每項計劃之 XML 顯示計劃輸出，都是在傳回的資料表之 query_plan 資料行中。

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

C. 擷取伺服器已從計劃快取中收集了查詢統計資料的每項查詢計劃

若要擷取伺服器已收集了目前在計劃快取中的統計資料之所有查詢計劃的快照集，請查詢 sys.dm_exec_query_stats 動態管理檢視，以擷取快取中這些計劃的計劃控制代碼。計劃控制代碼會儲存在 sys.dm_exec_query_stats 的 plan_handle 資料行中。之後，請依照下列方式，利用 CROSS APPLY 運算子，將計劃控制代碼傳給 sys.dm_exec_query_plan。伺服器已收集了目前在計劃快取中的統計資料之各項計劃之 XML 顯示計劃輸出，都是在傳回的資料表之 query_plan 資料行中。

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats 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

請參閱

參考

動態管理檢視和函數
sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_requests
sp_who (Transact-SQL)
sys.dm_exec_text_query_plan

其他資源

XML 顯示計劃
使用 APPLY
執行計劃快取與重複使用
邏輯與實體運算子參考
查詢效能

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本	歷程記錄
2006 年 12 月 12 日	新增內容： 新增當查詢計劃包含 128 或更多計劃層級時，這個函數會有的行為。 新增傳回參數化特定查詢的查詢計劃之相關資訊。 新增範例 D。 變更的內容： 釐清主題，指出查詢計劃可以是快取或目前正在執行的。 修改 dbid、objectid 和 number 的定義，指出特定查詢和準備批次的資料行會傳回 NULL。
2005 年 12 月 5 日	變更的內容： 變更顯示計劃 XML 結構描述的安裝位置。