Verwalten und Überwachen von Change Data Capture

  • Artikel

Gilt für:SQL ServerAzure SQL Managed Instance

In diesem Thema wird beschrieben, wie Sie die Änderungsdatenerfassung für SQL Server und Azure SQL verwaltete Instanz verwalten und überwachen.

Informationen zu Azure SQL-Datenbank, die einen anderen Auftragsmechanismus verwenden, 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, , continuousund pollinginterval für den Aufnahmeauftrag aus 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 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 der Überprüfung die Anzahl der zu verarbeitenden Transaktionen diesen Grenzwert erreicht, werden keine zusätzlichen Transaktionen in die aktuelle Überprüfung einbezogen. 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 der Scanzyklen an, die vor dem Zurückkehren (kontinuierlich = 0) oder dem Ausführen einer Waitfor-Anweisung (kontinuierlich = 1) auszuführen versucht werden, um das Protokoll zu leeren.

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).

One-Shot-Modus

Im Einmalmodus fordert der Aufzeichnungsauftrag sp_cdc_scan auf, bis zu maxtrans 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 Einmalmodus wird nicht für die Verwendung im Produktionsbereich 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 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.

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 gewünscht wird, ist das Ausführen des Aufnahmeauftrags im fortlaufenden Modus eine bessere Möglichkeit, die Protokollüberprüfung neu zu planen.

Kontinuierlicher Modus und das 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 auch einen Wert für die Anzahl von Sekunden zwischen der Protokollverarbeitung (dem Abrufintervall) bereitstellt. Im fortlaufenden Modus wird der Aufnahmeauftrag wieder Standard aktiv und führt eine WAITFOR Zwischenprotokollüberprüfung aus.

Hinweis

Wenn der Wert des Abrufintervalls größer als 0 (null) ist, gilt die gleiche Obergrenze für den Durchsatz des wiederkehrenden Einmalauftrags auch für den Auftragsvorgang im kontinuierlichen Modus. Das heißt, (maxtrans * maxscans) geteilt durch ein Abrufintervall ungleich 0 (null) legt eine Obergrenze für die durchschnittliche Anzahl der Transaktionen fest, die durch den Aufzeichnungsauftrag verarbeitet werden können.

Anpassung des Aufnahmeauftrags

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. Der Prozessfortschritt konnte auch überwacht werden, um festzustellen, wann alle transaktionen, die um Mitternacht zugesichert wurden, in Änderungstabellen gescannt und hinterlegt wurden. Dies beendet den Aufzeichnungsauftrag, der durch einen geplanten täglichen Neustart neu gestartet wird. Um das Verhalten anzupassen, können Sie den Auftragsschritt ersetzen, sp_cdc_scan der durch einen Aufruf eines vom Benutzer geschriebenen Wrappers aufgerufen sp_cdc_scanwird.

Bereinigungsauftrag

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

Struktur des sauber upauftrags

Das Change Data Capture-Feature verwendet eine beibehaltungsbasierte Cleanupstrategie zum Verwalten der Größe der Änderungstabellen. In SQL Server und Azure SQL verwaltete Instanz besteht der sauber up-Mechanismus 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 von dem maximalen tran_end_time-Wert aus der cdc.lsn_time_mapping-Tabelle subtrahiert, um die neue Untergrenzenmarkierung, angegeben als datetime-Wert, zu erhalten. 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.

Hinweis

Das Verwenden der Commitzeit der letzten Transaktion zum Berechnen der neuen Untergrenzenmarkierung hat den Vorteil, dass Änderungen in den Änderungstabellen für die angegebene Zeit erhalten bleiben. Dies geschieht sogar, wenn der Aufzeichnungsprozess zurückliegt. Alle Einträge, die dieselbe Commitzeit verwenden wie die aktuelle Untergrenzenmarkierung, werden weiterhin in den Änderungstabellen durch Wählen des kleinsten lsn dargestellt, der die gemeinsame Commitzeit für die aktuelle Untergrenzenmarkierung aufweist.

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.

Anpassen 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 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.

Identifizieren von Sitzungen mit leeren Resultsets

Jede Zeile in sys.dm_cdc_log_scan_sessions der Zeile 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 sys.dm_cdc_log_scan_sessions auf 1 festgelegt. 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 beispielsweise bereits 10 Zeilen für Scans enthalten sind, die Änderungen zurückgegeben haben und fünf leere Ergebnisse in einer Zeile enthalten sind, 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

Ermitteln der Latenz

Die Verwaltungsansicht sys.dm_cdc_log_scan_sessions enthält eine Spalte, in der die Latenzzeit für die einzelnen Aufzeichnungssitzungen erfasst 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

Mit dem SQL Server-Datensammler können Sie Momentaufnahme von Daten aus einer beliebigen Tabellen- oder dynamischen Verwaltungsansicht sammeln und ein Performance Data Warehouse erstellen. Wenn die Datenerfassung für eine Datenbank aktiviert ist, ist es nützlich, Momentaufnahme 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 übernehmen. 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 von Datensammlungen.

  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 CDC Performance Data Collector, 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.

Skriptupgrademodus

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