Verwalten und Überwachen der Änderungsdatenerfassung (SQL Server)

In diesem Thema wird beschrieben, wie Sie die Datenerfassung von Änderungen verwalten und überwachen.

Erfassungsauftrag

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 for the capture job from msdb.dbo.cdc_jobs. Diese konfigurierten Werte werden dann als Parameter an die gespeicherte Prozedur sp_cdc_scan übergeben. Diese werden verwendet, um sp_replcmds zum Ausführen des Protokollscans aufzurufen.

Parameter des Aufnahmeauftrags

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 Parameter maxtrans gibt die maximale Anzahl von Transaktionen an, die in einem einzelnen Scanzyklus des Protokolls verarbeitet werden können. Wenn während der Überprüfung die Anzahl der zu verarbeitenden Transaktionen diesen Grenzwert erreicht, werden keine zusätzlichen Transaktionen in die aktuelle Überprüfung einbezogen. Nach Abschluss eines Scanzyklus ist die Anzahl der verarbeiteten Transaktionen immer kleiner oder gleich maxtrans.

maxscans-Parameter

Der maxscans-Parameter legt die maximale Anzahl von Scanzyklen fest, die versucht werden, das Protokoll zu leeren, bevor entweder das Protokoll zurückgegeben wird (fortlaufend = 0) oder ein WAITFOR (fortlaufend = 1) ausgeführt wird.

fortlaufender Parameter

Der fortlaufende Parameter steuert, ob sp_cdc_scan die Steuerung nach dem Entladen des Protokolls oder nach der Ausführung der maximalen Anzahl von Scanzyklen (Einzelmodus) freigibt. Es wird auch kontrolliert, ob sp_cdc_scan bis zum expliziten Beenden im kontinuierlichen Modus weiterläuft.

Einzelaufnahme-Modus

Im Einzelbildmodus fordert die Anfrage des Aufnahmeauftrags sp_cdc_scan, bis zu maxtrans Scans durchzuführen, um das Protokoll zu leeren und zurückzukehren. Alle Transaktionen zusätzlich zu maxtrans , die im Protokoll vorhanden sind, werden in späteren Scans verarbeitet.

Einzelmodus wird in kontrollierten Tests verwendet, bei denen das Volumen der zu verarbeitenden Transaktionen bekannt ist. Ein Vorteil dabei ist, dass die Aufgabe automatisch abgeschlossen wird, wenn sie beendet ist. Der Einzelbildmodus wird nicht für die Produktionsverwendung empfohlen. Dies liegt daran, dass der Auftragszeitplan verwendet wird, um zu verwalten, wie häufig der Scanzyklus ausgeführt wird.

Wenn Sie im Einzeldurchlauf-Modus ausgeführt werden, können Sie mithilfe der folgenden Berechnung eine obere Durchsatzgrenze für den erwarteten Durchsatz des Aufnahmeauftrags berechnen, ausgedrückt in Transaktionen pro Sekunde:

(maxtrans * maxscans) / number of seconds between scans

Selbst wenn die Zeit, die zum Durchsuchen des Protokolls und zum Füllen der Änderungstabellen nicht erheblich von 0 abweicht, kann der durchschnittliche Durchsatz des Auftrags nicht den Wert überschreiten, der durch Dividieren der Höchstzahl der erlaubten Transaktionen für einen einzelnen Scan multipliziert mit der Höchstzahl der erlaubten Scans durch die Anzahl der Sekunden, die die Protokollverarbeitungsvorgänge trennen, bestimmt wird.

Sollte der Einzelaufnahmemodus verwendet werden, um die Protokollscans zu steuern, müsste die Anzahl der Sekunden zwischen der Protokollverarbeitung durch den Auftragszeitplan festgelegt werden. Wenn dieses Verhalten gewünscht ist, ist das Ausführen des Erfassungsauftrags im fortlaufenden Modus eine bessere Möglichkeit, die Neuplanung des Protokollscans zu verwalten.

Fortlaufender Modus und der Abrufintervall

Im kontinuierlichen Modus wird durch den Aufzeichnungsauftrag das kontinuierliche Ausführen von sp_cdc_scan angefordert. Auf diese Weise kann die gespeicherte Prozedur eine eigene Warteschleife verwalten, indem sie nicht nur für Maxtrans- und Maxscans, sondern auch einen Wert für die Anzahl von Sekunden zwischen der Protokollverarbeitung (dem Abrufintervall) bereitstellt. Wird in diesem Modus ausgeführt, bleibt der Aufnahmeauftrag aktiv und führt eine WAITFOR Zwischenprotokollüberprüfung aus.

Anpassung des Aufzeichnungsauftrags

Sie können für den Aufzeichnungsauftrag statt eines festen Abrufintervalls zusätzliche Logik anwenden, um zu bestimmen, ob sofort ein neuer Scan beginnen soll oder ob vor einem neuen Scan ein Ruhezustand erzwungen wird. Die Wahl kann einfach auf der Uhrzeit basieren. Z. B. können sehr lange Ruhezustände während Spitzenzeiten erzwungen werden. Es sind auch Abrufintervalle von 0 zum Tagesende möglich, wenn die Verarbeitungsvorgänge des Tages abgeschlossen und die Vorgänge der Nacht vorbereitet werden müssen. Die Überwachung des Fortschritts des Erfassungsprozesses kann auch genutzt werden, um zu ermitteln, wann alle Transaktionen, die bis Mitternacht verarbeitet wurden, gescannt und in Änderungstabellen hinterlegt wurden. Dies beendet den Aufzeichnungsauftrag, der durch einen geplanten täglichen Neustart neu gestartet wird. Durch das Ersetzen des übermittelten Auftragsschritts sp_cdc_scan durch einen Aufruf eines benutzerdefinierten Wrappers für sp_cdc_scan kann ein stark angepasstes Verhalten mit wenig zusätzlichem Aufwand erzielt werden.

Bereinigungsauftrag

Dieser Abschnitt enthält Informationen darüber, wie der Change Data Capture-Cleanupauftrag funktioniert.

Struktur des Bereinigungsauftrags

Change Data Capture verwendet eine aufbewahrungsbasierte Bereinigungsstrategie zur Verwaltung der Größe der Änderungstabelle. Der Bereinigungsmechanismus besteht aus einem SQL Server-Agent Transact-SQL Auftrag, der erstellt wird, wenn die erste Datenbanktabelle aktiviert ist. Ein einzelner Cleanupauftrag verarbeitet das Cleanup für alle Datenbankänderungstabellen und wendet denselben Beibehaltungswert auf alle definierten Aufzeichnungsinstanzen an.

Der Cleanupauftrag wird durch Ausführen der parameterlosen gespeicherten Prozedur sp_MScdc_cleanup_job initiiert. 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 der cdc.lsn_time_mapping Tabelle subtrahiert, um den neuen Niedrigwasserstand zu erhalten, der als Datetime-Wert ausgedrückt wird. Anschließend wird die Tabelle CDC.lsn_time_mapping verwendet, um diesen datetime-Wert in einen entsprechenden lsn-Wert zu konvertieren. Wenn mehrere Werte in der Tabelle dieselbe Commitzeit verwenden, wird der lsn, der dem Eintrag mit dem kleinsten lsn entspricht, als neue Untergrenzenmarkierung bestimmt. Dieser lsn-Wert wird an sp_cdc_cleanup_change_tables übergeben, um Einträge in den Änderungstabellen aus den Datenbankänderungstabellen zu entfernen.

Wenn ein Cleanup 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

Die Anpassungsmöglichkeiten für den Cleanupauftrag bestehen in der Strategie, die verwendet wird, um zu bestimmen, welche Einträge in der Änderungstabelle verworfen werden sollen. Im übermittelten Cleanupauftrag 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 Cleanupprozeduren auf lsn statt auf Zeit basieren, kann eine beliebige Anzahl von Strategien verwendet werden, um den kleinsten lsn zu bestimmen, der in den Änderungstabellen bewahrt 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. Obwohl die Standardstrategie denselben lsn für das Cleanup aller Änderungstabellen der Datenbank verwendet, kann auch die zugrunde liegende Cleanupprozedur für das Cleanup auf Aufzeichnungsinstanzebene aufgerufen werden.

Überwachen des Änderungsdatenerfassungsprozesses

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.

Identifizieren von Sitzungen mit leeren Ergebnislisten

Jede Zeile in sys.dm_cdc_log_scan_sessions stellt eine Protokollscansitzung dar (mit Ausnahme der Zeile mit einer ID von 0). 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 1 gesetzt. Folgen noch weitere leere Resultsets, z. B. wenn der Aufzeichnungsauftrag dauerhaft 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 es fünf leere Ergebnisse in einer Zeile gibt, enthält die Ansicht 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

Latenz ermitteln

Die sys.dm_cdc_log_scan_sessions-Verwaltungsansicht enthält eine Spalte, in der die Latenz für jede Aufnahmesitzung aufgezeichnet wird. Die Latenzzeit ist als die Zeitspanne zwischen dem Ausführen des Commit für eine Transaktion in einer Quelltabelle und dem Ausführen des Commit für die letzte aufgezeichnete 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 des Datensammlers zum Sammeln von Samplingdaten

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 die Änderungsdatenerfassung in einer Datenbank aktiviert ist, ist es nützlich, Momentaufnahmen der sys.dm_cdc_log_scan_sessions Ansicht und der sys.dm_cdc_errors Ansicht in regelmäßigen Abständen für spätere Analysen zu erstellen. Im folgenden Verfahren wird ein Datensammler zum Sammeln von Beispieldaten aus der sys.dm_cdc_log_scan_sessions-Verwaltungsansicht eingerichtet.

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 stellt eine Verlaufs-Momentaufnahme der Daten von Protokollscansitzungen bereit. Sie können diese Daten verwenden, um die Latenzzeit, den Durchsatz und andere Leistungskennzahlen in Abhängigkeit der Zeit zu analysieren.

Siehe auch

Nachverfolgen von Datenänderungen (SQL Server)
Über Change Data Capture (SQL Server)
Aktivieren und Deaktivieren von Change Data Capture (SQL Server)
Arbeiten mit Änderungsdaten (SQL Server)