Free Press

Der Zweck der Erstellung von SQL-Indizes für Datenbanktabellen besteht darin, die Leistung der Abfragen, die Daten aus der Basistabelle lesen, zu verbessern, indem die Datenabrufprozesse beschleunigt werden. Diese transaktionalen Tabellen sind jedoch nicht statisch und ändern sich im Laufe der Zeit sehr häufig. Zu den Änderungen, die an der Datenbanktabelle vorgenommen werden, gehören das Hinzufügen neuer Datensätze, das Ändern oder Löschen vorhandener Datensätze. Da diese Änderungen auf die zugehörigen Tabellenindizes repliziert werden sollten, wird der Tabellenindex mit der Zeit fragmentiert.

Übersicht über die Fragmentierung von SQL-Server-Indizes

Es gibt zwei Haupttypen von SQL-Indexfragmentierung: interne und externe Fragmentierung. Die interne Fragmentierung wird durch das Einfügen eines neuen Datensatzes oder das Aktualisieren eines vorhandenen Datensatzes mit Werten verursacht, die nicht in den aktuellen freien Speicherplatz auf der Datenseite passen und dazu führen, dass die Seite in zwei Seiten aufgeteilt wird, um den neuen Wert unterzubringen. In diesem Fall verschiebt die SQL-Server-Engine etwa 50 % der Daten von der aktuellen Seite auf die neue Datenseite, die durch die Aufteilung entsteht, um den SQL-Indexbaum im Gleichgewicht zu halten. Interne Fragmentierung kann auch durch zufällige Löschoperationen verursacht werden, die zu leerem Platz auf den Datenseiten führen. Diese Art von Operationen lässt die Datenseiten ungefüllt und führt zu einer größeren SQL Server-Indexgröße und einer langsameren Leistung, da mehr Datenseiten gelesen werden müssen, um die angeforderten Daten abzurufen.

Die externe Fragmentierung wird verursacht, wenn die Datenseiten des SQL Server-Index in der physischen Datenbankdatei verstreut sind, weil die logische Reihenfolge des SQL Server-Index nicht mit der physischen Reihenfolge auf dem zugrunde liegenden Laufwerk übereinstimmt. Externe Fragmentierung kann durch zufällige Löschvorgänge verursacht werden, die eine Reihe von Extent-Seiten leer lassen, während der Extent selbst reserviert bleibt. Diese Art der Fragmentierung führt zu einer Leistungsverschlechterung, da das Springen zwischen den verstreuten Datenseiten mehr Zeit und Ressourcen in Anspruch nimmt, um die angeforderten Daten vom zugrunde liegenden Laufwerk abzurufen.

Der Grund, warum wir uns so sehr um die SQL-Indexfragmentierung kümmern, ist, dass der Index, den Sie erstellt haben, um die Systemleistung zu verbessern, die Leistung Ihrer Abfragen beeinträchtigen kann, wenn er stark fragmentiert ist. Stellen Sie sich den Unterschied in der Zeit und der Anzahl der Festplattenlesevorgänge zwischen dem Lesen einer bestimmten Anzahl von Zeilen aus einem gesunden SQL-Index, in dem die Daten in zusammenhängenden Datenseiten innerhalb der Datenbankdatei gespeichert sind, und dem Lesen der gleichen Anzahl von Zeilen aus einem stark fragmentierten SQL-Server-Index, in dem die Daten in nicht zusammenhängenden Datenseiten innerhalb der Datenbankdatei verstreut sind, vor.

Wenn festgestellt wird, dass der SQL-Tabellenindex fragmentiert ist, müssen Sie das Fragmentierungsproblem beheben, indem Sie eine SQL-Server-Index-Reorganisation oder einen Index-Neuaufbau auf der Grundlage des Prozentsatzes der SQL-Indexfragmentierung durchführen. So kann beispielsweise ein SQL Server-Index mit einem Fragmentierungsgrad von weniger als 30 % reorganisiert werden, während ein SQL-Index mit einem Fragmentierungsgrad von mehr als 30 % neu erstellt werden sollte.

• Hinweis: Weitere Informationen finden Sie unter Pflegen von SQL Server-Indizes.

Sie können SQL Server-Indexfragmentierungs- und Seitenaufteilungsprobleme proaktiv überwinden, indem Sie den Index richtig entwerfen und die Indexerstellungsoptionen Fill Factor und pad_index mit den propervalues einstellen.

• Hinweis: Weitere Informationen finden Sie unter SQL Server Index Operations.

SQL Server Index Fragmentation Report

Es gilt nicht als Best Practice, einfach einen SQL-Index in Ihrer Datenbanktabelle zu erstellen, um von der Verbesserung der Datenabrufoperationen zu profitieren und diesen SQL Server-Index für immer zu belassen, ohne seine Fragmentierung kontinuierlich zu überwachen und zu pflegen.

SQL Server stellt uns mehrere Methoden zur Verfügung, mit denen wir den Prozentsatz der Fragmentierung des SQL-Indexes überprüfen können.

SQL Server Index Properties

Die erste Methode ist die Seite Fragmentierung im Fenster SQL Server Index Properties. Klicken Sie mit der rechten Maustaste auf den Index, den Sie überprüfen möchten, und wählen Sie die Option Eigenschaften. Auf der Seite „SQL Server-Indexeigenschaften“ können Sie die Seite „Fragmentierung“ aufrufen. Im Abschnitt „Fragmentierung“ sehen Sie den Prozentsatz der Indexfragmentierung und wie voll die SQL Server-Indexseiten sind, wie unten dargestellt:

In wenigen Schritten können Sie den Prozentsatz der SQL Server-Indexfragmentierung des ausgewählten Index im Fenster „Indexeigenschaften“ überprüfen. Was aber, wenn Sie einen Überblick über den Fragmentierungsprozentsatz für alle Tabellenindizes oder alle Datenbanktabellenindizes haben möchten? In diesem Fall müssen Sie ihn einzeln überprüfen. Daher reicht eine einzige Arbeitswoche für einen Datenbankbericht nicht aus!

sys.dm_db_index_physical_stats

Eine andere Methode, die zur Überprüfung des Fragmentierungsprozentsatzes für die Datenbankindizes verwendet werden kann, ist die Abfrage der dynamischen Verwaltungsfunktion sys.dm_db_index_physical_stats, die zum ersten Mal in SQL Server 2005 als Ersatz für den veralteten Befehl DBCC SHOWCONTIG eingeführt wurde. Sie liefert Informationen über die Größe und den Fragmentierungsprozentsatz für die Datenbankindizes.

Um aussagekräftige Informationen über den Fragmentierungsprozentsatz aller Indizes unter der angegebenen Datenbank von der DMF sys.dm_db_index_physical_stats zu erhalten, müssen Sie sie mit der DMV sys.indexes DMV verbinden, wie im folgenden T-SQL-Skript:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

Basierend auf Ihren SQL-Entwicklungskenntnissen können Sie die Filterung durchführen, die Sie benötigen, um die SQL-Indexfragmentierung im gewünschten Bereich abzurufen, ohne die Option, eine Zusammenfassung für die gesamten Instanzdatenbanken zu erhalten, wobei zu berücksichtigen ist, dass die angezeigten Daten seit dem letzten Neustart des SQL Server-Dienstes vorliegen. Das Ergebnis in unserem Fall sieht wie folgt aus:

SQL Server Index Physical Statistics Standard Report

SQL Server bietet uns mehrere Standardberichte, die nützliche statistische Informationen über die ausgewählte Datenbank enthalten. Der Bericht „Index Physical Statistics“ ist einer der SQL Server-Standardberichte, der statistische Informationen über die SQL Server-Indexpartitionen, den Fragmentierungsprozentsatz und die Anzahl der Seiten in jeder SQL-Indexpartition auf Datenbankebene seit dem letzten Neustart des SQL Server-Dienstes liefert.

Um den Bericht „SQL Server Index Physical Statistics“ anzuzeigen, klicken Sie mit der rechten Maustaste auf Ihre Datenbank, wählen Sie „Berichte -> Standardberichte“ und wählen Sie dann den Bericht „Index Physical Statistics“, wie unten dargestellt:

Der generierte Bericht auf Datenbankebene, der nicht gefiltert werden kann und bei dem die Ergebnisse der einzelnen Tabellenindizes manuell erweitert werden müssen, sieht wie unten dargestellt aus:

ApexSQL Defrag

ApexSQL Defrag ist ein SQL-Indexdefragmentierungstool eines Drittanbieters, das einfach verwendet werden kann, um die SQL Server-Indexfragmentierung und -Verwendungsinformationen zu überprüfen und die geeignete Lösung für das Indexfragmentierungsproblem durchzuführen oder zu planen.

Von einer zentralen Stelle aus können Sie die Fragmentierungs- und Nutzungsinformationen auf verschiedenen Ebenen analysieren und überprüfen, einschließlich der SQL-Indexfragmentierungs- und Nutzungsinformationen auf der SQL Server-Indexebene, der Tabellenebene, der Datenbankebene und schließlich auf der Ebene der gesamten SQL Server-Instanz. ApexSQL Defrag ermöglicht auch die Konfiguration des Füllfaktors für die Tabellenindizes, die Erstellung von Warnungen für alle Defragmentierungsaufträge und Richtlinien sowie das Versenden von E-Mail-Benachrichtigungen für erfolgreiche oder fehlgeschlagene Aufträge.

Die angezeigten Informationen bieten einen guten Überblick über die SQL-Indexgröße und den Fragmentierungsprozentsatz, der durch einen Neustart des SQL Server-Dienstes oder einen Server-Neustart nicht beeinträchtigt wird. Dies liegt daran, dass diese Informationen in einer zentralen Repository-Datenbank für das ApexSQL Defrag-Tool gespeichert werden.

Installation von ApexSQL Defrag

ApexSQL Defrag kann einfach vom ApexSQL Download Center heruntergeladen und auf Ihrem Server installiert werden, indem Sie dem einfachen Installationsassistenten folgen, wie unten gezeigt:

Zuerst werden Sie aufgefordert, das Dienstkonto anzugeben, das als Ausführungskontext für den ApexSQL Defrag-Agenten verwendet wird und Befehle an die SQL Server-Instanz übermittelt, wie folgt:

Nachdem Sie das ApexSQL Defrag Dienstkonto angegeben haben, werden Sie aufgefordert anzugeben, wo das ApexSQL Defrag Tool installiert werden soll und ob eine Verknüpfung für das Tool auf dem Desktop erstellt werden soll, wie unten gezeigt:

Wenn Sie überprüfen, dass der angegebene Pfad mindestens 127 MB freien Speicherplatz enthält, wird ApexSQL Defrag erfolgreich auf Ihrem Server installiert. Außerdem wird die folgende Meldung angezeigt, die Ihnen die Möglichkeit gibt, ApexSQL Defrag direkt zu starten:

Wenn Sie ApexSQL Defrag zum ersten Mal ausführen, werden Sie aufgefordert, die Erstellung der zentralen Repository-Datenbank zu bestätigen, in der diese historischen Daten und Konfigurationsdaten gespeichert werden:

Neuen Server hinzufügen

Nachdem Sie die Repository-Datenbank erstellt haben, wird ApexSQL Defrag ohne SQL-Instanz gestartet. Um den Prozentsatz der SQL-Indexfragmentierung auf einer bestimmten SQL Server-Instanz zu überprüfen, müssen Sie diese Instanz zu diesem Tool hinzufügen. Klicken Sie dazu auf der Registerkarte Home auf die Schaltfläche Hinzufügen, wie unten dargestellt:

Im Fenster Mit SQL Server verbinden werden Sie aufgefordert, den Namen der zu verwendenden SQL Server-Instanz und die für die Verbindung mit diesem Server erforderlichen Anmeldeinformationen anzugeben:

SQL Server Index Analysis

Wenn der Server hinzugefügt wurde, können Sie auf der Registerkarte Fragmentierung eine neue Prüfung aller SQL-Indexfragmentierungsinformationen durchführen:

ApexSQL Defrag bietet Ihnen die Möglichkeit, alle Indizes auf dieser Instanz auf der Grundlage von drei Modi zu analysieren:

• DETAILED: bei dem alle Daten und SQL-Indexseiten während des Scanvorgangs gelesen werden
• SAMPLED: bei dem nur 1 % der Seiten gelesen werden, wobei berücksichtigt wird, dass der SQL Server-Index mehr als 10.000 Seiten enthält
• LIMITED: In diesem Fall werden nur die Seiten gelesen, die sich auf der übergeordneten Ebene des B-Baums befinden

Um die SQL Server-Indexinformationen der angeschlossenen SQL Server-Instanz zu analysieren, klicken Sie auf die Schaltfläche „Analysieren“ auf der Registerkarte „Fragmentierung“ und wählen Sie „Schnell“ oder „Tief“ aus, um eine Analyse durchzuführen. Wir führen eine tiefe Analyse durch, um vollständige Informationen zu erhalten, wie unten gezeigt:

Beobachten Sie weiterhin den Fortschrittsbalken am unteren Rand des Tools, um den Fortschritt der Analyse zu überprüfen, wie folgt:

Und ein umfassender Bericht, der vollständige Informationen über alle SQL-Indizes enthält, die unter jeder Datenbank dieser SQL Server-Instanz erstellt wurden, wird wie unten gezeigt angezeigt:

Mit der Möglichkeit, zwischen den verschiedenen Ebenen – SQL Server-Instanz, Datenbank, Tabelle oder Index – mit einem einzigen Mausklick im selben zentralen Fenster zu wechseln und die abgerufenen Ergebnisse auf der Grundlage eines bestimmten Fragmentierungsgrads oder Schwellenwerts zu filtern, wie unten dargestellt:

Darüber hinaus können Sie eine beliebige Spalte aus den Berichtsspalten ziehen, um den Gesamtbericht auf der Grundlage dieser Spaltenwerte zu gruppieren, wie im folgenden Beispiel, in dem wir die Berichtsdaten auf der Grundlage des Tabellennamens gruppiert haben:

ApexSQL Defrag Reports

ApexSQL Defrag stellt uns eine Reihe von Berichten zur Verfügung, die statistische Informationen über die Datenbankindizes anzeigen. Diese Berichte können auf der Registerkarte „Berichte“ konfiguriert und überprüft werden:

Der erste Bericht ist der Gesamtbericht, der eine grafische Darstellung der SQL-Indexfragmentierung und der Speicherplatznutzung auf der ausgewählten Ebene anzeigt. Wenn Sie z. B. auf den Instanznamen klicken, zeichnet der Bericht den Fragmentierungsprozentsatz und die Speicherplatznutzung auf der Ebene der SQL Server-Instanz, wie unten dargestellt:

Wenn Sie im Server-Explorer-Fenster auf eine beliebige Datenbank klicken, ändert der Bericht automatisch die Ansicht, um das Diagramm auf Datenbankebene anzuzeigen, wie unten dargestellt:

Der zweite Bericht, der auch auf verschiedenen Ebenen angezeigt werden kann, ist der Statistikbericht. Dieser Bericht zeigt statistische Informationen über die Gesamtgröße und Fragmentierung der Indizes und die Verteilung der Indextypen auf der ausgewählten Ebene, wie unten dargestellt:

Der dritte Bericht ist der Top-10-Bericht, der die Top-10 geclusterten und nicht geclusterten Indizes mit der größten Größe und dem höchsten Prozentsatz an SQL-Indexfragmentierung auf der angegebenen Ebene anzeigt. Der folgende Bericht zeigt beispielsweise die 10 teuersten Indizes auf der Grundlage von Größe und Fragmentierung auf der Ebene der SQL-Server-Instanz an:

Wenn Sie auf eine bestimmte Datenbank heruntergehen, zeigt der Bericht automatisch die 10 teuersten Indizes auf der Grundlage von Größe und Fragmentierung auf der ausgewählten Datenbankebene an, wie unten dargestellt:

Wenn Sie tief in eine bestimmte Tabelle unter dieser Datenbank eintauchen, zeigt der Bericht die 10 teuersten Indizes auf der Grundlage der Größe und Fragmentierung wie folgt an:

ApexSQL Defrag Report Export

ApexSQL Defrag ermöglicht es, die Fragmentierungsberichte in verschiedene Dateiformate zu exportieren, darunter PDF, IMG, HTML, CS und XML, mit der Möglichkeit, die Berichtseinstellungen vor der Erstellung anzupassen. Klicken Sie auf der Registerkarte „Berichte“ auf die Schaltfläche „Optionen“:

Im Fenster „Exportoptionen bearbeiten“ können Sie die verschiedenen Optionen konfigurieren, angefangen von der Namenskonvention der erzeugten Datei, dem Pfad, in dem die Datei gespeichert wird, der Seitenausrichtung und schließlich dem Inhalt jedes Berichts, wie unten gezeigt:

Um beispielsweise den erstellten Gesamtbericht auf der angegebenen Ebene zu exportieren, klicken Sie auf die Schaltfläche Exportieren auf der Registerkarte Berichte und wählen Sie den Dateityp aus, in den der Gesamtbericht exportiert werden soll. In wenigen Sekunden wird der Bericht erstellt und Sie werden gefragt, ob Sie den erstellten Bericht öffnen möchten. Klicken Sie auf Ja, um den Bericht direkt zu öffnen, wie unten gezeigt:

Außerdem können Sie den Top-10-Bericht auf der angegebenen Ebene exportieren, indem Sie auf die Schaltfläche „Exportieren“ auf der Registerkarte „Berichte“ klicken und den Dateityp für den Export angeben; der Bericht wird dann schnell erstellt und Sie werden gefragt, ob Sie den erstellten Bericht direkt anzeigen möchten. Klicken Sie auf Ja, um die generierte Datei zu öffnen, wie unten gezeigt:

Aus den vorangegangenen Beispielen geht hervor, dass wir mit dem ApexSQL Defrag-Tool die Informationen zur prozentualen Fragmentierung des SQL-Indexes leicht überprüfen und analysieren können, und zwar auf verschiedenen Ebenen, angefangen von der SQL Server-Instanzebene bis hin zur SQL Server-Indexebene, und sie in verschiedenen exportierbaren Formaten anzeigen können, ohne dass die angezeigten Informationen durch einen Server- oder Dienstneustart beeinträchtigt werden, da sie in einer Repository-Datenbank gespeichert werden.