sys.dm_exec_text_query_plan （Transact-SQL）

適用於：SQL ServerAzure SQL 資料庫 Azure SQL 受控執行個體

針對 Transact-SQL 批次或批次內的特定語句，傳回 Text 格式的 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。
encrypted	bit	指出對應的預存程序是否加密。 0 = 未加密 1 = 加密 數據行不可為 Null。
query_plan	nvarchar(max)	包含以 plan_handle 指定之查詢執行計劃的編譯時間執行計劃表示法。 Showplan 是文字格式。 每個包含諸如特定 Transact-SQL 陳述式、預存程序呼叫和使用者定義函式呼叫的批次，都會產生一份計畫。 資料行可為 Null。

備註

在下列情況下，傳回數據表的 plan 數據行中不會傳回任何 Showplan 輸出，sys.dm_exec_text_query_plan：

• 如果使用 plan_handle 指定的查詢計劃已從計劃快取收回，則傳回數據表query_plan數據行為 null。 例如，如果擷取計劃句柄與搭配 sys.dm_exec_text_query_plan使用時有時間延遲，就可能發生此狀況。

• 某些 Transact-SQL 語句不會快取，例如大量作業語句或包含大小大於 8 KB 的字串常值語句。 無法使用 sys.dm_exec_text_query_plan 擷取 這類語句的執行程式表，因為它們不存在於快取中。

• 如果 Transact-SQL 批次或預存程式包含使用者定義函數的呼叫或動態 SQL 的呼叫，例如使用 EXEC （string），則使用者定義函數的已編譯 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，用戶必須是系統管理員固定伺服器角色的成員，或具有伺服器上的 VIEW SERVER STATE 許可權。

SQL Server 2022 和更新版本的權限

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

範例

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

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

注意

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

首先，使用 sp_who 預存程式擷取執行查詢或批次之進程的伺服器進程識別碼 （SPID）：

USE master;  
GO  
EXEC sp_who;  
GO  

傳 sp_who 回的結果集表示 SPID 為 54。 您可以使用 SPID 搭配 sys.dm_exec_requests 動態管理檢視，使用下列查詢來擷取計劃句柄：

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 如下所示。 每個計劃的 Showplan 輸出位於 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）