SQL Server の復旧モデルを完全で使わないといけないシチュエーションというものがいくつかあるかと思います。
その中でも「構成としての制約」から、完全復旧モデルが必要となる場合があります。
- AlwaysOn 可用性グループを使用している
- PaaS (SQL Database / Managed Instance / RDS for SQL Server 等) を使用している
というようなケースが考えられますが、これらの環境を使用している場合にトランザクションログの領域が不足しているが、インデックスの再構築を実行したい場合の対策の一案を。
「再開可能なオンラインのインデックス再構築」(ROIR) を使用するため、SQL Server 2017 Enterprise Edition 以上がベースとなっている必要があります。
ちなみに SQL Database と Managed Instance は SQL Server 2017 の EE 相当の機能を使うことができるようになっていますので、どのサービスレベルでも使用できます。
SQL DB では、この機能は、まだ Preview 中となっていますが。
Resumable Online Index Rebuild is in public preview for Azure SQL DB
再開可能なオンラインのインデックス再構築の特徴については、再開可能なインデックスの再構築に関する考慮事項 に記載されていますが、その中に次の記載があります。
インデックス再構築操作の間はトランザクション ログの切り捨てを有効にします (通常のオンライン インデックス操作に対してこの操作を実行することはできません)。
通常のインデックスの再構築や、オンラインのインデックス再構築については、すべてが完了したタイミングでトランザクションとして完了させる関係上、インデックスの再構築中に、トランザクションログのバックパップが取得されたとしても切り捨ては行われません。
そのため、大量データが格納されているインデックスの再構築を実施しようとした場合、トランザクションログとして使用できる領域が不足した場合は、該当のインデックスを再構築することができない可能性があります。
(パーティショニングを使用して、再構築の範囲を制御することで対応できる可能性はあります)
IaaS タイプの SQL Server であれば、トランザクションログのファイルを追加することで対応ができる可能性がありますが、PaaS タイプの SQL Server だと、利用可能なログの上限は決まっているため、これを緩和させることは難しいかと。
そのような場合の一つの解決案として「再開可能なオンラインのインデックスの再構築」を使用することができます。
単純なオンラインのインデックス再構築では、その最中にトランザクションログバックアップが取得されても、ログの切り捨ては行われないのですが、再開可能なオンラインインデックスの再構築の場合は、ログのバックアップによりログの切り捨てが行われますので、再構築中にログのバックアップが行われれば、ログ領域の不足を解消できる可能性があります。
例えば、次のようなクエリで再開可能なオンラインインデックス再構築を実行します。
ALTER INDEX CIX_LINEITEM ON LINEITEM REBUILD WITH(ONLINE=ON, RESUMABLE = ON, DATA_COMPRESSION = PAGE)
この状態で、他のセッションから次のクエリの実行します。
DECLARE @complete varchar(10)
DECLARE @log_used varchar(10)
DECLARE @date varchar(30)
WHILE(0=0)
BEGIN
SET @complete = (SELECT percent_complete FROM sys.index_resumable_operations )
SELECT @log_used = used_log_space_in_percent FROM sys.dm_db_log_space_usage
SELECT @date = GETDATE()
IF @complete IS NOT NULL
BEGIN
RAISERROR ('[%s] Complete (%%) : %s / Log Used (%%) : %s', 10, 1 , @date, @complete, @log_used) WITH NOWAIT
END
ELSE
BEGIN
RAISERROR('[%s] Index Operation Waiting... / Log Used (%%) : %s', 10, 1, @date, @log_used) WITH NOWAIT
END
WAITFOR DELAY '00:00:10'
END
このクエリは再開可能なインデックスの再構築の進捗状況と、ログの使用状況をメッセージとして出力するものになります。
今回は、SQL Database を使用しているのですが、再構築を実行してから 10 分ぐらい経過したタイミングで、次のような情報が取得できます。
途中で 66% だったログの使用状況が 10% まで下がったことが確認できますね。
インデックスの再構築の実行状況については継続して行われているため、
- インデックスの再構築を実行しながらログの切り捨てが行われた
ことが確認できます。
バックアップはどのくらいの頻度で行われますか。 に、SQL Database ログのバックアップ間隔が記載されているのですが、5-10 分間隔で実行されているという記載があります。
今回のクエリは 2:27 に実行を開始したものになりますので、10 分後にトランザクションログのバックアップが動作して、切り捨てが行われ、使用可能領域が増えたことが確認できますね。
再開可能なオンラインのインデックスの再構築は利用シナリオが多岐にわたるので、機能の使いこなしを考えてみると、今まで難しかった運用の解決策となることがあるのではないでしょうか。