sys.dm_exec_query_profiles (Transact-SQL)

[アーティクル]
05/23/2023

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

クエリの実行中にリアルタイムでクエリの進行状況を監視します。たとえば、この DMV を使用して、クエリのどの部分の実行速度が遅いかを判断します。この DMV をシステムの他の DMV と結合するには、説明フィールドで特定されている列を使用します。または、タイムスタンプ列を使用して、この DMV を他のパフォーマンスカウンター (パフォーマンスモニター、xperf など) と結合します。

返されるテーブル

返されるカウンターは、スレッドごとの演算子ごとに返されます。結果は動的であり、クエリが完了したときにのみ出力を作成するなど、既存のオプション SET STATISTICS XML ON の結果と一致しません。

列名	データ型	説明
session_id	smallint	このクエリが実行されるセッションを識別します。 dm_exec_sessions.session_id を参照します。
request_id	int	ターゲット要求を識別します。 dm_exec_sessions.request_id を参照します。
sql_handle	varbinary(64)	クエリが含まれているバッチまたはストアドプロシージャを一意に識別するトークンを指定します。 dm_exec_query_stats.sql_handle を参照します。
plan_handle	varbinary(64)	既に実行されてプランがプランキャッシュに格納されているバッチ、または現在実行中のバッチに関するクエリ実行プランの一意識別子を指定するトークンです。 dm_exec_query_stats.plan_handle を参照します。
physical_operator_name	nvarchar (256)	物理オペレーター名。
node_id	int	クエリツリー内の演算子ノードを識別します。
thread_id	int	同じクエリ演算子ノードに属するスレッド (並列クエリの場合) を区別します。
task_address	varbinary(8)	このスレッドが使用している SQLOS タスクを識別します。 dm_os_tasks.task_address を参照します。
row_count	bigint	これまでに演算子によって返された行の数。
rewind_count	bigint	これまでの巻き戻しの数。
rebind_count	bigint	これまでの再バインドの数。
end_of_scan_count	bigint	これまでのスキャンの終了の数。
estimate_row_count	bigint	推定行数。 estimated_row_countと実際のrow_countを比較すると便利です。
first_active_time	bigint	演算子が最初に呼び出されたときの時間 (ミリ秒単位)。
last_active_time	bigint	演算子が最後に呼び出された時刻 (ミリ秒単位)。
open_time	bigint	開いたときのタイムスタンプ (ミリ秒単位)。
first_row_time	bigint	最初の行が開かれた場合のタイムスタンプ (ミリ秒)。
last_row_time	bigint	最後の行を開いたときのタイムスタンプ (ミリ秒単位)。
close_time	bigint	終了時のタイムスタンプ (ミリ秒)。
elapsed_time_ms	bigint	これまでにターゲットノードの操作で使用された経過時間の合計 (ミリ秒)。
cpu_time_ms	bigint	これまでにターゲットノードの操作で使用された CPU 時間の合計 (ミリ秒)。
database_id	smallint	読み取りおよび書き込みが実行されたオブジェクトを含むデータベースの ID。
object_id	int	読み取りおよび書き込みが実行されたオブジェクトの識別子。 sys.objects.object_id を参照します。
index_id	int	行セットが開かれている対象のインデックス (ある場合)。
scan_count	bigint	これまでのテーブル/インデックススキャンの数。
logical_read_count	bigint	これまでの論理読み取りの数。
physical_read_count	bigint	これまでの物理読み取りの数。
read_ahead_count	bigint	これまでの先読みの数。
write_page_count	bigint	書き込みによるこれまでのページ書き込みの数。
lob_logical_read_count	bigint	これまでの LOB 論理読み取りの数。
lob_physical_read_count	bigint	これまでの LOB 物理読み取りの数。
lob_read_ahead_count	bigint	これまでの LOB 先行読み取りの数。
segment_read_count	int	これまでのセグメント先行読み取りの数。
segment_skip_count	int	これまでにスキップされたセグメントの数。
actual_read_row_count	bigint	残差述語が適用される前に演算子によって読み取られた行の数。
estimated_read_row_count	bigint	適用対象: SQL Server 2016 (13.x) SP1 以降。残差述語が適用される前に演算子によって読み取られると推定される行数。

全般的な解説

クエリプランノードに I/O がない場合、すべての I/O 関連カウンターが NULL に設定されます。

この DMV によって報告される I/O 関連カウンターは、次の 2 つの方法で報告される SET STATISTICS IO カウンターよりも詳細です。

SET STATISTICS IO は、すべての I/O のカウンターを特定のテーブルにまとめてグループ化します。この DMV を使用すると、テーブルへの I/O を実行するクエリプラン内のすべてのノードに対して個別のカウンターが取得されます。
並列スキャンがある場合、この DMV では、スキャンで使用される並列スレッドごとにカウンターがレポートされます。

SQL Server 2016 (13.x) SP1 以降、標準的なクエリ実行統計プロファイルインフラストラクチャは、軽量クエリ実行統計プロファイルインフラストラクチャと並べて存在します。 SET STATISTICS XML ON標準SET STATISTICS PROFILE ONのクエリ実行統計プロファイルインフラストラクチャを常に使用します。 sys.dm_exec_query_profiles設定するには、クエリプロファイルインフラストラクチャの 1 つを有効にする必要があります。詳細については、「クエリプロファイリングインフラストラクチャ」を参照してください。

Note

調査中のクエリは、クエリプロファイルインフラストラクチャが有効になった後に開始する必要があります。クエリの開始後に有効にすると、結果sys.dm_exec_query_profilesは生成されません。クエリプロファイルインフラストラクチャを有効にする方法の詳細については、「クエリプロファイルインフラストラクチャ」を参照してください。

アクセス許可

SQL Server と Azure SQL Managed Instance では、データベースロールのアクセス許可とメンバーシップがdb_owner必要VIEW DATABASE STATEです。
Azure SQL Database プレミアム層では、データベースのVIEW DATABASE STATEアクセス許可が必要です。
Azure SQL Database Basic、S0、S1 のサービス目標と、エラスティックプール内のデータベースの場合は、サーバー管理者アカウントまたは Microsoft Entra 管理者アカウントが必要です。他のすべての SQL Database サービス目標では、データベースで VIEW DATABASE STATE アクセス許可が必要です。

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

データベースに対する VIEW DATABASE PERFORMANCE STATE アクセス許可が必要です。

例

手順 1: 分析 sys.dm_exec_query_profiles対象のクエリを実行する予定のセッションにログインします。プロファイリングに使用 SET STATISTICS PROFILE ONするクエリを構成するには、この同じセッションでクエリを実行します。

--Configure query for profiling with sys.dm_exec_query_profiles  
SET STATISTICS PROFILE ON;  
GO  

--Or enable query profiling globally under SQL Server 2016 SP1 or above (not needed in SQL Server 2019)  
DBCC TRACEON (7412, -1);  
GO 
  
--Next, run your query in this session, or in any other session if query profiling has been enabled globally

手順 2: クエリが実行されているセッションとは異なる 2 番目のセッションにログインします。

次のステートメントは、セッション 54 で現在実行中のクエリによって行われた進行状況をまとめたものです。これを行うには、各ノードのすべてのスレッドからの出力行の合計数を計算し、そのノードの出力行の推定数と比較します。

--Run this in a different session than the session in which your query is running. 
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT node_id,physical_operator_name, SUM(row_count) row_count, 
  SUM(estimate_row_count) AS estimate_row_count, 
  CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)  
FROM sys.dm_exec_query_profiles   
WHERE session_id=54
GROUP BY node_id,physical_operator_name  
ORDER BY node_id;

参照

動的管理ビューと動的管理関数 (Transact-SQL)
実行関連の動的管理ビューと関数 (Transact-SQL)