変更データ キャプチャの管理と監視

適用対象: SQL Server Azure SQL Managed Instance

このトピックでは、SQL Server と Azure SQL Managed Instance で変更データ キャプチャを管理および監視する方法について説明します。

別のジョブ メカニズムを使用する Azure SQL Database については、「Azure SQL Database での CDC」を参照してください。

キャプチャ ジョブ

キャプチャ ジョブは、パラメーターなしのストアド プロシージャ sp_MScdc_capture_job を実行することによって開始されます。 このストアド プロシージャは、msdb.dbo.cdc_jobsからのキャプチャ ジョブの maxtrans、maxscans、continuous、および pollingintervalの構成値を抽出することによって開始されます。 これらの構成値は、パラメーターとしてストアド プロシージャ sp_cdc_scan に渡されます。 これは sp_replcmds を呼び出してログ スキャンを実行する場合に使用されます。

キャプチャ ジョブのパラメーター

キャプチャ ジョブの動作を理解するには、sp_cdc_scan における構成可能パラメーターの使用方法について理解する必要があります。

maxtrans パラメーター

maxtrans パラメーターは、ログの単一のスキャン サイクルで処理できるトランザクションの最大数を指定します。 スキャン時に、処理するトランザクションの数がこの制限値に達すると、現在のスキャンにはそれ以上のトランザクションは含まれません。 スキャン サイクルの完了後は、処理されたトランザクションの数は常に maxtrans 以下になります。

maxscans パラメーター

maxscans パラメーターは、制御を返す前 (continuous = 0)、または waitfor を実行する前 (continuous = 1) に、ログを空にするために実行されるスキャン サイクルの最大数を指定します。

continuous パラメーター

continuous パラメーターは sp_cdc_scan が、ログを空にするか、最大数のスキャン サイクル (ワン ショット モード) を実行した後で制御を開放するかどうかを決定します。 また、明示的に停止されるまで、sp_cdc_scan の実行を継続するかどうかもこのパラメーターが制御します (連続モード)。

ワンショット モード

ワンショット モードでは、キャプチャ ジョブは sp_cdc_scan が最大 maxtrans 回のスキャンを実行して、ログを空にして返すことを要求します。 ログに存在するトランザクションで maxtrans を超えた分は後のスキャンで処理されます。

ワンショット モードは、処理するトランザクションのボリュームがわかっている、制御されたテストで使用され、完了時にジョブが自動的に終了するという利点があります。 ワンショット モードは、実稼働環境用にはお勧めできません。 これは、このモードではジョブ スケジュールに依存してスキャン サイクル実行の頻度が管理されるためです。

ワンショット モードでの実行時には、キャプチャ ジョブの予想スループットの上限を計算できます。これは次の計算式を使用して、1 秒あたりのトランザクション数として表します。

(maxtrans * maxscans) / number of seconds between scans

ログをスキャンして変更テーブルにデータを設定するために必要な時間が 0 とあまり変わらない場合でも、ジョブの平均スループットは、単一スキャンでのトランザクションの最大許容数にスキャンの最大許容数を掛け、それをログ処理間隔の秒数で割った値を超えることはできません。

ワンショット モードでログ スキャンを制御する場合は、ログ処理間の秒数をジョブ スケジュールによって決める必要があります。 このような動作が必要な場合、ログ スキャンの再スケジュールには、連続モードでのキャプチャ ジョブの実行がより適しています。

連続モードとポーリング間隔

連続モードでは、キャプチャ ジョブは sp_cdc_scan が継続的に実行されることを要求します。 これにより、ストアド プロシージャは、maxtrans と maxscans だけではなく、ログ処理間 (ポーリング間隔) の秒数の値を指定することにより、独自の wait ループを管理できます。 継続モードではと、キャプチャ ジョブはアクティブな状態を維持し、ログ スキャン間に WAITFOR を実行します。

キャプチャ ジョブのカスタマイズ

キャプチャ ジョブでは、追加のロジックを適用することにより、固定したポーリング間隔に依存せずに、新しいスキャンをすぐに開始するか、スリープ状態を経てから新しいスキャンを開始するかを決定できます。 この決定は単に時刻に基づきます。たとえば、アクティビティのピーク時に非常に長いスリープを強制したり、その日の処理を完了し、夜間の運用に備えることが重要な 1 日の終わりにポーリング間隔を 0 にしたりします。 また、キャプチャ プロセスの進捗状態を監視して、午前零時までにコミットされたすべてのトランザクションのスキャンが完了して変更テーブルに格納された時刻を判断することもできます。 これにより、キャプチャ ジョブを終了して、毎日 1 回のスケジュールで再起動することが可能になります。 動作をカスタマイズするには、sp_cdc_scan を呼び出すジョブ ステップを、ユーザーが書いた sp_cdc_scan のラッパーへの呼び出しで置き換えることができます。

クリーンアップ ジョブ

ここでは、変更データ キャプチャのクリーンアップ ジョブの仕組みについて説明します。

クリーンアップ ジョブの構造

変更データ キャプチャでは、保有期間に基づくクリーンアップ戦略を使用して変更テーブルのサイズを管理します。 SQL Server と Azure SQL Managed Instance では、クリーンアップ メカニズムは、最初のデータベース テーブルが有効になったときに作成される SQL Server Agent Transact-SQL ジョブから構成されます。 単一のクリーンアップ ジョブがすべてのデータベース変更テーブルのクリーンアップを処理し、定義されたすべてのキャプチャ インスタンスに同じ保有期間値を適用します。

クリーンアップ ジョブは、パラメーターなしのストアド プロシージャ sp_MScdc_cleanup_job を実行することによって開始されます。 このストアド プロシージャは、構成された保有期間値およびしきい値をクリーンアップ ジョブのために msdb.dbo.cdc_jobs から抽出することによって開始されます。 保有期間値は、変更テーブルの新しい低水位マークの計算に使用します。 cdc.lsn_time_mapping テーブルの tran_end_time の最大値から指定の分数を差し引き、datetime 値として表す新しい低水位マークを取得します。 その後、CDC.lsn_time_mapping テーブルを使用して、対応する lsn 値にこの datetime 値を変換します。 テーブル内の複数のエントリが同じコミット時刻を共有する場合は、最小の lsn を持つエントリに対応する lsn が新しい低水位マークとして選択されます。 この lsn 値は sp_cdc_cleanup_change_tables に渡され、データベース変更テーブルから変更テーブル エントリが削除されます。

クリーンアップが実行されると、すべてのキャプチャ インスタンスの低水位マークが単一のトランザクションで最初に更新されます。 その後、使用されなくなったエントリの変更テーブルおよび cdc.lsn_time_mapping テーブルからの削除が試みられます。 単一のステートメントで削除できるエントリの数は、構成可能なしきい値によって制限されます。 1 つのテーブルで削除が失敗しても、残りのテーブルで削除操作ができなくなるわけではありません。

クリーンアップ ジョブのカスタマイズ

クリーンアップ ジョブの場合、カスタマイズが可能なのは、破棄する変更テーブル エントリを決定する戦略です。 配布されたクリーンアップ ジョブでサポートされる唯一の戦略は時間ベースのものです。 この状況では、新しい低水位マークは最後に処理されたトランザクションのコミット時刻から許容保有期間を引いて計算します。 基になるクリーンアップ プロシージャは、時間ではなく lsn に基づいているため、変更テーブルに保持する最小の lsn を決定する戦略はいくつもあります。 厳密には時間ベースのものはそのうちの一部のみです。 たとえばクライアントに関する知識は、変更テーブルへのアクセスを必要とするダウンストリーム プロセスを実行できない場合にフェールセーフを提供するために使用できます。 また、既定の戦略ではすべてのデータベースの変更テーブルのクリーンアップに同じ lsn を使用しますが、基になるクリーンアップ プロシージャを呼び出してキャプチャ インスタンス レベルでクリーンアップすることもできます。

プロセスを監視する

変更データ キャプチャ プロセスを監視すると、変更が変更テーブルに適切に書き込まれているかどうか、および書き込み時の待機時間が妥当かどうかを判断できます。 また、発生する可能性のあるエラーを特定することもできます。 SQL Server には、変更データ キャプチャの監視に役立つ sys.dm_cdc_log_scan_sessions と sys.dm_cdc_errors という 2 つの動的管理ビューが用意されています。

空の結果セットを含むセッションの特定

sys.dm_cdc_log_scan_sessions の各行はログ スキャン セッションを表します (ID が 0 の行を除く)。 ログ スキャン セッションは、 sp_cdc_scanの 1 回の実行に相当します。 セッション中、スキャンによって変更内容または空の結果のいずれかが返されます。 結果セットが空の場合、sys.dm_cdc_log_scan_sessions の empty_scan_count 列が 1 に設定されます。 空の結果セットが連続する場合 (キャプチャ ジョブを連続的に実行している場合など)、既存の最後の行の empty_scan_count が増分されます。 たとえば、変更内容を返したスキャンについて、sys.dm_cdc_log_scan_sessions に既に 10 行が格納されており、5 つの空の結果が行に含まれている場合、ビューには 11 行が格納されています。 最後の行の empty_scan_count 列の値は 5 になります。 空のスキャンを含むセッションを確認するには、次のクエリを実行します。

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

待機時間の判断

sys.dm_cdc_log_scan_sessions 管理ビューには、各キャプチャ セッションの待機時間を記録する列があります。 待機時間は、トランザクションがソース テーブルにコミットされてから、最後にキャプチャされたトランザクションが変更テーブルにコミットされるまでの経過時間として定義されます。 待機時間列では、アクティブなセッションについてのみ値が設定されます。 empty_scan_count 列の値が 0 より大きいセッションについては、待機時間列は 0 に設定されます。 次のクエリは、最新のセッションの平均待機時間を返します。

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

待機時間データを使用すると、キャプチャ プロセスでのトランザクションの処理時間を確認できます。 このデータが最も役立つのは、キャプチャ プロセスを連続的に実行している場合です。 スケジュールに従ってキャプチャ プロセスを実行している場合、トランザクションがソース テーブルにコミットされてから、スケジュールされた時間にキャプチャ プロセスが実行されるまでの間に遅延が生じるため、待機時間が長くなる可能性があります。

キャプチャ プロセスに関するもう 1 つの重要な指標はスループットです。 これは、各セッション中に処理された 1 秒あたりの平均コマンド数です。 セッションのスループットを確認するには、command_count 列の値を実行時間列の値で除算します。 次のクエリは、最新のセッションの平均スループットを返します。

SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

データ コレクターを使用したサンプル データの収集

SQL Server データ コレクターを使用すると、任意のテーブルまたは動的管理ビューからデータのスナップショットを収集して、パフォーマンス データ ウェアハウスを構築することができます。 データベースで変更データ キャプチャが有効になっている場合、sys.dm_cdc_log_scan_sessions ビューおよび sys.dm_cdc_errors ビューのスナップショットを一定間隔で取得すると、後の分析に役立ちます。 次の手順では、sys.dm_cdc_log_scan_sessions 管理ビューからサンプル データを収集するデータ コレクターを設定します。

データ収集の構成

1. データ コレクターを有効にし、管理データ ウェアハウスを構成します。 詳細については、「 データ コレクションの管理」を参照してください。

2. 次のコードを実行して、変更データ キャプチャ用のカスタム データ コレクターを作成します。

   USE msdb;  
   
   DECLARE @schedule_uid uniqueidentifier;  
   
   -- Collect and upload data every 5 minutes  
   SELECT @schedule_uid = (  
   SELECT schedule_uid from sysschedules_localserver_view
   WHERE name = N'CollectorSchedule_Every_5min')  
   
   DECLARE @collection_set_id int;  
   
   EXEC dbo.sp_syscollector_create_collection_set  
   @name = N' CDC Performance Data Collector',  
   @schedule_uid = @schedule_uid,
   @collection_mode = 0,
   @days_until_expiration = 30,
   @description = N'This collection set collects CDC metadata',  
   @collection_set_id = @collection_set_id output;  
   
   -- Create a collection item using statistics from
   -- the change data capture dynamic management view.  
   DECLARE @parameters xml;  
   DECLARE @collection_item_id int;  
   
   SELECT @parameters = CONVERT(xml,
       N'<TSQLQueryCollector>  
           <Query>  
             <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value>  
             <OutputTable>cdc_log_scan_data</OutputTable>  
           </Query>  
         </TSQLQueryCollector>');  
   
   EXEC dbo.sp_syscollector_create_collection_item  
   @collection_set_id = @collection_set_id,  
   @collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419',  
   @name = ' CDC Performance Data Collector',  
   @frequency = 5,
   @parameters = @parameters,  
   @collection_item_id = @collection_item_id output;
   
   GO  
   

3. SQL Server Management Studioで、[管理]、[データ コレクション]の順に展開します。 [CDC パフォーマンス データ コレクター]を右クリックし、[データ コレクション セットの開始] をクリックします。

4. 手順 1. で構成したデータ ウェアハウスで、custom_snapshots.cdc_log_scan_data テーブルを検索します。 このテーブルには、ログ スキャン セッションのデータの履歴スナップショットが格納されています。 このデータを使用すると、待機時間やスループットなどのパフォーマンス指標を時系列で分析できます。

スクリプト アップグレード モード

累積更新プログラムまたはサービス パックをインスタンスに適用する場合、再起動時に、インスタンスがスクリプト アップグレード モードになることがあります。 このモードでは、SQL Server が内部 CDC テーブルを分析し、アップグレードする手順を行うため、キャプチャ テーブルのインデックスなどのオブジェクトが再作成されることがあります。 関連するデータの量によっては、この手順にいくらかの時間がかかったり、または有効にされている CDC データベースのトランザクション ログの使用率が高くなったりする可能性があります。

関連するコンテンツ

• Azure SQL Database での CDC
• データ変更の追跡 (SQL Server)
• 変更データ キャプチャについて (SQL Server)
• 変更データ キャプチャの有効化と無効化 (SQL Server)
• 変更データの処理 (SQL Server)