sys.dm_exec_query_profiles (Transact-SQL)

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース はい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 では、スキャンで使用される並列スレッドごとにカウンターがレポートされます。

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

注意

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

アクセス許可

と SQL Server のAzure SQL Managed Instance、データベース VIEW DATABASE STATE ロールのアクセス許可とメンバーシップが db_owner 必要です。
Azure SQL データベースPremium レベルでは、 にはデータベースの VIEW DATABASE STATE アクセス許可が必要です。 [SQL Database Azure SQL データベース Basic、S0、S1 のサービス目標と、エラスティック プール内のデータベースの場合は、サーバー管理者アカウントまたはAzure Active Directory 管理者アカウントが必要です。 他のすべてのサービスSQL Database、データベース VIEW DATABASE 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)