SQL q & A: defragmentiert und Katastrophen

Fehlerprotokolle und temporäre Datenbankdateien können innerhalb kürzester Zeit ausufern. Die folgenden Konfigurationsmethoden können helfen.

Paul S. Randal

Standard für Defrag

F: Ich habe einige Datenbank-Wartungsprobleme haben wir auf unsere Server Adressierung wurde, und ich überlege Indexfragmentierung. Statt Zeit herauszufinden, ein Wert für Fillfactor für jeden Index wäre es einfacher, legen Sie die Standard-Fillfactor für diese Instanz nicht? Gibt es Nachteile zu tun?

**A.**Ich würde nicht im Allgemeinen empfehlen Instanz Wide Fillfactor, alles andere als Standard festlegen – 100 Prozent. Wenn Sie diese Einstellung ändern, kann dies zu verschwendeten Speicherplatz in der Datenbank führen.

Beim Erstellen von Indizes für eine Tabelle wird in der Regel nur eine dieser Einfügung Tabellenmuster überein. Alle anderen Indizes werden unweigerlich fragmentiert werden. Solange das Muster einfügen nur zum Anfügen ist (keine zufälligen Einfügungen müssen Sie einen zufälligen Schlüssel wie eine globally unique Identifier [GUID]), es ist sinnvoll, zu vermeiden, dass des größten Indexes fragmentiert.

Der größte Index ist immer der gruppierte Index, da der gruppierte Index der Tabelle ist. Diese enthält alle Datensätze mit den Tabellenspalten. Es ist sinnvoll für den gruppierten Index zu sein, die nicht fragmentiert erhalten. Beseitigen der Fragmentierung des gruppierten Indexes werden (in Bezug auf Datenträger Platz, Zeit und Transaction Log) teurer als die Verwendung von für alle anderen Index.

Der gruppierte Index in der Regel nicht fragmentiert erhalten, macht es daher sinnvoll, die der Wert für Fillfactor auf 100 festgelegt ist. Es erfordert keinen zusätzlichen Speicherplatz. Aus anderen Gründen möglicherweise fragmentiert, dass der gruppierte Index wird (beispielsweise von Aktualisierungen an Spalten mit variabler Länge, die Tabellenzeilen, die länger machen), aber das ist in der Regel true.

Wenn Sie Instanz Wide Fillfactor 100 Prozent festzulegen, müssen Sie speziell die Seitenteilungen aller gruppierten Indizes festlegen, die nicht fragmentiert erhalten. Wenn Sie nicht, sie verwenden die neue Instanz Wide Fillfactor und Raum zu verlassen, wenn sie neu erstellt werden. Diese Abfälle im wesentlichen Platz in der Datenbank. Sie können dies tun, aber es ist eine bewährte Methode im Allgemeinen nicht als.

Da es eine Vielzahl von Indizes in einer Datenbank gibt, wäre es, um einen einzelnen Wert für Fillfactor-Wert zu finden, der optimal für alle Indizes ist ungewöhnlich. Es ist in der Regel besser, aus Sicht der Verwaltbarkeit der Instanz-weiten Fillfactor allein zu lassen. Anschließend können Sie speziell niedrigere Seitenteilungen auf nur diese Indizes festlegen, die sie benötigen.

Starten Sie die Antworten

F: Einige Server in unserer Umgebung werden selten neu gestartet. Obwohl dies eine gute Sache in vielerlei Hinsicht ist, bedeutet das, dass das Fehlerprotokoll SQL Server Fließgleichgewichtskonzentration groß werden kann. Es scheint mit Zehntausenden von backup-Vollzugsmeldungen überfüllt, für die ich keine Verwendung haben. Gibt es etwas, was ich tun können, um die Fehlerprotokolle kleiner und einfacher zu machen?

**A.**Es gibt zwei Dinge, die Sie tun können: Ausschneiden nach unten auf die backup-Meldungen und Fehler-Protokoll-Management konfigurieren. Jedes Mal, wenn eine Sicherung abgeschlossen ist, wird einen Eintrag in das Fehlerprotokoll geschrieben. Dies ist von großem Nutzen. Erfolgreicher Abschluss der Sicherung ist die Norm nicht um einen Fehler.

Gibt es eine dokumentierte Ablaufverfolgungsflag – von Ablaufverfolgungsflags 3226 – backup Erfolgsmeldungen wird, verhindern. Sie sollten, die zur Liste der Autostart-Ablaufverfolgungsflags (mithilfe der SQL Server-Konfigurations-Manager) hinzufügen. Um aktivieren Sie diese Option, ohne zuvor zu stoppen und starten SQL Server, können auch Einschalten dieser Ablaufverfolgung kennzeichnen Sie mit dem Befehl "DBCC TRACEON (3226,-1)." -1 Mittel, um das Ablaufverfolgungsflag Global zu übernehmen. Das Team SQL Server in Blogs über das Ablaufverfolgungsflag im Jahr 2007 zurück.

Sie können auch Ihre Fehler-Protokoll-Management innerhalb der SQL Server Management Studio (SSMS) konfigurieren. Öffnen Sie in SSMS Objekt-Explorer, und Verbinden mit SQL Server. Erweitern Sie das Management, mit der rechten Maustaste auf SQL Server-Protokolle, und wählen Sie konfigurieren. Wenn das Dialogfeld "SQL Server-Fehlerprotokolle konfigurieren" angezeigt wird, überprüfen Sie die Option "Beschränken die Anzahl der Fehlerprotokolldateien vor der Wiederverwendung". Wählen Sie 99 Fehlerprotokolle. Dies überschreibt die Standardanzahl von Fehlerprotokollen, sechs gehalten.

Der letzte Konfigurationsschritt ist, was die Größe der einzelnen Fehlerprotokoll begrenzen. Weisen Sie SQL Server ein neues Fehlerprotokoll täglich (das Fehlerprotokoll "Radfahren" genannt) erstellen. Hierzu erstellen einen täglichen SQL Server-Agent-Auftrag, das einfach "EXEC Sp_cycle_errorlog." Danach sollte Ihre Fehlerprotokolle verwaltbar sein.

Bringen Sie die tempdb-Datenbank

F: Unsere Datenmenge wuchs in den letzten zwei Jahren enorm. Unsere Tempdb scheint immer ausfüllen, je nachdem, was es Laufwerk leuchtet. Wir führen einige komplexen Abfragen, so dass wir eine Menge für die Verwendung von temporären Tabellen vornehmen. Können Sie einen Rat zum Reduzieren von unserem Verwendungsprofil Tempdb geben?

A: Verwendung von Tempdb ist ein dauerproblem für SQL Server Benutzer. Es ist nur ein einzelnes Tempdb für jede Instanz von SQL Server, so dass Sie vorsichtig sein, wie es verwendet wird.

Ist eines der häufigsten Verwendungszwecke für Tempdb von temporären Tabellen. Diese Tabellen können Zwischenergebnisse aus komplexen Aggregationen oder Verknüpfungen beibehalten und werden Sie Teil einer größeren Abfrage. Dies kann oft eine effiziente Möglichkeit zum Aufteilen einer langen und komplexen Abfrage sein, aber es ist nicht immer der Fall. Manchmal Entwickler gewöhnen temporäre Tabellen in der Standardeinstellung verwenden, anstatt dies Performance-Tests zu sehen, ob eine temporäre Tabelle zu verwenden ist effizienter.

Das Problem mit der Erstellung ein Zwischenergebnis erstellt, die in einer temporären Tabelle festgelegt ist, dass die effiziente Nutzung von Daten-Pipeline durch eine komplexe Abfrage unterbrochen werden können. Es zwingt den Abfrageoptimierer zu verarbeiten, die Erstellung von temporären Tabellen oder Bevölkerung getrennt von nachfolgenden Operationen, die die temporäre Tabelle zu verwenden. Der Abfrageoptimierer erzeugt manchmal einen Abfrageplan effizienteren, wenn es keine temporäre Tabelle verwendet. Es kann auch durch ein Konstrukt andere Abfrage, wie z. B. eine abgeleitete Tabelle oder ein allgemeiner Tabellenausdruck effizienter ausgeführt.

Wenn eine temporäre Tabelle zu brechen Ihre Abfrage am effizientesten ist, gibt es zwei Dinge, die Sie tun können, um die Verwendung von Tempdb zu reduzieren:

  • Erstellen Sie die entsprechenden Indizes: stellen Sie sicher, die nur in der temporären Tabelle erstellten Indizes eignen sich tatsächlich für die weitere Verarbeitung (Überprüfen Sie dies durch eine Analyse und Überprüfung, welche den Abfrageplan Indizes verwendet). Nicht gruppierte Indizes für jede Spalte der temporären Tabelle erstellt werden kaum hilfreich sein. Stellen Sie auch sicher, dass die Indizes erstellt werden, nach dem Auffüllen der temporären Tabelle, so dass sie, anhand derer den Abfrageoptimierer den Index verwenden.
  • Minimieren Sie die Größe der temporären Tabelle: sicherstellen, dass die einzigen Spalten beibehalten, in der temporären Tabelle sind die zur weiteren Verarbeitung verwendet werden, andernfalls sind sie eine reine Verschwendung von Speicherplatz. Temporäre Tabellen werden oft erstellt, mit einer SELECT *-Konstrukt ohne gedacht, für welche Spalten wirklich erforderlich sind. Wenn Sie ein großes Resultset verarbeiten, kann diese Platzverschwendung summieren.

Schlechte Backups

F: Letzte Woche ist unsere SAN abgestürzt. Wir erlitten Datenverlust in unserer Produktionsdatenbank. Die aktuellsten Sicherungen wurden auf dem SAN zusammen mit den Datenbankdateien gespeichert, so dass die beschädigt wurden. Wir fanden auch unsere etwas ältere Backups sowie beschädigt wurden – in einigen Fällen die gleiche Beschädigung. Wie können wir diese Situation in der Zukunft zu vermeiden?

**A.**Dies ist eine häufig auftretende Situation – keine guten Sicherungen und einem single Point of Failure, die letztlich zu Datenverlust führt.

Das erste Problem ist, dass Ihre Sicherungen beschädigt sind. Sie müssen eine Sicherung Teststrategie, so Sie bestimmen können, ob die Sicherung beschädigt ist oder eine beschädigte Datenbank enthält implementieren. Es gibt mehrere Teile:

  • Implementieren Sie eine regelmäßige Konsistenzprüfung für die Produktionsdatenbank. Dies bedeutet, den Befehl DBCC CHECKDB für die Produktionsdatenbank selbst oder eine Kopie der Datenbank ausgeführt.
  • Aktivieren Sie Seitenprüfsummen für die Produktionsdatenbank (sofern nicht bereits aktiviert ist). Verwenden Sie die Option WITH CHECKSUM für alle Sicherungen. Datei Datenseiten für die Aufnahme in die Sicherung gelesen werden, wodurch zu vermeiden, erstellen eine Sicherung mit einer beschädigten Datenbank wird Seitenprüfsummen getestet.
  • Implementieren Sie eine Überprüfung der Gültigkeit von Sicherungen, nachdem sie getroffen haben. Dies beinhaltet, dauert die Sicherung an eine andere Instanz von SQL Server und Wiederherstellen (mithilfe der Option WITH CHECKSUM erneut), oder zumindest auf dem Ausführen einer RESTORE VERIFYONLY der Sicherung WITH CHECKSUM verwenden. Das Ziel ist die Datenbank wiederherstellen, und führen Sie DBCC CHECKDB. Dies ist auch eine gute Möglichkeit, die die Überprüfung der Datenbankkonsistenz Arbeitsauslastung vom Produktionsserver zu verlagern.
  • Implementieren Sie eine reguläre Testzeitplan, in denen Sie üben die Produktionsdatenbank aus verfügbaren Sicherungen wiederherstellen.

Alternativ können Sie auf einfache Weise die Gültigkeit von Sicherungen des Transaktionsprotokolls testen besteht darin einen Protokollversand sekundäre erstellen. Dadurch werden die Sicherungen des Transaktionsprotokolls ständig aus der Produktion wiederhergestellt. Es bietet Ihnen eine redundante Kopie der Datenbank.

Das zweite Problem ist, dass Ihre Sicherungen auf dem gleichen e/a-Subsystem als die Datenbank selbst gespeichert werden. Dies bedeutet, dass Sie keinen Schutz gegen Fehler beim e/a-Subsystem.

Sie müssen Kopien aller Sicherungen auf einem separaten e/a-Subsystem aus der Produktionsdatenbank zu halten – im Idealfall wäre dies in einem völlig separaten Speicherort. Helfen Sie lokale Kopien von Backups mit schnellere Disaster Recovery und remote-Kopien stellen Sie sicher, dass Disaster Recovery möglich ist, wenn der lokaler Speicher beschädigt oder zerstört wird.

Eine andere Sache zu prüfen ist viel versprechend regulären Disaster Recovery-Übungen. Täuschen Sie eine Katastrophe getroffen hat vor, und arbeiten Sie durch Ihre Disaster Recovery-Plan, um ihre Wirksamkeit zu bestimmen. Meinem Artikel vom April 2011 "SQL Server: Schützen Sie Daten unter allen Umständen," dies von einem Perspektive erläutert.

Disaster Recovery-Bereitschaft ist wie die Sicherheit. Eine der Richtlinien oft wiederholt ist "defense eingehende." Je mehr Optionen, die Sie für Recovery und mehr potenzielle Probleme vorhersehen und vermeiden Sie proaktiv, desto wahrscheinlicher werden Sie nach einem Notfall innerhalb Ihrer Vereinbarungen zum Servicelevel Ausfallzeiten und Datenverlust wiederherstellen können.

Paul S. Randal

**Paul S. Randal**ist Geschäftsführer von SQLskills.com, Microsoft regional Director und MVP für SQL Server. Er arbeitete an der SQL Server-Speicher-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 bloggt auf SQLskills.com/blogs/paul und Sie finden ihn auf Twitter bei Twitter.com/PaulRandal.

Verwandter Inhalt