SQL Server 2008 の Change Data Capture のパフォーマンス チューニング
SQL Server のベストプラクティスに関する資料
**著者:**Steffen Krause
**寄稿者:**Sanjay Mishra 、 Gopal Ashok 、 Greg Yvkoff 、 Rui Wang
**テクニカルレビューアー:**Burzin Patel 、 Denny Lee 、 Glenn Berry (SQL Server MVP) 、 Joseph Sack 、 Lindsey Allen 、 Michael Redman 、 Mike Ruthruff 、 Paul S. Randal (SQLskills.com)
発行: 2008 年 11 月
**対象:**SQL Server 2008
要約 : 変更データ キャプチャ (Change Data Capture) は SQL Server 2008 で導入された新機能です。この機能は、一連のデータベース テーブルに含まれるデータに対して行われた変更を捕捉 ( キャプチャ ) し、データ ウェアハウスなどの別のシステムに簡単に転送できるようにします。このドキュメントでは、データ キャプチャのパフォーマンスを最大限に高めながら、変更データ キャプチャの運用ワークロードがパフォーマンスに与える影響を最小限に抑えるようにパラメーターを構成する方法について説明します。ここでは、説明を変更データのキャプチャとクリーンアップ プロセスに限定し、変更後のデータを照会することには触れません。
はじめに
変更データ キャプチャは、 Microsoft® SQL Server® 2008 で導入された新機能です。この機能は、一連のデータベース テーブルに含まれるデータに対して行われた変更を捕捉 ( キャプチャ ) し、データ ウェアハウスなどの別のシステムに簡単に転送できるようにします。このドキュメントでは、データ キャプチャのパフォーマンスを最大限に高めながら、変更データ キャプチャの運用ワークロードがパフォーマンスに与える影響を最小限に抑えるようにパラメーターを構成する方法について説明します。ここでは、説明を変更データのキャプチャとクリーンアップ プロセスに限定し、変更後のデータを照会することには触れません。変更データ キャプチャ機能の概要については、 SQL Server 2008 オンライン ブックの「 変更データ キャプチャ 」を参照してください。
SQL Server 2008 ではもう 1 つ " 変更の追跡 " という新機能が導入されていて、 DML コマンド ( 挿入、更新、削除、マージ ) によって変更されたテーブル行の追跡も可能になります。変更の追跡は、複数のデータベース アプリケーションの結び付きが緊密でない場合にこうしたアプリケーション間で同期を取ることを目的としているため、変更された行の主キーとどの列が変更されたか ( オプション ) のみが追跡され、データの変更自体は追跡されません。この変更の追跡についても、ここでは説明しません。 この 2 つの機能の詳細については、 SQL Server 2008 オンライン ブックの「 変更データ キャプチャと変更の追跡の比較 」を参照してください。
変更データ キャプチャのメリット
これまで、ソース データベースに加えられた変更を検出してデータ ウェアハウスに転送する場合、ソース テーブルに特殊な列 ( タイム スタンプ列や行バージョン列 ) を用意したり、変更を捕捉するトリガーを設定したり、転送元のシステムと転送先のシステムを比較したりしていました。これらの方法にはそれぞれ大きなデメリットがあります。特殊な列を用意すると、ソース データベース スキーマへの変更が必要になり、多くの場合、アプリケーション ロジックの変更も必要になります。トリガーは手動での実装が必要で、転送元システムの DML コマンドに大きなオーバーヘッドが生じる可能性があります。ソース データベースとターゲット データベースを比較すると、おそらく、両システムに高い負荷がかかります。
変更データ キャプチャでは、ソース データベースのトランザクション ログを非同期に読み取ることで、転送元システムでの変更のキャプチャを可能にします。このため、トランザクション レプリケーションと同じログ リーダーが使用されます。変更データ キャプチャは既存のテーブル スキーマのままで機能するため、ソース データベースやアプリケーションを変更する必要がありません。また、ログ リーダー ジョブが非同期に機能することから、トリガーのような同期ソリューションに比べて、 DML トランザクションへの影響がはるかに少なくなります。ソース テーブルへの変更はすべて特別な変更テーブルに記録されるため、転送元システムと転送先システムとの間で変更を比較する必要がありません。
変更データ キャプチャの基本的な運用方法
データベースで変更データ キャプチャを有効にすると、スキーマ "cdc" とこのスキーマの一連のメタデータ テーブルが作成されます。作成される変更データ キャプチャ テーブルのほとんどにメタデータが含まれているため、データ量やトランザクション処理の負荷はごくわずかです。変更データ キャプチャの運用中に頻繁に書き込みが行われ、サイズが大きくなる可能性のあるメタデータ テーブルは、 cdc.lsn_time_mapping テーブルだけです。このテーブルには、各ログ シーケンス番号 (LSN) どうしのマッピングと、トランザクション処理が行われた日時が記録されます。
次に、テーブルで変更データ キャプチャを有効にすると、キャプチャ インスタンスが作成されます。キャプチャ インスタンスは、 1 つの変更テーブル (cdc.<capture_instance_name>_CT) とこの変更テーブルに照会するための最大 2 つのテーブル値関数から構成されます。各テーブルにはキャプチャ インスタンスを 2 つまで作成できます。トランザクション レプリケーションまたは別のキャプチャ インスタンスのログ スキャン ジョブがデータベース上になければ、 SQL Server エージェントで、既定のパラメーターを使用してキャプチャ ジョブおよびクリーンアップ ジョブが作成されます ( これらのパラメーターの変更の詳細については、「キャプチャ ジョブの構成」を参照してください ) 。このキャプチャ ジョブは自動的に開始されます。
キャプチャ ジョブが有効になると、ソース テーブルで行が挿入または削除されるたびに 1 行、ソース テーブルで行が更新されるたびに 2 行 ( 更新前の値を含む行と更新後の値を含む行 ) が変更テーブルに書き込まれます。変更テーブルには、更新時に値を変更した列だけでなく、ソース テーブルでキャプチャされたすべての列の値が常に書き込まれます。したがって、データ ウェアハウスに変更を反映するために必要なのは、変更テーブルのデータのみです。変更テーブルには、ソース テーブルのデータに加え、変更データの行ごとに少なくとも 37 バイトのデータが付加されます ( 追加されるデータ サイズは、キャプチャされた列の数によっては 37 バイト以上になります。詳細については、 SQL Server 2008 オンライン ブックの「 cdc.<capture_instance>_CT (Transact-SQL) 」を参照してください ) 。
sys.sp_cdc_enable_table のパラメーターの構成
sys.sp_cdc_enable_table ストアド プロシージャには複数のパラメーターがあり、一部のパラメーターは変更データ キャプチャのパフォーマンスにとって重要であることがわかっています。ここでは、パフォーマンスに影響するパラメーターのみについて説明します。 sys.sp_cdc_enable_table によるワークロードへの影響をテストした結果の詳細については、「 sys.sp_cdc_enable_table のパラメーターによる変更データ キャプチャのパフォーマンスへの影響 」を参照してください。
@capture_instance パラメーターは、該当テーブルのキャプチャ インスタンスの名前を指定します。名前自体はパフォーマンスには影響しません。ただし、このパラメーターを使用すると、同じテーブルにもう 1 つキャプチャ インスタンスを作成できます。この機能は、ソース テーブルのスキーマに変更が加えられるため、スキーマをアップグレードするシナリオのみが対象となります。元のキャプチャ インスタンスは、不要になったらすぐに無効にしてください。 1 つのテーブルで 2 つのキャプチャ インスタンスがアクティブな状態になっていると、 2 倍の変更データが書き込まれることになり、パフォーマンスに大きな影響を及ぼす可能性があります。
1. ソース テーブルのスキーマ変更は、次の手順で行います。
2. ソース テーブルのスキーマを変更します。
3. 新しいキャプチャ インスタンスを作成します。この新しいキャプチャ インスタンスは、ソース テーブルの新しい (変更された) スキーマで作成されます。
4. 変更が行われるまで待機してから、sys.fn_cdc_get_min_lsn (新しいキャプチャ インスタンス名) を使用して、新しいキャプチャ インスタンスから最小の LSN を読み取ります。
5. 元のキャプチャ インスタンスから順番にすべての変更を読み取って処理します。ただし、新しいキャプチャ インスタンスの最初の LSN は除きます。
6. 元のキャプチャ インスタンスを無効にします。
7. 元のキャプチャ インスタンス名への参照をすべてを新しい名前に更新します。
8. 新しいキャプチャ インスタンスから読み取りを続けます。
@captured_column_list パラメーターは、変更データ キャプチャがキャプチャの対象とし、変更テーブルに含めるソース テーブルの列を指定します。このパラメーターを指定しないか NULL を指定した場合、ソース テーブルのすべての列が変更テーブルに含まれます。キャプチャ対象の列の数とサイズは、変更データ キャプチャのパフォーマンスと必要なディスク容量に大きな影響を及ぼします。通常、変更データ キャプチャのパフォーマンスは、キャプチャ対象の列数が少ないほど向上します。これは、変更テーブルに書き込む必要のあるデータ量が少なくなるためです。
@supports_net_changesパラメーターは、(cdc.fn_cdc_get_net_changes_<capture_instance> を使用した) 変更テーブルへの差分変更の照会が可能かどうかを指定します。差分変更の照会では、変更回数に関係なく、変更が行われたソース行ごとに 1 行だけ書き込まれます。たとえば、株式銘柄単位に株価が追跡され、価格が変化するたびに価格列が 1 日に何度も更新されるとします。すべての変更照会では、照会する LSN の間隔で各株価に対するすべての変更が返されます。差分変更の照会では、データ内で株式銘柄ごとの最終価格を含む変更行が 1 つだけ返されます。
@supports_net_changes を 1 に設定すると、非クラスター化インデックスが変更テーブルに追加作成され、差分変更の照会関数が作成されます。このインデックスのメンテナンスを行う必要があるため、差分変更を有効にすると、変更データ キャプチャのパフォーマンスに悪影響を及ぼす可能性があります。
@filegroup_name パラメーターでは、変更テーブル作成先のファイル グループ名を指定します。このパラメーターを指定しなければ、既定のファイル グループが使用されます。ほとんどの場合、既定のファイル グループが PRIMARY になっていて、 PRIMARY ファイル グループは小さい状態にしておくことが推奨されるため、ファイル グループ名は必ず指定する必要があります。
キャプチャ ジョブの構成
トランザクション レプリケーション用に既に構成されているデータベースに対して変更データ キャプチャが有効になると、変更データ キャプチャではレプリケーション キャプチャ ジョブも使用されます。このため、ここでの説明は、トランザクション レプリケーションと変更データ キャプチャの両方が有効になっているデータベースには当てはまりません。
変更データ キャプチャ用に SQL Server エージェントに作成されたキャプチャ ジョブ ( 通常は cdc.database name_capture という名前 ) には、パラメーターを指定しない、 sys.sp_MScdc_capture_job プロシージャ呼び出しのみが含まれています。このプロシージャでは、スキャン ジョブのパラメーターを決定し、決定したパラメーターを指定して sys.sp_cdc_scan を呼び出します。 sys.sp_cdc_scan プロシージャでは、 (sys.sp_replcmds を使用して ) ログをスキャンし、キャプチャする必要のあるデータを変更テーブルに挿入することで、実際の処理を行います。
sys.sp_cdc_scan は、キャプチャ ジョブの動作を決める 4 つのパラメーターを受け取ります。
最初のパラメーターは continuous ( 既定値 1) です。このパラメーターでは、キャプチャ ジョブを連続実行するか ( 値 1) 、 1 回のスキャン フェーズ後に終了するか ( ワン ショット モード、値 0) を決定します。ワン ショット モードは、運用環境ではなくテスト環境で使用することをお勧めします。これは、変更データ キャプチャによって処理されるまで、ログ レコードがアクティブな状態のままになるのが主な理由です。その結果、ログは、スキャン ジョブが実行されていなくてもログが増大し続けます。
他の 3 つのパラメーターは、トランザクションをログから読み取って変更テーブルに挿入する頻度と数を決定します。
maxtrans パラメーター ( 既定値 500) は、ログから読み取って変更テーブルに書き込むトランザクション数を決定します。この書き込みは 1 つのトランザクションとして実行されます。 maxscans パラメーター ( 既定値 10) は、ジョブの終了前 (continuous = 0) または 1 回の実行間隔での一時停止前 (continuous=1) にスキャン サイクルが試行される回数を決定します。一時停止の長さは、 pollinginterval パラメーターによって設定されます ( 秒単位、既定値 5 秒 ) 。 WAITFOR は、スキャン サイクルによってログが完全に退避されるとき、または maxscans スキャン サイクルが完了するときに実行されます。
図 1: キャプチャ ジョブのパラメーターによる効果
スキャン ジョブのパラメーターは、 sys.sp_cdc_change_job を使用して変更できます。これらのパラメーターはキャプチャ ジョブの初期化時のみに読み取られるため、 EXEC sys.sp_cdc_stop_job @job\_type = 'capture' を使用してキャプチャ ジョブを停止した後、変更されたパラメーターを適用するために EXEC sys.sp_cdc_start_job @job\_type = 'capture' を使用してキャプチャ ジョブを再開する必要があります。
テスト用ワークロードとテスト環境
変更データ キャプチャのパフォーマンスとワークロードへの影響を検証するために、 ISV アプリケーションのワークロードに対してテストを行いました。テストは、データ、ログ、バックアップ、および変更データ キャプチャの各ファイル グループ用に個別のディスク ボリュームが備わっている、 HP EVA SAN に接続された HP DL 580 (4 基のデュアル コア、 32 GB RAM 、 x64) で実施しました。アプリケーション サーバーは、 HP BL460 (2 基のクアッド コア、 32 GB RAM 、 32 ビット ) で、ギガビット イーサネット経由で接続されます。テストで使用したハードウェアとソフトウェアの環境の詳細については「付録 A: テスト用のハードウェアとソフトウェア」を参照してください。
アプリケーションのメイン データベースでは、合計 603 列を含む 7 つのテーブルで変更データ キャプチャを有効にしました。このワークロードでは、変更データ キャプチャを使用して、マスター参照データ ( 顧客、製品、サイトのデータなど ) ではなくトランザクション データ ( 売上、出荷、監査のデータなど ) をキャプチャしている点に注意してください。多くのユーザーは、変更データ キャプチャを使用しても、トランザクション データではなくマスター参照データへの変更だけをキャプチャするようです。そのため、変更データ キャプチャでキャプチャする必要のあるトランザクションの比率がこのワークロードを使用した場合よりも大幅に低くなります。
このアプリケーションは、 " バッチ " 型のワークロードと多くの連続しないフェーズから成り立っています。フェーズ 3 を除いて ( 表 1 参照 ) 、アプリケーション データベースに対するほぼすべてのトランザクションが、変更データ キャプチャによって追跡されているテーブルに書き込まれました。
フェーズ |
期間 (分) |
1 秒あたりのアプリケーション データベースの平均トランザクション数 (tps) |
1 秒あたりの tempdb 平均トランザクション数 (tps) |
CPU 平均使用率 % ( データベース サーバー ) |
開始 |
数秒 |
2 つの大きなトランザクション (200,000 行と 20,000 行を更新 ) |
N/A |
N/A |
1 |
7 |
5 |
780 |
6 |
2 |
10 |
10 (最大 200 まで急増) |
40 |
13 |
3 |
17 |
3,000 から 1,800 まで徐々に減少 このうち、テーブルへの約 1,000 回の書き込みを変更データ キャプチャが対応 |
5,000 から 3,200 まで減少 |
60 |
4 |
3 |
1,800 ～ 2,500 |
1,000 |
37 |
5 |
7 |
850 |
2,000 |
62 |
6 |
6 |
3,000 ～ 4,000 |
12,000 ～ 1,200 |
45 |
7 |
3 |
変動 (最大 14,500 まで急増) |
変動 (低から中) |
変動 (低から中) |
表 1: アプリケーションの負荷状況
アプリケーションのワークロードに加え、人工的に合成したワークロード ( 特定の種類の DML を一定のレベルでテストするために人為的にスケール変換される単純なワークロード ) もテストしたいと考えました。この合成ワークロードでは、変更データ キャプチャに制限を設け、非常に負荷の高い環境で別のパラメーター セットを使用して変更データ キャプチャの動作を決定します。これらのワークロードはそれぞれ、テスト用のシステムにできるだけ高い負荷をかけるように設計しました。すべてのワークロードは、 100 個の並列スレッドを使用してテスト クライアントから実行し、アプリケーション ワークロードと同じデータベースを使用して 500 秒間実行しました。
人工的に合成したワークロードの詳細は次のとおりです。
1 . 挿入テスト 1: 列数の多いテーブル (82 列 ) に、 8 列にデータを設定した行を挿入します。このテーブルにはあらかじめ 390,000 行が入力されています。1 トランザクションあたり 1 行挿入します。
2 . 挿入 テスト 2: 列数の多いテーブル (21 列 ) に、 10 列にデータを設定した行を挿入します。このテーブルにはあらかじめ 666,000 行が入力されています。1 トランザクションあたり 1 行挿入します。
3 . 少量の更新テスト : 82 列のテーブルの 3 列を更新します。 1 トランザクションあたり 1 行更新します。
4 . トリガーによる挿入 : テスト 1 と同様の挿入を実行します。唯一の違いは、 INSERT ステートメントによって、挿入後に同一テーブルの 1 つの列を更新するトリガーが起動される点です。これにより、 1 回の挿入ごとに、変更キャプチャ テーブルに 3 行作成されます (1 つは挿入行、 1 つは更新前に値が存在する場合はその値を含む行、 1 つは更新後に値が存在する場合にその値を含む行 ) 。
5 . 挿入、更新、および選択の各ステートメントの組み合わせ。
6 . 大量の更新 : 1 トランザクションあたり多数の行 ( 無作為に 10 ～ 2,000 行 ) を更新します。
7 . 大量の挿入 : 列数の多いテーブル (82 列 ) に、 8 列にデータを設定した行を挿入します。このテーブルにはあらかじめ 390,000 行が入力されています。 1 トランザクションあたり 1,000 行挿入します。このワークロードの実行には 300 秒かかりました。
変更データ キャプチャのパフォーマンスと特性の判断
変更データ キャプチャのパフォーマンス特性を判断するには、次に示す 3 つの主な疑問点の答えを見つける必要があります。
. 変更データ キャプチャ自体のパフォーマンスとは何か。
. 変更データ キャプチャを有効にすると、元のワークロードのパフォーマンスにはどのような影響を及ぼすか。
. 変更データ キャプチャによってシステムのどの部分 (CPU 、 I/O など ) にどの程度の影響が出るか。
" 変更データ キャプチャ自体のパフォーマンス " は、元のトランザクションがデータベース内で実行された時刻と変更テーブルに変更レコードが書き込まれた時刻との差異で判明します。この時間を " 待機時間 " と呼びます。待機時間がポーリング間隔を大幅に上回ることがなく、また、時間の経過と共に増加しなければ、変更データ キャプチャを有効にしてもワークロードに十分対応できるシステムと言えます。
待機時間は、 sys.dm_cdc_log_scan_sessions 動的管理ビューを使用しても確認できます。このドキュメントでは、この動的管理ビューを使用して、スキャン セッションにかかった時間、処理されたコマンドとトランザクションの数、待機時間の長さを確認しました。 sys.dm_cdc_log_scan_sessions 動的管理ビューでは、最新のスキャン セッションの一部だけを少量追跡しています。ワークロードの実行時間全体を通じて待機時間の増加を監視したいと考えたため、 sys.dm_cdc_log_scan_sessions を照会するのではなく、拡張イベント sqlserver.cdc_session をファイルにキャプチャしました。
拡張イベントとは、 SQL Server 2008 新しいイベント キャプチャ メカニズムで、パフォーマンスが優れています。これにより、ターゲット ( この場合はファイル ) にイベント ( この場合は sqlserver.cdc_session イベント ) を登録できます。イベントが発生するたびに ( この場合はログ スキャン セッションごとに ) 、イベント データがターゲットに書き込まれます。拡張イベントの大きなメリットは、サーバーにかかるオーバーヘッドが非常に少ない点です。
sqlserver.cdc_session 拡張イベントは、 sys.dm_cdc_log_scan_sessions 動的管理ビューと同じ情報を保持しいますが、拡張イベントを使用すれば、ワークロードで行われたすべてのスキャン セッションを記録できます。
イベントをキャプチャするには、まず、キャプチャ対象のイベントを特定するイベント セッションと、イベントが書き込まれるターゲットを作成する必要があります。
CREATE EVENT SESSION cdc_session ON SERVER
ADD EVENT sqlserver.cdc_session
ADD TARGET package0.asynchronous_file_target
(SET filename='c:\cdc_session.xel',
metadatafile='c:\cdc_session.xem', max_file_size=10)
次に、ワークロードを毎回実行する前に、イベント セッションを有効にします。
ALTER EVENT SESSION cdc_session ON SERVER STATE = start
変更データ キャプチャで指定のワークロードのすべてのトランザクションを追跡するのにかかる時間を記録して、最大待機時間を明らかにするには、ワークロードの終了後に変更データ キャプチャでログの読み取りを終了したかどうかを調べる方法も必要でした。これを確認するには、動的管理ビューを定期的に ( このテストでは 1 分ごと ) に照会して、最後のスキャンが空のスキャン ( つまり、変更テーブルに挿入する必要のあるログ レコードが検出されないスキャン ) であったかどうかを調べます。動的管理ビューでは、空のスキャンは個別に表示されません。代わりに、これらは、空のスキャンの数が設定された empty_scan_count 列を含む 1 つの行として表示されます。今回のワークロードの場合、変更データ キャプチャは、最後のスキャンで空のスキャン回数が 0 以外になるとすぐに遅れを取り戻しました。
SELECT empty_scan_count FROM appdb.sys.dm_cdc_log_scan_sessions WHERE start_time =
(select MAX(start_time) from appdb.sys.dm_cdc_log_scan_sessions)
ワークロードと変更データ キャプチャの両方が終了した後、イベント セッションは無効になり、結果がテーブルに書き込まれます。拡張イベントは XML 形式で記録されるため、結果の XML は解析が必要です。
ALTER EVENT SESSION cdc_session ON SERVER STATE = stop
SELECT
CAST (event_data as
xml).value('(/event/data[@name="start_time"]/text/text())[1]','datetime2(2)' )
start_time,
CAST (event_data as
xml).value('(/event/data[@name="end_time"]/text/text())[1]','datetime2(2)' )
end_time,
CAST (event_data as
xml).value('(/event/data[@name="last_commit_cdc_time"]/text/text())[1]','datetime2(2)
' ) last_commit_cdc_time,
CAST (event_data as
xml).value('(/event/data[@name="duration"]/value/text())[1]','int' ) duration,
CAST (event_data as
xml).value('(/event/data[@name="tran_count"]/value/text())[1]','int' ) tran_count,
CAST (event_data as
xml).value('(/event/data[@name="command_count"]/value/text())[1]','int' )
command_count,
CAST (event_data as xml).value('(/event/data[@name="latency"]/value/text())[1]','int'
) latency
FROM sys.fn_xe_file_target_read_file
('C:\cdc_session_*.xel','C:\cdc_session_*.xem', null, null)
このキャプチャでは、ワークロード中に発生したすべてのログ スキャン セッションの完全なテーブルが生成されます。テーブルには、最大待機時間も含まれます。また、これらの数値から最大スループットも計算します。スループットは、 1 秒あたりのトランザクション数ではなく、 1 秒あたりのコマンド数を測定しました。変更データ キャプチャのスループットを計算するには、十分な負荷がかかった状態で変更データ キャプチャが実行された期間を考慮に入れるだけです。つまり、待機時間が 5 秒を超える期間を考慮します。ここでは、処理されたコマンドの数を計算し、これを待機時間が 5 秒を超える最初のスキャンを開始してから最後のスキャンを終了するまでの時間で除算しました。たとえば、変更データ キャプチャが待機時間に到達した後 1000 秒ちょうどで終了し、その時間内に 1,000,000 個のコマンドを処理していれば、スループットは、 1,000 コマンド / 秒になります。この数値は、変更データ キャプチャが特定のワークロードで適切に対応できるコマンド数になります。
" ワークロードのパフォーマンスに対する変更データ キャプチャの影響 " を判断するには、 ( アプリケーション自体によってテーブルに記録される ) ISV アプリケーション ワークロードにワークロードの実行時間を使用するだけです。実行時間が 500 秒に固定される合成ワークロードの場合、そのワークロード内で完了したトランザクション数を記録しました。
" 変更データ キャプチャによってシステムに生じる追加の負荷 " を判断するために、 Windows® オペレーティング システムと SQL Server 動的管理ビューの多数のパフォーマンス カウンターを記録しました。次のパフォーマンス カウンターは、変更データ キャプチャによるシステムへの影響を測定する際に役立つことが実証されました。
. Logical Disk: データ ディスク (M:) 、ログ ディスク (L:) 、および CDC ファイル グループ ディスク (P:) のディスク キューの平均の長さ
. Databases: アプリケーション データベースおよび tempdb のトランザクション数 / 秒
. Processor: % プロセッサ時間 (合計)
データ ファイルとログ ファイルに負荷が追加される量に関するデータを収集するために、 sys.dm_io_virtual_file_stats 動的管理ビューを使用しました。変更データ キャプチャによってシステムにさらに待機時間が生じるかどうかについては、 sys.dm_os_wait_stats 動的管理ビューを使用しました。この動的管理ビューは、各ワークロードの実行前に DBCC SQLPERF("sys.dm_os_wait_stats" , CLEAR) コマンドを使用してリセットします。また、アプリケーション データベースを復元し、 DBCC FREEPROCCACHE と DBCC DROPCLEANBUFFERS を使用して、 ( すべてのキャッシュがコールド キャッシュになるようにして ) 同じベースラインで各テストの実行を開始しました。
変更データ キャプチャのパフォーマンスに関する考慮事項
スキャン ジョブのパラメーターによる変更データ キャプチャのパフォーマンスへの影響
「キャプチャ ジョブの構成」で説明したとおり、既定のパラメーター (maxscans=10 、 maxtrans=500 、および pollinginterval=5) を指定したキャプチャ ジョブは、理論上でも、 1 秒あたり平均 1,000 件を超えるトランザクションを処理することはできません。
図 2: スキャン ジョブの既定のパラメーター (maxscans=10 、 maxtrans=500 、および pollinginterval=5) を使用した場合の待機時間の増加
実際、 ISV アプリケーション ワークロードでは、 1 秒あたり約 670 件のトランザクションが処理され、処理する必要のあるトランザクションが非常に大きい場合は、 1 秒あたりのトランザクションの処理数が減少することがわかりました。その結果、すべてのトランザクションが変更データ キャプチャによって処理されるまでの総待機時間は 3123 秒になりました。待機時間は、図 2 に示すように、ワークロードが 1 秒あたり 670 件を超えるトランザクションに到達するとすぐに増加し始めました。
変更データ キャプチャによって処理できる 1 秒あたりのトランザクション数を増やしたり、待機時間を短縮したりするには、次の 3 つのいずれかを実行します。
- maxtrans の値を増やして、スキャンごとのトランザクション数を増加する
- maxscans の値を増やして、一時停止前のスキャン回数を増加する
- pollinginterval の値を短くして、スキャン サイクル間の一時停止時間を短縮する
表 2 では、これらのパラメーターを変更した場合に得られる結果を示します。
表 2: スキャン ジョブのさまざまなパラメーターによる変更データ キャプチャのパフォーマンス (ISV ワークロード )
表 2 に示したすべての結果は、 ISV アプリケーション ワークロードを使用して取得しました。このワークロードでは、 7 つのテーブルの 603 列すべてが変更データ キャプチャによってキャプチャされ、差分変更が無効になっており、アプリケーション データと同じファイル グループ上の変更テーブルが含まれています。また、別の LUN の異なるファイル グループに変更テーブルが含まれるようにしても測定しましたが、ワークロードやハードウェアに大きな違いは見つかりませんでした。
スキャン ジョブのパラメーターを変更するだけで、待機時間を約 52 分から約 9 分に短縮し ( 緑の列 ) 、スループットを 1 秒あたり 1,900 個のコマンドに引き上げる ( 最初の濃い黄色の列 ) ことができました。ワークロードの実行時間への影響は 10% を下回りました ( 最初の青い列 ) 。変更データ キャプチャを使用しなくても、ワークロードの実行時間には、既に ± 5% の差異が出ていることに注意してください。これにより、ポーリング間隔が短いテストでは、予期せず優れたワークロード実行時間結果が出たことも説明できます。これは、別のテスト実行には表れていません。
図 3 は、 maxtrans=5000 、 maxscans=100 、および pollinginterval=5 を指定したテストの 1 秒あたりのトランザクション数および待機時間を示しています。図 2 とは異なる目盛りが時間軸 ( 下 ) と待機時間軸 ( 右 ) の両方に使用されていることに注意してください。このようにパラメーターを変更すると、待機時間が大幅に短縮されます。また、待機時間は、ワークロードの実行時間が 35 分を経過するまで増加しません。これは、変更データ キャプチャがより高いトランザクションの負荷に対応できるようになったためです。
図 3: スキャンジョブのパラメーターを変更した (maxscans=100、maxtrans=5000、および pollinginterval=5) 場合の待機時間の増加
この結果では、変更データ キャプチャによるアプリケーション ワークロードへの影響が非常に低い ( ワークロード実行時間の変化が 10% を下回る ) ため、十分な I/O 処理能力を備えたシステムではアプリケーションのパフォーマンスに悪影響を及ぼすことなく、変更データ キャプチャを使用できることが示されています。 I/O サブシステム ( 特にログ ディスク ) が最適とは言えないシステムでは、結果が異なる場合があります。今回のテストでは、ログ ディスクのディスク キューの平均長はほとんどのテストで 3 倍になり、書き込まれたログ バイト数は通常の 2.5 倍まで増加しています。また、ログ ディスクでは、変更データ キャプチャによる多くの読み取りのアクティビティが行われました。読み取りと書き込みの停止時間やデータ ディスクへの書き込み数など、この表に示されていないディスク関連の他のカウンターも大幅に増加していました。
ほとんどのテスト ケースでは、変更データ キャプチャによって、プロセッサの使用率がほんの少しだけ増加します。唯一の例外は、 pollinginterval の値を 0 まで下げたテストです ( スキャン サイクル間の待機時間は発生しません ) 。
maxtrans を大幅に増加しても、アプリケーションのパフォーマンスには悪影響が及ばないことが実証されました。また、パフォーマンスの観点からは、 maxscans または maxtrans を大きくすることと pollinginterval を小さくすることにはほぼ違いがないこともわかりました。アプリケーション ワークロードに対する他の一連のテストでは、 maxscans よりも maxtrans を大きくした場合にわずかにメリットがあることが示されました。ただし、 maxtrans を大きくすると、スキャン サイクルの期間が長くなり (maxtrans = 500,000 の場合、スキャン サイクルは最大 390 秒になりました ) 、 1 件のトランザクションで、変更された行が多数変更テーブルに書き込まれます。この処理では、多数のロックが保持されるため、変更テーブルの読み取りに悪影響を及ぼすことがあります。
合成ワークロードでは、ワークロード 2 ( 列数の少ないテーブルへの少量の挿入 ) を使用した場合、 maxtrans を非常に大きくする (50,000 および 500,000) か pollinginterval を非常に小さくする (0) と、パフォーマンスの低下を示す場合があります ( ワークロードの 1 秒あたりのトランザクション数が減少し、変更データ キャプチャの待機時間が増加します ) 。
表 3: 合成ワークロード 2 の場合の変更データ キャプチャのパフォーマンス
他の合成ワークロードでは、同様の動作は見受けられません。
推奨事項 : 既定のパラメーターを使用した変更データ キャプチャでワークロードに対応できず、待機時間が非常に長くなる場合は、 maxscans や maxtrans を 10 倍にしたり、 pollinginterval を小さくして 1 にしたりすることができます。待機時間が短くなったとしてもまだ長いと感じる場合は、 maxtrans をさらに大きくできますが、ワークロードのパフォーマンス、待機時間、および変更テーブルに対するクエリのパフォーマンスをしっかりと監視してください。
ワークロード特性による変更データ キャプチャのパフォーマンスへの影響
変更データ キャプチャのソリューションを計画する場合、ワークロードの特性が非常に重要です。考慮すべき主な要因には、 INSERT/DELETE と UPDATE の比較、 DML 操作による影響を受けるのがトランザクションにつき 1 行なのか多数の行なのかがあります。 INSERT 操作と UPDATE 操作を比較するために、ワークロード 1 ( トランザクションごとに 1 行テーブルに挿入する ) とワークロード 3 ( 同一テーブル内でトランザクションごとに 1 行更新する ) を比較しました。これらのワークロードの詳細については、「 テスト用ワークロードとテスト環境」を参照してください。
表 4: INSERT トランザクションと UPDATE トランザクションの比較
SQL Server は、 INSERT トランザクションよりも高い割合で UPDATE トランザクションを保持できます (1 秒あたり約 5 倍のトランザクション ) 。変更データ キャプチャでは、 1 つの INSERT で変更テーブルに作成されるのは 1 行だけです。一方、 1 つの UPDATE で変更テーブルに挿入されるのは 2 行です。つまり、合計すると、 UPDATE のワークロードでは、 INSERT のワークロードよりも約 10 倍の行を変更テーブルに挿入する必要があります。その結果、 表 3 に示されている待機時間が長くなっています。変更データ キャプチャによるワークロード パフォーマンス (500 秒間のトランザクション数 ) の低下は、 INSERT ワークロードの場合は 3 ～ 14% 、 UPDATE ワークロードの場合は 10 ～ 17% でした。これは、変更データ キャプチャが原因で、実行する必要のある作業量に対して非常に低いオーバーヘッドです。
アプリケーションの一般的なシナリオでは、 1 行がテーブルに挿入されるとすぐに、データが設定されていないフィールドにデータを設定するため更新が行われます。これは、アプリケーションで行われることも、データが設定されていないフィールドを検出して必要に応じて更新する INSERT トリガーを使用して行われることもあります。このようなシナリオで変更データ キャプチャをテストしました。ワークロード 1 および 4 で挿入されるテーブルには、 INSERT トリガーがあります。このトリガーでは、ある列の値が設定されているかどうかを確認して、 NULL の場合に別の列値に更新します。
表 5: 挿入された行がすぐに更新される場合の変更データ キャプチャのパフォーマンス
合成ワークロード 1 では、トリガーで更新が行われないように INSERT コマンドでこの列の値を設定します。合成ワークロード 4 では、列の値が設定されていなければ、 ( 同一トランザクション内で ) 挿入後トリガーによってすぐに行が更新されます。これにより、元のテーブルに行を挿入するたびに変更テーブルに書き込む必要のある行が 1 行ではなく 3 行になります。結果からは、挿入後すぐに更新が行わない方がワークロードと変更データ キャプチャ両方のパフォーマンスが向上することが示されています。
推奨事項 : 挿入直後に行を更新する必要があるシナリオは避けます。
ワークロード 6 ( 大量の更新、トランザクションごとに 10 ～ 2,000 行を更新 ) では、変更データ キャプチャのパラメーターに関係なく、パフォーマンスや待機時間に大きな差異は示されませんでした。この理由は、大きな更新トランザクションが少量でも、変更データ キャプチャでは、ソース テーブルで行が更新されるたびに変更テーブルに 2 行挿入する必要があるためです。そのため、このようなワークロードでは、 1 つのトランザクションによって、変更テーブルに平均 2,000 行の挿入が行われます。こうした大量の挿入にかかる時間は非常に長くなるため、スキャン ジョブのパラメーターによる影響はそれほど大きくありません。既定のパラメーターを使用しても、変更データ キャプチャは、 1 回のスキャン サイクルで 100 万行を変更テーブルに挿入する必要があります。この場合、待機時間は、変更テーブルに挿入する必要があるデータの合計によってのみ決まります。約 1,800 万行の更新に 800 秒かかるワークロードの場合、待機時間は 13,000 秒 ( すべての列をキャプチャする場合 ) か 7,000 秒 ( キャプチャする列数を少なくした場合 ) になります。
推奨事項 : 大量の更新トランザクションを頻繁に実行するテーブルへの変更をキャプチャする場合は、変更データ キャプチャの使用を避けます。
ワークロード 7 ( 大量の挿入、トランザクションごとに 1,000 行を挿入、 300 秒間で約 170 万行を挿入 ) でも、変更データ キャプチャのパラメーターに関係なく、パフォーマンスおよび待機時間に大きな差異は示されませんでした。待機時間は、差分変更のサポート、変更データ キャプチャでキャプチャする列数、およびスキャン ジョブのパラメーターに応じて、約 250 ～ 399 秒になります。つまり、変更データ キャプチャでは、ワークロードの終了後に変更テーブルに行を挿入するのに、元のワークロードとほぼ同じ時間がかかります。
sys.sp_cdc_enable_table のパラメーターによる変更データ キャプチャのパフォーマンスへの影響
アプリケーションのワークロードでは、合計 603 列の 7 つのテーブルで変更データ キャプチャが有効になっています。変更テーブルを調査すると、複数の異なる値が設定されているのは 171 列だけであることがわかりました。他の列には、 NULL 値、または常に同じ値が設定されています。 2 回目のテスト実行では、異なる値が設定された 171 列だけをキャプチャすることに決めました。そのため、 sys.sp_cdc_enable_table の @captured\_column\_list パラメーターを指定します。キャプチャする列数を少なくしたことで、特にスキャン ジョブに既定以外のパラメーターを指定した場合、変更データ キャプチャ全体のパフォーマンスに大きな差が生じることがわかりました。
表 6: キャプチャするデータ量に応じた変更データ キャプチャのパフォーマンス
キャプチャする列を挿入や更新に関連する列に限定することで、ワークロード全体の待機時間が 8 分以下に短縮されるだけでなく、ワークロードの実行時間が短縮され、変更データ キャプチャによって I/O サブシステムに生じる負荷も軽減することができました。合成ワークロードでも、同様に、キャプチャする列数が少ないときに 1 秒あたりのトランザクション数の増加と待機時間の短縮が見られました。
図 4: キャプチャする列数を少なくした場合の待機時間の増加 (maxscans=100 、 maxtrans=5000 、 pollinginterval=5)
図 4 ( 図 3 と同じスキャン ジョブ パラメーターを使用 ) では、キャプチャする列数を少なくしたことで、実行時間が短くなり、パフォーマンスが向上するだけでなく、待機時間は 36 ～ 43 分に縮小されます。
推奨事項 : sys.sp_cdc_enable_table の @captured\_column\_list パラメーターを指定して、変更データ キャプチャによってキャプチャする列の一覧を、常に、実際に追跡する必要がある列のみ限定します。
@supports\_net\_changes パラメーターは、変更データ キャプチャのパフォーマンスに大きな影響を及ぼす可能性があります。特に変更データ キャプチャがワークロードに対応できる場合でも、差分変更の照会をサポートするために追加のインデックスを保持することで生じる負荷によって、変更データ キャプチャがワークロードに対応できなくなることがあります。次の表では、合成ワークロード 4 ( 挿入直後に行を更新するトリガーを使用して挿入 ) で差分変更の照会をサポートした場合とサポートしない場合のパフォーマンスを示しています。
表 7: @supports\_net\_changes パラメーターによる影響
推奨事項 : 差分変更をサポートする必要がなければ、 @supports\_net\_changes を 0 に設定します。差分変更の照会は必要でも、変更データ キャプチャの待機時間が非常に長くなる場合は、差分変更のサポートを無効にし、後からステージング データベースで差分変更の検出を実行することをお勧めします。
クリーンアップ ジョブに関する考慮事項
クリーンアップのパフォーマンスをテストするために、アプリケーション ワークロードの終了後にクリーンアップを手動で実行しました。このテストでは、 sys.sp_cdc_cleanup_change_table を実行することで、さまざまなしきい値パラメーターを使用して 4,147,855 行を含む 1 つの変更テーブルを完全に消去しました。構成可能なしきい値により、任意の 1 ステートメントで削除されるエントリ数が制限されます。このテストが行われたのは、 (SELECT count(*) FROM change_table_name を実行して ) 変更テーブルに照会し、変更テーブルが最初に読み取られてから消去されるときのシナリオのシミュレーションを行った後です。
クリーンアップ ジョブをワークロードを追加しないで実行するときは、しきい値が最大 500,000 まで増加することにより、クリーンアップのパフォーマンス全体が向上することが示されました。さらに (1 つのトランザクションでテーブル全体がクリーンアップされるように ) しきい値を 5,000,000 に増加した場合は、図 5 に示すように、パフォーマンスは低下しました。
図 5: しきい値に応じたクリーンアップ ジョブの実行時間
クリーンアップ ジョブをワークロード ( ワークロード 5: 挿入、更新、および選択のトランザクションの組み合わせ ) と並行して実行すると、クリーンアップの実行時間が大幅に増加しました。このテストは、変更データ キャプチャ ジョブのパラメーター (maxtrans=5000 、 maxscans=100 、および pollinginterval=5) を使用して実行されました。
図 5: ワークロードを同時実行した場合のクリーンアップ ジョブの実行時間
ここでも、しきい値 500,000 が、このワークロードで最適な結果が得られます。図 6 では、クリーンアップ ジョブ実行時のトランザクションの平均実行時間も示しています。特に大きなしきい値を使用すると、変更テーブルでロックのエスカレーションが発生することがあるのがわかっています。これにより、アプリケーションの応答時間が低下することがあります。また、変更データ キャプチャのスキャン ジョブの待機時間が増加することもあります。
推奨事項 : 可能であれば、他にアクティブなワークロードがないときにクリーンアップを実行します。ワークロードにとって最適な結果が得られるまで、しきい値パラメーターを大きくしながらテストを行います。
トランザクション ログ ファイルに関する考慮事項
変更データ キャプチャで注意すべき最も重要な点の 1 つに、トランザクション ログ I/O サブシステムがあります。既に説明したとおり、変更データ キャプチャがデータベースで有効な場合、ログ ファイル I/O は大幅に増加します。その上、ログ レコードは、変更データ キャプチャの処理が完了するまでアクティブな状態のままになります。つまり、特に待機時間が大幅に増加する環境では、ログ ファイルも大幅に増大する可能性があります。これは、単純復旧モデルであっても、完全復旧モデルにおけるログ バックアップ後でも、変更データ キャプチャのスキャン ジョブによってログ レコードが処理されない限り、ログ領域を再利用できないためです。
変更データ キャプチャはすべての復旧モデルと連携することに注意してください。ただし、通常、単純復旧モデルや一括ログ復旧モデルで最小限しかログに記録されない操作でも、変更データ キャプチャが有効であれば、すべての変更をキャプチャできるように、すべての操作がログに記録されます。
ログ ディスクがいっぱいになっても、変更データ キャプチャによってすべてのトランザクションが処理されるまで、バックアップでも手動でもログ ファイルを圧縮できないことに注意してください。ただし、ログ ディスクがいっぱいになっていると、変更データ キャプチャではトランザクションを処理できません。これは、変更データ キャプチャによる変更テーブルへの書き込みがログに記録される操作であるためです。この場合、この状況から復旧する最も簡単な方法は、一時的に別のディスク上にもう 1 つログ ファイルを追加することです。
推奨事項 : 変更データ キャプチャのアーキテクチャを計画する場合は、ログ サイズとログ ボリュームの I/O 操作が大幅に増加することを考慮します。変更データ キャプチャによってキャプチャされるデータの量と、変更データ キャプチャで変更に対応する際に必要な時間に応じて、ログ ファイルのサイズは元のサイズの 2 ～ 3 倍になります。場合によっては、さらに大きくなることもあります。ログ ファイルのサイズは適宜調整してください。ログ ファイルのサイズが大きくなっても、ログ ディスクがいっぱいにならないようにします。
ファイル グループに関する考慮事項
変更テーブルがアプリケーション テーブルと同じファイル グループにある場合と別のファイル グループにある場合とではテスト結果に差はありませんでした。既に高負荷状態にある I/O サブシステムにデータ ファイルが保存されている場合は差が出る可能性があります。このような場合は、別の物理ディスク上のファイル グループに変更テーブルを配置すれば、変更データ キャプチャのパフォーマンスが向上することがあります。
推奨事項 : PRIMARY ファイル グループを小さい状態のままにし、アプリケーション データと変更データ明確に分離するには、 sys.sp_cdc_enable_table に @filegroup\_name を指定します。
変更テーブルに加え、 cdc.lsn_time_mapping テーブルもサイズが大きくなり、多くの I/O 操作の対象になります。 sys.sp_cdc_enble_db がデータベースに対して実行されると、このテーブルが既定のファイル グループに作成されます。
推奨事項 :sys.sp_cdc_enble_db を実行する前にデータベースの既定のファイル グループを変更することを検討します。これにより、変更データ キャプチャのメタデータと特に cdc.lsn_time_mapping が PRIMARY とは別のファイル グループに保存されます。既定のファイル グループは、変更データ キャプチャのメタデータ テーブルが作成された後、元に戻すことができます。
まとめ
変更データ キャプチャにより、一連のテーブルに加えられた変更をキャプチャするための簡単でパフォーマンスの高い方法が提供されます。変更データ キャプチャのパフォーマンスをチューニングする方法は多数あります。ワークロードの特性、システム I/O の使用量、許容可能な待機時間を把握することが、基本となるワークロードに悪影響を及ぼすことなく、変更データ キャプチャのパフォーマンスをチューニングするための鍵になります。スキャン ジョブのパラメーター、 sys.sp_cdc_enable_table のパラメーター、さらに、可能な場合はワークロードのクエリをチューニングすると、負荷のかかった状態でも変更データ キャプチャのパフォーマンスを大幅に向上することができます。
このホワイト ペーパーで得られた推奨事項を以下にまとめます。
ストレージ:
. 変更データ キャプチャのアーキテクチャを計画する場合は、ログ サイズとログ ボリュームの I/O 操作が大幅に増加することを考慮します。
. sys.sp_cdc_enable_table でファイル グループを指定することを検討します。
. sys.sp_cdc_enble_db を実行する前にデータベースの既定のファイル グループを変更することを検討します。これにより、変更データ キャプチャのメタデータと特に cdc.lsn_time_mapping が PRIMARY とは別のファイル グループに保存されます。
ワークロードの動作:
. 挿入直後に行を更新する必要があるシナリオは避けます。
. 大量の更新トランザクションを頻繁に実行するテーブルへの変更をキャプチャする場合は、変更データ キャプチャの使用を避けます。
変更データ キャプチャのパラメーター :
. 変更データ キャプチャによってキャプチャする列の一覧は、常に、実際に追跡する必要がある列のみに限定します。
. 差分変更をサポートする必要がなければ、 @supports\_net\_changes を 0 に設定します。
. 変更データ キャプチャでワークロードに対応できるかどうかを確認するには、 sys.dm_cdc_log_scan_sessions を使用します。
. 変更データ キャプチャでワークロードに対応できない場合は、スキャン ジョブのパラメーターを変更してからスキャン ジョブを再開します。
クリーンアップ:
. 可能であれば、他にアクティブなワークロードがないときにクリーンアップを実行します。
. ワークロードにとって最適な結果が得られるまで、しきい値パラメーターを大きくしながらテストを行います。
付録 A: テスト用のハードウェアとソフトウェア
データベース サーバー
HP DL 580
. 4 ソケットのデュアル コア
. Intel Xeon 3.4 GHz
. 32 GB RAM
. Windows Server® 2003 Enterprise x64 Edition Service Pack 2 (SP2)
アプリケーション サーバー (ISV アプリケーション用 )
HP BL 460
. 2 ソケットのクアッド コア
. Intel Xeon 2.83 GHz
. 32 GB RAM
. Windows Server 2003 (32 ビット版) SP2
ストレージ
2 つのディスク グループを備えた HP EVA SAN:
. ディスク グループ 1 : 152 ディスク (300 GB 、 15,000 RPM 、 RAID1+0) 。ディスク グループ 1 には、次の用途で個別の論理ボリュームを含みます。
. ソース テーブル用のデータベース データ ファイル
. 変更テーブル用のデータベース データ ファイル
. tempdb データベース用のデータ ファイル
. バックアップ
. ディスク グループ 2: 88 ディスク (72 GB 、 15,000 RPM 、 RAID1+0) 。ディスク グループ 2 には、次のファイルを含みます。
. データベース ログ ファイル
ソフトウェア
SQL Server 2008
関連情報:
https://www.microsoft.com/japan/sqlserver/: SQL Server Web サイト
https://technet.microsoft.com/ja-jp/sqlserver/: SQL Server TechCenter
https://msdn.microsoft.com/ja-jp/sqlserver/: SQL Server デベロッパー センター
http://www.sqlcat.com/: SQL Server Customer Advisory チーム ( 英語 )
このホワイト ペーパーはお役に立ちましたか ? フィードバックをお寄せください。 1 ( 役に立たなかった ) ～ 5 ( 非常に役に立った ) の 5 段階で評価してください。また、その評価の理由もお知らせください。以下に例を示します。
- 評価が高いのは、例が適切、図がわかりやすい、説明が明快といった理由からですか。
- 評価が低いのは、例が少ない、図がわかりにくい、説明があいまいといった理由からですか。
このようなフィードバックをお寄せいただくと、今後のホワイト ペーパーの品質向上につながります。