sys.dm_exec_text_query_plan
新增： 2006 年 12 月 12 日
針對 Transact-SQL 批次或批次內的特定陳述式，以文字格式傳回顯示計劃。計劃控制代碼指定的查詢計劃可以是快取或目前正在執行的。這個資料表值函數類似於 sys.dm_exec_query_plan，但有下列差異：
- 查詢計劃的輸出會以文字格式傳回。
- 查詢計劃的輸出沒有大小限制。
- 可以指定批次內的個別陳述式。
.gif "主題連結圖示")
語法
sys.dm_exec_text_query_plan
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)
引數
plan_handle
用來唯一識別批次的查詢計劃，該批次可能已快取或正在執行。plan_handle 是 varbinary(64)。您可以從下列動態管理物件中取得計劃控制代碼：
statement_start_offset | 0 | DEFAULT
表示資料列於其批次或保存物件的文字中描述之查詢的起始位置 (以位元組為單位)。statement_start_offset 是 int。0 值代表批次的開頭。預設值是 0。您可以從下列動態管理物件中取得陳述式開頭位移：
statement_end_offset | -1 | DEFAULT
表示資料列於其批次或保存物件的文字中描述之查詢的結束位置 (以位元組為單位)。statement_start_offset 是 int。
-1 值代表批次的結尾。預設值是 -1。
傳回的資料表
| 資料行名稱 | 資料類型 | 描述 |
|---|---|---|
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 |
nvarchar(max) |
包含以 plan_handle 指定之查詢執行計劃的編譯階段顯示計劃表示法。顯示計劃是文字格式。每個包含諸如特定 Transact-SQL 陳述式、預存程序呼叫和使用者自訂函數呼叫的批次，都會產生一份計劃。 資料行可為 Null。 |
備註
在下列狀況中，sys.dm_exec_text_query_plan 的傳回資料表之 plan 資料行不會傳回任何顯示計劃輸出：
- 如果已從計劃快取中收回使用 plan_handle 指定的查詢計劃，傳回的資料表之 query_plan 資料行便是 Null。例如，如果從擷取計劃控制代碼到以 sys.dm_exec_text_query_plan 使用計劃控制代碼之間，延遲了一段時間，就可能出現這個情況。
- 尚未快取某些 Transact-SQL 陳述式，如大量作業陳述式或包含大小超出 8 KB 字串文字的陳述式。您無法利用 sys.dm_exec_text_query_plan 來擷取這些陳述式的 XML 顯示計劃，因為它們不在快取中。
- 如果 Transact-SQL 批次或預存程序包含使用者自訂函數呼叫或動態 SQL 呼叫，例如使用 EXEC (string)，批次或預存程序的 sys.dm_exec_text_query_plan 所傳回的資料表，就不會包括使用者自訂函數之已編譯的 XML 顯示計劃。相反地，您必須針對使用者自訂函數所對應的 plan_handle，個別呼叫 sys.dm_exec_text_query_plan。
當特定查詢使用簡單或強制參數化時，query_plan 資料行只會包含陳述式文字，而非實際查詢計劃。若要傳回查詢計劃，請呼叫 sys.dm_exec_text_query_plan，以取得準備參數化查詢的計劃控制代碼。您可以藉由參考 sys.syscacheobjects 檢視的 sql 資料行，或 sys.dm_exec_sql_text 動態管理檢視的文字資料行，判斷查詢是否參數化。如需有關參數化的詳細資訊，請參閱＜簡單參數化＞和＜強制參數化＞。
權限
若要執行 sys.dm_exec_text_query_plan，使用者必須是系統管理員 (sysadmin) 固定伺服器角色的成員，或有伺服器的 VIEW SERVER STATE 權限。
範例
A. 擷取執行緩慢的 Transact-SQL 查詢或批次之快取查詢計劃
如果 Transact-SQL 查詢或批次在特定 SQL Server 連接上執行了很長一段時間，請擷取這項查詢或批次的執行計劃來找出延遲的原因。下列範例會顯示如何針對執行緩慢的查詢或批次擷取顯示計劃。
.gif "Bb326654.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 若要執行這個範例，請用伺服器專用的值來取代 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。下列範例會傳回指定計劃控制代碼的查詢計劃，並使用預設值 0 和 -1 傳回查詢或批次中的所有陳述式。
USE master;
GO
SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
GO
B. 從計劃快取中擷取每份查詢計劃
若要擷取計劃快取中所有查詢計劃的快照集，請查詢 sys.dm_exec_cached_plans 動態管理檢視，來擷取快取中所有查詢計劃的計劃控制代碼。計劃控制代碼會儲存在 sys.dm_exec_cached_plans 的 plan_handle 資料行中。之後，請依照下列方式，利用 CROSS APPLY 運算子，將計劃控制代碼傳給 sys.dm_exec_text_query_plan。目前在計劃快取中的每項計劃之顯示計劃輸出，都是在傳回的資料表之 query_plan 資料行中。
USE master;
GO
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
GO
C. 擷取伺服器已從計劃快取中收集了查詢統計資料的每項查詢計劃
若要擷取伺服器已收集了目前在計劃快取中的統計資料之所有查詢計劃的快照集，請查詢 sys.dm_exec_query_stats 動態管理檢視，以擷取快取中這些計劃的計劃控制代碼。計劃控制代碼會儲存在 sys.dm_exec_query_stats 的 plan_handle 資料行中。之後，請依照下列方式，利用 CROSS APPLY 運算子，將計劃控制代碼傳給 sys.dm_exec_text_query_plan。每個計劃的顯示計劃輸出都在所傳回資料表的 query_plan 資料行中。
USE master;
GO
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
GO
D. 擷取按平均 CPU 時間排列之前五項查詢的相關資訊
下列範例會傳回前五項查詢的查詢計劃和平均 CPU 時間。sys.dm_exec_text_query_plan 函數會指定預設值 0 和 -1，傳回查詢計劃中批次的所有陳述式。
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_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;
GO