Verwalten und Überwachen von Change Data Capture

Gilt für:SQL ServerVerwaltete Azure SQL-Instanz

Dieses Thema beschreibt, wie Sie die Erfassung von Change Data Capture für SQL Server und Azure SQL Managed Instance verwalten und überwachen.

Informationen zur Azure SQL-Datenbank, die einen anderen Auftragsmechanismus verwendet, finden Sie unter CDC mit Azure SQL-Datenbank.

Aufzeichnungsauftrag

Der Aufzeichnungsauftrag wird durch Ausführen der parameterlosen gespeicherten Prozedur sp_MScdc_capture_job initiiert. Diese gespeicherte Prozedur beginnt mit dem Extrahieren der konfigurierten Werte für maxtrans, maxscans, continuous und pollinginterval für den Aufzeichnungsauftrag aus msdb.dbo.cdc_jobs. Diese konfigurierten Werte werden dann als Parameter an die gespeicherte Prozedur sp_cdc_scan übergeben. Dies wird verwendet, um sp_replcmds aufzurufen und den Protokollscan auszuführen.

Parameter von Aufzeichnungsaufträgen

Um das Verhalten von Aufzeichnungsaufträgen zu verstehen, müssen Sie verstehen, wie die konfigurierbaren Parameter von sp_cdc_scan verwendet werden.

maxtrans Parameter

Der maxtrans-Parameter gibt die maximale Anzahl von Transaktionen an, die während eines einzelnen Scanzyklus des Protokolls verarbeitet werden kann. Wenn während des Scans die Anzahl der zu verarbeitenden Transaktionen diese Grenze erreicht, werden keine zusätzlichen Transaktionen in den aktuellen Scan eingeschlossen. Wenn ein Scanzyklus abgeschlossen ist, ist die Anzahl der verarbeiteten Transaktionen immer kleiner als oder gleich maxtrans.

maxscans Parameter

Der maxscans-Parameter gibt die maximale Anzahl von Scanzyklen an, die unternommen werden, um das Protokoll zu entleeren, bevor entweder zurückgekehrt wird (continuous = 0) oder eine WAITFOR-Anweisung ausgeführt wird (continuous = 1).

continuous Parameter

Der continuous-Parameter steuert, ob sp_cdc_scan die Steuerung entweder nach dem Leeren des Protokolls oder nach dem Ausführen der maximalen Anzahl von Scanzyklen (Einmalmodus) aufgibt. Er steuert auch, ob sp_cdc_scan weiter ausgeführt wird, bis er explizit beendet wird (kontinuierlicher Modus).

Einzelaufnahmemodus

Im Einmalmodus fordert der Aufzeichnungsauftrag sp_cdc_scan auf, bis zu maxscans Scans auszuführen, um zu versuchen, das Protokoll zu leeren und zurückzukehren. Alle Transaktionen zusätzlich zu maxtrans, die im Protokoll vorhanden sind, werden in späteren Scans verarbeitet.

Der Einmalmodus wird in gesteuerten Tests verwendet, bei denen die Anzahl der zu verarbeitenden Transaktionen bekannt ist und wo es vorteilhaft ist, dass der Auftrag nach seiner Beendigung automatisch geschlossen wird. Der One-Shot-Modus wird für den produktiven Einsatz nicht empfohlen. Das liegt daran, dass er den Auftragsplan verwendet, um zu verwalten, wie häufig der Scanzyklus ausgeführt wird.

Mithilfe der folgenden Berechnung können Sie bei der Ausführung im Einmalmodus eine Obergrenze des erwarteten Durchsatzes für den Aufzeichnungsauftrag in Transaktionen pro Sekunde berechnen:

(maxtrans * maxscans) / number of seconds between scans

Selbst wenn die zum Scannen des Protokolls und zum Befüllen der Änderungstabellen erforderliche Zeit nicht wesentlich größer als 0 wäre, könnte der durchschnittliche Durchsatz des Auftrags den Wert nicht überschreiten, der sich ergibt, wenn man die für einen einzelnen Scan maximal zulässige Anzahl von Transaktionen mit der maximal zulässigen Anzahl von Scans multipliziert und das Ergebnis durch die Anzahl der Sekunden zwischen den Protokollverarbeitungsvorgängen teilt.

Beim Verwenden des Einmalmodus zum Steuern von Protokollscanvorgängen müsste die Anzahl der Sekunden zwischen Protokollverarbeitungsvorgängen durch den Auftragszeitplan festgelegt werden. Wenn diese Art von Verhalten erwünscht ist, ist es besser, den Erfassungsauftrag im kontinuierlichen Modus auszuführen, um die Protokollprüfung neu zu planen.

Kontinuierlicher Modus und Abfrageintervall

Im kontinuierlichen Modus fordert der Aufzeichnungsauftrag an, dass sp_cdc_scan kontinuierlich ausgeführt wird. Dadurch kann die gespeicherte Prozedur ihre eigene Warteschleife verwalten, indem sie nicht nur für maxtrans und maxscans, sondern auch einen Wert für die Anzahl der Sekunden zwischen der Protokollverarbeitung (das Abfrageintervall) bereitstellt. Im fortlaufenden Modus bleibt der Aufzeichnungsauftrag aktiv und führt eine WAITFOR zwischen den Protokollüberprüfungen aus.

Auftragsanpassung erfassen

Für den Erfassungsauftrag können Sie zusätzliche Logik anwenden, um zu bestimmen, ob ein neuer Scan sofort beginnt oder ob stattdessen vor dem Start eines neuen Scans eine Wartezeit eingelegt wird, anstatt sich auf ein festes Abfrageintervall zu verlassen. Die Wahl könnte einfach von der Tageszeit abhängen, wobei während Zeiten hoher Aktivität möglicherweise sehr lange Schlafzeiten erzwungen werden und am Tagesende sogar auf ein Polling-Intervall von 0 umgestellt wird, wenn es wichtig ist, die Verarbeitung des Tages abzuschließen und die nächtlichen Läufe vorzubereiten. Der Fortschritt des Erfassungsprozesses konnte außerdem überwacht werden, um festzustellen, wann alle bis Mitternacht festgeschriebenen Transaktionen gescannt und in Änderungstabellen eingetragen worden waren. Dies beendet den Aufzeichnungsauftrag, der durch einen geplanten täglichen Neustart neu gestartet wird. Um das Verhalten anzupassen, können Sie den Auftragsschritt, der sp_cdc_scan aufruft, durch einen Aufruf eines vom Benutzer geschriebenen Wrappers für sp_cdc_scan ersetzen.

Bereinigungsauftrag

Dieser Abschnitt enthält Informationen zur Funktionsweise des Change Data Capture-Bereinigungsauftrags.

Struktur des Bereinigungsauftrags

Change Data Capture verwendet eine auf Aufbewahrung basierende Bereinigungsstrategie, um die Größe der Änderungstabellen zu verwalten. In SQL Server und Azure SQL Managed Instance besteht der Bereinigungsmechanismus aus einem SQL Server-Agent Transact-SQL-Job, der erstellt wird, wenn die erste Datenbanktabelle aktiviert wird. Ein einzelner Bereinigungsauftrag führt die Bereinigung für alle Änderungstabellen der Datenbank durch und wendet denselben Aufbewahrungswert auf alle definierten Erfassungsinstanzen an.

Der Bereinigungsauftrag wird durch Ausführen der parameterlosen gespeicherten Prozedur sp_MScdc_cleanup_job gestartet. Diese gespeicherte Prozedur beginnt mit dem Extrahieren der konfigurierten Beibehaltungs- und Schwellenwerte für den Cleanupauftrag aus msdb.dbo.cdc_jobs. Der Beibehaltungswert wird verwendet, um eine neue Untergrenzenmarkierung für die Änderungstabellen zu berechnen. Die angegebene Anzahl von Minuten wird vom maximalen tran_end_time-Wert aus der cdc.lsn_time_mapping-Tabelle subtrahiert, um die neue untere Grenzmarke zu erhalten, ausgedrückt als datetime-Wert. Anschließend wird die Tabelle CDC.lsn_time_mapping verwendet, um diesen datetime-Wert in einen entsprechenden lsn-Wert zu konvertieren. Wenn mehrere Einträge in der Tabelle dieselbe Commit-Zeit haben, wird der lsn, der dem Eintrag mit dem kleinsten lsn entspricht, als neue niedrige Wassermarke ausgewählt. Dieser lsn-Wert wird an sp_cdc_cleanup_change_tables übergeben, um Einträge in den Änderungstabellen aus den Datenbankänderungstabellen zu entfernen.

Wenn eine Bereinigung ausgeführt wird, wird die Untergrenzenmarkierung für alle Aufzeichnungsinstanzen zunächst in einer einzelnen Transaktion aktualisiert. Anschließend wird versucht, veraltete Einträge aus den Änderungstabellen und der Tabelle cdc.lsn_time_mapping zu entfernen. Der konfigurierbare Schwellenwert begrenzt, wie viele Einträge in jeder einzelnen Anweisung gelöscht werden. Das Fehlschlagen des Löschvorgangs für einzelne Tabellen führt nicht dazu, dass die Ausführung des Vorgangs nicht für die übrigen Tabellen versucht wird.

Anpassung des Bereinigungsauftrags

Für den Bereinigungsauftrag liegen die Möglichkeiten zur Anpassung in der Strategie, mit der bestimmt wird, welche Einträge in der Änderungstabelle zu verwerfen sind. Im übermittelten Bereinigungsauftrag wird nur eine zeitbasierte Strategie unterstützt. In diesem Fall wird die neue Untergrenzenmarkierung durch Subtrahieren der zulässigen Beibehaltungsdauer von der Commitzeit der letzten verarbeiteten Transaktion berechnet. Da die zugrunde liegenden Bereinigungsprozeduren auf lsn statt auf Zeit basieren, können beliebig viele Strategien verwendet werden, um den kleinsten lsn zu bestimmen, der in den Änderungstabellen beibehalten werden soll. Nur einige von diesen sind streng zeitbasiert. Es könnte z. B. Wissen über die Clients zum Bereitstellen einer Sicherung verwendet werden, wenn nachfolgende Prozesse, die Zugriff auf die Änderungstabellen erfordern, nicht ausgeführt werden können. Außerdem gilt: Obwohl die Standardstrategie dasselbe lsn zur Bereinigung aller Änderungstabellen der Datenbanken anwendet, kann die zugrunde liegende Bereinigungsprozedur auch auf der Ebene der Erfassungsinstanz aufgerufen werden.

Überwachung des Prozesses

Indem Sie den Change Data Capture-Prozess überwachen, können Sie ermitteln, ob Änderungen korrekt und mit einer akzeptablen Latenzzeit in die Änderungstabellen geschrieben werden. Das Überwachen kann Ihnen auch dabei helfen, jegliche Fehler zu identifizieren, die auftreten könnten. SQL Server enthält zwei dynamische Verwaltungsansichten, mit denen Sie die Datenerfassung von Änderungen überwachen können: sys.dm_cdc_log_scan_sessions und sys.dm_cdc_errors.

Sitzungen mit leeren Ergebnismengen identifizieren

Jede Zeile in sys.dm_cdc_log_scan_sessions stellt eine Protokoll-Scan-Sitzung (außer der Zeile mit einer ID von 0) dar. Eine Protokollscansitzung entspricht einer Ausführung von sp_cdc_scan. Während einer Sitzung kann der Scan entweder Änderungen oder ein leeres Ergebnis zurückgeben. Wenn das Resultset leer ist, wird die Spalte empty_scan_count in sys.dm_cdc_log_scan_sessions auf den Wert 1 gesetzt. Wenn es aufeinanderfolgende leere Ergebnismengen gibt, z. B. wenn der Capture-Job kontinuierlich ausgeführt wird, wird empty_scan_count in der letzten vorhandenen Zeile inkrementiert. Wenn sys.dm_cdc_log_scan_sessions z. B. bereits 10 Zeilen für Scans enthält, die Änderungen zurückgegeben haben, und fünf leere Ergebnisse aufeinander folgen, enthält die Anzeige 11 Zeilen. Die letzte Zeile verfügt in der Spalte empty_scan_count über einen Wert von 5. Führen Sie die folgende Abfrage aus, um Sitzungen zu ermitteln, die einen leeren Scan aufweisen:

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Bestimmung der Wartezeit

Die Verwaltungsansicht sys.dm_cdc_log_scan_sessions enthält eine Spalte, in der die Latenzzeit für die einzelnen Aufzeichnungssitzungen erfasst wird. Die Latenz wird als die Zeitspanne zwischen dem Commit einer Transaktion in einer Quelltabelle und dem Commit der zuletzt erfassten Transaktion in der Änderungstabelle definiert. Die Latenzzeitspalte wird nur für aktive Sitzungen aufgefüllt. Für Sitzungen, die in der Spalte empty_scan_count column einen höheren Wert als 0 enthalten, wird die Latenzzeitspalte auf 0 gesetzt. Die folgende Abfrage gibt die durchschnittliche Latenzzeit für die letzten Sitzungen zurück:

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

Sie können Latenzzeitdaten verwenden, um zu ermitteln, wie schnell bzw. langsam der Aufzeichnungsprozess Transaktionen verarbeitet. Diese Daten sind sehr hilfreich, wenn der Aufzeichnungsprozess kontinuierlich ausgeführt wird. Wenn der Aufzeichnungsprozess gemäß einem Zeitplan ausgeführt wird, kann die Latenzzeit u. U. lang sein. Dies liegt an der Verzögerung zwischen den Transaktionen, für die in der Quelltabelle ein Commit ausgeführt wird, und dem Aufzeichnungsprozess, der zum geplanten Zeitpunkt ausgeführt wird.

Eine andere wichtige Kennzahl für die Effizienz des Aufzeichnungsprozesses ist der Durchsatz. Dies ist die durchschnittliche Anzahl von Befehlen pro Sekunde, die während einer Sitzung verarbeitet werden. Um den Durchsatz einer Sitzung zu ermitteln, teilen Sie den Wert in der Spalte command_count durch den Wert in der Spalte mit der Dauer (duration). Die folgende Abfrage gibt den durchschnittlichen Durchsatz für die letzten Sitzungen zurück:

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

Verwenden Sie den Datensammler zum Erfassen von Sampling-Daten

Mithilfe des SQL Server-Datensammlers können Sie Momentaufnahmen von Daten aus allen Tabellen oder dynamischen Verwaltungssichten erfassen und ein Data Warehouse für die Leistung erstellen. Wenn Change Data Capture für eine Datenbank aktiviert ist, ist es nützlich, in regelmäßigen Abständen Momentaufnahmen der Ansicht sys.dm_cdc_log_scan_sessions und der Ansicht „sys.dm_cdc_errors“ für eine spätere Analyse zu erstellen. Die folgende Prozedur richtet einen Datensammler ein, der Datenstichproben aus der Verwaltungsansicht sys.dm_cdc_log_scan_sessions entnimmt.

Konfigurieren der Datensammlung

1. Aktivieren Sie den Datensammler, und konfigurieren Sie ein Management Data Warehouse. Weitere Informationen finden Sie unter Verwalten der Datensammlung.

2. Führen Sie den folgenden Code aus, um für Change Data Capture einen benutzerdefinierten Sammler zu erstellen.

   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. Erweitern Sie in SQL Server Management Studio die Verwaltung, und erweitern Sie dann die Datensammlung. Klicken Sie mit der rechten Maustaste auf den CDC-Leistungsdatensammler, und klicken Sie dann auf "Datensammlungssatz starten".

4. Greifen Sie in dem Data Warehouse, das Sie in Schritt 1 konfiguriert haben, auf die Tabelle custom_snapshots.cdc_log_scan_data zu. Diese Tabelle bietet eine historische Momentaufnahme der Daten aus Protokollscan-Sitzungen. Sie können diese Daten verwenden, um die Latenzzeit, den Durchsatz und andere Leistungskennzahlen in Abhängigkeit der Zeit zu analysieren.

Skriptaktualisierungsmodus

Wenn Sie kumulative Updates oder Service Packs auf eine Instanz anwenden, kann die Instanz beim erneuten Starten im Skriptupgrademodus gestartet werden. In diesem Modus führt SQL Server einen Schritt zum Analysieren und Aktualisieren von internen CDC-Tabellen aus, wodurch Objekte wie Indizes in Capture-Tabellen gegebenenfalls neu erstellt werden. Abhängig von der Menge der enthaltenen Daten kann dieser Schritt einige Zeit in Anspruch nehmen oder hohen Transaktionsprotokollverbrauch für aktivierte CDC-Datenbanken verursachen.

Zugehöriger Inhalt

• Change Data Capture (CDC) mit Azure SQL-Datenbank
• Nachverfolgen von Datenänderungen (SQL Server)
• Was ist Änderungsdatenerfassung (Change Data Capture, CDC)?
• Aktivieren und Deaktivieren der Änderungsdatenerfassung
• Arbeiten mit Änderungsdaten