リアルタイムでクエリの進行状況を出力する：SQL Server動的管理ビューレファレンス（66）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、リアルタイムでクエリの進行状況を出力する方法について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_exec_query_profiles」における、リアルタイムでクエリの進行状況を出力する方法について解説します。対応バージョンは、SQL Server（サポートされている全てのバージョン）です。

概要

　SQL Server 2016のSQL Server Management Studioから、ライブクエリ統計機能によりリアルタイムでクエリの進行情報を確認できるようになりました。ライブクエリ統計の機能では、経過時間や進捗（しんちょく）状況などの全体的なクエリの状態と、実行プランのオペレーターレベルでの実行統計を、クエリの完了を待つことなくリアルタイムの情報として表示できます。

　「sys.dm_exec_query_profiles」動的管理ビューを使用することで、ライブクエリ統計と同様に、オペレーターレベルでの実行統計をリアルタイムに出力できます。そのため、「sys.dm_exec_query_profiles」動的管理ビューを繰り返し出力しておくことで、後から実行時間の長いクエリやタイムアウトの問題をデバッグすることができます。

　なお、「sys.dm_exec_query_profiles」動的管理ビューを使用してクエリの実行統計を出力するには、出力対象のクエリを実行するセッションで、クエリ実行統計プロファイリングインフラストラクチャが有効となっている必要があります。

　SQL Server 2019では、既定で軽量クエリ実行統計プロファイリングインフラストラクチャv3が有効化されていますが、SQL Server 2017以前のバージョンでは「SET STATISTICS XML」や「SET STATISTICS PROFILE」、トレースフラグ「7412」などを使用して、クエリ実行統計プロファイリングインフラストラクチャを有効化する必要があります。

• sys.dm_exec_query_profiles（Transact-SQL）

出力内容

列名	データ型	説明
session_id	smallint	クエリが実行されるセッションID
request_id	int	要求を識別するリスエストID
sql_handle	varbinary（64）	クエリが含まれているバッチまたはストアドプロシージャを一意に識別する識別子
plan_handle	varbinary（64）	実行中の実行プランを一意に識別する識別子
physical_operator_name	nvarchar（256）	物理オペレーター名
node_id	int	クエリツリー内のオペレーターノードの識別子
thread_id	int	同じクエリオペレーターノードに属するスレッド（並列クエリの場合）の識別子
task_address	varbinary（8）	このスレッドが使用しているSQLOSタスクの識別子
row_count	bigint	これまでにオペレーターによって返された行の数
rewind_count	bigint	これまでの巻き戻しの数
rebind_count	bigint	これまでの再バインドの数
end_of_scan_count	bigint	これまでのスキャンの終了数
estimate_row_count	bigint	推定行数
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	読み取りおよび書き込みが実行されたオブジェクトID
index_id	int	行セットをオープンする際のインデックスID（存在する場合）
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 SP1以降

動作例

　動作確認用のクエリを作成しました。クエリの先頭で「SET STATISTICS XML」を有効化して、標準クエリ実行統計プロファイリングインフラストラクチャを有効化しました（図1）。

図1　推定実行プランを表示すると並列クエリとなっている

　まずは「ライブクエリ統計を含む」オプションを有効化してクエリを実行し、ライブクエリ統計を表示してみます。ライブクエリ統計では、クエリ全体の経過時間や実行プラン内の各オペレーターの進捗状況などが表示されました（図2）。

図2　ライブクエリ統計では各オペレーターの進捗情報などを表示できる

　もう一度クエリを実行しながら、今度は別のセッションで「sys.dm_exec_query_profiles」動的管理ビューを出力してみます（図3）。

図3　「sys.dm_exec_query_profiles」動的管理ビューによりクエリの進捗状況を表示したところ

　SQL Server 2019では、既定で軽量クエリ実行統計プロファイリングインフラストラクチャv3が有効になっているため、対象のクエリ以外にも「sys.dm_exec_query_profiles」動的管理ビューを出力したクエリを対象とした結果が表示されました。

　「sys.dm_exec_query_profiles」動的管理ビューでは、演算子オペレーターノードごとにスレッド別で情報が出力されました。並列クエリをライブクエリ統計で表示してもスレッド別の進捗状況は表示されませんので、「sys.dm_exec_query_profiles」動的管理ビューの方が詳しい情報を確認できました。

　なお、クエリの実行終了後に「sys.dm_exec_query_profiles」動的管理ビューを実行しても、実行が終了したクエリの情報は表示されないようです。

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019 RC1」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

日本ユニシス株式会社所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

日本ユニシス株式会社所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。