sys.dm_exec_text_query_plan (Transact-SQL)

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

針對 Transact-SQL 批次或批次內的特定語句，以文字格式傳回 Showplan。 計畫控制代碼指定的查詢計畫可以是快取或目前正在執行的。 此資料表值函式類似于 sys.dm_exec_query_plan (Transact-SQL) ，但有下列差異：

• 查詢計畫的輸出會以文字格式傳回。
• 查詢計畫的輸出沒有大小限制。
• 可以指定批次內的個別陳述式。

適用于：SQL Server (SQL Server 2008 (10.0.x) 及更新版本) ，Azure SQL Database。

Transact-SQL 語法慣例

語法

sys.dm_exec_text_query_plan   
(   
    plan_handle   
    , { statement_start_offset | 0 | DEFAULT }  
        , { statement_end_offset | -1 | DEFAULT }  
)  

引數

plan_handle
這是一個權杖，可唯一識別已執行的批次查詢執行計畫，且其計畫位於計畫快取中，或目前正在執行中。 plan_handle為 Varbinary (64) 。

您可以從下列動態管理物件取得 plan_handle ：

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

statement_start_offset |0 |預設
表示資料列於其批次或保存物件的文字中描述之查詢的起始位置 (以位元組為單位)。 statement_start_offset 為 int。值為 0 表示批次的開頭。 預設值為 0。

您可以從下列動態管理物件中取得陳述式開頭位移：

• sys.dm_exec_query_stats

• sys.dm_exec_requests

statement_end_offset |-1 |預設
表示資料列於其批次或保存物件的文字中描述之查詢的結束位置 (以位元組為單位)。

statement_start_offset 為 int。

-1 值代表批次的結尾。 預設值為 -1。

傳回的資料表

資料行名稱	資料類型	描述
dbid	smallint	編譯對應至此計畫之 Transact-SQL 語句時生效的內容資料庫識別碼。 對於隨選和準備的 SQL 陳述式而言，則為編譯陳述式的資料庫識別碼。 資料行可為 Null。
objectid	int	這個查詢計畫的物件識別碼 (如預存程序或使用者自訂函數)。 若為特定和準備批次，這個資料行是 Null。 資料行可為 Null。
number	smallint	編號預存程序整數。 例如，orders 應用程式的一組程序可以命名為 orderproc;1、orderproc;2，依此類推。 若為特定和準備批次，這個資料行是 Null。 資料行可為 Null。
加密	bit	指出對應的預存程序是否加密。 0 = 未加密 1 = 加密 資料行不可為 Null。
query_plan	nvarchar(max)	包含使用 plan_handle指定的查詢執行計畫的編譯時間 Showplan 標記法。 顯示計畫是文字格式。 每個批次都會產生一個方案，例如臨機操作 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 (字串) ，則使用者定義函數的編譯 XML Showplan 不會包含在批次或預存程式 sys.dm_exec_text_query_plan 傳回的資料表中。 相反地， 您必須針對對應 至使用者定義函式 的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 權限。

SQL Server 2022 和更新版本的許可權

需要伺服器上的 VIEW SERVER PERFORMANCE STATE 許可權。

範例

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

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

注意

若要執行此範例，請將 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 動態管理檢視，來擷取快取中所有查詢計畫的計畫控制代碼。 計畫控制代碼會儲存在 plan_handle 的 sys.dm_exec_cached_plans 資料行中。 之後，請依照下列方式，利用 CROSS APPLY 運算子，將計畫控制代碼傳給 sys.dm_exec_text_query_plan。 目前計畫快取中每個計畫的 Showplan 輸出位於 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 動態管理檢視，以擷取快取中這些計畫的計畫控制代碼。 計畫控制代碼會儲存在 plan_handle 的 sys.dm_exec_query_stats 資料行中。 之後，請依照下列方式，利用 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  

另請參閱

sys.dm_exec_query_plan (Transact-SQL)