sys.dm_exec_text_query_plan （Transact-SQL）

適用於：SQL ServerAzure SQL DatabaseAzure 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）