SQL – Fragen und Antworten: Feinabstimmung für eine optimale Leistung
Doppelte Indizes, abgebrochene Rollbackvorgänge und E/A-Spitzen können zu Leistungsproblemen führen, die Sie jedoch umgehen können.
Paul S. Randal
Doppelte Indizes
F: SQL Server scheint, lassen mich die Indizes erstellen, die genau auf die gleiche Tabelle identisch sind. Wie hilft dies meine-Workload – Performance? Werden unterschiedliche Abfragen unterschiedliche Kopien von denselben Index verwenden?
**A.**Es ist bedauerlich, dass SQL Server doppelte Indizes erlaubt, da sie überhaupt keinen Vorteil bieten. In der Tat können die doppelten Indizes in vielerlei Hinsicht schädlich.
Ein doppelter Index tritt auf, wenn die Indexschlüssel genau einen anderen Index, angegeben in der gleichen Reihenfolge und mit derselben ASC oder DESC Spezifikation identisch sind. Die eingeschlossen Spalten (falls vorhanden) sind ebenfalls identisch (obwohl die eingeschlossen Spalten in beliebiger Reihenfolge angegeben werden können).
SQL Server wird nur mit einer der die doppelten Indizes um mit Abfragen zu helfen, aber es muss verwalten alle Indizes für eine Tabelle während einfügen, aktualisieren und Löschen von Operationen. Dies bedeutet, jedes Mal gibt es eine Insert oder delete für die Tabelle, es muss in allen Indizes berücksichtigt werden. Das gleiche gilt für Updates, wenn die Spalten aktualisiert werden Teil des Indexes sind.
Diese Indexwartung verwendet zusätzliche Ressourcen und zusätzliche Transaktionsprotokoll-Datensätze generiert – alles für Indizes, die im wesentlichen nutzlos sind. Diese doppelte Indizes nehmen zusätzlichen Speicherplatz auf der Festplatte und zusätzlichen Platz in Sicherungen — und die Seiten für die Indexwartung erforderlich nehmen zusätzlichen Speicherplatz im Arbeitsspeicher zu.
Doppelte Indizes werden fragmentiert. Sie erfordern auch zusätzliche Ressourcen während des Entfernens der regulären Index-Fragmentierung. Die zusätzliche Transaktionsprotokoll von Indexwartung und Beseitigung der Fragmentierung kann auch führen um zu niedrigeren Leistung von hoher Verfügbarkeit (HA) Features wie Datenbankspiegelung und Transaktionsreplikation.
SQL Server gibt Ihnen keine Warnung, dass Sie nur einen doppelten Index erstellt haben, so dass auf Sie, um zu vermeiden, damit es obliegt. Überprüfen, ob Sie bereits doppelte Indizes haben, ist keine Kleinigkeit. Es geht um alle Indexdefinitionen und manuell vergleichen Sie oder umfassende programmgesteuerte Analysieren des Systemkatalogs Skripting. Letztes Jahr, Kimberly Tripp gebucht ein komplette Lösung für dieses Problem.
Hüten Sie sich vor das Rollback
F: Ich hatte vor kurzem eine lang andauernde Aktualisierung abbrechen. Nachdem der Vorgang Rollback, war die nächste Transaktionsprotokollsicherung tägliche riesig. Ich erwartete es sehr klein, wie in der Datenbank nichts geändert hatte. Können Sie diese Anomalie erklären?
**A.**Dies ist ein ziemlich weit verbreitetes Missverständnis. Wenn Sie eine große Operation zurückgesetzt werden, sollte die nächste differenzielle Sicherung recht klein, sein? Falsch!
Jedes Mal, wenn SQL Server eine Änderung an der Datenbank vornimmt, geschehen zwei Dinge. Es generiert zunächst Transaktionsprotokoll-Datensätze, die die Änderung beschreiben. Zweitens, wird das entsprechende Bit für jede Datei Datenseiten geändert, indem die Änderung, in eine differenzielle Bitmap festgelegt. Dies bedeutet, dass diese Seiten die nächste differenzielle Sicherung gesichert werden sollte.
Wenn Sie einen Vorgang ein Rollback durchführen, ist SQL Server Änderungen rückgängig machen, den Betrieb hergestellt. Dies bedeutet, dass es alle Transaktionsprotokoll-Datensätze generiert, indem der vordere Teil der Operation untersucht. Es hat diese Änderungen in umgekehrter Reihenfolge rückgängig zu machen. Jedes Transaktionsprotokoll-Datensatz beschreibt eine einzige Änderung an die Datenbank als Teil der Operation. Um diese Änderung ein Rollback auszuführen, müssen Sie eine weitere Änderung an der Datenbank vornehmen, die die Wirkung der ursprünglichen Änderung negiert. Beispielsweise würden Sie einen Datensatz einfügen Rollback durch Löschen des Datensatzes. Der Netto-Effekt ist, dass der Eintrag nicht vorhanden ist.
Hier ist der verwirrendste Teil: jede Änderung beim Rollback ausgeführt ist wirklich nur eine andere Datenbank (wenn auch eine besondere). Für jede Änderung an die Datenbank muss es ein Protokolldatensatz Transaktion. So auch Änderungen während eines Rollbacks müssen ordnungsgemäß angemeldet sein. Dies bedeutet, dass ein Rollback der großen Operation nicht nur Transaktionsprotokolldatensätze für der vordere Teil der Operation, sondern auch für das Rollback generiert wird. Sicherungen des Transaktionsprotokolls werden alle diese Transaktionsprotokoll-Datensätze, sichern für die großen Transaktionsprotokollsicherung.
Wenn der vordere Teil der Operation das differenzielle Bitmuster veranlasst zu Bits festgelegt, da Teile der Datenbank geändert haben, können nicht Sie die Bits in das differenzielle Bitmuster wieder deaktivieren, da die Datenbank geändert wurde. Es spielt keine Rolle, ob die Änderung schließlich ein Rollback ausgeführt wurde. Die Daten-Datei-Seiten noch (zweimal, in der Tat) geändert wurden, und also müssen gesichert werden die differenzielle Sicherung.
Der Haken an der Sache ist, dass selbst wenn ein Vorgang ein Rollback ausgeführt wird, noch die Datenbank geändert wird. Alle Sicherungen müssen diese Änderungen widerspiegeln.
Auf der Suche nach Spitzen
F: Ich bin ein Problem beheben, wo wir regelmäßige I/O Spitzen aus einem SQL Server sehen. Ich habe es auf Prüfpunkte mithilfe von PerfMon eingeengt, aber ich kann nicht sagen, welche Datenbank die wichtigsten Täter ist. Wie kann ich weiter bohren?
**A.**Prüfpunkte existieren aus zwei Gründen. Zunächst aktualisieren sie Daten Datei Seiten mit was in das Transaktionsprotokoll geschrieben wurde. SQL Server verwendet einen Mechanismus namens Write-ahead-Logging, wo sind Änderungen in der Datenbank im Transaktionsprotokoll beschrieben, bevor in den Datendateien reflektiert wird. Dies garantiert die Langlebigkeit der Änderungen bei einem Absturz. Zweitens, reduzieren sie die Dauerbelastung I/O schriftlich nur geänderte Daten Datei Seiten in regelmäßigen Abständen, anstatt nach jeder Änderung auf jeder Seite der Daten-Datei.
Prüfpunkte auftreten für jede Datenbank separat. Sie sind ausgelöst, basierend auf einer Reihe von Faktoren, einschließlich des Wiederherstellungsintervalls — Dies ist der SQL Server schätzt, dass genügend Transaktionsprotokoll seit dem letzten Prüfpunkt generiert wurde damit die Wiederherstellung nach einem Systemabsturz ungefähr eine Minute nehmen (standardmäßig).
Diese Zahl entspricht der Generation viele Zehntausende von einzelnen Transaktionsprotokoll-Datensätze. Je mehr Daten-Datei-Seiten geändert, indem diese Transaktionsprotokoll-Datensätze, die größer den Betrag der i/O, die von Datenbank-Kontrollpunkte durchgeführt werden müssen.
Sie können Prüfpunkte mit den Leistungsindikator "Checkpoint Seiten/s" in der SQL Server verfolgen: Puffer-Manager-Leistungsobjekt. Das gibt nur eine Count-aggregate in allen Datenbanken auf dem SQL Server-Instanz. Um festzustellen, welche Datenbank jederzeit "geprüfte" wird, müssen Sie Ablaufverfolgungsflags verwenden.
Wenn Sie schalten Sie Ablaufverfolgungsflags 3502 (Spur drucken, wenn ein Prüfpunkt auftritt), 3504 (print Ablaufverfolgungsdetails über Checkpoint) und 3605 (zulassen, dass die Ablaufverfolgung druckt, gehen in das Fehlerprotokoll), werden Sie feststellen, welche Datenbank für die I/O-Spikes aufgrund Prüfpunkte Rechnungswesen ist.
Sie können diese Ablaufverfolgungsflags, die mithilfe des Befehls aktivieren:
DBCC TRACEON (3502, 3504, 3605, -1)
Deaktivieren sie wieder mit dem Befehl:
DBCC TRACEOFF (3502, 3504, 3605, -1)
Nachfolgende Prüfpunkte Ausgabe werden ähnlich dem folgenden in das Fehlerprotokoll:
2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete
So können Sie sehen, welche Datenbank geprüfte ist und übereinstimmen, die die Informationen von PerfMon. Sie können dann untersuchen warum es so viel Daten zwischen Checkpoints geändert wird gibt, führen Sie häufiger Prüfpunkte zur Reduzierung der I/O-Sammlung, oder Erhöhung der Kapazität der i/O-Subsystem.
Konsolidierung betrifft
F: Meine Firma hat eingeführt eine neue Richtlinie verlangt, dass wir möglichst viel Hardware-Kosten reduzieren konsolidieren. Ich bin auch geschoben, um die Anzahl der Instanzen von SQL Server zum Speichern der Lizenzkosten zu reduzieren. Gibt es irgendwelche Richtlinien, wie viele Datenbanken pro SQL Server-Instanz sinnvoll?
**A.**Die Antwort auf diese Frage ist ein großes "es hängt." Die Liste der Faktoren enthält die Größe der Datenbanken, die Arten von Arbeitsauslastungen, die sie laufen, die Volatilität der Daten, der Typ benötigt regelmäßige Wartung, und die Disaster-Recovery und HA-Anforderungen.
Jede SQL Server-Instanz hat eine endliche Menge an Speicherplatz im Arbeitsspeicher zum Speichern von Daten Datei Seiten jederzeit verarbeitet werden (Dies wird auch als Pufferpool bezeichnet). Weitere Datenbanken haben Sie auf einer Instanz mit unterschiedlichen Arbeitsauslastungen alle die Verarbeitung, des mehr Wettbewerbs wird es unter der Arbeitsauslastungen für Puffer-Pool-Speicherplatz.
Dies kann zu Prügel der Puffer Poolspeicher führen. Ständige Abwanderung machen Platz für neue Daten Datei Seiten vom Datenträger gelesen werden. Es werden auch große Mengen an lesen I/O mit höher als akzeptabel lesen Sie Wartezeiten. Alle diese Faktoren werden-Workload – Performance beeinträchtigen.
Wenn die verschiedenen Arbeitslasten Datenbankänderungen verbunden, werden es auch schreiben i/O von periodischen Prüfpunkte. Mit zahlreichen Datenbanken auf einer einzigen Instanz konsolidiert gibt es möglicherweise mehrere Kontrollpunkte gleichzeitig auftreten. Dies könnte dazu führen, dass I/O Schreibwartezeiten — Verlangsamung der Prüfpunktvorgängen und weiter zur Arbeitsauslastung Leistungsabfall.
Normalen Datenbankpflege wird auch ein Problem mit einer großen Anzahl von Datenbanken. Wenn jede Datenbank Index und Statistiken Wartung, Überprüfung der Datenbankkonsistenz und Sicherungen erforderlich ist, kann es eine Herausforderung für alle diese Vorgänge für alle Datenbanken zu planen, so dass sie nicht miteinander in Konflikt stehen und noch mehr I/O laden auf dem Server.
Gibt die weitere Datenbanken auf einer Instanz sind, desto schwieriger wird es, die nativen HA-Technologien von SQL Server verwenden, um sie alle zu schützen. Es ist eher wahrscheinlich, müssen Sie irgendeine Art von i/O-Subsystem-Ebene Technology — auch nur aus Sicht der Benutzerfreundlichkeit-Management. Dies bedeutet zusätzliche Kapitalaufwand, die die Kosteneinsparungen durch Serverkonsolidierung ausgleichen konnte.
Konsolidierung ist ein riesiges Thema. Voll tut es Gerechtigkeit ist im Rahmen einer einzelnen Spalte. Dies ist genug Denkanstöße zu machen Sie vorsichtig over-consolidating. Auf der anderen Seite müssen Sie viele kleine Datenbanken mit minimalen Arbeitslasten, die Sie auf einer einzelnen Instanz ohne Probleme hosten können. Wie ich bereits erwähnt, kommt es.
.jpg)
Paul S. Randal ist der Geschäftsführer von SQLskills.com, Microsoft regional Director und ein SQL Server-MVP. Er arbeitete an der SQL Server Storage 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 SQL Server 2008-Entwicklung. 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 finden ihn auf Twitter bei Twitter.com/PaulRandal.