Indirect Checkpoints mit SQL Server

Indirect Checkpoints wurden bereits mit SQL Server 2012 eingeführt. Mit SQL Server 2016 und vor Allem mit SQL Server 2017 wurden Indirect Checkpoints weiter optimiert und sind jetzt der Standard, falls nichts anderes angegeben wurde. Nach einer Migration zu SQL Server 2016 oder höher sollte man eine Umstellung von Automatic Checkpoints auf Indirect Checkpoints prüfen. Wie das geht schauen wir uns im Folgenden genauer an.

Automatic Checkpoints

Automatic Checkpoints werden in den Einstellungen der Instanz festgelegt. Hier kann man unter Database Settings das Recovery interval festlegen. Dies gibt in Minuten an, wie lange die Datenbanken benötigen um nach einem Crash wiederhergestellt und online gebracht zu werden. Der SQL Server setzt die Checkpoints dabei selbstständig um, so dass die Wiederherstellungszeit eingehalten werden kann. Bei einem Checkpoint werden dabei die Dirty Pages im Speicher in die Datendateien (*.mdf, *.ndf) geschrieben. Ein Recovery interval von 0 entspricht dabei einer Checkpoint Frequenz von etwa einem Checkpoint pro Minute bei aktiven Datenbanken und einer Recovery Zeit von unter einer Minute.

Automatic Checkpoints werden immer dann verwendet, wenn auf Datenbank-Ebene unter Target recovery time eine 0 eingetragen ist (siehe weiter unten).

Das Problem besteht dabei, dass der SQL Server für einen Checkpoint jeweils relativ viel Schreib-I/O produziert und dieser I/O nicht gleichmäßig vorkommt sondern eben immer zu den Zeiten an denen die Checkpoints stattfinden.

Indirect Checkpoints

Indirect Checkpoints werden durchgeführt, wenn in den Datenbankeinstellungen unter Options der Wert Target Recovery Time (Seconds) nicht auf 0 steht.

Seit SQL Server 2016 wird hier 60 Sekunden als Standard vorgegeben (Standardeinstellung in der model Datenbank). Die Einstellung selbst existiert bereits seit dem SQL Server 2012. Alte Datenbanken haben hier jedoch meist einen Wert von 0 stehen und verwenden daher Automatic Checkpoints.

Bei Indirect Checkpoints werden Dirty Pages im Speicher jetzt kontinuierlich in die Datendateien geschrieben, das I/O Verhalten hat also deutlich weniger Spitzen und ist ausgeglichener. Dieses Verhalten wird auch mit dem SQL Server 2017 weiter optimiert. In der Regel sollte man, falls man SQL Server 2016 oder höher im Einsatz hat, also Indirect Checkpoints bevorzugen und diese Einstellung gegebenenfalls anpassen.

Wer eine detaillierte Erklärung zum Unterschied von Automatic Checkpoints zu Indirect Checkpoints findet sich im Microsoft Blog unter:
https://blogs.msdn.microsoft.com/sqlcat/2016/08/03/changes-in-sql-server-2016-checkpoint-behavior/

Per T-SQL lässt sich leicht ermitteln, welche Target Recovery Time pro Datenbank eingetragen ist:

Auf einen Blick ist auch sichtbar, welche Datenbanken noch nicht auf Indirect Checkpoints umgestellt wurden.

Mit folgendem Script ist es möglich, alle Benutzerdatenbanken direkt auf Indirect Checkpoints umzustellen mit einer Target Recovery Time von 60 Sekunden: