Change Data Capture Performance Teil 1 - die Grundlagen

Mit diesem Artikel möchte ich eine kleine Serie über Change Data Capture beginnen, die als Vorbereitung für mein kommendes Whitepaper dienen soll.

Was Change Data Capture ist und wie man es grundsätzlich konfiguriert habe ich schon vor etwas über einem Jahr hier beschrieben. Zur RTM-Version hat sich daran wenig geändert. Der einzige wesentliche Unterschied ist, dass die Prozeduren zum aktivieren und deaktivieren jetzt etwas kürzere Namen haben, aus sys.sp_cdc_enable_db_change_data_capture wurde sys.sp_cdc_enable_db, aus sys.sp_cdc_enable_table_change_data_capture wurde sys.sp_cdc_enable_table, analog für die ...disable... Prozeduren. Ansonsten funktioniert alles so wie damals beschrieben. Zu einigen Erweiterungen komme ich evtl. später. CDC ist in der SQL Server Dokumentation gut beschrieben.

In diesem Artikel möchte ich jetzt darauf eingehen, wie der CDC Capture-Job funktioniert und welche Parameter es gibt, um sein Verhalten zu beeinflussen und die CDC Performance zu erhöhen.

CDC ermittelt die Änderungen an den überwachten Tabellen, indem es das Transaktionslog ausliest und die dort gefundenen Änderungen in die Änderungsverfolgungstabellen schreibt. Zum Auslesen des Transaktionslogs wurde allerdings keine neue Funktionalität implementiert, sondern es wird die entsprechende Funktionalität aus der transaktionalen Replikation verwendet. Basis ist die Funktion sp_replcmds, die die (für CDC oder Replikation markierten) Logeinträge zurückliefert.

Wenn für eine Datenbank CDC und transaktionale Replikation aktiviert ist, so wird der Log Reader Job der transaktionalen Replikation mit dem zugehörigen Parametersatz verwendet. Daher gilt alles, was ich ab hier schreibe nur für den Fall, das CDC, aber keine transaktionale Replikation aktiviert ist.

Der im SQL Server Agent für CDC erzeugte Job (normalerweise cdc.Datenbankname_capture benannt) enthält im Wesentlichen nur den Aufruf der Prozedur sys.sp_MScdc_capture_job ohne Parameter. Diese Prozedur ermittelt die notwendigen Parameter (mit Hilfe von sys.sp_cdc_get_job_parameters) und übergibt diese an die Prozedur sp_cdc_scan, die die eigentliche Arbeit durchführt und im Endeffekt das Log mit Hilfe von sp_replcmds scannt.

Für die Arbeit von sp_cdc_scan gibt es nun vier Parameter, die wesentlich die Performance von CDC beeinflussen.

Der erste Parameter ist continuous (Standardwert 1). Er legt fest, ob der Capture-Job dauerhaft laufen soll oder ob er sich nach einmaliger Ausführung beendet. Soll also das Log nicht kontinuierlich ausgelesen werden sondern nur z.B. in Wartungsfenstern so setzt man continuous auf 0 und stellt den Zeitplan des Agent-Jobs geeignet ein.

Die drei anderen Parameter definieren, wie oft wie viele Transaktionen aus dem Log gelesen werden.

Der Parameter maxtrans (Standardwert 500) legt fest, wie viele Transaktionen in einem Scan aus dem Log gelesen und in die Änderungsverfolgungstabellen geschrieben werden (dieses Schreiben erfolgt pro Scan in einer Transaktion). Der Parameter maxscans (Standardwert 10) legt fest, wie viele solcher Scans erfolgen bevor der Job beendet wird (continuous = 0) oder bevor eine Pause eingelegt wird (continuous = 1). Wie lang (in Sekunden, Standardwert 5) diese Pause ist legt der Parameter pollinginterval fest. Diese Pause immer dann eingelegt, wenn ein Scan keine zu lesenden Transaktionen mehr gefunden hat oder maxscans Scans durchgeführt wurden.

Grafisch sieht das so aus:

Mit Standardwerten ist das Verfahren also so:

1. Lese bis zu 500 Transaktionen aus dem Log und füge diese in einer Transaktion in die Änderungsverfolgungstabellen ein
2. Führe 1. bis zu 10 Mal aus oder bis ein Scan keine Transaktionen gelesen hat
3. Warte 5 Sekunden
4. Fahre mit 1. fort

Aus dieser Diskussion wird klar, das mit den Standardparametern von CDC immer weniger als 5000 Transaktionen in 5 Sekunden verarbeitet werden können. Übersteigt die Transaktionslast auf Tabellen, die mit CDC überwacht werden in einer Anwendung also 1000 Transaktionen pro Sekunde (real eher 500-800 kleine Transaktionen, da die Scans und die Verarbeitung ja auch Zeit brauchen) so kommt CDC nicht mehr hinterher und die Latenz steigt.

Unter Latenz versteht man dabei die Zeit vom Abschluss der Transaktion (genauer: vom Abschluss der letzten bei einem Scan gelesenen Transaktion) bis zur Verarbeitung dieser Transaktion durch CDC. Diese Latenz kann man mit der DMV sys.dm_cdc_log_scan_sessions ermitteln, doch dazu mehr in einem folgenden Artikel.

Will man mehr Transaktionen pro Sekunde mit CDC verarbeiten, so muss man die Parameter mit Hilfe von sys.sp_cdc_change_job ändern. Da diese Parameter wie beschrieben nur beim Start des Agent-Jobs ausgelesen werden muss der Capture-Job danach mit EXEC sp_cdc_stop_job @job_type = 'capture' gestoppt und mit EXEC sp_cdc_start_job @job_type = 'capture' wieder gestartet werden.

Für all das gibt es übrigens im Template Explorer vom Management Studio schöne Vorlagen.

Gruß,
Steffen