Von: Brady Upton / Aktualisiert: 2016-06-22 / Kommentare (5) / Verwandt: Mehr > Fragmentierung und Indexwartung
Problem
Wir haben alle von Datenbank- / Indexfragmentierung gehört (und wenn nicht, lesen Sie weiter), aber was ist das? Ist es ein Problem? Woher weiß ich, ob es sich in meiner Datenbank befindet?Wie kann ich es beheben? Diese Fragen könnten ein Tipp für sich sein, aber ich werde versuchen, Ihnen in diesem Tipp eine Vorstellung von jedem zu geben.
Lösung
Ohne auf viele Details einzugehen, speichert SQL Server Daten auf 8 KB großen Seiten. Wenn wir Daten in eine Tabelle einfügen, weist SQL Server eine Seite zum Speichern dieser Daten zu, es sei denn, die eingefügten Daten betragen mehr als 8 KB, in denen sie mehrere Seiten umfassen würden.Jede Seite ist einer Tabelle zugeordnet. Wenn wir 10 Tabellen erstellen, haben wir 10 verschiedene Seiten.
Wenn Sie Daten in eine Tabelle einfügen, werden die Daten zuerst in die Transaktionsprotokolldatei übernommen. Die Transaktionsprotokolldatei ist ein sequenzieller Datensatz, dh wenn Sie Datensätze einfügen, aktualisieren und löschen, zeichnet das Protokoll diese Transaktionen von Anfang bis Ende auf. Die Datendatei auf der anderen Seite ist notsequential. Die Protokolldatei löscht die Daten in die Datendatei und erstellt überall Seiten.
Nun, da wir eine Vorstellung davon haben, wie Daten gespeichert werden, was hat das damit zu tunfragmentierung?
Es gibt zwei Arten der Fragmentierung: Interne Fragmentierung und externe Fragmentierung.
Interne SQL Server-Fragmentierung
Die interne SQL Server-Fragmentierung wird durch Seiten verursacht, die zu viel freien Speicherplatz haben.Nehmen wir an, wir haben zu Beginn des Tages eine Tabelle mit 40 Seiten, die zu 100% gefüllt sind, aber am Ende des Tages haben wir eine Tabelle mit 50 Seiten, die nur zu 80% gefüllt sind, da im Laufe des Tages verschiedene Delete- und Insert-Anweisungen ausgeführt wurden.Dies führt zu einem Problem, da wir jetzt, wenn wir aus dieser Tabelle lesen müssen, 50 Seiten anstelle von 40 scannen müssen, was zu einer Leistungsminderung führen kann.Sehen wir uns ein schnelles und schmutziges Beispiel an. Angenommen, ich habe die folgende Tabelle mit einem Primärschlüssel und einem nicht gruppierten Index für Vorname und Nachname:
Ich werde später in diesem Tipp über Möglichkeiten zur Analyse der Fragmentierung sprechen, aber im Moment können wir mit der rechten Maustaste auf den Index klicken, auf Eigenschaften und Fragmentierung klicken, um Fragmentierung und Seitenfülle anzuzeigen. Dies ist ein brandneuer Index, also bei 0% Fragmentierung.
Fügen wir 1000 Zeilen in diese Tabelle ein:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
Überprüfen wir nun unseren Index erneut:
Sie können sehen, dass unser Index zu 75% fragmentiert wird und der durchschnittliche Prozentsatz der Vollseiten (Seitenfülle) auf 80% ansteigt. Diese Tabelle ist immer noch so klein, dass eine Fragmentierung von 75% wahrscheinlich keine Leistungsprobleme verursachen würde, aber wenn die Tabelle größer wird und die Seitenzahlen zunehmen, kann es zu Leistungseinbußen kommen. Sie können auch von thescreenshot oben sehen, dass diese Tabelle von 0 Seiten zu 4 ging.
Externe SQL Server-Fragmentierung
Externe Fragmentierung wird durch fehlerhafte Seiten verursacht. Let’spreend zu Beginn des Tages haben wir einen perfekt geordneten Tisch. Während des Tages geben wir Hunderte von Update-Anweisungen aus, die möglicherweise leeren Platz auf einer Seite lassen und versuchen, Platz in andere Seiten einzufügen. Dies bedeutet, dass unser Speicher herumspringen muss, um die benötigten Daten zu erhalten, anstatt in eine Richtung zu lesen.
Analyse der SQL Server-Fragmentierung
Ist Fragmentierung also ein Problem? Ich glaube, es ist. Wenn Sie Ihre gesamte Datenbank im Speicher speichern können oder wenn Ihre Datenbank nur gelesen wird, würde ich mir keine Sorgen machen, aber die meisten von uns haben diesen Luxus nicht.Ich habe an Tausenden von Servern gearbeitet und die Analyse der Fragmentierungsstufen ist eines der ersten Dinge, die ich mir ansehe. Tatsächlich habe ich durch das Beheben der Fragmentierung eine Verbesserung der Abfrageleistung um bis zu 200% festgestellt.
Apropos Analyse der Fragmentierungsstufen Sie fragen sich vielleicht, wie wir das tun könnendies. Nun, es gibt ein paar Möglichkeiten …
DBCC SHOWCONTIG – Diese Funktion ist alt und wird in zukünftigen Versionen von SQL Server entfernt, aber wenn Sie immer noch SQL Server 2000 oder niedriger verwenden, wird dies helfen. Insteadof darüber zu schreiben, werde ich youhere zeigen, oder Sie können Chad Boyds tiphere überprüfen. Beides sind gute Ressourcen.
sys.dm_db_index_physical_stats – Diese dynamische Verwaltungsansicht (Dynamic Managementview, DMV) wurde in SQL Server 2005 eingeführt und gibt Größen- und Fragmentierungsinformationen für die Daten und Indizes der angegebenen Tabelle oder Ansicht zurück.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
Dies ist wahrscheinlich die am weitesten verbreitete Methode zur Analyse der Fragmentierung. Sie können dem obigen Screenshot entnehmen, dass ich einen Index mit dem Namen IX_RPCust_1 in der Tabelle theRPCust habe, der zu 98,83% fragmentiert ist. Weitere Informationen zu diesem DMV finden Sie hiervon Arshad Ali.
Software von Drittanbietern – Software von Drittanbietern hilft bei der Identifizierung von Fragmentierungen, sodass Sie dies nicht tun müssen. Greg Robidoux bietet hier einen guten Tipp zum SQL Defrag Manager von Idera.
SQL Server-Fragmentierung beheben
Wie können wir nun die Fragmentierung in der Datenbank beheben? Wie bei der Analyse von Indizes gibt es mehrere Möglichkeiten.
SQL Server-Wartungspläne – Wartungspläne werden mit SQLServer ausgeliefert und eignen sich für einige Aufgaben. Indexpflege gehört meiner Meinung nach nicht dazusie. Sie können dem maintenanceplan einen Rebuild-Index-Task hinzufügen oder den Index-Task reorganisieren, aber das Problem ist, dass Sie keine Logik auf den Plan anwenden können. Withoutlogic wird der Plan JEDEN Index neu erstellen / reorganisieren. Einige Indizes sind möglicherweise nicht fragmentiert, sodass sie nicht neu erstellt werden müssen. Einige Indizes haben möglicherweise eine minimale Fragmentation, sodass sie mit einer Reorganisation in Ordnung wären. Es gibt keine Möglichkeit, dies in einem maintenanceplan anzugeben. InSQL Server 2016 Es gibt neue Optionen für die Verwaltung der Fragmentierung.Benutzerdefiniertes Skript – Benutzerdefinierte Skripte sind wahrscheinlich der richtige Weg, da Sie Logik anwenden können, um die Indexfragmentierung zu überprüfen und dann zu entscheiden, ob der Index übersprungen, neu organisiert oder neu erstellt werden soll. Ich benutze die Skripte vonola Hallengren und sie tun genau das, wofür ich sie brauche. MSSQLTips.com hat auch theseresources -SQL Server Fragmentierung und Indexwartung Tipps.
Software von Drittanbietern – Software von Drittanbietern hilft bei der Defragmentierung von Indexesso Sie müssen nicht.
Nächste Schritte
- Dieser Tipp gibt Ihnen eine Vorstellung von einigen der Komponenten, die an der Indexfragmentierung beteiligt sind. MSSQLTips.com bietet umfassendere Tipps, die sich auf jedes der hier genannten Themen konzentrieren.
- Schauen Sie sich unbedingt das SQL Server-Fragmentierungs-Tutorial von Chad Boyd an, das großartige Einblicke in zahlreiche Themen zur Fragmentierung bietet
Zuletzt aktualisiert: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…