SQL – Fragen und Antworten: Verwalten von Protokollen und Indizes

Artikel
08/30/2016

Einige der wichtigsten Strategien zur effizienten Ausführung von SQL Server umfassen die Aufbewahrung von Protokollsicherungen und die sorgfältige Pflege von Indizes.

Paul S. Randal

Die Zertifikatkette nicht unterbrechen

Frage: Ich habe eine Sicherungsstrategie für unsere Datenbanken definiert wurde. Plan umfasst Transaktionsprotokollsicherungen, sodass die Notfall-Wiederherstellung mit wenig Datenverlust durchgeführt werden kann. Ich habe wurden untersucht einige der Probleme, die ich auftreten könnte, und ich habe mehrmals gelesen, dass ich nicht auf die Protokollsicherungskette unterbrochen werden muss. Können Sie erklären, was dies ist und wie es unterbrochen werden kann?

A: Das ist eine gute Frage, und es ist etwas, das häufig übersehen. Die Protokollsicherungskette (manchmal einfach die Kette Protokoll genannt) bezieht sich auf ein ununterbrochener Folge von Transaktionsprotokollsicherungen, die die Zeit aus der letzten Datensicherung der (vollständige oder differenzielle Sicherung) bis zu dem Punkt abdecken, an dem Sie wiederherstellen möchten. Eine Wiederherstellungssequenz Beispiel würde wie folgt aussehen:

Der letzten vollständigen Datenbanksicherung
Dann der letzten differenziellen Datenbanksicherung
Dann alle Transaktionsprotokollsicherungen danach ausgeführt

Die meisten Menschen behalten weitere Transaktion Protokollsicherungen, um den Fall, dass eine der Sicherungen beschädigt wird und Sie eine weniger aktuelle Datensicherung wiederherstellen müssen. Sie erhalten weitere Informationen zu Sicherungen und Wiederherstellungen in zwei TechNet Magazine Artikel, die ich im letzten Jahr habe, “ Grundlegendes zu SQL Server-Sicherungen ” und “ mit Sicherungen wiederherstellen nach Notfällen . ”

Wenn keines der erforderlichen Protokollsicherungen beschädigt sind oder nicht verfügbar für die Wiederherstellungssequenz Sie ausgewählt haben, ist die Protokollsicherungskette unterbrochen, und nicht zum Wiederherstellen der Vergangenheit, die zeigen. Wenn nur eine der Transaktionsprotokollsicherungen beschädigt ist, um eine Wiederherstellung unter Verwendung der Option WITH CONTINUE_AFTER_ERROR erzwingen möglicherweise werden. Würde eine Wiederherstellung von beschädigten Transaktionsprotokolleinträge erzwingen, die eine Beschädigung der Datenbank führen würde. Ich wäre sehr zum Erzwingen von diese Art der Wiederherstellung zögern.

Eine Operation, die die einer erforderlichen Protokollsicherung nicht verfügbar ist, wird eine Protokollsicherung “ außerhalb des Bereichs ” durchgeführt, ohne zu gewährleisten, dass eine Protokollsicherung bleiben. Sie könnten dies z. B. eine Kopie an einem Entwickler geben tun. Diese Protokollsicherung ist Teil der Protokollsicherungskette ist die einzige, die Protokolldatensätze generiert, die seit der vorherigen Protokollsicherung enthält.

D. h., wenn Sie die Option WITH COPY_ONLY verwenden, die die Protokollsicherung ausgeführt, jedoch können auch nächsten Protokollsicherung effektiv sichern den gleichen Satz von Protokolleinträgen. Finden Sie in meinem Blogbeitrag “ BACKUP WITH COPY_ONLY, ” um weitere Details anzuzeigen, wie Sie vermeiden, unterbrechen die backup-Kette .

Eine weitere häufige ein Vorgang unterbrechen die Protokollsicherungskette ist, die verhindert, dass eine Transaktionsprotokollsicherung durchführen, während des normalen Betriebs. Diese Operationen zählen:

Modellieren und anschließend wieder auf FULL oder BULK_LOGGED SIMPLE Wiederherstellung wechseln
Sichern das Protokoll in SQL Server 2005 und früheren Versionen mithilfe der BACKUP LOG … WITH NO_LOG oder TRUNCATE_ONLY-Optionen
Wiederherstellen einer Datenbank von einem Datenbanksnapshot der

Sie müssen zum Durchführen einer Datensicherung (vollständige oder differenzielle Sicherung) nach dieser Vorgänge zum Zulassen von Protokollsicherungen, um den Vorgang fortzusetzen. Neustarten der Protokollsicherungskette wird aufgerufen.

Eine Sache: Im Gegensatz zur gängigen Mythos eine vollständige oder differenzielle Sicherung wird nicht unterbrechen die Protokollsicherungskette und in der Tat hat keine Auswirkungen auf die Protokollsicherungen überhaupt.

Die Indizes Cluster

Frage: Anzahl der Tabellen in der Datenbank von SQL Server 2008 Don ’t einen gruppierten Index verfügen. Ich habe gehört, dass ich Leistungsprobleme mit weitergeleiteten Datensätze verursacht zusätzliche EA-Vorgängen haben könnte. Können Sie mir mitteilen wie ich dies prüfen können, und wie ich dabei vorgehen?

A: Ein Heap ist eine Tabelle, die einen gruppierten Index besitzt. Es ist grundsätzlich ungeordnet. Für diesen Leser, die Don ’t Wissen über die weitergeleiteten Datensätze in Heaps und wie diese verwendet werden, meinem Blogbeitrag, “ Weiterleitung und weitergeleiteten Datensätze und die Rückseite Zeigergröße , ” weitere Details finden Sie unter. Weitergeleiteten Datensätze in Heaps können zu sehr zufälligen e/a-Vorgänge während der Abfrageverarbeitung der führen, was wiederum zu schlechter Leistung führt.

Die einfachste Möglichkeit zum Überprüfen, ob Sie die weitergeleitete Datensätze verarbeiten Abfragen haben besteht darin, die weitergeleitete Datensätze/Sekunde Leistungsindikator im Leistungsobjekt Zugriffsmethoden betrachten. Verwenden Sie die dynamische Verwaltungsfunktion sys. dm_db_index_physical_stats mit Details-Modus an einige der Tabellen in der Datenbank, und die Anzahl der weitergeleiteten Datensätze für jede Tabelle wird in der Spalte Forwarded_record_count der Ausgabe zurückgegeben. In diesem Thema in der Onlinedokumentation für weitere Details finden Sie in.

Die schlechteste Methode zum Entfernen von weitergeleiteten Datensätze besteht darin, einen gruppierten Index erstellen, und legen Sie es erneut. Dadurch werden alle nicht gruppierten Indizes für die Tabelle automatisch zweimal neu erstellt werden, die einen enormen Verschwendung von Ressourcen ist. Finden Sie in meinem Blogbeitrag Weitere Details: “Was nicht gruppierte Indizes geschieht, wenn die Struktur der Tabelle geändert wird?”

Am einfachsten können Sie endgültig entfernen und Verweigern von weitergeleiteten Datensätze in Heaps ist zum Erstellen von gruppierten Indizes. Ich möchte nicht erhalten, die in einem Vs “ gruppierten Index. Heap ” Debatte hier dazu, warum die Indizes in den meisten Fällen anstelle des Heaps gruppiert haben sollte. Meine Frau Kimberly Tripp “ Clustering Key ” Blogbeitrag Reihe auf diesem weitere Details finden Sie in. Ich empfehle Ihnen zum Auswerten von gruppierten Indizes verwenden.

Wenn Datensätze in der Größe erhöhen, kann dies weitergeleiteten Datensätze führen, wenn nicht genügend Speicherplatz vorhanden ist. Eine weitere Möglichkeit, um zu verhindern, dass ist weitergeleitete Datensätze, deshalb zum Ändern der Größe der Datensätze zu verhindern. Dies könnte bedeuten, z. B. bei Verwendung als Standardwerte für Spalten variabler Länge.

In SQL Server 2008 ist eine neue ALTER TABLE … REBUILD enthalten, mit dem Sie die Heaps neu zu erstellen. Dies funktioniert auf die gleiche Weise, dass die Anweisung ALTER INDEX … REBUILD Indizes neu erstellen kann. Microsoft hat diese Anweisung zur Unterstützung der Datenkomprimierung Funktion jedoch für unsere Zwecke angewendet werden. In diesem Thema in der Onlinedokumentation für weitere Details finden Sie in.

Index-Wartung

Frage: Unsere Wartungsroutinen Index online Index Neukompilierungen verwenden geändert haben, aber immer noch unerwünschte Blockierungsprobleme manchmal bei der Ausführung der Wartungsroutinen. Worauf ist dies zurückzuführen? Ich dachte Onlineindexvorgänge sperren, nicht verwenden, damit alle Sperren sollte nicht angezeigt. Ist dieses erwartete Verhalten oder dies einen Fehler bin?

A: Sie sehen die erwartetes Verhalten. Es ist eine gemeinsame Tabellensperre zu Beginn der Operation erforderlich ist, während der Vorgang (sehr schnellen Prozess) initialisiert. Dies wird sofort gelöscht. Diese Sperre muss wie jede andere Sperre Warteschlange sein, und es wird verhindert, dass neuen Abfragen Änderungen an der Tabelle vornehmen, bis Sie erteilen und die Sperre wieder freigeben können.

Diese Sperre kann nicht erhalten werden, bis Sie alle aktuell ausgeführten Abfragen von Änderungen abgeschlossen haben. Dies kann eine Weile abhängig von der Arbeitsauslastung dauern. Dies bedeutet, dass zu Beginn einer Operation online Index Sperren auftreten kann.

Am Ende des Vorgangs müssen Sie eine Schemaänderung Sperre – stellen Sie sich dies als eine exklusive Sperre – damit Sie abschließen kann. Dies geschieht ebenfalls extrem schnell. Legen Sie es sofort. Diese Sperre wird jede Art von neue Abfragen in der Tabelle (Lesen oder Schreiben) bis Sie gewähren und Freigabe der Sperre verhindert.

Wiederum können Sie diese Sperre zu erhalten, bis SQL alle derzeit ausgeführten Lesevorgänge abgeschlossen ist oder Abfragen schreiben. Dies bedeutet jedoch erneut die Möglichkeit besteht, zu blockieren.

Zusammenfassen, obwohl der Name des Features Onlineindexvorgänge ist, ist es immer noch zwei kurzfristige Sperren erforderlich, die Blockierungen verursachen können. Der Gewinn über herkömmliche offline Index-Operationen ist, dass für den größten Teil der Index-Operation keine Sperren sind und Parallelität wird also insgesamt erhöht. Im Whitepaper “ Online Indizierung-Vorgänge in SQL Server 2005 ” weist sehr viel mehr Details über die Funktionsweise dieser Operationen.

Index Maintenance Zeit zu reduzieren.

Frage: Ich habe einige Systeme geerbt, reguläre Indexwartung Aufträge lange dauern ausführen und eine Vielzahl von e/A erzeugen, wobei ein ich Don Neukompilierungen Index durchführen, da die Indizes fragmentiert abrufen werden nicht. Ich möchte die Arbeit erledigt wird, reduzieren wie ich keine Leistungsgewinn erhalten habe. Können Sie eine Strategie zu empfehlen?

A: Dies ist ein relativ häufig auftretendes Problem. Es sind Wiederherstellungsschlüssel gegenüber Index Wartungsaufträge bestimmen, welche Indizes neu erstellen oder neu zu organisieren.

Die meisten Menschen, führen Sie die sys. dm_db_index_physical_stats dynamic Management-Funktion (erwähnt) für alle Indizes in der Datenbank dann wählen, ob neu erstellen, neu anordnen oder kein. Sie basieren diese Entscheidung auf die Avg_fragmentation_in_percent, die Page_count und die Avg_page_space_used_in_percent Werte mithilfe einer WHERE-Klausel für die Ausgabe.

Das Problem ist, dass Indexfragmentierung im Arbeitsspeicher wie andere Statistik nicht gespeichert ist. Diese Funktion muss lesen und Verarbeiten von jeder Index bestimmt das Ausmaß der seine Fragmentierung. Wenn ein Großteil der Indizes in der Datenbank statisch sind oder sehr langsam (hinsichtlich der Fragmentierung) ändern, wird nicht dann Sie neu erstellt oder neu angeordnet werden. Überprüfen die Fragmentierung bei jeder Ausführung ein Auftrags Indexwartung ist im Wesentlichen Zeitverschwendung.

Die meisten dynamische Verwaltungsansichten unterstützen “ Prädikat Push-ab, ”, wobei nur die Daten verarbeitet werden, die das Prädikat in der WHERE-Klausel entsprechen. Sys. dm_db_index_physical_stats ist jedoch eine Funktion keine Ansichten, so dass dies nicht möglich. Daher müssen Sie manuell Filtern und nur die Funktion, um diese Indizes zu verarbeiten, die Sie kennen das Potenzial fragmentiert werden und erfordert möglicherweise, Neuerstellen oder Neuorganisation bitten.

Ich empfehle die Fragmentierung im Laufe von ein paar Wochen überwachen. Auf diese Weise erhalten Sie eine Vorstellung der Indizes, sind lohnt es sich auf Fragmentierung überprüfen, statt alles überprüfen. Nachdem Sie die Liste der Indizes haben, erstellen Sie eine Tabelle mit dem Namen der Tabelle, einen Index und die Fragmentierung der Schwellenwert für die Aktion. Sie finden möglicherweise, dass einige Indizes mehr Fragmentierung vor als andere Leistung beeinträchtigt sein können. Dadurch werden, die Sie, verwenden um den Auftrag Indexwartung Laufwerk “ Treiber Tabelle ”. Es sollten über alle Indizes in der Tabelle beschriebenen Schleife und nur die sys. dm_db_index_physical_stats-Funktion ausgeführt, auf Sie.

Ich habe dies für mehrere Clients implementiert. In einigen Fällen hat es die Common Language Runtime die Indexwartung Auftrags von Stunden auf 15 Minuten oder weniger reduziert. Das ist nur aus dieser Funktion auf statische Indizes nicht ausgeführt. Sie könnten auch einen Schritt weitergehen und verfolgen wie oft ein Index neu erstellt wird und ggf. ändern den Index FILLFACTOR automatisch festlegen, was hoffentlich zu einer weiteren Verringerung der Arbeit, die der Indexwartung Auftrag ausgeführt.

Weitere Informationen zu den verschiedenen Methoden Indexwartung finden Sie in meinem Blogbeitrag, “ Bedeutung der Indexwartung ” und eine detaillierte Erläuterung was im Hintergrund der Funktion geht auch finden Sie in meinem Blogbeitrag “ in sys. dm_db_index_physical_stats . ”

Dank an Kimberly l. Tripp von SQLskills.com für Ihre technischen Überprüfung der Artikel dieses Monats.

Paul Randal

Paul S. Randal ist die Verwaltung von Director SQLskills.com, einen regionalen Microsoft-Leiter und SQL Server-MVP. Er gearbeitet, die SQL Server-Speichermodul Team bei Microsoft von 1999 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und während der Entwicklung von SQL Server 2008 für das Kernspeichermodul zuständig ist. 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 können ihn auf Twitter am Twitter.com/PaulRandal finden.