こんにちは、NRIデジタルの湯川です。

SQL Server/SQL DatabaseにはADR（Accelerated database recovery、高速データベース復旧）という機能があります。この機能はSQL Server 2019から追加になったもので、同じタイミングでAzureからPaaSとして提供されているSQL Databaseでも提供されるようになりました。SQL Server 2022でも改善が行われています。

ADRはMSの論文に「it allowed us to improve the availability of Azure SQL Database by guaranteeing consistent recovery times of under 3 minutes for 99.999% of recovery cases in production.（これにより、本番環境において99.999%の復旧ケースで3分以内の安定した復旧時間を保証し、Azure SQL Databaseの可用性を向上させることができた）」と書かれている重要機能です。

ADR はAzureのSQLDatabase と SQL Managed Instance で既定で有効になっており、無効にすることはできませんが、AWSのRDS for SQL Server 2019や製品版のSQL Serverではデータベース作成時に自分で有効にしなければなりません。

今回はADRが有効な環境と無効な環境でどの程度の差があるのかを検証し、SQL Server 2022で行われた改善のポイントについても調査を行いました。

目次

　・ADRを有効にした場合の注意点

ADR（高速データベース復旧）とは

MSのドキュメントにはADRは以下を実現するための機能であると記載されています。

高速かつ一貫性のあるデータベース復旧
ADR を利用すると、実行時間の長いトランザクションが全体的な復旧時間に影響を与えることがありません。システム内でアクティブになっているトランザクションの数やそのサイズに関係なく、高速かつ一貫性のあるデータベース復旧が可能になります。

トランザクションの瞬間的ロールバック
ADR を利用すると、トランザクションがアクティブになっていた時間や実行された更新プログラムの数に関係なく、トランザクションのロールバックが一瞬で完了します。

ログの積極的切り捨て
ADR を利用すると、トランザクション ログが積極的に切り捨てられます。この積極的な切り捨ては、実行時間の長いアクティブなトランザクションが存在しても行われ、制御不能になることを防止します。

逆に言えばADRを有効にしない場合は以下の問題が存在すると読み替えることができます。

• 実行時間が長いトランザクションがあるとデータベース復旧時間は長時間化する
• トランザクションの実行時間が長い場合や更新プログラムの数が多いとロールバックに時間がかかる
• 実行時間が長いトランザクションがあるとトランザクションログが溢れる可能性がある

私はオンプレミスの環境でSQL Server 2008R2とSQL Server 2014を合わせて8年運用していましたが、これらはいずれも遭遇したことのある事象です。これらの状態が発生するとただ待つことしかできず、はやく復旧してくれることを祈っていたことをよく覚えています…

ADRはこうした問題を改善するために作られた夢の機能です。

ADRの机上調査

机上調査の参考文献

ADRの実機検証を行う前にどのような仕組みになっているのかの机上調査を行いました。机上調査の中でとても参考になったのが以下のドキュメントです。

MSのドキュメント

• 高速データベース復旧 – SQL Server | Microsoft Learn ★MSのドキュメントと記載
• Constant Time Recovery in Azure SQL Database – Microsoft Research ★MSの論文と記載
• ADR クリーナー再試行タイムアウト (分) 構成オプション – SQL Server | Microsoft Learn
• ADR 事前割り当て係数の構成オプション – SQL Server | Microsoft Learn

MS MVPの方々など有識者の記事

• 高速データベース復旧 (ADR) の動作を把握するために必要となる情報 at SE の雑記
• How Does Accelerated Database Recovery Work? – Simple Talk

ここでは★印のMSドキュメントを元に調査したADRの設計コンセプトと仕組みについてまとめておきたいと思います。

ADRの設計コンセプト

ADRの設計コンセプトについてはMSの論文のイントロダクションに記載されています。ポイントとなる部分を書き出すと以下のようになります。

• この仕組はARIES※１とMVCC※２（Multi Version Concurrency Control）を組み合わせた新しいデータベース復旧アルゴリズムである
• 挿入、更新、削除などのデータベース操作が行われた際に行バージョンを生成し、コミットされていないトランザクションのロールバック時に逆操作を行う必要をなくしている
• 行バージョンを活用することで、復旧を行う際にコミットされていないトランザクションのロックをすべて開放することができる

※１ARIESはWrite Ahead Log（ログ先行書き込み）を利用してトランザクションを保証する古典的なデータベース復旧アルゴリズム。
※２MVCCは更新を行う際に行バージョンを生成することで、他のユーザによる読み取りアクセスがあった場合に直前の状態（スナップショット）を返す仕組み。

ADRでは行バージョンを活用することで、ロールバックや復旧を行う際の書き込み操作やロックを排除することで高速化を実現しているということが分かりました。

ADRの主要コンポーネント

MSのドキュメントを元に次にADRの主要コンポーネントについて確認しておきます。以下の4つのコンポーネントがあります。

• 永続的なバージョン ストア (PVS)
  永続化されたバージョンストアです。「永続化された」という部分がポイントです。SQL Serverでは2005からMVCCが提供されていますが、従来のバージョンストアはtempdbに生成され、DBが再起動するとリセットされるものでした。ADRで利用されるバージョンストアは永続化されたもので、tempdbではなく通常のデータベース内に保存されます。ADRを有効にするとtempdbにバージョンストアは作られません。

• 論理的に元に戻すプロセス
  少し変な日本語ですが、論理的に元に戻すプロセスというものが追加になっています。これは、行レベルでバージョンに基づいて元に戻す操作を担う非同期プロセスです。以下の役割を担っています。

  • 中止となったトランザクションをすべて追跡記録する
  • すべてのユーザー トランザクションを対象に、PVS を使用してロールバックを実行する
  • トランザクション中止の直後、すべてのロックを解放する

• SLOG
  バージョン管理されない操作 (メタデータ キャッシュの無効化やロックの取得など) のログ です。次の特徴があります。

  • ボリュームが少なく、メモリ内に収められる
  • チェックポイント プロセス中にシリアル化され、ディスクに永続化される
  • トランザクションのコミットに合わせて定期的に切り捨てられる
  • バージョン管理されていない操作のみを処理することでやり直し操作と元に戻す操作を高速にする
  • 必須のログ レコードのみを保持することでトランザクション ログの積極的な切り捨てを可能にする

• PVSクリーナー
  クリーナーは、定期的に起動し、いずれのトランザクションからも参照されなくなり、不要になったページバージョンを消去する非同期プロセスです。

SQL Server 2019を利用したADRの実機検証

今回は前述したADRの効果を確認するため、ADRが有効な環境とADRが無効な環境で以下の３つの比較を行いました。

1. フェイルオーバー発生時のデータベース復旧時間
2. トランザクションロールバック時間
3. 実行時間が長いトランザクションが存在している状況下でのトランザクションログ量

このうち、１についてはAWSのRDS for SQL Server 2019 Enterprise Editionを利用して確認を行いました。前提となる冗長構成が構築済みの環境を手軽に利用することができるためです。

２と３についてはローカルのFAT端末にSQL Server 2019のDeveloper Editionを入れて確認を行いました。２はRDSでも実施可能なのですが、３の検証作業で必要なトランザクションログの任意のタイミングでのバックアップがRDSでは実施できないため、FAT端末で実施しています。

検証用データベースの準備

前述した通り、ADRはAzureのSQL Databaseではデフォルトで有効化されていて、無効にするこはできません。一方で、AWSのRDS for SQL Server 2019や製品版のSQL Serverではデータベース作成時にデータベースオプションの「ACCELERATED_DATABASE_RECOVERY」を「ON」に変更する必要があります。

今回はRDSの場合もFAT端末の場合も以下のクエリでADRが有効なデータベースと有効になっていないデータベースを作成しました。

ADRが有効なデータベース

CREATE DATABASE ADRON COLLATE Japanese_XJIS_140_CS_AS_KS_WS_UTF8;
ALTER  DATABASE ADRON SET RECOVERY FULL;
ALTER  DATABASE ADRON SET READ_COMMITTED_SNAPSHOT ON;
ALTER  DATABASE ADRON SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER  DATABASE ADRON SET ACCELERATED_DATABASE_RECOVERY = ON;

ADRが無効なデータベース

CREATE DATABASE ADROFF COLLATE Japanese_XJIS_140_CS_AS_KS_WS_UTF8;
ALTER  DATABASE ADROFF SET RECOVERY FULL;
ALTER  DATABASE ADROFF SET READ_COMMITTED_SNAPSHOT ON;
ALTER  DATABASE ADROFF SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER  DATABASE ADROFF SET ACCELERATED_DATABASE_RECOVERY = OFF;

シナリオ１：フェイルオーバー発生時のデータベース復旧時間

検証用のテーブルを２つのデータベースに作成します。

CREATE TABLE ADR_TEST (
col1 BIGINT,
col2 NVARCHAR(MAX)
);
 
INSERT INTO ADR_TEST (col2) SELECT TOP 100000 REPLICATE('A', 10000) FROM sys.objects o1, sys.objects o2, sys.objects o3;

　
作成したテーブルに以下のクエリを実行し、コミットもロールバックもせず完了を待ちます。

BEGIN TRANSACTION
UPDATE ADR_TEST SET col2 = REPLICATE('B', 10000)
UPDATE ADR_TEST SET col2 = REPLICATE('B', 10000)
UPDATE ADR_TEST SET col2 = REPLICATE('B', 10000)
UPDATE ADR_TEST SET col2 = REPLICATE('B', 10000)
UPDATE ADR_TEST SET col2 = REPLICATE('B', 10000)

この状態でフェイルオーバーを実行して、DBのログを確認します。
ADRONの場合のログは以下です。10分近い時間をかけてリカバリをしているのが分かります。

2023-03-28 02:43:05.30 spid9s      Skipping the default startup of database 'ADROFF' because the database belongs to an availability group (Group ID:  65536). The database will be started by the availability group. This is an informational message only. No user action is required.
2023-03-28 02:43:05.64 spid34s     Starting up database 'ADROFF'.
2023-03-28 02:43:06.00 spid34s     Parallel redo is started for database 'ADROFF' with worker pool size [2].
2023-03-28 02:43:06.00 spid34s     Recovery of database 'ADROFF' (7) is 0% complete (approximately 16893 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:43:26.01 spid34s     Recovery of database 'ADROFF' (7) is 5% complete (approximately 661 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:43:46.01 spid34s     Recovery of database 'ADROFF' (7) is 8% complete (approximately 644 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:44:06.01 spid34s     Recovery of database 'ADROFF' (7) is 11% complete (approximately 616 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:44:26.01 spid34s     Recovery of database 'ADROFF' (7) is 14% complete (approximately 593 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:44:46.02 spid34s     Recovery of database 'ADROFF' (7) is 17% complete (approximately 571 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:45:06.02 spid34s     Recovery of database 'ADROFF' (7) is 20% complete (approximately 549 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:45:26.02 spid34s     Recovery of database 'ADROFF' (7) is 23% complete (approximately 524 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:45:46.01 spid34s     Recovery of database 'ADROFF' (7) is 26% complete (approximately 501 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:45:46.02 spid34s     Recovery of database 'ADROFF' (7) is 26% complete (approximately 501 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:45:46.04 spid34s     Always On Availability Groups connection with primary database established for secondary database 'ADROFF' on the availability replica 'EC2AMAZ-TDJJSVS' with Replica ID: {21084677-f545-4291-a503-413f312288d8}. This is an informational message only. No user action is required.
2023-03-28 02:45:46.04 spid117s    Always On Availability Groups connection with secondary database established for primary database 'ADROFF' on the availability replica 'EC2AMAZ-D69KM0J' with Replica ID: {6cb40965-85a3-4708-9014-7e9448dfad57}. This is an informational message only. No user action is required.
2023-03-28 02:45:46.70 spid34s     Availability database 'ADROFF', which is in the secondary role, is being restarted to resynchronize with the current primary database. This is an informational message only. No user action is required.
2023-03-28 02:45:46.71 spid34s     Parallel redo is shutdown for database 'ADROFF' with worker pool size [2].
2023-03-28 02:45:46.71 spid34s     State information for database 'ADROFF' - Hardened Lsn: '(335:14387:1)'    Commit LSN: '(0:0:0)'    Commit Time: 'Jan  1 1900 12:00AM'
2023-03-28 02:45:46.95 spid34s     State information for database 'ADROFF' - Hardened Lsn: '(335:14387:1)'    Commit LSN: '(0:0:0)'    Commit Time: 'Jan  1 1900 12:00AM'
2023-03-28 02:45:46.95 spid34s     Starting up database 'ADROFF'.
2023-03-28 02:45:47.21 spid34s     Parallel redo is started for database 'ADROFF' with worker pool size [2].
2023-03-28 02:45:47.21 spid34s     Recovery of database 'ADROFF' (7) is 0% complete (approximately 16893 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:46:07.22 spid34s     Recovery of database 'ADROFF' (7) is 5% complete (approximately 670 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:46:27.22 spid34s     Recovery of database 'ADROFF' (7) is 8% complete (approximately 651 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:46:47.23 spid34s     Recovery of database 'ADROFF' (7) is 11% complete (approximately 608 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:47:07.23 spid34s     Recovery of database 'ADROFF' (7) is 14% complete (approximately 583 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:47:27.23 spid34s     Recovery of database 'ADROFF' (7) is 17% complete (approximately 560 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:47:47.23 spid34s     Recovery of database 'ADROFF' (7) is 20% complete (approximately 538 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:48:07.24 spid34s     Recovery of database 'ADROFF' (7) is 23% complete (approximately 513 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:48:17.29 spid34s     Recovery of database 'ADROFF' (7) is 25% complete (approximately 501 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2023-03-28 02:48:17.32 spid113s    Always On Availability Groups connection with secondary database established for primary database 'ADROFF' on the availability replica 'EC2AMAZ-D69KM0J' with Replica ID: {6cb40965-85a3-4708-9014-7e9448dfad57}. This is an informational message only. No user action is required.
2023-03-28 02:48:17.37 spid34s     Always On Availability Groups connection with primary database established for secondary database 'ADROFF' on the availability replica 'EC2AMAZ-TDJJSVS' with Replica ID: {21084677-f545-4291-a503-413f312288d8}. This is an informational message only. No user action is required.
2023-03-28 02:48:17.38 spid34s     Always On Availability Groups connection with secondary database established for primary database 'ADROFF' on the availability replica 'EC2AMAZ-D69KM0J' with Replica ID: {6cb40965-85a3-4708-9014-7e9448dfad57}. This is an informational message only. No user action is required.
2023-03-28 02:48:17.38 spid34s     Recovery completed for database ADROFF (database ID 7) in 151 second(s) (analysis 0 ms, redo 150083 ms, undo 0 ms [system undo 7494224614048 ms, regular undo 7494224614048 ms].) This is an informational message only. No user action is required.
2023-03-28 02:48:17.39 spid52s     Always On Availability Groups connection with primary database established for secondary database 'ADROFF' on the availability replica 'EC2AMAZ-TDJJSVS' with Replica ID: {21084677-f545-4291-a503-413f312288d8}. This is an informational message only. No user action is required.
2023-03-28 02:52:50.29 spid106s    1 transactions rolled back in database 'ADROFF' (7:0). This is an informational message only. No user action is required.
2023-03-28 02:52:50.29 spid106s    Recovery completed for database ADROFF (database ID 7) in 592 second(s) (analysis 0 ms, redo 0 ms, undo 591402 ms [system undo 0 ms, regular undo 591402 ms].) This is an informational message only. No user action is required.
2023-03-28 02:52:50.30 spid106s    Parallel redo is shutdown for database 'ADROFF' with worker pool size [2].

ADRONの場合のログは以下です。ほとんど何も出ず数秒でオンライン状態になっています。

2023-03-28 02:43:05.30 spid9s      Skipping the default startup of database 'ADRON' because the database belongs to an availability group (Group ID:  65536). The database will be started by the availability group. This is an informational message only. No user action is required.
2023-03-28 02:43:05.64 spid32s     Starting up database 'ADRON'.
2023-03-28 02:43:05.96 spid32s     Parallel redo is started for database 'ADRON' with worker pool size [2].
2023-03-28 02:43:15.76 spid35s     Always On Availability Groups connection with secondary database established for primary database 'ADRON' on the availability replica 'EC2AMAZ-D69KM0J' with Replica ID: {6cb40965-85a3-4708-9014-7e9448dfad57}. This is an informational message only. No user action is required.
2023-03-28 02:43:15.76 spid32s     Always On Availability Groups connection with primary database established for secondary database 'ADRON' on the availability replica 'EC2AMAZ-TDJJSVS' with Replica ID: {21084677-f545-4291-a503-413f312288d8}. This is an informational message only. No user action is required.
2023-03-28 02:43:16.08 spid32s     Availability database 'ADRON', which is in the secondary role, is being restarted to resynchronize with the current primary database. This is an informational message only. No user action is required.
2023-03-28 02:43:16.08 spid32s     Parallel redo is shutdown for database 'ADRON' with worker pool size [2].
2023-03-28 02:43:16.08 spid32s     State information for database 'ADRON' - Hardened Lsn: '(525:107713:1)'    Commit LSN: '(0:0:0)'    Commit Time: 'Jan  1 1900 12:00AM'
2023-03-28 02:43:16.10 spid32s     State information for database 'ADRON' - Hardened Lsn: '(525:107713:1)'    Commit LSN: '(0:0:0)'    Commit Time: 'Jan  1 1900 12:00AM'
2023-03-28 02:43:16.10 spid32s     Starting up database 'ADRON'.
2023-03-28 02:43:17.00 spid32s     Parallel redo is started for database 'ADRON' with worker pool size [2].
2023-03-28 02:43:17.02 spid45s     Always On Availability Groups connection with secondary database established for primary database 'ADRON' on the availability replica 'EC2AMAZ-D69KM0J' with Replica ID: {6cb40965-85a3-4708-9014-7e9448dfad57}. This is an informational message only. No user action is required.
2023-03-28 02:43:17.02 spid32s     Always On Availability Groups connection with primary database established for secondary database 'ADRON' on the availability replica 'EC2AMAZ-TDJJSVS' with Replica ID: {21084677-f545-4291-a503-413f312288d8}. This is an informational message only. No user action is required.
2023-03-28 02:43:17.05 spid45s     Always On Availability Groups connection with secondary database established for primary database 'ADRON' on the availability replica 'EC2AMAZ-D69KM0J' with Replica ID: {6cb40965-85a3-4708-9014-7e9448dfad57}. This is an informational message only. No user action is required.
2023-03-28 02:43:17.05 spid28s     Always On Availability Groups connection with primary database established for secondary database 'ADRON' on the availability replica 'EC2AMAZ-TDJJSVS' with Replica ID: {21084677-f545-4291-a503-413f312288d8}. This is an informational message only. No user action is required.

MSの論文やドキュメントの記載通り、ADRを有効にすることで大幅に復旧時間が短縮されることが確認できました。

シナリオ２：トランザクションロールバック時間

トランザクションのロールバックにかかる時間を比較するため、以下のクエリを実行してみます。

DECLARE @start DATETIME2
SET @start = SYSDATETIME()

BEGIN TRANSACTION

CREATE TABLE ADR_TEST (
col1 BIGINT IDENTITY PRIMARY KEY,
col2 NVARCHAR(MAX)
);

DECLARE @cnt BIGINT
SET @cnt = 0

WHILE @cnt <100000
BEGIN
INSERT INTO ADR_TEST (col2) VALUES (REPLICATE('A', 10000))
SET @cnt = @cnt + 1
END

ROLLBACK TRANSACTION

SELECT DATEDIFF(SECOND, @start, SYSDATETIME());

結果は以下です。ADR無効の場合のロールバックは非常に時間がかかることが分かります。

シナリオ３：実行時間が長いトランザクションが存在している状況下でのトランザクションログ量

シナリオ１と同じ検証用のテーブルを２つのデータベースに作成します。

CREATE TABLE ADR_TEST (
col1 BIGINT,
col2 NVARCHAR(MAX)
);
<br>
<br>
INSERT INTO ADR_TEST (col2) SELECT TOP 100000 REPLICATE('A', 10000) FROM sys.objects o1, sys.objects o2, sys.objects o3;

チェックポイントを作成し、フルバックアップを取得します。ここではバックアップは使わないので捨ててしまいます。

CHECKPOINT;
GO
BACKUP DATABASE ADRON TO DISK = N'NUL';
GO
BACKUP LOG ADRON TO DISK = N'NUL';
GO
 
CHECKPOINT;
GO
BACKUP DATABASE ADROFF TO DISK = N'NUL';
GO
BACKUP LOG ADROFF TO DISK = N'NUL';
GO

この状態のトランザクションログサイズを確認します。

SELECT db_name(database_id) AS database_name,
used_log_space_in_bytes, used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

ADRONについては追加でPVSのサイズを確認します。

SELECT
 db_name(pvss.database_id) AS DBName,
 pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
 100 * pvss.persistent_version_store_size_kb / df.total_db_size_kb AS pvs_pct_of_database_size,
 df.total_db_size_kb/1024./1024 AS total_db_size_gb,
 pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
 pvss.current_aborted_transaction_count,
 pvss.aborted_version_cleaner_start_time,
 pvss.aborted_version_cleaner_end_time,
 dt.database_transaction_begin_time AS oldest_transaction_begin_time,
 asdt.session_id AS active_transaction_session_id,
 asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds,
 pvss.pvs_off_row_page_skipped_low_water_mark,
 pvss.pvs_off_row_page_skipped_min_useful_xts
FROM sys.dm_tran_persistent_version_store_stats AS pvss
CROSS APPLY (SELECT SUM(size*8.) AS total_db_size_kb FROM sys.database_files WHERE [state] = 0 and [type] = 0 ) AS df
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id   AND   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num   OR   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

この状態で２つのデータベースに以下のクエリを実行します

BEGIN TRANSACTION
UPDATE ADR_TEST SET col2 = REPLICATE('B', 10000)

トランザクションログサイズとPVSのサイズを確認します。
まずはトランザクションログです。この時点ではあまり差がありません。

PVSは以下のようになっていました。

さきほどのUPDATE文をコミットもロールバックもしないまま、トランザクションログのバックアップをしてみます。

BACKUP LOG ADRON TO DISK = N'NUL';
GO
BACKUP LOG ADROFF TO DISK = N'NUL';
GO

もう一度、トランザクションログサイズとPVSのサイズを確認します。

ADRが有効なデータベースではログが切り捨てられたことが分かります。

PVSの方は何も変化していません。

手動でPVSのクリーンアップを実行してみます。

EXEC sys.sp_persistent_version_cleanup ADRON;

実行中のトランザクションが残っているので変わりません。

さきほどのUPDATE文をコミットし、クリーナを実行してからもう一度PVSを確認すると、クリーナが参照しているトランザクションがいなくなったバージョンストアを回収してくれていました。

今回の検証結果から、ADRを有効にした場合、トランザクションログが期待どおり積極的に切り詰められることが分かりました。

また、ADRを有効にするとPVSが作られ、バージョンストアを参照しているトランザクションが終了するまで、クリーナはバージョンストアを回収することができないということも確認することができました。

SQL Server 2022を利用したADRの実機検証

SQL Server 2022の変更点

SQL Server 2022で行われたADR機能改善について、MSのドキュメントを元に確認します。

1. ユーザー トランザクションのクリーンアップ
   テーブル レベルのロックの競合が原因で通常のクリーンアップ プロセスでは対処できなかったページに対して、ユーザー トランザクションでクリーンアップを実行できます。
2. PVS ページ トラッカーのメモリ占有領域の削減
   バージョン管理されたページを維持するために必要なメモリ占有領域を減らすために、永続的なバージョン ストア (PVS) ページがエクステント レベルで追跡されます。
3. 高速データ復旧 (ADR) クリーナーの機能強化
   中止されたバージョンのページを追跡して記録する方法が改善されメモリと容量が向上しました。
4. トランザクションレベルの永続的なバージョン ストア (PVS)
   システムに中止されたトランザクションがあるかどうかに関係なく、コミットされたトランザクションに属するバージョンをクリーンアップできます。クリーンアップが遅い場合や失敗する場合でも、PVS の増加が減少します。
5. マルチスレッド バージョンのクリーンアップ
   SQL Server 2019では、クリーンアップ プロセスは SQL Server インスタンス内のシングル スレッドです。SQL Server 2022 (16.x) 以降、このプロセスではマルチスレッド バージョンのクリーンアップが使用されます。 これにより、同じSQL Server インスタンス内の複数のデータベースを並列にクリーンアップできます。
6. 新しいPVSクリーナーの用の拡張イベント「tx_mtvc2_sweep_stats」の追加
   いずれもPVSのクリーナーに関する改善ですね。

2, 3, 5, 6はクリーナーのリソース使用量や処理性能、監視に関するものですが、1と4はクリーナーがクリーンアップを実行できなかったケースについての改善になっています。

この1と4について、2019までの挙動と2022で行われた改善について、MSに問い合わせて確認をしてみました。

ユーザー トランザクションのクリーンアップ

SQLServer 2019までの動作

クリーンアッププロセスが不要になったページを解放するためにはIXロックを取得する必要あるそうなのですが、この時にユーザトランザクションとの間でロック競合が発生することがあります。
ロック競合発生した場合、ユーザトランザクションの方が優先されるので、クリーンアッププロセスは不要になったバージョンを解放できず、次の実行まで残されてしまいます。ユーザトランザクションとのロック競合が次の実行でも発生すると不要バージョンの解放はまた後回しにされてしまうので、PVSの肥大化が発生します。

SQLServer 2022からの動作

ロック競合が発生し、クリーンアップに失敗した場合、該当のオブジェクトに対してロックを取得しているユーザー トランザクションの中で不要になったバージョンの削除が行われます。

トランザクションレベルの永続的なバージョン ストア (PVS)

SQLServer 2019までの動作

明示的にロールバックされた場合やセッションが切断されてしまった場合のように、トランザクションが中断された場合、同じタイミングで実行されているトランザクションが存在していると、クリーンアッププロセスは中断されたトランザクションの生成したPVSを解放することができませんでした。

SQLServer 2022からの動作

SQL Server 2022 では、中断されたトランザクションと、コミットされたトランザクションのバージョンが、内部的に別で管理されるようになりました。これによって、同じタイミングで実行されているトランザクションが存在していても、中断されたトランザクションの生成したバージョンを解放することができるようになりました。

以上がSQL Server 2022で行われたはADRについての機能改善です。

まとめ

ADRは有効にするべき

今回の検証結果から、製品版のSQL Serverを利用する場合や、AWSのRDS for SQL Server 2019などを利用する場合もADRは有効化する必要があると考えるべきだと思います。

MSの論文にも実行時間が長いトランザクションが存在する場合の復旧時間について以下のような非常に恐ろしい記載があります。

The database required 12 hours to recover while the corresponding tables were completely inaccessible due to the exclusive locks held by the transaction performing the data load.

データベースの復旧には12時間を要し、データロードを実行したトランザクションが持つ排他的ロックのため、対応するテーブルには完全にアクセスできない状態でした。

今回の検証ではさすがに12時間もかかりませんでしたが、実際にこのくらいの時間かかる事例が存在しているということなのでしょう。

ADRを有効にした場合の注意点

シナリオ３：「実行時間が長いトランザクションが存在している状況下でのトランザクションログ量」でも確認したように、ADRを有効にすると、トランザクションログは積極的に切り捨てられますが、PVSはバージョンストアを参照しているトランザクションが終了するまで回収されません。

MSのドキュメントでも注意が必要とされているのがPVS のクリーンアップです。

ADR は、大量の OLTP など、更新や削除の回数が多く、PVS クリーンアップ プロセスで領域を再利用するための保存/復旧期間がないデータベース環境の場合は推奨されません。 通常、ビジネス運用サイクルではこの時間を確保できますが、シナリオによっては、アプリケーションのアクティビティ状況を考慮して、PVS クリーンアップ プロセスを手動で開始することが必要になる場合もあります。

公式の「ADR は、大量の OLTP など、更新や削除の回数が多く、PVS クリーンアップ プロセスで領域を再利用するための保存/復旧期間がないデータベース環境の場合は推奨されません」という気になる記載があります。

私も過去に「ロングトランザクションによってPVSが肥大化し、CPU使用率の高騰しクエリの論理読取も増加する」という問題に遭遇したことがあります。
この時のロングトランザクションはリードレプリカで実行されているものだったため、気づくのに時間がかかってしまいました。

ADRを有効にすることでロングトランザクションが存在する場合でも、これまでのようなトランザクションログ領域が溢れてしまうという問題は起こりにくくなりましたが、代わりにPVSが肥大化してしまうという問題が発生することがある、ということは知っておいた方が良いと思います。