SQL – Fragen und AntwortenGroße Transaktionsprotokolle, Verwendungsfälle für repair und mehr

Paul S. Randal

F: Ich habe in Verbindung mit Sicherungen ein seltsames Verhalten bemerkt, von dem ich hoffe, dass Sie es mir erklären können. Wir sichern gelegentlich unsere 62 GB große Produktionsdatenbank, um die von unseren Anwendungsentwicklern verwendeten Daten zu aktualisieren. Dabei löschen wir stets die alte Kopie, bevor wir die neue wiederherstellen. Die wiederhergestellte Kopie hat die gleiche Größe wie die Produktionsdatenbank, und die Daten sehen identisch aus, der Wiederherstellungsvorgang dauert jedoch viel länger als der Sicherungsvorgang. Woran liegt das? Warum dauert die Wiederherstellung so viel länger als die Sicherung?

A: Die beschriebene Situation hat nichts Seltsames an sich. In Abhängigkeit von den Umständen ist dieses Verhalten im Allgemeinen zu erwarten. Der Unterschied zwischen der für eine Sicherung und der für eine Wiederherstellung benötigten Zeit beruht auf den Schritten, die bei den einzelnen Prozessen durchgeführt werden müssen.

Das Sichern einer Datenbank umfasst zwei Schritte. Im Grunde werden nur E/A-Lesevorgänge in der Datenbank und E/A-Schreibvorgänge auf dem Sicherungsgerät durchgeführt:

Sicherungsschritt 1 Alle zugeordneten Daten in den Datendateien lesen und auf das Sicherungsgerät schreiben.

Sicherungsschritt 2 Ein bestimmtes Transaktionsprotokoll lesen und auf die Sicherungsgeräte schreiben.

Die genaue Menge der erforderlichen Transaktionsprotokolleinträge kann stark variieren. Dabei handelt es sich um die Menge, die erforderlich ist, um die Datenbank bis zu einem konsistenten Zeitpunkt wiederherstellen zu können. (Eine ausführlichere Erklärung hierzu ist in meinem Blogbeitrag unter sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx zu finden.)

Die Wiederherstellung einer Datenbank kann hingegen bis zu vier Schritte umfassen. Zudem sind die durchzuführenden Aufgaben komplizierter als reine E/A-Lese- und -Schreibvorgänge:

Wiederherstellungsschritt 1 Wenn die Datenbankdateien nicht vorhanden sind, diese erstellen.

Wiederherstellungsschritt 2 Alle Daten und das Transaktionsprotokoll der Sicherung lesen und in die betreffenden Datenbankdateien schreiben.

Wiederherstellungsschritt 3 Die Rollforwardphase der Wiederherstellung für das Transaktionsprotokoll ausführen.

Wiederherstellungsschritt 4 Die Rollbackphase der Wiederherstellung für das Transaktionsprotokoll ausführen.

Die erforderliche Gesamtzeit für beide Sicherungsschritte sollte ungefähr der für Wiederherstellungsschritt 2 benötigten Zeit entsprechen (bei ähnlicher Hardware und einem Server ohne Benutzeraktivität). Schritt 1 der Wiederherstellung kann viel Zeit in Anspruch nehmen, wenn die Datendateien groß sind und mit Null initialisiert werden müssen (das Verhalten in SQL Server 2000 und das Standardverhalten in SQL Server 2005).

Um dies zu verhindern, dürfen Sie vor dem Starten der Wiederherstellung die vorhandenen Dateien nicht löschen. Alternativ können Sie die sofortige Initialisierung aktivieren, damit die Dateien sehr schnell erstellt werden (weitere Informationen hierzu finden Sie unter msdn.microsoft.com/library/ms175935.aspx).

In Wiederherstellungsschritt 3 und 4 wird die Wiederherstellung für die Datenbank ausgeführt, um sie im Hinblick auf die Transaktionen in einen konsistenten Zustand zu überführen. Dabei handelt es sich um den gleichen Prozess, den eine Datenbank bei der Wiederherstellung nach einem Systemabsturz durchläuft. Die Dauer dieser Wiederherstellung hängt von der Menge der zu verarbeitenden Transaktionsprotokolleinträge ab. Wenn beispielsweise eine Transaktion mit sehr langer Laufzeit aktiv war, als die Sicherung erstellt wurde, befindet sich das gesamte Transaktionsprotokoll für diese Transaktion in der Sicherung und muss zurückgesetzt werden.

F: Ich versuche, mich in Bezug auf die Bereitstellung einer redundanten Kopie unserer Produktionsdatenbank zwischen Protokollversand und Datenbankspiegelung zu entscheiden. Ich bin hinsichtlich der Menge der Transaktionsprotokolleinträge besorgt, die zwischen den Servern versandt werden müssen, insbesondere für die Vorgänge zur Indexneuerstellung, die wir jede Nacht durchführen. Ich habe gehört, dass bei der Spiegelung anstelle des Transaktionsprotokolls die tatsächlichen Neuerstellungsbefehle gesendet werden und die Neuerstellungsvorgänge in der Spiegeldatenbank durchgeführt werden. Trifft dies zu? Dadurch sollte die Spiegelung eine noch bessere Lösung als der Protokollversand mit dem Modell der massenprotokollierten Wiederherstellung sein, oder?

A: Was Sie gehört haben, entspricht nicht der Wahrheit. Bei der Datenbankspiegelung werden die tatsächlichen Transaktionsprotokoll-Datensätze aus der Prinzipaldatenbank an den Spiegelserver gesendet, wo sie in der Spiegeldatenbank „wiedergegeben“ werden. Für eine gespiegelte Datenbank findet keinerlei Übersetzung oder Filterung statt, und T-SQL-Befehle werden nicht abgefangen.

Die Datenbankspiegelung unterstützt nur das vollständige Wiederherstellungsmodell, was bedeutet, dass ein Vorgang zur Indexneuerstellung stets vollständig protokolliert wird. Je nach der Größe der betroffenen Indizes kann dies die Generierung einer Vielzahl von Transaktionsprotokolleinträgen und somit eine große Protokolldatei für die Prinzipaldatenbank und eine beträchtliche Netzwerkbandbreite für das Senden der Protokolldatensätze zur Spiegeldatenbank bedeuten.

Sie können sich die Datenbankspiegelung als Echtzeitprotokollversand vorstellen (tatsächlich wurde in den frühen Entwicklungsphasen von SQL Server 2005 diese Bezeichnung für das Feature verwendet). Beim Protokollversand werden Sicherungen des Transaktionsprotokolls der primären Datenbank regelmäßig an den sekundären Server versandt und in der sekundären Datenbank wiederhergestellt.

Der Protokollversand unterstützt das vollständige Wiederherstellungsmodell und das Modell der massenprotokollierten Wiederherstellung. Für eine Indexneuerstellung in einer Datenbank mit Protokollversand und dem vollständigen Wiederherstellungsmodell wird dieselbe Menge von Transaktionsprotokolleinträgen wie für eine gespiegelte Datenbank generiert. Bei dem Datenbankszenario mit Protokollversand werden die Daten jedoch nicht kontinuierlich, sondern in einer Protokollsicherung (oder eine Reihe von Protokollsicherungen) an die redundante Datenbank gesendet.

Wenn das Modell der massenprotokollierten Wiederherstellung während der Indexneuerstellung in der Datenbank mit Protokollversand verwendet wird, wird nur eine minimale Menge von Transaktionsprotokolleinträgen generiert. Die nächste Transaktionsprotokollsicherung enthält dann jedoch auch alle Datendateiblöcke, die durch die Indexneuerstellung mit minimaler Protokollierung geändert wurden. Dies bedeutet, dass die Protokollsicherungen, die die Indexneuerstellung beim Modell der massenprotokollierten Wiederherstellung abdecken, nahezu dieselbe Größe aufweisen wie die Protokollsicherungen, die die Indexneuerstellung beim vollständigen Wiederherstellungsmodell abdecken.

Daher ist die Menge der Informationen, die an die redundante Datenbank gesendet werden müssen, für eine Indexneuerstellung in einer gespiegelten Datenbank und in einer Datenbank mit Protokollversand nahezu identisch. Der wirkliche Unterschied liegt darin, wie die Informationen gesendet werden – kontinuierlich oder in Batches.

Bei der Wahl zwischen diesen beiden Ansätzen sind zahlreiche weitere Faktoren zu berücksichtigen (die in nur einer Ausgabe von „SQL – Fragen und Antworten“ nicht alle erläutert werden können). Sie sollten feststellen, inwieweit alle diese Faktoren zu Ihren Anforderungen passen (beispielsweise akzeptable Datenverlustgrenze und zulässige Ausfallzeit), bevor Sie eine Entscheidung treffen.

F: Ich führe SQL Server 2005 aus, und eine meiner Datenbanken weist ein Transaktionsprotokoll auf, dessen Größe ständig zunimmt. Die Datenbank befindet sich im vollständigen Wiederherstellungsmodus, und ich führe Transaktionsprotokollsicherungen durch. Ich war davon ausgegangen, dass dies die Größenzunahme des Transaktionsprotokolls verhindern sollte. Wo genau liegt der Fehler?

A: Sie haben recht, dass die Durchführung von Transaktionsprotokollsicherungen im vollständigen Wiederherstellungsmodus von entscheidender Bedeutung ist. Es gibt jedoch weitere Faktoren, die dazu beitragen können, dass die Größe des Transaktionsprotokolls zunimmt. Es kommt vor allem darauf an, aus welchem Grund das Transaktionsprotokoll benötigt wird (oder aktiv sein muss). Weitere allgemeine Faktoren (außer fehlenden Transaktionsprotokollsicherungen), die Ihr Problem verursachen können, sind Replikation, Datenbankspiegelung und eine aktive Transaktion.

Bei der Replikation werden Ihre Transaktionsprotokoll-Datensätze asynchron gelesen und dann die zu replizierenden Transaktionen in eine separate Verteilungsdatenbank geladen. Alle Transaktionsprotokoll-Datensätze, die noch nicht vom Task „Replikationstransaktionsprotokoll-Leser“ gelesen wurden, können nicht freigegeben werden. Wenn Ihre Arbeitsauslastung zahlreiche Transaktionsprotokoll-Datensätze generiert und Sie ein großes Intervall für die Ausführung des Replikationstransaktionsprotokoll-Lesers festgelegt haben, kann sich eine große Menge von Datensätzen ansammeln und dazu führen, dass die Größe des Transaktionsprotokolls zunimmt.

Wenn Sie eine asynchrone Datenbankspiegelung ausführen, könnte es einen Überhang von Transaktionsprotokoll-Datensätzen geben, die noch nicht von der Prinzipaldatenbank zur Spiegeldatenbank gesendet wurden (als Sendewarteschlange der Datenbankspiegelung bezeichnet). Die Transaktionsprotokoll-Datensätze können erst freigegeben werden, nachdem sie erfolgreich gesendet wurden. Bei einer hohen Anzahl von generierten Transaktionsprotokoll-Datensätzen und begrenzter Bandbreite im Netzwerk (oder anderen Hardwareproblemen) kann der Überhang recht groß werden und dazu führen, dass die Größe des Transaktionsprotokolls zunimmt.

Wenn ein Benutzer eine explizite Transaktion startet (z. B. mithilfe einer BEGIN TRAN-Anweisung) und dann eine bestimmte Änderung vornimmt (beispielsweise durch eine DDL-Anweisung oder eine Einfüge-/Aktualisierungs-/Löschaktion), müssen die generierten Transaktionsprotokoll-Datensätze gespeichert werden, bis der Benutzer für die Transaktion einen Commit oder Rollback ausführt. Das bedeutet, dass alle nachfolgenden Transaktionsprotokoll-Datensätze, die durch andere Transaktionen generiert werden, ebenfalls nicht freigegeben werden können, da das Transaktionsprotokoll nicht selektiv freigegeben werden kann. Wenn dieser Benutzer beispielsweise nach Hause geht, ohne die Transaktion abzuschließen, nimmt die Größe des Transaktionsprotokolls weiter zu, da immer mehr Transaktionsprotokoll-Datensätze generiert werden, aber nicht freigegeben werden können.

Sie können feststellen, warum das Transaktionsprotokoll nicht freigegeben werden kann, indem Sie wie folgt die sys.databases-Systemkatalogsicht abfragen und die log_reuse_wait_desc-Spalte prüfen:

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

Wenn sich herausstellt, dass die Ursache eine aktive Transaktion ist, können Sie mithilfe der DBCC OPENTRAN-Anweisung weitere Informationen zur Transaktion abrufen:

DBCC OPENTRAN ('dbname')

F: Ich habe gehört, dass REPAIR_ALLOW_DATA_LOSS nur als letzter Ausweg zur Wiederherstellung nach einer Beschädigung verwendet und die Wiederherstellung stattdessen mithilfe von Sicherungen durchgeführt werden sollte. Können Sie erklären, warum „repair for SQL Server 2005“ nicht verwendet werden sollte und warum diese Option angesichts ihrer „Gefährlichkeit“ überhaupt im Produkt enthalten ist?

A: Zunächst einmal möchte ich darauf hinweisen, dass repair für SQL Server 2005 von mir geschrieben wurde. Das Problem bei REPAIR_ALLOW_DATA_LOSS (im Folgenden einfach als „repair“ bezeichnet) besteht darin, dass nicht klar ist, wie diese Option funktioniert. Der Name der repair-Option wurde gewählt, um darauf hinzuweisen, dass ihre Ausführung zum Verlust von Daten in der Datenbank führen kann. Die Methode, mit der das Feature in der Regel eine fehlerhafte Datenbankstruktur repariert, besteht darin, die fehlerhafte Struktur zu löschen und dann alle anderen Elemente in der Datenbank zu reparieren, auf die die gelöschte Struktur verwiesen hat oder die auf sie verwiesen haben. Die repair-Option ist wirklich als letztes Mittel dafür vorgesehen, die Datenbank in einen strukturell konsistenten Zustand zu überführen, und zielt nicht auf das Speichern von Benutzerdaten ab. Zwar legt es repair nicht darauf an, Benutzerdaten zu löschen, aber es werden auch keine Anstrengungen zum Speichern von Benutzerdaten unternommen.

Dies mag wie eine unverantwortliche Methode für die Durchführung von Reparaturen erscheinen, aber wenn die repair-Option verwendet werden muss, bietet sie die schnellste und zuverlässigste Methode für die Behebung von Schäden. Im Falle einer Notfallwiederherstellung ist Geschwindigkeit von höchster Wichtigkeit und Korrektheit unbedingt erforderlich. Es ist fast unmöglich, komplexere Reparaturalgorithmen zu entwickeln, die nachweislich in allen Fällen schnell und korrekt funktionieren. Der repair-Code enthält einige komplexe Algorithmen für den Fall, dass zwei Indizes beispielsweise dieselbe Seite oder derselbe Block zugewiesen ist. Der Algorithmus ist jedoch hauptsächlich für die Reparatur und damit verbundene Korrekturen vorgesehen.

Dennoch gibt es einige Probleme in Verbindung mit repair, die Sie kennen sollten:

• Von repair werden beim Löschen fehlerhafter Strukturen keine Fremdschlüsseleinschränkungen berücksichtigt, sodass möglicherweise Datensätze in einer Tabelle gelöscht werden, die eine Fremdschlüsselbeziehung zu einer anderen Tabelle aufweist. Es kann nur festgestellt werden, ob dies geschehen ist, indem nach der Ausführung von repair DBCC CHECKCONSTRAINTS ausgeführt wird.
• Repair berücksichtigt keine inhärente Geschäftslogik und keine auf Anwendungsebene definierten Datenbeziehungen, die durch das Löschen bestimmter Daten unterbrochen werden könnten. Eine solche Berücksichtigung ist auch nicht möglich. Beschädigungen können wiederum nur festgestellt werden, indem die in die Anwendung integrierte benutzerdefinierte Konsistenzüberprüfung ausgeführt wird.
• Bestimmte Reparaturvorgänge können nicht repliziert werden. Die Ausführung von repair für einen Verleger oder einen Knoten in einer Peer-to-Peer-Topologie kann innerhalb der Topologie zu Inkonsistenzen führen, die manuell korrigiert werden müssen.

Aus diesen Gründen empfiehlt es sich stets, die Wiederherstellung nach einer Beschädigung mithilfe einer Sicherung und nicht durch Ausführung der repair-Option vorzunehmen. Repair wird jedoch im Produkt geboten, weil immer die Möglichkeit besteht, dass eine Situation eintritt, in der Ihre Datenbank fehlerhaft ist, keine Sicherung vorhanden ist und Sie eine Methode benötigen, mit der Sie die Datenbank schnell wieder online bringen können.

F: Ich bin gerade als DBA zu einem neuen Unternehmen gewechselt, und mir wurde die Verantwortung für mehrere Anwendungen und ihre Back-End-Datenbanken übertragen. Eine der Anwendungen weist bei der Durchführung von Aktualisierungen eine sehr schlechte Leistung auf. Ich habe das Problem untersucht und festgestellt, dass für jede der Tabellen, die von der Anwendung verwendet werden, eine enorme Anzahl von Indizes vorhanden ist. Ich habe mich umgehört, und es scheint so, als ob der vorherige DBA die Gewohnheit hatte, einen Index jeder Tabellenspalte sowie einige Kombinationen hinzuzufügen. Ich kann nicht glauben, dass all diese Indizes notwendig sind – wie kann ich feststellen, welche ich ohne Bedenken löschen kann? Wir verwenden SQL Server 2005.

A: Wie Sie bereits vermuten, trägt die große Anzahl von Indizes höchstwahrscheinlich wesentlich zu der schlechten Leistung bei. Jedes Mal, wenn eine Zeile in der Tabelle eingefügt, aktualisiert oder gelöscht wird, müssen entsprechende Vorgänge in jedem nicht gruppierten Index durchgeführt werden. Dadurch entsteht ein hoher Mehraufwand in Bezug auf E/A, CPU-Auslastung und Transaktionsprotokollgenerierung.

In SQL Server 2000 bestand die einzige Möglichkeit festzustellen, welche Indizes verwendet wurden, darin, Profile zu verwenden und Abfragepläne zu untersuchen. SQL Server 2005 enthält eine neue dynamische Verwaltungssicht (Dynamic Management View, DMV), mit der die Indexverwendung überwacht werden kann – sys.dm_db_index_usage_stats.

Diese DMV verfolgt, wann (und wie) ein Index seit dem Start der Datenbank, deren Bestandteil er ist, verwendet wurde. Die Statistiken für alle Datenbanken gehen beim Beenden von SQL Server verloren, und die Statistiken für eine einzelne Datenbank gehen verloren, wenn diese geschlossen oder getrennt wird. Dahinter steht der Gedanke, dass ein Index seit dem Start der Datenbank nicht verwendet wurde, wenn er nicht in der Ausgabe angezeigt wird.

Ein einfacher Ansatz für die langfristige Überwachung der Indexverwendung besteht darin, regelmäßig Snapshots der DMV-Ausgabe zu erstellen und die Snapshots dann zu vergleichen. Vielen Benutzern entgeht, dass Sie die Indexverwendung über einen gesamten Geschäftszyklus hinweg überwachen müssen. Wenn Sie lediglich einen Snapshot für einen einzigen Tag erstellt haben, finden Sie möglicherweise mehrere Indizes, die nicht verwendet werden. Falls diese Indizes aber verwendet werden, damit beispielsweise die Monatsendberichte schneller ausgeführt werden, sollten die Indizes wahrscheinlich nicht entfernt werden. Wenn ein Index wirklich über einen gesamten Geschäftszyklus hinweg nicht verwendet wurde, können Sie ihn wahrscheinlich löschen und dadurch Speicherplatz freigeben und die Leistung erhöhen.

In meinem Blogbeitrag unter sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx finden Sie einfachen Code, den Sie zum Erstellen periodischer Snapshots der DMV verwenden können.

Paul S. Randal ist der leitende Direktor von SQLskills.com und ein SQL Server-MVP. Er schrieb DBCC CHECKDB/repair für SQL Server 2005 und war bei der Entwicklung von SQL Server 2008 für das Kernspeichermodul zuständig. Als Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung ist Paul Randal ein regelmäßiger Referent bei Konferenzen. Sein Blog befindet sich unter SQLskills.com/blogs/paul.