管理和監視異動資料擷取 （SQL Server）

本主題描述如何管理和監視異動數據擷取。

擷取工作

擷取作業是透過執行無參數的預存程序 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） 之前，嘗試清空記錄的掃描週期數目上限。

連續參數

連續參數會控制在清空記錄檔或執行掃描週期數目上限后，是否sp_cdc_scan放棄 控制 （一次拍攝模式）。 它也控制是否 sp_cdc_scan 繼續執行，直到明確停止（連續模式）。

單次拍攝模式

在單次拍攝模式中，擷取作業請求 sp_cdc_scan 執行最多 maxtrans 次掃描，以便嘗試清空日誌並返回。 除了 maxtrans 之外，任何已存在於記錄檔的交易都會在稍後的掃描中處理。

單次執行模式用於受控制的測試中，其中將處理的交易量是已知的，而且這種模式的優勢在於作業完成後能自動關閉。 不建議在生產環境中使用一次拍攝模式。 這是因為 t 依賴作業排程來管理掃描週期的執行頻率。

當以一次性模式運行時，您可以使用下列計算來計算捕獲工作的期望吞吐量上限，單位為每秒的交易次數：

(maxtrans * maxscans) / number of seconds between scans

即使掃描記錄檔和擴展變更資料表所需的時間並未與 0 完全不同，此作業的平均輸送量仍無法超過將單一掃描允許的最大交易數乘以允許的最大掃描數再除以分隔記錄檔處理的秒數所取得的值。

如果要使用一個拍攝模式來規範記錄掃描，記錄處理之間的秒數必須受到作業排程的控管。 當需要此類行為時，以連續模式執行捕捉任務更有利於管理重新排程日誌掃描。

連續模式和輪詢間隔

在連續模式中，擷取作業會要求 sp_cdc_scan 連續執行。 這可讓預存程式能管理自己的等候迴圈，不僅提供 maxtrans 和 maxscans 的設定，還能指定記錄處理之間的秒數值（即輪詢間隔）。 在這裡模式中執行時，擷取作業會保持作用中，在記錄掃描之間執行 WAITFOR 。

擷取任務客製化

您可以為擷取任務套用其他邏輯，以決定新的掃描是否立即開始，或是在啟動新的掃描前加入暫停，而不是依賴固定的輪詢間隔。 該選擇可能僅僅基於一天中時間的考量，例如在尖峰活動時段強制執行非常長的休眠，甚至在一天結束時將輪詢間隔設為 0，以便於在日間處理結束後準備夜間的運行，這對於完成流程至關重要。 您可以監視擷取程序的進度，以判斷所有在午夜前已認可的交易是否已掃描並儲存在變更表中。 這會讓擷取作業結束，以便按照每日排程自動重新啟動。 藉由將作業步驟中對 sp_cdc_scan 的呼叫替換為用戶撰寫的包裝器對 sp_cdc_scan 的呼叫，即可實現高度定制的行為，並且只需付出極少的額外努力。

清除作業

本節提供有關異動資料擷取清除作業如何運作的資訊。

清除作業的結構

異動數據擷取會使用保留型清除策略來管理變更數據表大小。 清除機制是由啟用第一個資料庫數據表時所建立的 SQL Server Agent Transact-SQL 作業所組成。 單一清除作業會處理所有資料庫變更資料表的清除，並且將相同的保留值套用至所有定義的擷取執行個體。

清除作業是透過執行無參數的預存程序 sp_MScdc_cleanup_job 起始的。 這個預存程序一開始會從 msdb.dbo.cdc_jobs 中擷取清除作業的設定保留和臨界值。 此保留值會用來計算變更資料表的新下限標準。 指定的分鐘數會從數據表的最大 tran_end_time 值減去，以取得以日期時間值 cdc.lsn_time_mapping 表示的新低水位標記。 然後，使用 CDC.lsn_time_mapping 資料表來將這個日期時間值轉換成對應的 lsn 值。 如果資料表中的多個項目共用相同的認可時間，就會選擇對應至具有最小 lsn 之項目的 lsn 成為新的下限標準。 這個 lsn 值會傳遞給 sp_cdc_cleanup_change_tables，以便從資料庫變更資料表中移除變更資料表項目。

執行清除時，所有擷取實例的低水位標記一開始是在單一交易中更新。 然後，它會嘗試從變更資料表和 cdc.lsn_time_mapping 資料表中移除已過時的項目。 可設定的臨界值會限制在任何單一陳述式中刪除的項目數。 如果無法針對任何個別的資料表執行刪除，將無法防止針對其餘資料表嘗試進行此作業。

清除作業自定義

對於清除作業而言，自訂的可能性在於用來決定哪些變更資料表項目要捨棄的策略。 在提供的清除作業中，唯一支援的策略是時間為基礎的策略。 在該情況下，新下限標準的計算方式是從上次處理之交易的認可時間中減去允許的保留週期。 由於基礎清除程序是以 lsn 而非時間為基礎，因此可以使用任何策略來決定要保留在變更資料表中的最小 lsn。 其中只有某些是嚴格以時間為基礎的。 例如，如果需要存取變更資料表的下游處理序無法執行，用戶端的相關知識就可用來提供保全。 此外，雖然預設策略會套用相同 lsn 來清除所有資料庫的變更資料表，但是您也可以呼叫基礎清除程序，以便在擷取執行個體層級進行清除。

監視變更資料擷取程序

監視異動資料擷取程序可讓您判斷變更是否正確地並且以合理的延遲寫入變更資料表。 監視也可以協助您識別可能發生的任何錯誤。 SQL Server 包含兩個動態管理檢視，可協助您監視異動資料擷取：sys.dm_cdc_log_scan_sessions 與 sys.dm_cdc_errors。

識別具有空白結果集的會話

sys.dm_cdc_log_scan_sessions中的每個數據列都代表記錄掃描會話（但標識符為 0 的數據列除外）。 記錄檔掃描工作階段相當於執行一次 sp_cdc_scan。 在一個工作階段中，掃描可以傳回變更，也可能傳回空結果。 如果結果集是空的，sys.dm_cdc_log_scan_sessions中的empty_scan_count數據行會設定為 1。 如果含有連續的空結果集 (例如擷取作業連續執行)，最後一個現有資料列中的 empty_scan_count 就會遞增。 例如，如果 sys.dm_cdc_log_scan_sessions 已經包含 10 行返回變更的掃描記錄，而出現五次連續的空白結果，則該檢視包含 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 時，latency 資料行會被設為 0。 下列查詢會針對最近的工作階段傳回平均延遲：

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

您可以使用延遲資料來判斷擷取程序處理交易的速度快慢。 當擷取程序連續執行時，這項資料便最有用。 如果擷取程序正按照排程執行，延遲可能會很高，因為在來源資料表上認可交易與按照排程時間執行的擷取程序之間存在延遲。

擷取程序效率的另一個重要量值是輸送量。 這是指每個工作階段期間每秒處理的平均命令數目。 若要判斷某個工作階段的輸送量，請將 command_count 欄位中的值除以 duration 欄位中的值。 下列查詢會針對最近幾個工作階段傳回平均輸送量：

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)
關於異動資料擷取 (SQL Server)
啟用和停用異動資料擷取 （SQL Server）
處理變更資料 (SQL Server)