SQL Server 2016 以降ではクエリプロファイリングにより、実行中のクエリの情報の分析容易性が向上しています。
この機能は、かなり強力であり、実行中のクエリに対して、様々なアプローチが可能となります。
活用の方法として「インデックスの再構築」と組み合わせた方法を紹介したいと思います。
今回は SQL Server 2017 を例としています。
インデックスの再構築を実行した際に確認したい情報の一つとして「実行中の再構築がどこまで進んでいるのか？」を確認したいということがあるのではないでしょうか。
クエリの実行中の進行状況については、sys.dm_exec_requests の percent_complete から一部の操作については確認することができます。
インデックスの操作については「REORGANIZE」であれば、作業状況を確認することはできるのですが、「REBUILD」については対象となっていません。
REBUILD の進行状況を取得したい場合は、
- ONLINE のインデックス操作を progress_report_online_index_operation のイベントから取得する
- 再開可能なオンラインインデックス再構築で実行して sys.index_resumable_operations から進行状況を取得
というような方法をとることができます。
それでは、次のようなクエリでインデックスの再構築を実行する場合、どのような方法をとることができるでしょうか？
ALTER INDEX NICX_LINEITEM_SHIPDATE ON LINEITEM REBUILD
このクエリはオフライン操作であり、再開可能なインデックス操作でもありません。
Standard Edition のようなエディションでは、REBUILD はオフライン操作のみが可能ですので、REBUILD を実行する場合はこの形式になることを避けることはできません。
通常の運用でも実行する可能性があるクエリですね。
このようなオプションで実行されたインデックスの再構築の進行状況を取得しようとした場合、冒頭で紹介したクエリプロファイリングの機能を活用すると情報の取得が行える可能性があります。
クエリプロファイリングを有効にする方法はいくつかありますが、今回は次のようなクエリでインデックスの再構築を実行してみます。
SET STATISTICS PROFILE ON; ALTER INDEX NICX_LINEITEM_SHIPDATE ON LINEITEM REBUILD
クエリプロファイリングに対応している SQL Server であれば、インデックスの再構築のクエリの情報を sys.dm_exec_query_profiles から取得できるようになります。
この情報を活用して、インデックスの再構築の進行状況を取得するというアプローチをすることができます。
オンラインでないインデックスの再構築の実行時には、取得されるロックが厳しいものとなりますので、再構築対象のデータの件数を事前に取得しておきます。
SELECT rows FROM sys.partitions WITH(NOLOCK) WHERE object_id = OBJECT_ID('LINEITEM') AND index_id = 21
今回は、29,999,795 件 (約 3,000 万件) のレコードが入っています。
それでは、先ほどのクエリプロファイリングの対象となる方法で実行した REBUILD を実行している最中に次のクエリを実行してみます。
(@rows には先ほど取得した件数を指定しています)
DECLARE @rows bigint = 29999795
SELECT
session_id,
SUM(row_count) AS row_count,
SUM(row_count) * 1.0 / @rows * 100 AS percent_complete
FROM
sys.dm_exec_query_profiles AS p
WHERE
EXISTS(
SELECT
session_id
FROM
sys.dm_exec_requests AS r
WHERE
command = 'ALTER INDEX'
AND r.session_id = p.session_id
)
AND p.physical_operator_name LIKE '%Scan%'
GROUP BY
p.session_id
今回のケースであれば、次のような情報が取得できます。
インデックスの REBUILD ですが、sys.dm_exec_requests のコマンドの情報としては「ALTER INDEX」となりますので、このコマンドが実行されているセッションの ID を取得します。
そして、そのセッションのクエリプロファイリングの情報を取得し、「Scan」が含まれる操作で処理された行数の取得を行うということを実施しています。
インデックスの再構築は、既存のデータを読み取って新しい領域にインデックスの作成を行うという動作ですので、再構築の実行プランから、何行のスキャンが行われたのかを取得することで、処理の完了具合を確認することができます。
このような進行状況の取得は、シンプルな処理であれば他のパターンでも活用することができます。
例えば、「SELECT INTO」でデータを移行する場合などは、コマンドとして「SELECT INTO」となりますので、これを利用して、次のようなクエリを実行することで、SELECT INTO によって INSERT されているデータの進捗状況を確認するということもできるようになります。
DECLARE @rows bigint = 29999795
SELECT
session_id,
SUM(row_count) AS row_count,
SUM(row_count) * 1.0 / @rows * 100 AS percent_complete
FROM
sys.dm_exec_query_profiles AS p
WHERE
EXISTS(
SELECT
session_id
FROM
sys.dm_exec_requests AS r
WHERE
command = 'SELECT INTO'
AND r.session_id = p.session_id
)
AND p.physical_operator_name LIKE '%Scan%'
GROUP BY
p.session_id
複雑なクエリですと、単純な最大行数と処理が実行された行数では、進行状況が取得できないことがありますが、シンプルな処理であれば、十分に処理の進行状況を把握するということが可能です。
処理対象の行数については、クエリプロファイルの「estimate_row_count」から推測するということが有効なケースもあると思いますので、「処理対象の件数の取得方法」については、いくつかの方法が考えられるかと。
まとめ
クエリプロファイリングは「実行中のクエリの処理状況」を取得するための強力な機能です。
この機能を使用することで、今までは、確認できなかったデータベースメンテナンス用のクエリの進行状況を取得することも可能となりますので「新しく取得できるようになった情報の活用」にも目を向けてみると、運用の利便性を向上させるためのデータ取得につなげることができるようになります。