Datenbankprüfpunkte (SQL Server)

Artikel
10/13/2015

Dieses Thema bietet eine Übersicht über SQL Server Datenbankprüfpunkte. 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 über Prüfpunkte

Aus Leistungsgründen führt die Datenbank-Engine Änderungen an Datenbankseiten im Arbeitsspeicher im Puffercache durch 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 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.

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
Automatic	EXEC sp_configure *"`recovery interval`","`seconds`"*	Wird automatisch im Hintergrund ausgegeben, um die von der `recovery interval` Serverkonfigurationsoption vorgeschlagene Obergrenze zu erfüllen. Automatische Prüfpunkte werden vollständig ausgeführt. Automatische Prüfpunkte werden basierend auf der Anzahl der ausstehenden Schreibvorgänge gedrosselt und abhängig davon, ob die Datenbank-Engine eine Erhöhung der Schreiblatenz von über 20 Millisekunden erkennt. Weitere Informationen finden Sie unter Configure the recovery interval Server Configuration Option.
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. Die standardmäßige Zielwiederherstellungszeit ist 0. Sie löst die Verwendung der Heuristik für automatische Prüfpunkte auf der Datenbank aus. Wenn Sie ALTER DATABASE verwendet haben, um TARGET_RECOVERY_TIME auf >0 festzulegen, wird dieser Wert anstelle des für den Server instance angegebenen Wiederherstellungsintervall verwendet. Weitere Informationen finden Sie unter Ändern der Zielwiederherstellungszeit einer Datenbank (SQL Server)konfiguriert wird.
Manuell	CHECKPOINT [ 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. Das Drosseln erfolgt auf die gleiche Weise wie für automatische Prüfpunkte. 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.

Hinweis

Die -koption SQL Server erweiterte Einrichtung ermöglicht es einem Datenbankadministrator, das E/A-Verhalten des Prüfpunkts basierend auf dem Durchsatz 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

Lang andauernde Transaktionen ohne Commit erhöhen die Wiederherstellungszeit für alle Typen von Prüfpunkten.

Interaktion der Optionen "TTARGET_RECOVERY_TIME" und "Wiederherstellungsintervall"

Die folgende Tabelle fasst die Interaktion zwischen der serverweiten sp_configure"recovery interval- Einstellung und der datenbankspezifischen ALTER DATABASE ... TARGET_RECOVERY_TIME Einstellung.

Zielwiederherstellungszeit	'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 tritt jedes Mal auf, wenn die Anzahl der Protokolldatensätze die Von der Datenbank-Engine geschätzte Anzahl erreicht, die sie während der in der recovery interval Serverkonfigurationsoption angegebenen Zeit verarbeiten kann. In jeder Datenbank ohne benutzerdefinierte Zielwiederherstellungszeit generiert die Datenbank-Engine automatische Prüfpunkte. Die Häufigkeit der automatischen Prüfpunkte hängt von der recovery interval Erweiterten Serverkonfigurationsoption ab, die die maximale Zeit angibt, die ein bestimmter Server instance zum Wiederherstellen einer Datenbank während eines Systemneustarts verwenden soll. Die Datenbank-Engine schätzt die maximale Anzahl von Protokolldatensätzen, die innerhalb des Wiederherstellungsintervalls verarbeitet werden können. Wenn eine Datenbank, die automatische Prüfpunkte verwendet, diese maximale Anzahl von Protokolldatensätzen erreicht, gibt die Datenbank-Engine einen Prüfpunkt für die Datenbank aus. Das Zeitintervall zwischen automatischen Prüfpunkten kann stark variieren. 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.

Im Fall des einfachen Wiederherstellungsmodells 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 Fall des vollständigen und massenprotokollierten Wiederherstellungsmodells keine Protokollkürzung aus, sobald eine Protokollsicherungskette eingerichtet wurde. Weitere Informationen finden Sie unter Das Transaktionsprotokoll (SQL Server).

Nach einem Systemabsturz hängt die Zeitdauer, die für die Wiederherstellung einer bestimmten Datenbank erforderlich ist, stark vom zufälligen E/A-Volumen zur Wiederherstellung der zum Zeitpunkt des Absturzes modifizierten Seiten ab. Dies bedeutet, dass die recovery interval Einstellung unzuverlä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 eher unvorhersehbar, wenn ein automatischer Prüfpunkt ausgeführt wird.

Auswirkungen des Wiederherstellungsintervalls auf die Wiederherstellungsleistung

Bei einem OLTP-System (Online Transaction Processing), das kurze Transaktionen verwendet, ist der primäre Faktor, recovery interval der die Wiederherstellungszeit bestimmt. Die Option wirkt sich jedoch nicht auf die Zeit aus, recovery interval die zum Rückgängigmachen einer Transaktion mit langer Ausführungsdauer erforderlich ist. Die Wiederherstellung einer Datenbank mit einer Transaktion mit langer Ausführungsdauer kann viel länger dauern als in der recovery interval Option angegeben. Wenn z. B. eine Transaktion mit langer Ausführungszeit zwei Stunden dauerte, um Updates durchzuführen, bevor der Server instance deaktiviert wurde, dauert die tatsächliche Wiederherstellung erheblich länger als der recovery interval Wert, um die lange Transaktion wiederherzustellen. Weitere Informationen zu den Auswirkungen einer Transaktion mit langer Ausführungszeit auf die Wiederherstellungsdauer finden Sie unter Das Transaktionsprotokoll (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:

Die Wiederherstellung dauert routinemäßig bedeutend länger als 1 Minute, wenn kein Rollback für Transaktionen mit langer Laufzeit ausgeführt wird.
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. Dieser Ansatz ist wichtig, da die recovery interval Datenbankwiederherstellung mit zunehmender Einstellung um ein Vielfaches länger dauert. Wenn Sie z. B. 10 ändern recovery interval , dauert die Wiederherstellung etwa 10 Mal länger, als wenn recovery interval auf 0 festgelegt ist.

Indirekte Prüfpunkte

Indirekte Prüfpunkte, neu in SQL Server 2012, bieten eine konfigurierbare Alternative zu automatischen Prüfpunkten auf Datenbankebene. 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:

Im Fall einer Arbeitsauslastung für Onlinetransaktionen bei einer Datenbank, die für indirekte Prüfpunkte konfiguriert ist, kann eine Verringerung der Leistung auftreten. Indirekte Prüfpunkte stellen sicher, dass die Anzahl der modifizierten Seiten unter einem bestimmten Schwellenwert liegt, sodass die Datenbankwiederherstellung innerhalb der Zielwiederherstellungszeit abgeschlossen wird. 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. 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. 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 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. 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.
Indirekte Prüfpunkte reduzieren prüfpunktbezogene E/A-Spitzen, indem modifizierte Seiten im Hintergrund kontinuierlich auf den Datenträger geschrieben werden.

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

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 Antwort auf die folgenden Ereignisse erstellt:

Datenbankdateien wurden mit ALTER DATABASE hinzugefügt oder entfernt.
Eine vollständige Datenbanksicherung wird ausgeführt.
Eine Datenbank-Momentaufnahme wird erstellt, entweder explizit oder intern für DBCC CHECK.
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 instance von SQL Server wird durch Beenden des SQL Server -Diensts (MSSQLSERVER) beendet. Durch jede der Aktionen wird ein Prüfpunkt in jeder Datenbank der SQL Server-Instanz ausgelöst.
Eine SQL Server-Failoverclusterinstanz (FCI) wird in den Offlinemodus versetzt.