件名の通りですが、SQL Database v12 や SQL Server 2014 以降では、「クエリの進捗どうですか (クエリの進行状況)」 を取得するための DMV が使用できるようになっています。
あまり知られていない DMV のような気がしたので、紹介してみたいと思います。
SQL Server 2016 ベースのデータベースエンジンのサーバーであれば「ライブクエリ統計」の機能を使用することで、クエリの進行状況を取得することができます。
この機能自体は、SQL Server 2016 の SSMS から使用できるものとなりますが、対象は SQL Server 2014 以降となっており、SQL Server 2014 でも使用することができます。
この理由が、「sys.dm_exec_query_profiles」という動的管理ビューのサポート状況になります。
この動的管理ビューは「SET STATISTICS PROFILE ON」または、「SET STATISTICS XML ON」のどちらかが有効になっている場合に、有効にしたクエリの進行状況を取得することが可能となります。
この条件は、「利用状況モニター」からクエリの実行状況を確認する場合と同じですね。
今回は SQL Database v12 で以下のクエリを実行した際の実行状況を確認してみます。
SET STATISTICS PROFILE ON ALTER TABLE PARTSUPP REBUILD
インデックスの再構築を実行するクエリですが、クエリの最初に「STATISITCS PROFILE ON」を実行し、その後に、インデックスの再構築を実行しています。
このクエリが実行されている状態で、以下のクエリを実行して、クエリの進行状況を取得することができます。
SELECT qp.session_id, qp.request_id, t.text, --p.query_plan, ot.task_state, qp.physical_operator_name, qp.node_id, qp.thread_id, qp.row_count, qp.estimate_row_count, CASE row_count WHEN 0 THEN 0.0 ELSE convert(float,qp.row_count) / qp.estimate_row_count * 100.0 END as progress FROM sys.dm_exec_query_profiles qp CROSS APPLY sys.dm_exec_sql_text (sql_handle) t CROSS APPLY sys.dm_exec_query_plan(plan_handle) p LEFT JOIN sys.dm_os_tasks ot ON qp.task_address = ot.task_address ORDER BY session_id, request_id, node_id, thread_id
以下の画像は、実行中のクエリの状態を取得したものとなるのですが「progress」が進んでいることが確認できるかと。
オンライン系のショートトランザクションで取得を行った場合には、頻繁に情報を取得することのオーバーヘッドがあるかと思いますので、利用することはないかと思いますが、バッチ系の一回の実行が長いクエリでこの機能が利用できるようにクエリ実行をしておくと、進捗が確認でき、便利かと思います。
インデックスの再構築についても再構築の処理の状態が取得できますので、便利かと。