Share via

SQL q & A: der Überlieferung von Protokollen

  • Artikel

Transaktionsprotokolle sind ein wesentlicher Bestandteile jeder Instanz von SQL Server. Ebenso wichtig ist deren korrekte und effiziente Verwaltung.

Paul S. Randal

Schnelles Wachstum

F: Ich habe erfolgreich die Indexneuerstellung online verwendet seit es in SQL Server 2005 eingeführt wurde. Ich habe auch das massenprotokollierte Wiederherstellungsmodell verwendet um die Vergrößerung der Transaktionsprotokolle beim Defragmentieren der Indizes zu verringern. Wir SQL Server 2008 kürzlich aktualisiert und jetzt das Transaktionsprotokoll wächst mehr als je zuvor. Können Sie erklären, was passiert?

**A.**SQL Server 2005 immer Vorgänge zur Indexneuerstellung erwarb blockierende Sperren für die Dauer des Vorgangs neu erstellen. Neuerstellen eines gruppierten Indexes bedeutete eine exklusive Tabellensperre (keine gleichzeitigen Leser oder Schreiber). Nicht gruppierte Indizes neu erstellt werden soll eine gemeinsame Tabellensperre (keine gleichzeitigen Ersteller der Tabelle).

Mit dem Aufkommen von Onlineindexvorgänge in der Enterprise Edition von SQL Server 2005 wurde die Sperren für online-Betrieb geändert. Blockierende Sperren am Anfang und Ende der Operation nur für kurze Zeiträume gehalten wurden (siehe Dieser Blogbeitrag eine Erläuterung).

Verwenden Sie in SQL Server 2005 minimalen Protokollierung aller Vorgänge zur Indexneuerstellung. Dies bedeutet, dass nur Seitenreservierungen angemeldet sind, anstatt alle Zeileneinfügungen in den neuen Index. Dies verringert drastisch die Transaktionsprotokoll-Datensätze von der Indexneuerstellung generierten. Es bedeutet auch die Transaktion Protokolldatei selbst muss nicht so groß sein (da es nicht um eine Operation vollständig protokollierten Indexneuerstellung gerecht zu werden). Dieses Verhalten tritt nur auf, wenn der massenprotokollierten oder einfachen Wiederherstellungsmodell verwendet. Alle Vorgänge werden in das vollständige Wiederherstellungsmodell vollständig protokolliert – daher der Name.

SQL Server 2008 an wurden Onlineindexvorgänge in alle Wiederherstellungsmodelle vollständig protokolliert werden. Dadurch wird sichergestellt, dass die Datenbank-Restore-Vorgang eine Onlineoperation Index in das Transaktionsprotokoll umfasst Probleme werden nicht. (Weitere Informationen hierzu finden Sie im Microsoft im Microsoft Knowledge Base-Artikel 2407439.)

Wenn begrenzen die Vergrößerung der Transaktionsprotokolle wichtiger ist als gleichzeitige Tabelle Zugriffsfreigabe während der Indexneuerstellung, leider müssen Sie auf offline-Index erstellt, um dieses Verhalten für die minimale Protokollierung wieder zurückgesetzt. Eine andere Sache zu prüfen ist ALTER INDEX verwenden … REORGANIZE, um die Fragmentierung zu entfernen. Dies immer funktioniert ohne Blockierung verursacht und die Menge der Transaktionsprotokolleinträge reduzieren. (Es ist ein Vergleich der Neuerstellung und neu organisiert, in einem der folgenden Antworten.)

BULK-up Your Backup

F: Wir haben versucht, eine Sicherungsstrategie entwerfen, die mit unseren nächtlichen Datenimportvorgang übereinstimmt, so dass unsere vollständige Sicherung aller Daten aus den Importvorgang enthält. Wir habe es nicht gelungen einen zuverlässigen Weg hierzu arbeiten – manchmal Daten alle vorhanden und manchmal gar nicht vorhanden ist. Können Sie weiterhelfen?

**A.**Sie geben an, dass "manchmal Daten brauchen es, und manchmal, die gar nicht vorhanden ist." Das macht mich denken, dass Sie den Importprozess als eine große Transaktion ausführen.

Wenn dies ist, was Sie tun, sollten Sie es in kleinere Transaktionen aufteilen. Dies wird SQL für das Löschen von Transaktionslogs zwischen importieren Sie Buchungen mehr potentielle und weniger langfristige Sperren geben. Ein langer, Single-Transaction Prozess kann die Sperrenausweitung auf eine exklusive Tabellensperre führen. Sie möglicherweise nicht um den Importvorgang zu ändern, aber Sie sollten dies tun, wenn Sie können.

Eine vollständige Sicherung erfolgt in zwei Phasen: Lesen der Daten und das Transaktionsprotokoll lesen. Wenn sie die Daten vollständig gelesen hat, es wird dann gelesen dem Transaktionsprotokoll aus, dass Punkt wieder so weit wie erforderlich ist (siehe meinen Artikel Juli 2009 "Grundlegendes SQL Server Sicherungen," für weitere Details).

Beim Wiederherstellen einer vollständigen Sicherung, die auf die Wiederherstellung der Datenbank ist die Zeit, an dem der Daten lesen Teil der Sicherungskopie abgeschlossen ist. Die Transaktion Protokoll im Wesentlichen in der Sicherung enthalten Crash Recovery auf der wiederhergestellten Datenbank läuft. Dies macht es transaktionell konsistent.

Sie haben während des Fluges zu diesem Zeitpunkt genau wie eine reguläre Crash Recovery Rollback für Transaktionen. Der Importvorgang noch ausgeführt wurde, wenn die Daten lesen Teil abgeschlossen, sie während des Wiederherstellungsvorgangs zurückgesetzt werden würde.

Ist die einzige Möglichkeit zur Gewährleistung von einer bestimmten Transaktions ist eine vollständige Sicherung enthaltenen Operationen zu serialisieren, also vor der Sicherung die betreffenden Transaktion abgeschlossen ist beginnt. Andernfalls ist keine Möglichkeit zu wissen, ob die Transaktion abgeschlossen ist, bevor die Daten-Lesung abgeschlossen ist.

Es gibt natürlich eine Alternative zum Serialisieren von Operationen. Führen Sie eine Sicherung des Transaktionsprotokolls nach Abschluss des Importvorgangs. Wiederherstellen der vollständigen Sicherung sowie der Sicherung des Transaktionsprotokolls. Dadurch wird die gesamte Import Process Transaktion enthalten.

Neu erstellen oder neu organisieren

F: Ich bin eine Indexwartung Strategie entwerfen. Ich verstehe nicht, warum gibt es zwei Methoden, um die Indexfragmentierung zu entfernen. Wie kann ich feststellen, ob unsere Indizes neu erstellen oder reorganisieren sie? Eine Liste der Kompromisse zwischen den beiden Methoden ist nicht auffindbar.

**A.**Es wurden zwei Methoden zum Entfernen von Fragmentierung, da ich DBCC INDEXDEFRAG für SQL Server 2000 geschrieben. Die beiden Methoden sind notwendig, da sie sehr unterschiedliche Merkmale aufweisen. Leider gibt es keine Whitepaper, die die Unterschiede zwischen den drei Methoden in SQL Server 2005 an angemessen beschreibt: ALTER INDEX … REORGANIZE (die neuen DBCC INDEXDEFRAG), ALTER INDEX … REBUILD (neue DBCC DBREINDEX) und die online-Version von ALTER INDEX … ERSTELLEN SIE NEU.

Hier ist ein kurzer Vergleich der Optionen ALTER INDEX neu erstellen und neu ordnen:

  • NEUERSTELLUNG erfordert den neuen Index vor dem Löschen der alten zu erstellen. Dies bedeutet, es muss ausreichend freier Speicherplatz in der Datenbank, um Platz für den neuen Index werden; Andernfalls wächst die Datenbank den erforderlichen freien Speicherplatz bereitzustellen. Dies kann bei großen Indizes problematisch sein. REORGANIZE benötigt nur 8 KB zusätzlicher Speicherplatz in der Datenbank.
  • REBUILD können mehrere CPUs, damit der Vorgang schneller ausgeführt wird. REORGANIZE ist immer Singlethreaded.
  • REBUILD erfordern langfristige Sperren für die Tabelle, die gleichzeitige Vorgänge einschränken können. REORGANIZE Sperren nicht blockierende (es ist immer ein Onlinevorgang).
  • REBUILD können minimale Protokollierung um Vergrößerung der Transaktionsprotokolle zu reduzieren. Neu ordnen wird immer vollständig protokolliert, aber nicht verhindert, dass Transaktionslog löschen.
  • REBUILD wird alle Index-Spaltenstatistiken automatisch neu erstellt, während REORGANIZE Statistiken auf allen aktualisieren wird nicht.

So gibt es mehrere Kompromisse hinsichtlich der Speicherplatzanforderungen, Sperr-, Protokollierung und Parallelität. Das wichtigste zu berücksichtigen ist die algorithmische Unterschiede zwischen den beiden Vorgängen.

Neuerstellen ein Index wird immer den gesamten Index unabhängig von dem Ausmaß der Fragmentierung neu erstellt. Dies bedeutet eine Indexneuerstellung für einen leicht fragmentierte Index wirklich übertrieben ist. Eine Reorganize Index nur kümmert die vorhandenen Fragmentierung. Dadurch eine bessere Wahl für die Fragmentierung aus einem leicht fragmentierte Index zu entfernen, aber eine schlechte Wahl für die Fragmentierung von einem stark fragmentierten Index entfernen. In diesem Fall wäre es besser, nur den Index neu erstellen.

Dies führt uns die verschiedene Schwellenwerte für die Wahl zwischen Neuerstellung und neu ordnen. Es gibt weit verbreiteten Fragmentierung Schwellenwerte auf Grundlage der Spalte Avg_fragmentation_in_percent in der Ausgabe von sys. dm_db_index_physical_stats:

  • 0 bis 10 Prozent: nichts
  • 10 bis 30 Prozent: Verwenden Sie ALTER INDEX … REORGANISIEREN
  • 30 Prozent und höher: Verwenden Sie ALTER INDEX … NEU ERSTELLEN

Dies sind die allgemeinen Leitlinien und vorkommen, dass unterschiedliche Werte in Ihrer Umgebung besser funktionieren. Sie müssen auch prüfen, ob Sie Datenbankspiegelung verwenden die erfordert des vollständige Wiederherstellungsmodell verwendet. Dies bedeutet, dass Vorgänge zur Indexneuerstellung vollständig protokolliert werden. Viele Leute finden, dass dies effizient zwischen Spiegelung Prinzipal- und Spiegelserverinstanzen senden zu viele Transaktionsprotokolleinträge erzeugt. In solchen Fällen kann es besser zu reorganisieren der Indizes des Transaktionsprotokolls zu minimieren.

Sie sollten auch mit einer anderen Person Indexwartung Skripts um Zeit zu sparen. OLA Hallengren hat einige Skripts umfassende und weit verbreitet.

Die Höhen und Tiefen der Protokollgröße

F: Ich habe gerade ein DBA geworden. Ich habe Probleme beim Einrichten von einige neuen Datenbanken für unsere Entwickler. Das Problem, das ich habe ist herauszufinden, wie groß das Transaktionslog geschrieben werden soll. Ich wähle, unabhängig von Größe vergrößert und auf diese Weise bleibt. Wenn ich es verkleinern, wächst sie immer noch auf die gleiche Größe erneut. Gibt es eine Möglichkeit zum Festlegen der Größe ordnungsgemäß beim Erstellen der Datenbank?

**A.**Zunächst einmal nicht regelmäßig das Transaktionsprotokoll zu verkleinern. Wenn das Transaktionsprotokoll wachsen muss, hat der neue Speicherplatz im Transaktionsprotokoll mit 0 (null) initialisiert werden. Dies bedeutet, dass Transaktionen, die darauf warten, Protokolldatensätze generieren möglicherweise warten, während diese Initialisierung erfolgt.

Wenn Sie ein Shrink-grow sind, verkleinern-vergrößern Zyklus mit dem Transaktionslog haben Sie eine unnötige Leistungseinbußen auf SQL Server verursacht. Es ist besser, das Transaktionsprotokoll auf die erforderliche Größe zu verlassen. Shrink sollte eine seltene Operation auf Daten- oder Transaktionsprotokolldatei anmeldet.

Sie können auf der Grundlage von Datenbankoperationen Transaktionsprotokollgröße schätzen. Lesen Sie mehr über die in meinem Artikel vom Januar 2011. Wenn nach jeder Verkleinern des Transaktionsprotokolls eine verlässliche Größe vergrößert wird, ist, die wahrscheinlich die Größe sollte es sein. Lassen Sie es in dieser Größe, es sei denn, es wirklich ist groß. Wenn es zu groß ist, bestimmen Sie die Ursache des Wachstums und gibt an, ob Sie diesen Vorgang in mehrere Teile teilen können, so dass das Transaktionslog deaktivieren kann.

Paul S. Randal

Paul S. Randalist Geschäftsführer von SQLskills.com, Microsoft regional Director und MVP für SQL Server. Er arbeitete an der SQL Server Storage Engine Team bei Microsoft von 1999 bis 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und war verantwortlich für das Kernspeichermodul während der Entwicklung der SQL Server 2008. Paul Randal ist Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung und regelmäßiger Referent bei Konferenzen in aller Welt. Er Blogs unter SQLskills.com/blogs/paul, und Sie finden ihn auf Twitter bei Twitter.com/PaulRandal.

Verwandter Inhalt