SQL q &A: Engpässe und Transaktionsprotokolle

  • Artikel

Manchmal ist es einfach zu bestimmen, die Art der einen Leistungsengpass — andere Zeiten, nicht so viel. Gleiches gilt für die Konfigurierung von Transaktionsprotokollen.

Paul S. Randal

Storage-Engpass

Frage: Ich versuche zur Bestimmung der Art der mein system's Performance-Engpass. Ich bin überzeugt, es ist die Speicherschicht der, wie ich bin sehend die Disk-Warteschlange, die höher als zwei Längen gehen. Ich habe gelesen, dies ist ein guter Weg, um zu beweisen, dass SQL Server den Speicher überladen ist. Ist das wahr? Wenn ja, was kann ich dagegen tun?

A: Leider, was Sie gelesen haben, ist eine urbane Legende. SQL Server wurde entwickelt, um asynchrone e/A zu verwenden und wird gerne die Warteschlangenlänge des Datenträgers oben zwei push. Jeder Thread, der eine IO Probleme dann weiterhin etwas anderes (potenziell), bis das IO ist abgeschlossen. SQL Server versucht, Durchsatz aus dem IO-Subsystem zu maximieren durch die Ausgabe von asynchronen i/O gleichzeitig. Es wird auch wie Read-ahead Bearbeitungsvorgänge beim Scannen von großer Datenmengen.

Einige Operationen wie DBCC CHECKDB werden in der Tat, das e/A-Subsystem sättigen. Es ist nicht ungewöhnlich, dass Disk-Warteschlangenlängen von mehreren hundert. Lesen Sie mehr über diese urbane Legende von Disk-Warteschlangenlängen in diesem Blog-post .

Die Frage ist dann, "was tun Sie betrachten, um festzustellen, ob ein e/A-Subsystem-Engpass vorhanden ist?" Es gibt zwei Leistungsindikatoren im Leistungsobjekt physikalischer Datenträger. Sie sollten diese achten:

  • Durchschnittl. Disk sec/Read
  • Durchschnittl. Sek./Schreibvorgänge

Diese geben die Zeit in Millisekunden, die es, dauert bis eine IO abzuschließen. Wenn diese Nummern durchweg höher sind (oder regelmäßige höhere Spitzen haben) als die Norm (die zwischen 5ms und 12ms sein sollte), dann ist der physikalische Datenträger der e/A-Engpass. Natürlich kann, dass physikalische Datenträger einer SAN-LUN, aber Sie können nicht tiefer zu graben von Windows.

Wenn Sie mehrere SQL Server-Daten und Protokoll-Dateien auf dem physikalischen Datenträger verfügen, müssen Sie bestimmen, welche Dateien die IO-Belastung verursachen. Verwenden Sie der dynamischen Management-Ansicht (DMV) sys. dm_io_virtual_file_stats und durchführen Sie einige einfachen Zeitreihen-Analysen, auf die Ergebnisse.

Wenn die DMV-Ergebnisse, eine schwere Last auf dem physikalischen Datenträger zeigen nicht, kann ein Speicheradministrator Dateien aus anderen Anwendungen auf diesem Teil des Teilsystems IO platziert haben. Diese Arbeitsauslastung könnte sein, was die IO-Bandbreite Monopol ist. In diesem Fall müssen Sie bitten, dass bestimmte Administrator die SQL Server-Dateien auf einen dedizierten Teil des Teilsystems IO zu verschieben.

Sollten Sie es ist rein SQL Server-Dateien auf dem Subsystem, und können Sie identifizieren, welche die übermäßige IO verursachen, diese Strategien:

  • Betrachten Sie die Arbeitsauslastung der Datenbank-Abfrage, und bestimmen Sie, wenn es übermäßige Tabellenscans aufgrund von einer falschen indexing-Strategie oder schlechte Abfragepläne, verursacht durch veraltete Statistiken durchführt.
  • Verschieben Sie einige der Dateien auf einen anderen Teil des Teilsystems IO.
  • Fügen Sie mehr Arbeitsspeicher auf dem Server um einen größeren SQL Server-Pufferpool (in-Memory-Cache von Datenseiten-Datei) zu ermöglichen, und vermeiden so viel IO lesen.

Wenn keines dieser Arbeit, und es wirklich nur ein Fall wo die Arbeitsauslastung das e/A-Subsystem Гјberholt hat ist, verschieben Sie auf ein leistungsfähigeres IO-Subsystem. Sie könnten auch erwägen, Enterprise-Class-Flash-Speicher-Speicher wie Fusion-Io.

Size Matters

Frage: Ich bin die Speicheranforderungen für einige neue Server layouten und ich bin habend Ärger bestimmen, wie groß die Transaktionsprotokolle zu machen. In der Vergangenheit habe ich versucht, es auf Transaktionsgrösse stützen. Manchmal, es scheint zu verdoppeln. Können Sie erklären, wie ich eine anständige Schätzung erreichen können?

A: Es gibt keine einfache Formel für die Berechnung der optimalen Transaktionsprotokollgröße. Leider ist es auch schwierig zu verhindern, dass die Transaktion Log Wachstum, es sei denn Sie haben wandtet weg automatische Vergrößerung für die Protokolldatei. Allerdings deaktivieren der automatische Vergrößerung ist nie ratsam.

Die erste davon ist die zugrunde liegende RAID-Stufe. Die verschiedenen RAID-Ebenen haben unterschiedliche Trade-Offs, soweit die Leistung und Redundanz betroffen sind. Beispielsweise die billigste RAID-Konfiguration, die noch einige Redundanz bietet ist RAID-5, aber diese Konfiguration bewältigen kann nur ein einziges Laufwerk-Fehler (sofern RAID-6, verwenden oder hot-Spare-Laufwerken konfiguriert), und kann die Leistung für Write-Heavy-Arbeitslasten, je nachdem, wie viele Laufwerke im Array sind manchmal beeinträchtigen.

Das Transaktionsprotokoll sollte immer in der Lage, um die automatische Vergrößerung. Dies gilt insbesondere für den Notfall, wenn Ihre Protokolldatei Größe Überwachung fehlschlägt. Genommen Sie an, Sie haben eine SQL Server-Agent-Warnung auf Prozent Log verwendet Leistungsindikators gehen höher als 90 Prozent, aber der Notfallkontakt per e-Mail/Pager benachrichtigt ist krank. Wenn das Protokoll nicht wachsen kann, alle aktiven Transaktionen, die Änderung der Datenbank stoppen und Rollback. Dies entspricht einem Ausfallzeiten für Ihre Arbeitslast.

Aber zu sagen, es gibt keine einfache Formel ist ein wenig irreführend. Es gibt zahlreiche Vorgänge, die Transaktionsprotokoll-Speicherplatz aufnehmen. Sie können die Größe dieser Vorgänge verwenden, um Ihre Transaktion Log-Anforderungen zu schätzen. Diese dürfen Operationen im Rahmen der täglichen Arbeitszeit oder mehr sporadisch Aktionen wie Datenbankwartung. Sie haben, Sie alle zu betrachten. Die Operation-Liste enthält:

  • Die größte einzelne Insert/Update/Delete-Transaktion führt Ihre Arbeitsbelastung (ob einer einzigen Anweisung implizite Transaktion betreffen Millionen von Tabellenzeilen, oder eine explizite Transaktion, die viele Operationen ausführt).
  • Die größte Bulk-Betrieb Ihrer Arbeitsauslastung durchführt, z. B. eine BULK INSERT. Wenn Sie den vollständigen Wiederherstellungsmodus verwenden, können zur Verringerung der Menge von Transaktionsprotokolleinträgen generiert mithilfe von Modell das massenprotokollierten Wiederherstellung möglicherweise. Wenn Sie das Modell der massenprotokollierten Wiederherstellung für minimale-Protokollierung für einige Vorgänge verwenden, kann es Ihre Fähigkeit zur Wiederherstellung nach einer Katastrophe auswirken. Siehe meine Blog-post, "$ eine SQLServer DBA Mythos ein Tag: BULK_LOGGED Recovery Model $. "
  • Eine Indexneuerstellung Ihre größte gruppierten Index. Sie können BULK_LOGGED hier auch nutzen können.

Mit all diese Operationen ist es nicht genau die Menge der Transaktionsprotokolle, die müssen Sie berücksichtigen, müssen Sie auch berücksichtigen, der Raum das Transaction Log-Management-System "" Reserve wird ordnungsgemäße Transaktions-Rollback zu ermöglichen. Eine Transaktion 100 MB des Transaktionsprotokoll-Datensätze generiert, wird das System behalten, wenn ca. 100 MB des Speicherplatzes im Transaktionslog kann die Transaktion abgebrochen und korrekt Rollback zu gewährleisten. Es ist ein Sicherheitsmechanismus zu inkonsistenten Datenbank zu verhindern. Dies ist, warum Sie das Transaktionslog, die wachsen, gesehen haben können, auch wenn Sie denken, Sie es genug Platz für die größte Transaktion gegeben habe.

Eine andere Sache zu prüfen ist, ob es Gründe, warum Transaktionsprotokoll-Datensätze im Protokoll bleiben müssen. Dies könnte zu einem wachsenden Transaktionslog müssend noch mehr wachsen. Einige der möglichen Ursachen gehören:

  • Die Datenbank ist verwenden die Wiederherstellungsmodellen FULL oder BULK_LOGGED und nicht durchführen von Transaktionsprotokollsicherungen (oder ausführen Sie selten). Sie müssen Protokolldatensätze sichern, bevor Sie zu verwerfen.
  • Eine ungewöhnlich lange andauernde Transaktion ist vorhanden. Dadurch wird verhindert, verwerfen alle Transaktionsprotokoll-Datensätze generiert, da die lange andauernde Transaktion gestartet.
  • Die Datenbankspiegelung wird verwendet, und einige Transaktionsprotokoll-Datensätze nicht vom Prinzipalserver zum Spiegelserver gesendet wurden. Sie können diese nicht verwerfen, bis Sie gesendet wurden, haben.
  • Transaktionsreplikation ist (Peer-to-Peer-Replikation) in benutzen und es gibt einige Transaktionsprotokoll-Datensätze, die der Protokolllese-Agent-Auftrag verarbeitet noch nicht.

Wenn du bist das Transaction Log wachsen zu sehen und Sie sind nicht sicher, warum es passiert ist, Fragen Sie SQL Server. Ausgabe der Abfrage:

SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO

Das Ergebnis wird sein, dass der Grund können Sie nicht einige Protokolldatensätze zu verwerfen und den Protokollspeicherplatz (genannt "clearing" oder "durch" Abschneiden des Protokolls) wiederverwenden.

Wie Sie sehen können, gibt es durchaus einige Sachen gibt, die beeinflussen können, die Größe des Transaktionsprotokolls, noch mehr, wenn Sie die Tempdb-Datenbank als auch erwägen. Lesen Sie ein bisschen mehr über dieses Thema in meinem Blog-Post, " Bedeutung der richtigen Größe der Transaktionsprotokollverwaltung," und in die TechNet Magazin Artikel " Verständnis SQLServer-Protokollierung und Wiederherstellung ."

Anmeldung obligatorisch

Frage: Können Sie erklären, warum ich SQL Server Vorgänge nicht protokolliert machen kann? Ich habe gelesen, dass Tabellenkürzung nicht protokolliert — Warum kann nicht dort sein, eine Einstellung, um alle Vorgänge, die nicht protokolliert also SQL Server schneller läuft zu machen? Was passiert, wenn I dont care über die Möglichkeit zur Wiederherstellung nach einer Katastrophe? Ich möchte vor allem in der Lage sein, um das Tempdb-Transaktionsprotokoll zu ignorieren.

A: Es ist nicht wahr, was Sie gelesen haben, über eine TRUNCATE TABLE-Operation. Alle Operationen auf alle Datenbanken werden zu einem gewissen Grad protokolliert. Einige sind "minimal, z. B. Tabellenkürzung protokolliert". Einfach gesagt, ist eine minimal protokollierte Operation eine wo nur die Zuordnung und wieder-Freigabe von Datenseiten Datei wird protokolliert. Alle Operationen auf Tabelle/Index-Datensätze auf den Seiten werden nicht protokolliert. Dies beschleunigt die Operationen und bedeutet weniger Transaktionslog wird generiert — aber es gibt noch einige Protokollierung.

Eine Tabellenkürzung ist in allen Wiederherstellungsmodellen immer minimal protokolliert. Die anderen minimal protokollierte Vorgänge (z. B. Index Build/Rebuild und Bulk Loads) werden nur minimal protokolliert, wenn das Wiederherstellungsmodell SIMPLE oder BULK_LOGGED verwendet.

Nur wirklich nicht protokollierter Vorgänge in SQL Server sind die Auswirkungen auf den Versionsspeicher in Tempdb, die Funktionen wie die Snapshot-Isolation und Onlineindexvorgänge unterstützt. Diese können nicht protokolliert werden weil es niemals eine Notwendigkeit zu schlingern Rücken eine Version Store-Vorgang oder run Crash-Recovery auf der Tempdb-Datenbank.

Dies ruft zum Kern Ihrer Frage. Warum werden können nicht Vorgänge in SQL Server nicht protokolliert? SQL Server muss immer in der Lage, einen Vorgang Rollback, wenn etwas schief geht. Wenn es keine Beschreibung (wie die Transaktionsprotokoll-Datensätze) welche der Operation getan hatte, wie wissen SQL Server würde was zu tun beim Rollback? Dies können Sie nur durch Protokollierung erreichen.

Selbst wenn Sie Don't über Crash-Recovery Care, muss SQL Server noch in der Lage zu schlingern Rücken Operationen, wenn die Datenbank mehr über genügend Speicherplatz oder einen beschädigten Sektor Begegnungen, oder wenn die Abfrage nicht über genügend Arbeitsspeicher ausgeführt wird. Wenn SQL Server einen Rollback des Vorgangs nicht möglich, die Datenbank unbrauchbar und die Arbeitsauslastung stoppt.

Dies gilt auch für die Tempdb-Datenbank. Protokollierung ist zwar vereinfacht und in Tempdb reduziert, können Sie es vollständig aus den gleichen Gründen nie entfernen. SQL Server hat auch, um Crash-Recovery ausführen können nach jeder Absturz zu gewährleisten, jede Datenbank konsistent ist. Andernfalls ist die Datenbank unbrauchbar. Fazit: Es gibt keine Möglichkeit, Operationen, die in SQL Server nicht protokolliert, und ich würde nicht erwarten, dass ändern.

Paul Randal

Paul S. Randal ist der Geschäftsführer von Microsoft regional Director, SQLskills.com und ein SQL Server-MVP. Er arbeitete an der SQL Server-Speicher-Engine Team bei Microsoft von 1999 zu 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und war verantwortlich für das Kernspeichermodul während Entwicklung von 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 SQLskills.com/blogs/paul, und Sie können ihn auf Twitter bei Twitter.com/PaulRandal finden.

Verwandter Inhalt