Datenbankprüfpunkte (SQL Server)Database Checkpoints (SQL Server)

Ein Prüfpunkt erstellt einen bekannten fehlerfreien Punkt, von dem aus SQL Server-DatenbankmodulSQL Server Database Engine Änderungen übernehmen kann, die im Protokoll während der Wiederherstellung nach einem unerwarteten Herunterfahren oder einem Absturz enthalten sind.A checkpoint creates a known good point from which the SQL Server-DatenbankmodulSQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

Übersicht Overview

Aus Leistungsgründen führt DatenbankmodulDatabase Engine Änderungen an Datenbankseiten im Arbeitsspeicher aus (im Puffercache) und schreibt diese Seiten nicht nach jeder Änderung auf den Datenträger.For performance reasons, the DatenbankmodulDatabase Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Vielmehr gibt DatenbankmodulDatabase Engine in regelmäßigen Abständen einen Prüfpunkt auf jeder Datenbank aus.Rather, the DatenbankmodulDatabase Engine periodically issues a checkpoint on each database. Ein Prüfpunkt schreibt die aktuellen, speicherintern geänderten Seiten (auch bekannt als modifizierte Seiten) sowie Transaktionsprotokollinformationen vom Arbeitsspeicher auf den Datenträger und erfasst auch Informationen zum Transaktionsprotokoll.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

DatenbankmodulDatabase Engine unterstützt mehrere Typen von Prüfpunkten. Dazu gehören "automatisch", "indirekt", "manuell" und "intern".The DatenbankmodulDatabase Engine supports several types of checkpoints: automatic, indirect, manual, and internal. In der folgenden Tabelle werden die Prüfpunkttypenzusammengefasst:The following table summarizes the types of checkpoints:

NameName Transact-SQLTransact-SQL -Schnittstelle Interface BeschreibungDescription
AutomaticAutomatic EXEC sp_configure 'Wiederherstellungsintervall','seconds'EXEC sp_configure 'recovery interval','seconds' Wird automatisch im Hintergrund ausgegeben, um das obere, mittels Serverkonfigurationsoption Wiederherstellungsintervall vorgeschlagene Zeitlimit zu erfüllen.Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatische Prüfpunkte werden vollständig ausgeführt.Automatic checkpoints run to completion. Automatische Prüfpunkte werden auf Basis der Anzahl an ausstehenden Schreibvorgängen gedrosselt. Zudem hängt die Drosselung auch davon ab, ob DatenbankmodulDatabase Engine eine Erhöhung der Schreiblatenz auf über 50 Millisekunden erkennt.Automatic checkpoints are throttled based on the number of outstanding writes and whether the DatenbankmodulDatabase Engine detects an increase in write latency above 50 milliseconds.

Weitere Informationen finden Sie unter Configure the recovery interval Server Configuration Option.For more information, see Configure the recovery interval Server Configuration Option.
IndirektIndirect ALTER DATABASE …ALTER DATABASE … SET TARGET_RECOVERY_TIME =Zielwiederherstellungszeit { SECONDS | MINUTES }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.Issued in the background to meet a user-specified target recovery time for a given database. Ab SQL Server 2016SQL Server 2016ist der Standardwert 1 Minute.Beginning with SQL Server 2016SQL Server 2016, the default value is 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.The default is 0 for older versions, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance.

Weitere Informationen finden Sie unter Ändern der Zielwiederherstellungszeit einer Datenbank (SQL Server).For more information, see Change the Target Recovery Time of a Database (SQL Server).
ManuellManual CHECKPOINT [ checkpoint_duration ]CHECKPOINT [ checkpoint_duration ] Wird ausgegeben, wenn Sie einen Transact-SQLTransact-SQL -CHECKPOINT-Befehl ausführen.Issued when you execute a Transact-SQLTransact-SQL CHECKPOINT command. Der manuelle Prüfpunkt tritt in der aktuellen Datenbank für die Verbindung auf.The manual checkpoint occurs in the current database for your connection. Standardmäßig werden manuelle Prüfpunkte vollständig ausgeführt.By default, manual checkpoints run to completion. Das Drosseln erfolgt auf die gleiche Weise wie für automatische Prüfpunkte.Throttling works the same way as for automatic checkpoints. Optional gibt der checkpoint_duration -Parameter die Anforderung an, welchen Zeitraum in Sekunden ein Prüfpunkt benötigen darf, bis er abgeschlossen ist.Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

Weitere Informationen finden Sie unter CHECKPOINT (Transact-SQL).For more information, see CHECKPOINT (Transact-SQL).
InternInternal Keine.None. Wird von verschiedenen Servervorgängen wie Sicherung und Erstellung einer Datenbank-Momentaufnahme ausgegeben. So wird gewährleistet, dass Datenträgerabbilder dem aktuellen Protokollstatus entsprechen.Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.
Hinweis

-k SQL ServerSQL Server ermöglicht Datenbankadministratoren, das Prüfpunkt-E/A-Verhalten auf Basis des Durchsatzes des E/A-Teilsystems für einige Typen von Prüfpunkten zu drosseln.The -k SQL ServerSQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. Die Setupoption -k gilt für automatische Prüfpunkte sowie für andere nicht gedrosselte manuelle und interne Prüfpunkte.The -k setup option applies to automatic checkpoints and any otherwise unthrottled manual and internal checkpoints.

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.For automatic, manual, and internal checkpoints, only modifications made after the latest checkpoint need to be rolled forward during database recovery. Dadurch verringert sich die Zeit, die zur Wiederherstellung einer Datenbank erforderlich ist.This reduces the time required to recover a database.

Wichtig

Lang andauernde Transaktionen ohne Commit erhöhen die Wiederherstellungszeit für alle Prüfpunkttypen.Long-running, uncommitted transactions increase recovery time for all checkpoint types.

Interaktion der Optionen "TTARGET_RECOVERY_TIME" und "Wiederherstellungsintervall" Interaction of the TARGET_RECOVERY_TIME and 'recovery interval' Options

In der folgenden Tabelle wird die Interaktion zwischen der serverweiten Einstellung sp_configure'recovery interval' und der datenbankspezifischen ALTER DATABASE …The following table summarizes the interaction between the server-wide sp_configure'recovery interval' setting and the database-specific ALTER DATABASE … TARGET_RECOVERY_TIME-Einstellung zusammengefasst.TARGET_RECOVERY_TIME setting.

ZielwiederherstellungszeitTARGET_RECOVERY_TIME 'Wiederherstellungsintervall''recovery interval' Verwendeter PrüfpunkttypType of Checkpoint Used
00 00 Automatische Prüfpunkte, deren Zielwiederherstellungsintervall 1 Minute beträgt.automatic checkpoints whose target recovery interval is 1 minute.
00 >0>0 Automatische Prüfpunkte, deren Zielwiederherstellungsintervall anhand der benutzerdefinierten Einstellung der Option sp_configure recovery interval angegeben wurde.Automatic checkpoints whose target recovery interval is specified by the user defined setting of the sp_configurerecovery interval option.
>0>0 Nicht verfügbar.Not applicable. Indirekte Prüfpunkte, deren Zielwiederherstellungszeit anhand der TARGET_RECOVERY_TIME-Einstellung definiert ist (in Sekunden ausgedrückt).Indirect checkpoints whose target recovery time is determined by the TARGET_RECOVERY_TIME setting, expressed in seconds.

Automatische Prüfpunkte Automatic checkpoints

Ein automatischer Prüfpunkt tritt auf, sobald die Anzahl von Protokolldatensätzen die Anzahl der DatenbankmodulDatabase Engine -Schätzungen erreicht, die während der Zeit verarbeitet werden können, die in der Serverkonfigurationsoption Wiederherstellungsintervall angegeben ist.An automatic checkpoint occurs each time the number of log records reaches the number the DatenbankmodulDatabase Engine estimates it can process during the time specified in the recovery interval server configuration option.

In jeder Datenbank ohne benutzerdefinierte Zielwiederherstellungszeit generiert DatenbankmodulDatabase Engine automatische Prüfpunkte.In every database without a user-defined target recovery time, the DatenbankmodulDatabase Engine generates automatic checkpoints. 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.The frequency depends on the recovery interval advanced server configuration option, which specifies the maximum time that a given server instance should use to recover a database during a system restart. DatenbankmodulDatabase Engine schätzt die maximale Anzahl an Protokolldatensätzen, die innerhalb des Wiederherstellungsintervalls verarbeitet werden können.The DatenbankmodulDatabase Engine estimates the maximum number of log records it can process within the recovery interval. Erreicht eine Datenbank, die automatische Prüfpunkte verwendet, diese maximale Anzahl an Protokolldatensätzen, gibt DatenbankmodulDatabase Engine einen Prüfpunkt in der Datenbank aus.When a database using automatic checkpoints reaches this maximum number of log records, the DatenbankmodulDatabase Engine issues an checkpoint on the database.

Das Zeitintervall zwischen automatischen Prüfpunkten kann in hohem Maß variieren.The time interval between automatic checkpoints can be highly variable. Bei einer Datenbank mit einer beträchtlichen Transaktionsarbeitsauslastung treten häufiger Prüfpunkte auf als bei einer Datenbank für hauptsächlich schreibgeschützte Vorgänge.A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. Im einfachen Wiederherstellungsmodell wird ein automatischer Prüfpunkt auch in die Warteschlange aufgenommen, wenn das Protokoll zu 70 Prozent gefüllt ist.Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

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.Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. Im Gegensatz dazu lösen automatische Prüfpunkte im vollständigen und im massenprotokollierten Wiederherstellungsmodell keine Protokollkürzung aus, sobald eine Protokollsicherungskette eingerichtet wurde.By contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation. Weitere Informationen finden Sie unter Das Transaktionsprotokoll (SQL Server).For more information, see The Transaction Log (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.After a system crash, the length of time required to recover a given database depends largely on the amount of random I/O needed to redo pages that were dirty at the time of the crash. Dies bedeutet, dass die Einstellung Wiederherstellungsintervall nicht zuverlässig ist.This means that the recovery interval setting is unreliable. Sie ermöglicht keine genaue Bestimmung der Wiederherstellungsdauer.It cannot determine an accurate recovery duration. Zudem erhöht sich die allgemeine E/A-Aktivität für Daten erheblich und eher unvorhersehbar, wenn ein automatischer Prüfpunkt ausgeführt wird.Furthermore, when an automatic checkpoint is in progress, the general I/O activity for data increases significantly and quite unpredictably.

Auswirkungen des Wiederherstellungsintervalls auf die Wiederherstellungsleistung Impact of recovery interval on recovery performance

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.For an online transaction processing (OLTP) system using short transactions, recovery interval is the primary factor determining recovery time. 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.However, the recovery interval option does not affect the time required to undo a long-running transaction. Die Wiederherstellung einer Datenbank mit einer Transaktion mit langer Ausführungszeit kann deutlich länger dauern als mit der Option Wiederherstellungsintervall angegeben.Recovery of a database with a long-running transaction can take much longer than the specified in the recovery interval option.

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.For example, if a long-running transaction took two hours to perform updates before the server instance became disabled, the actual recovery takes considerably longer than the recovery interval value to recover the long transaction. Weitere Informationen zu den Auswirkungen einer Transaktion mit langer Ausführungszeit auf die Wiederherstellungsdauer finden Sie unter Das Transaktionsprotokoll (SQL Server).For more information about the impact of a long running transaction on recovery time, see The Transaction Log (SQL Server).

In der Regel gewährleisten die Standardwerte eine optimale Wiederherstellungsleistung.Typically, the default values provides optimal recovery performance. Durch Ändern des Wiederherstellungsintervalls kann sich jedoch die Leistung in folgenden Fällen verbessern:However, changing the recovery interval might improve performance in the following circumstances:

  • Die Wiederherstellung dauert routinemäßig bedeutend länger als 1 Minute, wenn kein Rollback für Transaktionen mit langer Laufzeit ausgeführt wird.If recovery routinely takes significantly longer than 1 minute when long-running transactions are not being rolled back.

  • Sie stellen fest, dass häufige Prüfpunkte die Datenbankleistung beeinträchtigen.If you notice that frequent checkpoints are impairing performance on a database.

    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.If you decide to increase the recovery interval setting, we recommend increasing it gradually by small increments and evaluating the effect of each incremental increase on recovery performance. 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.This approach is important because as the recovery interval setting increases, database recovery takes that many times longer to complete. Ä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.For example, if you change recovery interval 10 minutes, recovery takes approximately 10 times longer to complete than when recovery interval is set to 1 minute.

Indirekte Prüfpunkte Indirect checkpoints

Indirekte Prüfpunkte (eingeführt in SQL Server 2012SQL Server 2012) stellen auf Datenbankebene eine konfigurierbare Alternative zu automatischen Prüfpunkten dar.Indirect checkpoints, introduced in SQL Server 2012SQL Server 2012, provide a configurable database-level alternative to automatic checkpoints. Im Fall eines Systemabsturzes ermöglichen indirekte Prüfpunkte eine potenziell schnellere und besser vorhersehbare Wiederherstellungszeit als automatische Prüfpunkte.In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. Indirekte Prüfpunkte bieten folgende Vorteile:Indirect checkpoints offer the following advantages:

  • Im Fall einer Arbeitsauslastung für Onlinetransaktionen bei einer Datenbank, die für indirekte Prüfpunkte konfiguriert ist, kann eine Verringerung der Leistung auftreten.An online transactional workload on a database configured for indirect checkpoints can experience performance degradation. Indirekte Prüfpunkte stellen sicher, dass die Anzahl der modifizierten Seiten unter einem bestimmten Schwellenwert liegt, sodass die Datenbankwiederherstellung innerhalb der Zielwiederherstellungszeit abgeschlossen wird.Indirect checkpoints ensure that the number of dirty pages are below a certain threshold so the database recovery completes within the target recovery time.

Die Konfigurationsoption „Wiederherstellungsintervall“ ermittelt die Wiederherstellungszeit über die Anzahl der Transaktionen. Im Gegensatz dazu greifen indirekte Prüfpunkte auf die Anzahl der modifizierten Seiten zurück.The recovery interval configuration option uses the number of transactions to determine the recovery time as opposed to indirect checkpoints which makes use of number of dirty pages. Wenn für eine Datenbank, die eine große Anzahl von DML-Vorgängen empfängt, indirekte Prüfpunkte aktiviert sind, können beim Schreiben im Hintergrund leere modifizierte Puffer aggressiv auf den Datenträger geleert werden. Dadurch wird sichergestellt, dass der Zeitaufwand für die Wiederherstellung innerhalb der Zielwiederherstellungszeit der Datenbank liegt.When indirect checkpoints are enabled on a database receiving a large number of DML operations, the background writer can start aggressively flushing dirty buffers to disk to ensure that the time required to perform recovery is within the target recovery time set of the database. 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.This can cause additional I/O activity on certain systems which can contribute to a performance bottleneck if the disk subsystem is operating above or nearing the I/O threshold.

  • Indirekte Prüfpunkte ermöglichen Ihnen eine zuverlässige Kontrolle der Datenbankwiederherstellungszeit, indem die Kosten für das zufällige E/A-Volumen während des REDO-Vorgangs berücksichtigt werden.Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during REDO. Dadurch überschreitet eine Serverinstanz nicht den Obergrenzwert der Wiederherstellungszeiten für eine bestimmte Datenbank, sofern eine Transaktion mit langer Laufzeit keine übermäßig langen UNDO-Vorgänge verursacht.This enables a server instance to stay within an upper-bound on recovery times for a given database (except when a long-running transaction causes excessive UNDO times).

  • Indirekte Prüfpunkte reduzieren prüfpunktbezogene E/A-Spitzen, indem modifizierte Seiten im Hintergrund kontinuierlich auf den Datenträger geschrieben werden.Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.

    Im Fall einer Arbeitsauslastung für Onlinetransaktionen bei einer Datenbank, die für indirekte Prüfpunkte konfiguriert ist, kann jedoch eine Verringerung der Leistung auftreten.However, an online transactional workload on a database configured for indirect checkpoints can experience performance degradation. Dies ist darauf zurückzuführen, dass der Hintergrundschreiber, der von indirekten Prüfpunkten verwendet wird, manchmal die Gesamtarbeitsauslastung zum Schreiben für eine Serverinstanz erhöht.This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

    Wichtig

    Indirekte Prüfpunkte sind das Standardverhalten für neue Datenbanken , die in SQL Server 2016SQL Server 2016 erstellt wurden.Indirect checkpoint is the default behavior for new databases created in SQL Server 2016SQL Server 2016. Datenbanken, die direkt aktualisiert oder aus einer früheren Version von SQL ServerSQL Server wiederhergestellt wurden, verwenden das frühere Verhalten der automatischen Prüfpunkte, sofern nicht explizit die Verwendung indirekter Prüfpunkte angegeben wurde.Databases which were upgraded in place or restored from a previous version of SQL ServerSQL Server will use the previous automatic checkpoint behavior unless explicitly altered to use indirect checkpoint.

Interne Prüfpunkte Internal checkpoints

Interne Prüfpunkte werden von verschiedenen Serverkomponenten generiert, um so gewährleisten zu können, dass Datenträgerabbilder dem aktuellen Protokollstatus entsprechen.Internal Checkpoints are generated by various server components to guarantee that disk images match the current state of the log. Interne Prüfpunkte werden als Antwort auf die folgenden Ereignisse erstellt:Internal checkpoint are generated in response to the following events:

  • Datenbankdateien wurden mit ALTER DATABASE hinzugefügt oder entfernt.Database files have been added or removed by using ALTER DATABASE.

  • Eine vollständige Datenbanksicherung wird ausgeführt.A database backup is taken.

  • Eine Datenbank-Momentaufnahme wird erstellt, entweder explizit oder intern für DBCC CHECK.A database snapshot is created, whether explicitly or internally for DBCC CHECK.

  • Eine Aktivität wird ausgeführt, für die das Herunterfahren einer Datenbank erforderlich ist.An activity requiring a database shutdown is performed. 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.For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

  • Eine SQL ServerSQL Server -Instanz wird beendet, indem der Dienst SQL ServerSQL Server (MSSQLSERVER) beendet wird.An instance of SQL ServerSQL Server is stopped by stopping the SQL ServerSQL Server (MSSQLSERVER) service . Durch jede der Aktionen wird ein Prüfpunkt in jeder Datenbank der Instanz von SQL ServerSQL Serverausgelöst.Either action causes a checkpoint in each database in the instance of SQL ServerSQL Server.

  • Aktivieren des Offlinemodus einer SQL ServerSQL Server -Failoverclusterinstanz (FCI).Bringing a SQL ServerSQL Server failover cluster instance (FCI) offline.

So ändern Sie das Wiederherstellungsintervall auf einer ServerinstanzTo change the recovery interval on a server instance

Siehe auchSee also