Datenbankprüfpunkte (SQL Server)

Gilt für:SQL ServerAzure SQL-Datenbank

Ein Prüfpunkt erstellt einen bekannten fehlerfreien Punkt, von dem aus die SQL Server-Datenbank-Engine Änderungen übernehmen kann, die während der Wiederherstellung nach einem unerwarteten Herunterfahren oder einem Absturz im Protokoll enthalten sind.

Übersicht

Aus Leistungsgründen führt die Datenbank-Engine Änderungen an Datenbankseiten im Arbeitsspeicher aus (im Puffercache) und schreibt diese Seiten nicht nach jeder Änderung auf den Datenträger. Vielmehr gibt die Datenbank-Engine in regelmäßigen Abständen einen Prüfpunkt für jede Datenbank aus. Ein Checkpoint schreibt die aktuell im Arbeitsspeicher geänderten Seiten (die sogenannten Dirty Pages) sowie Transaktionsprotokollinformationen aus dem Arbeitsspeicher auf den Datenträger und protokolliert diese Informationen auch im Transaktionsprotokoll.

Die Datenbank-Engine unterstützt mehrere Typen von Prüfpunkten. Dazu gehören automatische, indirekte, manuelle und interne Prüfpunkte. In der folgenden Tabelle werden die Prüfpunkttypen zusammengefasst.

Name Transact-SQL-Schnittstelle Beschreibung
Automatisch EXEC sp_configure „Wiederherstellungsintervall“, Sekunden Wird automatisch im Hintergrund ausgegeben, um das obere, mittels Serverkonfigurationsoption Wiederherstellungsintervall vorgeschlagene Zeitlimit zu erfüllen. Automatische Prüfpunkte werden vollständig ausgeführt. Automatische Prüfpunkte werden auf Basis der Anzahl von ausstehenden Schreibvorgängen gedrosselt. Zudem hängt die Drosselung auch davon ab, ob die Datenbank-Engine eine Erhöhung der Schreiblatenz auf über 50 Millisekunden erkennt.

Weitere Informationen finden Sie unter Konfigurieren der Serverkonfigurationsoption für das Wiederherstellungsintervall.
Indirekt ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES } Wird im Hintergrund ausgegeben, um eine benutzerdefinierte Zielwiederherstellungszeit für eine bestimmte Datenbank zu erfüllen. Ab SQL Server 2016 (13.x) beträgt der Standardwert 1 Minute. Der Standard für ältere Versionen ist 0 und gibt an, dass die Datenbank automatische Prüfpunkte verwendet, deren Frequenz von der Einstellung für das Wiederherstellungsintervall der Serverinstanz abhängt.

Weitere Informationen finden Sie unter Ändern der Zielwiederherstellungszeit einer Datenbank (SQL Server).
Manuell PRÜFPUNKT [ checkpoint_duration ] Wird ausgegeben, wenn Sie einen Transact-SQL CHECKPOINT-Befehl ausführen. Der manuelle Prüfpunkt tritt in der aktuellen Datenbank für die Verbindung auf. Standardmäßig werden manuelle Prüfpunkte vollständig ausgeführt. Die Drosselung funktioniert auf die gleiche Weise wie bei automatischen Checkpoints. Optional gibt der checkpoint_duration -Parameter die Anforderung an, welchen Zeitraum in Sekunden ein Prüfpunkt benötigen darf, bis er abgeschlossen ist.

Weitere Informationen finden Sie unter CHECKPOINT (Transact-SQL).
Intern Keine Wird von verschiedenen Servervorgängen wie Sicherung und Erstellung einer Datenbank-Momentaufnahme ausgegeben. So wird gewährleistet, dass Datenträgerabbilder dem aktuellen Protokollstatus entsprechen.

Die erweiterte Setupoption -k von SQL Server ermöglicht Datenbankadministrator*innen, das Prüfpunkt-E/A-Verhalten auf Basis des Durchsatzes des E/A-Subsystems für einige Prüfpunkttypen zu drosseln. Die Setupoption -k gilt für automatische Prüfpunkte sowie für andere nicht gedrosselte manuelle und interne Prüfpunkte.

Für automatische, manuelle und interne Prüfpunkte muss nur für Änderungen, die nach dem letzten Prüfpunkt erfolgt sind, während der Datenbankwiederherstellung ein Rollforward ausgeführt werden. Dadurch verringert sich die Zeit, die zur Wiederherstellung einer Datenbank erforderlich ist.

Wichtig

Lange andauernde, nicht festgeschriebene Transaktionen erhöhen die Wiederherstellungszeit für alle Checkpoint-Typen.

Interaktion der Optionen TARGET_RECOVERY_TIME und „Wiederherstellungsintervall“

In der folgenden Tabelle wird die Interaktion zwischen der serverweiten Einstellung sp_configure 'recovery interval' und der datenbankspezifischen ALTER DATABASE ... TARGET_RECOVERY_TIME-Einstellung zusammengefasst.

Angestrebte Wiederherstellungszeit 'Wiederherstellungsintervall' Verwendeter Prüfpunkttyp
0 0 Automatische Prüfpunkte, deren Zielwiederherstellungsintervall 1 Minute beträgt.
0 > 0 Automatische Prüfpunkte, deren Zielwiederherstellungsintervall anhand der benutzerdefinierten Einstellung der Option sp_configure 'recovery interval' angegeben wurde.
> 0 Nicht zutreffend Indirekte Prüfpunkte, deren Zielwiederherstellungszeit anhand der TARGET_RECOVERY_TIME-Einstellung definiert ist (in Sekunden ausgedrückt).

Automatische Prüfpunkte

Ein automatischer Prüfpunkt erfolgt jedes Mal, wenn die Anzahl der Protokolldatensätze die Anzahl erreicht, die die Datenbank-Engine schätzungsweise während des in der Serverkonfigurationsoption Wiederherstellungsintervall angegebenen Zeitraums verarbeiten kann. Weitere Informationen finden Sie unter Konfigurieren der Serverkonfigurationsoption für das Wiederherstellungsintervall.

In jeder Datenbank ohne benutzerdefinierte Zielwiederherstellungszeit generiert die Datenbank-Engine automatische Prüfpunkte. Die Frequenz richtet sich nach der erweiterten Serverkonfigurationsoption Wiederherstellungsintervall , mit der die maximale Zeit angegeben wird, die von einer bestimmten Serverinstanz zur Datenbankwiederherstellung während eines Systemneustarts benötigt werden sollte. Die Datenbank-Engine schätzt die maximale Anzahl von Protokolldatensätzen, die innerhalb des Wiederherstellungsintervalls verarbeitet werden können. Erreicht eine Datenbank, die automatische Prüfpunkte verwendet, diese maximale Anzahl von Protokolldatensätzen, gibt die Datenbank-Engine einen Prüfpunkt in der Datenbank aus.

Das Zeitintervall zwischen automatischen Prüfpunkten kann in hohem Maß variieren. Eine Datenbank mit hohem Transaktionsaufkommen hat häufiger Checkpoints als eine Datenbank, die hauptsächlich für schreibgeschützte Operationen verwendet wird. Im einfachen Wiederherstellungsmodell wird ein automatischer Prüfpunkt auch in die Warteschlange aufgenommen, wenn das Protokoll zu 70 Prozent gefüllt ist.

Unter dem einfachen Wiederherstellungsmodell wird der ungenutzte Abschnitt des Transaktionsprotokolls durch einen automatischen Prüfpunkt abgeschnitten, sofern nicht gewisse Faktoren die Protokollkürzung verzögern. Im Gegensatz dazu lösen automatische Prüfpunkte im vollständigen und im massenprotokollierten Wiederherstellungsmodell keine Protokollkürzung aus, sobald eine Protokollsicherungskette eingerichtet wurde. Weitere Informationen finden Sie unter Das Transaktionsprotokoll (SQL Server).

Nach einem Systemabsturz richtet sich die Zeitdauer, die für die Wiederherstellung einer bestimmten Datenbank erforderlich ist, in hohem Maß nach der Menge zufälliger E/A-Vorgänge, die zur Wiederherstellung der zum Zeitpunkt des Absturzes modifizierten Seiten erforderlich sind. Dies bedeutet, dass die Einstellung Wiederherstellungsintervall nicht zuverlässig ist. Sie ermöglicht keine genaue Bestimmung der Wiederherstellungsdauer. Zudem erhöht sich die allgemeine E/A-Aktivität für Daten erheblich und unvorhersehbar, wenn ein automatischer Prüfpunkt aktiv ist.

Auswirkungen des Wiederherstellungsintervalls auf die Wiederherstellungsleistung

Bei einem System zur Onlinetransaktionsverarbeitung (Online Transaction Processing, OLTP), das Transaktionen mit kurzer Ausführungszeit verwendet, ist das Wiederherstellungsintervall der wichtigste Faktor für die Bestimmung der Wiederherstellungszeit. Die Option Wiederherstellungsintervall wirkt sich jedoch nicht auf die Zeit aus, die für das Rückgängigmachen einer Transaktion mit langer Ausführungszeit erforderlich ist. Die Wiederherstellung einer Datenbank mit einer Transaktion mit langer Ausführungszeit kann deutlich länger dauern als mit der Einstellung Wiederherstellungsintervall angegeben.

Wenn beispielsweise eine Transaktion mit langer Ausführungszeit zwei Stunden für Updates benötigt hat, bevor die Serverinstanz deaktiviert wurde, dauert die tatsächliche Wiederherstellung erheblich länger als im Wert für das Wiederherstellungsintervall zur Wiederherstellung der Transaktion mit langer Ausführungszeit angegeben. Weitere Informationen zu den Auswirkungen einer Transaktion mit langer Ausführungszeit auf die Wiederherstellungsdauer finden Sie unter Das Transaktionsprotokoll (SQL Server). Weitere Informationen zum Wiederherstellungsprozess finden Sie unter Übersicht über Wiederherstellung und Recovery (SQL Server).

In der Regel gewährleisten die Standardwerte eine optimale Wiederherstellungsleistung. Durch Ändern des Wiederherstellungsintervalls kann sich jedoch die Leistung in folgenden Fällen verbessern:

  • Wenn die Wiederherstellung regelmäßig deutlich länger als 1 Minute dauert, obwohl lang andauernde Transaktionen nicht zurückgesetzt werden.

  • Sie stellen fest, dass häufige Prüfpunkte die Datenbankleistung beeinträchtigen.

Wenn Sie die Einstellung recovery interval erhöhen möchten, empfehlen wir eine schrittweise Erhöhung des entsprechenden Werts. Werten Sie zudem die Auswirkungen der jeweiligen stufenweisen Erhöhung auf die Wiederherstellungsleistung aus. Diese Vorgehensweise ist wichtig, da mit der Erhöhung des Werts für die Einstellung Wiederherstellungsintervall die Ausführung der Datenbankwiederherstellung entsprechend länger dauert. Ändern Sie beispielsweise den Wert für das Wiederherstellungsintervall in 10 Minuten, dauert die Wiederherstellung ungefähr 10-mal länger als bei einem Wiederherstellungsintervall-Wert von 1 Minute.

Indirekte Prüfpunkte

Indirekte Prüfpunkte, die in SQL Server 2012 (11.x) eingeführt wurden, stellen auf Datenbankebene eine konfigurierbare Alternative zu automatischen Prüfpunkten dar. Dies kann durch Festlegen der Datenbankkonfigurationsoption Wiederherstellungszeit für das Ziel konfiguriert werden. Weitere Informationen finden Sie unter Ändern der Zielwiederherstellungszeit einer Datenbank (SQL Server). Im Fall eines Systemabsturzes ermöglichen indirekte Prüfpunkte eine potenziell schnellere und besser vorhersehbare Wiederherstellungszeit als automatische Prüfpunkte.

Indirekte Prüfpunkte bieten folgende Vorteile:

  • Indirekte Prüfpunkte stellen sicher, dass die Anzahl der geänderten Seiten unter einem bestimmten Schwellenwert bleibt, sodass die Datenbankwiederherstellung innerhalb der angestrebten Wiederherstellungszeit abgeschlossen werden kann.

    Die Konfigurationsoption Wiederherstellungsintervall verwendet die Anzahl der Transaktionen zur Bestimmung der Wiederherstellungszeit, im Gegensatz zu indirekten Prüfpunkten, die die Anzahl geänderter Seiten verwenden. Wenn für eine Datenbank, bei der eine große Anzahl von DML-Vorgängen verarbeitet wird, indirekte Prüfpunkte aktiviert sind, kann der Hintergrundschreiber damit beginnen, geänderte Puffer aggressiv auf den Datenträger zu schreiben, um sicherzustellen, dass die für die Wiederherstellung erforderliche Zeit innerhalb der für die Datenbank festgelegten Zielzeit für die Wiederherstellung liegt. Dies kann auf bestimmten Systemen zusätzliche E/A-Aktivitäten verursachen, die zu einem Leistungsengpass beitragen können, wenn das Datenträgersubsystem über oder nahe dem E/A-Schwellenwert arbeitet.

  • Indirekte Prüfpunkte ermöglichen es Ihnen, die Wiederherstellungszeit der Datenbank zuverlässig zu steuern, indem Sie die Kosten zufälliger E/A-Vorgänge während REDO berücksichtigen. Dadurch überschreitet eine Serverinstanz nicht den oberen Grenzwert der Wiederherstellungszeiten für eine bestimmte Datenbank, sofern eine Transaktion mit langer Laufzeit keine übermäßig langen UNDO-Vorgänge verursacht.

  • Indirekte Prüfpunkte reduzieren prüfpunktbezogene E/A-Spitzen, indem modifizierte Seiten im Hintergrund kontinuierlich auf den Datenträger geschrieben werden.

Allerdings kann es bei einer Datenbank, die für indirekte Checkpoints konfiguriert ist, unter einer Online-Transaktionsarbeitslast zu Leistungseinbußen kommen. Dies liegt daran, dass der beim indirekten Prüfpunkt verwendete Hintergrundschreibprozess manchmal die gesamte Schreiblast einer Serverinstanz erhöht.

Wichtig

Indirekte Prüfpunkte sind das Standardverhalten für neue Datenbanken, die in SQL Server 2016 (13.x) erstellt wurden, einschließlich der model- und tempdb-Datenbank.

Datenbanken, die per direktem Upgrade aktualisiert oder aus einer vorherigen Version von SQL Server wiederhergestellt wurden, verwenden weiterhin das bisherige Verhalten für automatische Prüfpunkte, sofern sie nicht explizit für die Verwendung indirekter Prüfpunkte geändert wurden.

Verbesserte Skalierbarkeit indirekter Prüfpunkte

Vor SQL Server 2019 (15.x) kann es zu Fehlern aufgrund eines nicht reagierenden Schedulers kommen, wenn es eine Datenbank gibt, die eine große Anzahl geänderter Seiten erzeugt, z. B. tempdb. Mit SQL Server 2019 (15.x) wird die Skalierbarkeit für indirekte Prüfpunkte verbessert, um diese Fehler in Datenbanken zu vermeiden, die Workloads mit vielen UPDATE/INSERT-Vorgängen enthalten.

Interne Prüfpunkte

Interne Prüfpunkte werden von verschiedenen Serverkomponenten generiert, um so gewährleisten zu können, dass Datenträgerabbilder dem aktuellen Protokollstatus entsprechen. Interne Prüfpunkte werden als Reaktion auf die folgenden Ereignisse erstellt:

  • Datenbankdateien wurden mithilfe von ALTER DATABASE hinzugefügt oder entfernt.

  • Eine Datenbanksicherung wird erstellt.

  • Eine Datenbank-Momentaufnahme wird erstellt, entweder explizit oder intern für DBCC CHECKDB.

  • Eine Aktivität wird ausgeführt, für die das Herunterfahren einer Datenbank erforderlich ist. Beispielsweise besitzt AUTO_CLOSE den Status ON, und die letzte Benutzerverbindung mit der Datenbank wird geschlossen, oder eine Änderung einer Datenbankoption wird vorgenommen, für die ein Neustart der Datenbank erforderlich ist.

  • Eine SQL Server-Instanz wird angehalten, indem der SQL Server-Dienst (MSSQLSERVER) angehalten wird. Durch diese Aktionen wird ein Prüfpunkt in jeder Datenbank der SQL Server-Instanz erstellt.

  • Eine SQL Server-Failoverclusterinstanz (FCI) wird in den Offlinemodus versetzt.

Nächste Schritte

Siehe auch

  • Last updated on