sys.dm_exec_sql_text (Transact-SQL)

[アーティクル]
07/05/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

指定した sql_handleによって識別される SQL バッチのテキストを返します。このテーブル値関数は、システム関数の fn_get_sqlを置き換えます。

構文

sys.dm_exec_sql_text(sql_handle | plan_handle)

引数

sql_handle
実行中または現在実行中のバッチを一意に識別するトークンです。 sql_handle は varbinary(64)です。

sql_handleは、次の動的管理オブジェクトから取得できます。

plan_handle
既に実行されてプランがプランキャッシュに格納されているバッチ、または現在実行中のバッチに関するクエリ実行プランの一意識別子を指定するトークンです。 plan_handle は varbinary(64) です。

次の動的管理オブジェクトから plan_handle を取得できます。

返されるテーブル

列名	データ型	説明
dbid	smallint	データベースの ID。ストアドプロシージャ内の静的 SQL の場合、ストアドプロシージャを含むデータベースの ID。その他の場合は NULL が返されます。
objectid	int	オブジェクトの ID。アドホックおよび準備済み SQL ステートメントの場合は NULL です。
number	smallint	番号付きストアドプロシージャの場合、この列はストアドプロシージャの番号を返します。詳細については、「 sys.numbered_procedures (Transact-SQL)」を参照してください。アドホックおよび準備済み SQL ステートメントの場合は NULL です。
encrypted	bit	1 = SQL テキストは暗号化されます。 0 = SQL テキストは暗号化されません。
text	nvarchar(max )	SQL クエリのテキスト。暗号化されたオブジェクトの場合は NULL です。

アクセス許可

サーバーに対する VIEW SERVER STATE 権限が必要です。

SQL Server 2022 以降でのアクセス許可

サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。

解説

アドホッククエリの場合、SQL ハンドルはサーバーに送信される SQL テキストに基づくハッシュ値であり、任意のデータベースから送信できます。

ストアドプロシージャ、トリガー、関数などのデータベースオブジェクトの場合、SQL ハンドルはデータベース ID、オブジェクト ID、およびオブジェクト番号から派生します。

プランハンドルは、バッチ全体のコンパイル済みプランから派生したハッシュ値です。

Note

アドホッククエリのsql_handleから dbid を特定することはできません。アドホッククエリの dbid を決定するには、代わりに plan_handle を使用します。

例

A. 概念の例

次に示すのは、 sql_handle を直接渡すか、 CROSS APPLY を使用して渡す方法を示す基本的な例です。

アクティビティを作成します。
SQL Server Management Studio の新しいクエリウィンドウで、次の T-SQL を実行します。
```
-- Identify current spid (session_id)
SELECT @@SPID;
GO

-- Create activity
  WAITFOR DELAY '00:02:00';
```
CROSS APPLY を使用します。
sys.dm_exec_requestsからのsql_handleは、CROSS APPLY を使用してsys.dm_exec_sql_textに渡されます。新しいクエリウィンドウを開き、手順 1 で識別された spid を渡します。この例では、spid は 59されます。
```
SELECT t.*
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id = 59 -- modify this value with your actual spid
```
sql_handleを直接渡します。
sys.dm_exec_requestsからsql_handleを取得します。次に、 sql_handle を sys.dm_exec_sql_textに直接渡します。新しいクエリウィンドウを開き、手順 1 で指定した spid を sys.dm_exec_requestsに渡します。この例では、spid は 59されます。次に、返された sql_handle を引数として sys.dm_exec_sql_textに渡します。
```
-- acquire sql_handle
SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = 59  -- modify this value with your actual spid

-- pass sql_handle to sys.dm_exec_sql_text
SELECT * FROM sys.dm_exec_sql_text(0x01000600B74C2A1300D2582A2100000000000000000000000000000000000000000000000000000000000000) -- modify this value with your actual sql_handle
```

B. 平均 CPU 時間で上位 5 つのクエリに関する情報を取得する

次の例では、上位 5 つのクエリにかかった平均 CPU 時間と SQL ステートメントのテキストを返します。

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement_end_offset  
         END - qs.statement_start_offset)/2) + 1) AS statement_text  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
ORDER BY total_worker_time/execution_count DESC;

C: バッチ実行の統計情報を提供する

次の例では、バッチで実行されている SQL クエリのテキストを返し、それらに関する統計情報を提供します。

SELECT s2.dbid,   
    s1.sql_handle,    
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,   
      ( (CASE WHEN statement_end_offset = -1   
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)   
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,  
    execution_count,   
    plan_generation_num,   
    last_execution_time,     
    total_worker_time,   
    last_worker_time,   
    min_worker_time,   
    max_worker_time,  
    total_physical_reads,   
    last_physical_reads,   
    min_physical_reads,    
    max_physical_reads,    
    total_logical_writes,   
    last_logical_writes,   
    min_logical_writes,   
    max_logical_writes    
FROM sys.dm_exec_query_stats AS s1   
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2    
WHERE s2.objectid is null   
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

次の方法で共有