管理和監視異動資料擷取

文章
05/03/2025

本主題說明如何管理和監視 SQL Server 和 Azure SQL 受控執行個體的異動資料擷取。

如需使用不同作業機制的 Azure SQL 資料庫，請參閱使用 Azure SQL 資料庫 CDC。

擷取作業

擷取作業是透過執行無參數的預存程序 sp_MScdc_capture_job 起始的。這個預存程序一開始會從 maxtrans 中擷取擷取作業之 maxscans、continuous、pollinginterval 和 msdb.dbo.cdc_jobs 的設定值。然後，這些設定值會當做參數傳遞給 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 以外的任何交易將在後續掃描中處理。

單次模式會用於受控的測試中，因為已知要處理的交易量，而且由於作業完成時能自動關閉，這具有一些優勢。不建議在生產環境中使用單次模式。這是因為此模式會仰賴作業排程管理掃描週期的執行頻率。

在一次模式中執行時，您可以使用下列計算，來計算擷取作業預期輸送量的上限 (以每秒交易數表示)：

(maxtrans * maxscans) / number of seconds between scans

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

如果您使用單次模式控制日誌掃描，記錄檔處理之間的秒數需由作業排程來控制。當需要這種行為時，以連續模式執行捕捉作業是重新安排記錄掃描的更佳方式。

連續模式和輪詢間隔

在連續模式中，擷取作業會要求 sp_cdc_scan 連續執行。這可讓儲存過程管理自己的等候迴圈，方法是不僅提供 maxtrans 和 maxscans，還提供記錄處理之間的秒數作為輪詢間隔。在連續模式中，擷取作業會持續運行，並在記錄檔掃描之間執行 WAITFOR。

備註

當輪詢間隔的值大於 0 時，用於一次性作業的輸送量相同上限也適用於連續模式下的作業操作。亦即，(maxtrans * maxscans) 除以非零的輪詢間隔，會針對擷取作業可以處理的平均交易數目設置上限。

作業自訂設定

您可以為擷取任務套用其他邏輯，以決定新的掃描是否立即開始，或是在啟動新的掃描前加入暫停，而不是依賴固定的輪詢間隔。該選擇可能僅僅基於一天中時間的考量，例如在尖峰活動時段強制執行非常長的休眠，甚至在一天結束時將輪詢間隔設為 0，以便於在日間處理結束後準備夜間的運行，這對於完成流程至關重要。您也可以監視擷取過程進度，以便判斷直至午夜認可的所有交易是否已經被掃描並儲存在變更資料表中。這會讓擷取作業結束，以便按照每日排程自動重新啟動。若要自訂行為，您可以將呼叫 sp_cdc_scan 的作業步驟取代為針對 sp_cdc_scan 撰寫包裝函式的使用者呼叫。

清除作業

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

清除作業的結構

異動資料擷取會使用保留性清除策略來管理變更資料表大小。在 SQL Server 與 Azure SQL 受控執行個體中，清除機制包含啟用第一個資料庫資料表時所建立的 SQL Server Agent Transact-SQL 作業。單一清除作業會處理所有資料庫變更資料表的清除，並且將相同的保留值套用至所有定義的擷取執行個體。

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

備註

使用最近交易之認可時間當做計算新下限標準之基礎的優點在於，它會在指定的時間內讓變更保留在變更資料表中。即使擷取過程落後於進度，這種情況也會發生。具有相同認可時間當做目前下限標準的所有項目會透過選擇具有實際下限標準之共用認可時間的最小 lsn，繼續在變更資料表內部表示。

執行清除時，所有擷取實例的低水位標記一開始是在單一交易中更新。然後，它會嘗試從變更資料表和 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。若要判斷具有空白掃描的會話，請執行下列查詢：

SQL

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。下列查詢會針對最近的工作階段傳回平均延遲：

SQL

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

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

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

SQL

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 管理檢視中收集取樣資料的資料收集器。

設定資料收集

啟用資料收集器並設定管理資料倉儲。如需詳細資訊，請參閱管理資料收集。

執行下列程式碼來建立異動資料擷取的自訂收集器。

SQL

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

在 SQL Server Management Studio 中，依序展開 [管理] 和 [資料收集]。以滑鼠右鍵按一下 [CDC 效能資料收集器]，然後按一下 [啟動資料收集組]。
在步驟 1 中設定的資料倉儲內，找出資料表 custom_snapshots.cdc_log_scan_data。這張表提供日誌掃描會話數據的歷史快照。這份資料表可用於分析經過一段時間的延遲、輸送量和其他效能量值。

指令碼升級模式

當您將累積更新或 Service Pack 套用到執行個體時，在重新啟動時執行個體可以進入指令碼升級模式。在此模式中，SQL Server 可能會執行步驟來分析和升級內部 CDC 資料表，這可能會導致重新建立像是擷取資料表上的索引等物件。根據所涉及的資料量，此步驟中可能需要一些時間，或對已啟用 CDC 的資料庫造成大量交易記錄使用量。

分享方式：

管理和監視異動資料擷取

擷取作業

擷取作業參數

`maxtrans` 參數

`maxscans` 參數

`continuous` 參數

單次模式

連續模式和輪詢間隔

作業自訂設定

清除作業

清除作業的結構

清理作業自訂

監視流程

識別含有空白結果集的工作階段

判斷延遲

使用資料收集器收集取樣資料

設定資料收集

指令碼升級模式

意見反映

更多資源

分享方式：

管理和監視異動資料擷取

擷取作業

擷取作業參數

maxtrans 參數

maxscans 參數

continuous 參數

單次模式

連續模式和輪詢間隔

作業自訂設定

清除作業

清除作業的結構

清理作業自訂

監視流程

識別含有空白結果集的工作階段

判斷延遲

使用資料收集器收集取樣資料

設定資料收集

指令碼升級模式

相關內容

意見反映

更多資源

`maxtrans` 參數

`maxscans` 參數

`continuous` 參數