SQL Server の Enterprise Edition を使用している場合は、「オンラインでのインデックスメンテナンス」を実行することができます。
オンラインインデックスのメンテナンスの動作については オンライン インデックス操作の動作原理 で解説が行われていますが、オンラインのインデックスの最後のフェーズでは、瞬間的ではありますが、「SCH-M」のロックが取得されます。
Contents
オンラインインデックス再構築によるロック競合の発生
SQL Server のロックを確認する際の定番の情報となる トランザクションのロックおよび行のバージョン管理ガイド にロックの競合についてのマトリックスが公開されていますが、「SCH-M」のロックは、様々なロックと競合します。
「SCH-M」のロックは検索で取得が行われる「IS」(インテント共有) のロックとも競合しますので、「SCH-M」のロックが取得されるタイミングで、該当のテーブルに対して、時間のかかる検索が行われている場合などは、オンラインインデックスのメンテナンスの最終フェーズで取得する「SCH-M」のロック取得で、ロック競合が発生する可能性があります。
実際に状況を再現させると次のようなロック競合が発生することになります。
このインデックスのメンテナンスについては、次のクエリで実行しています。
ALTER INDEX PK__T1__32149A13993094BE ON T1 REBUILD WITH(ONLINE=ON)
オンラインでのインデックス再構築を実行しているのですが、SELECT で取得されている IS のロックにより、オンラインのインデックス再構築の最終フェーズで取得する「SCH-M」のロック取得がブロッキングされた状態となります。
この状態になると該当のテーブルに「SCH-M」のロック取得要求が残った状態となりますので、以降に実行した SELECT 当の操作は NOLOCK ヒントを指定していても、ロック競合が発生することになります。
(16 コア以上の環境では、ロックのパーティション分割の概念が入るため、すべての SELECT がブロッキング対象とならない可能性があります)
オンラインのインデックスの最終フェーズで取得される SCH-M のロックでブロッキングが発生した状態で、NOLOCK の検索を複数セッションで実行した結果がこちらになるのですが、SCH-M に起因して、複数のセッションでブロッキングが発生していることが確認できますね。
SQL Server 2012 までのオンラインのインデックス再構築では、再構築中にデータアクセスが可能な状態にすることはできるのですが、最後に取得される SCH-M のロックが取得できないとこのような状態となる可能性がありました。
「WAIT_AT_LOW_PRIORITY」 による低優先度のロックの取得
SQL Server 2014 以降、ならびに、SQL Database では、この動作の改善が行われ「WAIT_AT_LOW_PRIORITY」という、低優先度のロックでインデックスのメンテナンスが可能となりました。
(SQL Database では、この低優先度のロックを統計情報の非同期更新でも使用できるようになっています)
WAIT_AT_LOW_PRIORITY
適用対象: SQL Server (SQL Server 2014 (12.x) 以降) と Azure SQL データベース。オンライン インデックス再構築では、このテーブルに対する操作がブロックされるまで待機する必要があります。 WAIT_AT_LOW_PRIORITY は、オンライン インデックス再構築操作が低優先度のロックを待機して、オンライン インデックス構築操作が待機している間、他の操作を実行可能にすることを示します。 WAIT AT LOW PRIORITY オプションを省略することは、
WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)と同じです。
この機能を使用すると、先ほどのオンラインのインデックスメンテナンスを次のように書き換えることができます。
ALTER INDEX PK__T1__32149A13993094BE ON T1 REBUILD WITH(ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)))
この実行方法でも、最終フェーズでは「SCH-M」のロックが取得されることには変わりはありません。
ただし、先ほどとは ALTER INDEX の wait_type が異なっています。
「WAIT_AT_LOW_PRIORITY」オプションを使用した場合、ALTER INDEX で取得される SCH-M は「低優先度のロック」となります。
通常のロックは、「通常優先度のキュー」で管理されていますが、「WAIT_AT_LOW_PRIORITY」を指定した場合、そのロックについては「低優先度のキュー」で管理が行われることになります。
低優先度のキューについては、通常の優先度のキューより、低優先となります。
「WAIT_AT_LOW_PRIORITY」を指定しなかった場合は、ALTER INDEX で取得しようとした SCH-M により、SELECT もブロックされていましたが、オプションを指定した場合は、低優先キューの扱いとなるため、このキューより、SELECT で取得される IS のロックのほうが優先度が高くなり、検索 (または他の更新) のブロックが発生しなくなります。
「WAIT_AT_LOW_PRIORITY」を指定した場合、「MAX_DURATION」の設定が必要となるようで、何分ロック競合が発生したら、ステートメントをキャンセルするかを指定することになります。
先ほどのクエリであれば、1 分間ロック競合が発生した場合は、ALTER INDEX が ABORT することになります。
ABORT したインデックスの再構築については、処理結果が破棄されるため、再度インデックスのメンテナンスを実行した場合には、最初からやり直すことになります。
ここまでの動作が、SQL Server 2014 で可能な内容となっていました。
再開可能なオンラインインデックス再構築と組み合わせて利用
SQL Server 2017 以降の Enterprise Edition では、「再開可能なオンラインインデックス再構築」(Resumable Online Index Rebuild) という機能が追加されました。
この機能は、インデックスのオンライン再構築中に何らかの障害や明示的なキャンセルにより、インデックス再構築を中止した場合、それまでの処理結果は残した状態で、別のタイミングでインデックスの再構築を途中から再開できるという機能となります。
この機能は「WAIT_AT_LOW_PRIORITY」と組み合わせることが可能です。
先ほどのクエリをさらに次のように変更します。
ALTER INDEX PK__T1__32149A13993094BE ON T1 REBUILD WITH(RESUMABLE = ON, ONLINE=ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)))
ロックの取得状況や、上記の設定では 1 分間のロック競合が発生した場合の動作については、先ほどと変わりません。
ただし、今回は「再開可能」な形でインデックスのメンテナンスを実行しています。
この場合、1 分ロック競合が発生したインデックスの再構築については「停止状態」で、処理結果が保たれた状態となります。
SELECT * FROM sys.index_resumable_operations
今回の検証ケースであれば、最終フェーズの処理のみが残された状態となります。
この状態であれば、ロック競合の原因が解消すれば、次のようなクエリでインデックスの再構築を再開し、それまでの再構築の処理結果を無駄にすることなく低優先度のロックにより処理を行うことができるようになります。
ALTER INDEX PK__T1__32149A13993094BE ON T1 RESUME WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF))
この方法を使用すると低優先度のロックを使用しながら、一定時間、ロック競合が発生した場合にも、それまでの処理家結果を有効に活用してインデックスのメンテナンスが実行できるのではないでしょうか。
現状、低優先度のロックについて「インデックスのオンライン再構築」「統計情報の非同期更新」の操作に限定されており、ALTER TABLE については、インデックスの再構築の処理でしか利用することはできません。
ALTER TABLE の ADD COLUMN でも WAIT_AT_LOW_PRIORITY が指定できるようにできないかという フィードバック は出してみたのですが (既に他にも出ていそうですが) 、このような方法が ALTER TABLE による列の追加 / 変更にも使用できるようになると、MySQL の「LOCK = SHARED」のような柔軟性が出てうれしいですね。