sys.dm_exec_query_plan (Transact-SQL)

發行項
03/07/2014

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

Microsoft 網站發行及提供顯示計畫的 XML 結構描述。安裝有 SQL Server 的目錄也會提供這個項目。

適用於：SQL Server (SQL Server 2008 透過目前版本)、Windows Azure 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 陳述式時，作用中內容資料庫的識別碼。對於隨選和準備的 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。

備註

在下列狀況之下，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 中，這會讓查詢計畫無法傳回並產生錯誤 6335。在 SQL Server 2005 Service Pack 2 和更新版本中，query_plan 資料行會傳回 NULL。您可以使用 sys.dm_exec_text_query_plan (Transact-SQL) 動態管理函數，以文字格式傳回查詢計畫的輸出。

權限

若要執行 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 查詢或批次之快取查詢計畫

各類型 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