In diesem Artikel erfahren Sie, wie Sie Indexfragmentierung in SQL Server identifizieren und beheben können. Die Identifizierung der Indexfragmentierung und die Indexwartung sind wichtige Bestandteile der Datenbankwartung. Microsoft SQL Server aktualisiert die Indexstatistiken bei jeder Einfüge-, Aktualisierungs- oder Löschaktivität in einer Tabelle. Die Indexfragmentierung ist der prozentuale Wert der Indexleistung, der von SQL Server DMV abgefragt werden kann. Entsprechend dem Indexleistungswert können Benutzer die Indizes in Wartung nehmen, indem sie den Fragmentierungsprozentsatz mit Hilfe der Rebuild- oder Reorganize-Operation überarbeiten.
Warum variiert der Indexfragmentierungsprozentsatz?
Der Indexfragmentierungsprozentsatz variiert, wenn die logische Seitenreihenfolge nicht mit der physischen Seitenreihenfolge in der Seitenzuweisung eines Index übereinstimmt. Bei der Datenänderung in der Tabelle können die Informationen auf der Datenseite in der Größe verändert werden. Die Seite war vor dem Aktualisierungsvorgang der Tabelle randvoll. Mit einer Aktualisierungsoperation der Tabelle konnte jedoch freier Platz auf der Datenseite gefunden werden. Die Benutzer können die störende Seitenreihenfolge bei der massiven Löschoperation auf der Tabelle beobachten. Zusammen mit den Aktualisierungs- und Löschvorgängen ist die Datenseite nicht mehr voll oder leer. Daher erhöht nicht genutzter freier Speicherplatz mit zunehmender Fragmentierung die Diskrepanz zwischen logischer Seite und physischer Seite, was zu einer schlechteren Abfrageleistung führen kann und auch mehr Serverressourcen verbraucht.
Es ist wichtig zu betonen, dass die Indexfragmentierung die Abfrageleistung nur beim Page Scan beeinflusst. In solchen Fällen steigt die Wahrscheinlichkeit, dass auch andere SQL-Anfragen eine schlechte Leistung aufweisen, da eine Abfrage mit einem stark fragmentierten Index über die Tabelle mehr Zeit zur Ausführung benötigt und mehr Ressourcen wie Cache, CPU und IO verbraucht. Daher ist es für die übrigen SQL-Anfragen schwierig, die Operation mit den inkonsistenten Serverressourcen zu beenden. Es kann sogar zu Blockierungen bei Aktualisierungs- und Löschvorgängen kommen, da der Optimierer bei der Erstellung des Ausführungsplans für die Abfrage keine Informationen über die Indexfragmentierung sammelt.
Es kann eine Reihe von Indizes auf einer einzigen Tabelle mit der Kombination verschiedener Spalten erstellt werden, und jeder Index kann einen unterschiedlichen Fragmentierungsprozentsatz haben. Bevor man nun einen Index in die Wartung nimmt, muss man diesen Schwellenwert in der Datenbank ermitteln. Die folgende T-SQL-Anweisung ist ein effizienter Weg, ihn mit Objektdetails zu finden.
Ermitteln des Indexfragmentierungsstatus mit der T-SQL-Anweisung
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT S.name as ‚Schema‘,
T.name as ‚Tabelle‘,
I.name as ‚Index‘,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
|
Hier können wir sehen, dass der maximale durchschnittliche Fragmentierungsprozentsatz 99% beträgt, was mit einer Aktion zur Verringerung der Fragmentierung mit der Wahl zwischen REBUILD oder REORGANIZE angegangen werden muss. REBUILD oder REORGANIZE ist der Indexwartungsbefehl, der mit der ALTER INDEX-Anweisung ausgeführt werden kann. Benutzer können diesen Befehl auch mit SSMS ausführen.
Index neu erstellen und reorganisieren mit SQL Server Management Studio (SSMS)
Suchen und erweitern Sie die Tabelle im Object Explorer >> Öffnen Sie Indizes >> Klicken Sie mit der rechten Maustaste auf den Zielindex >> Neu erstellen oder reorganisieren.
Wie in der obigen Abbildung zu sehen ist, sind REBUILD und REORGANIZE die beiden verfügbaren Optionen, um den Trimmvorgang auf der Seite auszuführen. Idealerweise sollte dieser Vorgang in den Randzeiten durchgeführt werden, um Auswirkungen auf andere Transaktionen und Benutzer zu vermeiden. Microsoft SQL Server Enterprise Edition unterstützt Index-Online- und -Offline-Funktionen mit Index REBUILD.
REBUILD INDEX
INDEX REBUILD löscht immer den Index und reproduziert ihn mit neuen Indexseiten. Dieser Vorgang kann über eine Online-Option (Enterprise Edition) mit dem ALTER INDEX-Befehl parallel ausgeführt werden, was die laufenden Anfragen und Aufgaben einer ähnlichen Tabelle nicht beeinträchtigt.
REBUILD Index kann mit den folgenden SQL-Befehlen online oder offline gesetzt werden:
|
1
2
3
4
5
|
–Basic Rebuild Command
ALTER INDEX Index_Name ON Table_Name REBUILD
–REBUILD Index mit ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Wenn ein Benutzer den REBUILD INDEX offline durchführt, dann ist die Objektressource (Tabelle) des Indexes bis zum Abschluss des REBUILD-Prozesses nicht zugänglich. Dies wirkt sich auch auf zahlreiche andere Transaktionen aus, die mit diesem Objekt verbunden sind. Die Rebuild-Index-Operation erstellt den Index neu. Daher generiert er neue Statistiken und fügt die Protokolldatensätze des Index in der Datenbanktransaktionsprotokolldatei hinzu.
Nehmen wir zum Beispiel vor dem Neuaufbau des Index die aktuelle Seitenzuweisung für den Index der AdventureWorks-Datenbank, die Tabelle „Sales.OrderTracking“ und den Index „IX_OrderTracking_CarrierTrackingNumber“.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‚AdventureWorks‘), OBJECT_ID(‚Sales.OrderTracking‘),NULL, NULL, ‚DETAILED‘) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‚IX_OrderTracking_CarrierTrackingNumber‘
ORDER BY allocated_page_page_id
|
Hier, 1961 Seiten sind in der Datenbankdatei für diesen Index vorhanden, und die ersten 5 Seiten sind die 861, 862, 1627, 1628 und 1904 in der Reihenfolge der Seitenzahl.
Index REBUILD Operation ist erfolgreich abgeschlossen und nehmen Seitenzuweisungsreferenzen für den gleichen Index mit Hilfe der gleichen T-SQL Abfrage wieder.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‚AdventureWorks‘), OBJECT_ID(‚Sales.OrderTracking‘),NULL, NULL, ‚DETAILED‘) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‚IX_OrderTracking_CarrierTrackingNumber‘
ORDER BY allocated_page_page_id
|
Nachdem der Index neu aufgebaut wurde, beträgt die aktualisierte Seitenzahl 1457, was vorher 1961 war. Wenn Sie die ersten 5 zugewiesenen Seiten desselben Index überprüfen, wurden diese mit den neuen Seitenverweisen geändert. Es wird angenommen, dass der Index gelöscht und neu erstellt wurde. Wir sollten den Prozentsatz der aufgefrischten Fragmentierung für denselben Index überprüfen, und wie unten zu sehen ist, beträgt er jetzt 0,1 %.
REBUILD clustered index over the table wirkt sich auch auf andere Indizes der Tabelle aus, da der REBUILD clustered index auch den nicht-geclusterten Index der Tabelle neu aufbaut. Führen Sie die Rebuild-Operation für alle Indizes der Tabelle oder Datenbank zusammen durch; ein Benutzer kann den Befehl DBCC DBREINDEX() verwenden.
|
1
|
DBCC DBREINDEX (‚DatenbankName‘, ‚Tabellenname‘);
|
REORGANIZED INDEX
Der Befehl REORGANIZE INDEX ordnet die Indexseite neu, indem er den freien oder ungenutzten Platz auf der Seite entfernt. Im Idealfall werden die Indexseiten in der Datendatei physisch neu geordnet. Bei REORGANIZE wird der Index nicht gelöscht und neu erstellt, sondern es werden lediglich die Informationen auf der Seite neu strukturiert. REORGANIZE hat keine Offline-Option, und REORGANIZE hat im Vergleich zur REBUILD-Option keine Auswirkungen auf die Statistiken. REORGANIZE wird immer online ausgeführt.
Nehmen wir zum Beispiel vor der Durchführung von REORGANIZE für den Index den Fragmentierungswert für die Datenbank „AdventureWorks“, die Tabelle „Sales.OrderTracking“ und den Index „IX_OrderTracking_SalesOrderID“.
Hier beträgt der Prozentsatz der Indexfragmentierung 98,39 vor REORGANIZE. Die Liste unten im Bild ist die Zuordnungsseiten zum Index.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‚AdventureWorks‘), OBJECT_ID(‚Sales.OrderTracking‘),NULL, NULL, ‚DETAILED‘) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‚IX_OrderTracking_CarrierTrackingNumber‘
ORDER BY allocated_page_page_id
|
In der obigen Abbildung sind insgesamt 459 Seiten aufgeführt, und die ersten fünf Seiten sind 1065, 1068, 1069, 1944 und 1945. Führen wir nun den Befehl REORGANIZE für den Index mit der folgenden T-SQL-Anweisung aus und sehen wir uns die Seitenzuordnung erneut an.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Hier verringert sich die Gesamtzahl der Seiten auf 331, vorher waren es 459. Außerdem sind in der Liste der ersten fünf Seiten keine neuen Seiten zu sehen, was bedeutet, dass die Daten nur umstrukturiert und nicht neu aufgefüllt wurden. Man könnte auch neue Seiten sehen, das passiert, wenn der große Index stark fragmentiert ist und die Umstrukturierung der Daten eine neue Seite verwendet.
Um die REORGANIZE-Index-Operation auf allen Indizes der Tabelle oder Datenbank zusammen durchzuführen, kann der Benutzer den Befehl DBCC INDEXDEFRAG() verwenden:
|
1
|
DBCC INDEXDEFRAG(‚DatenbankName‘, ‚Tabellenname‘);
|
Wie man sieht, besteht ein wesentlicher Unterschied zwischen dem Index REBUILD und REORGANIZE. Hier haben die Benutzer die Wahl, eine der Alternativen entsprechend dem Prozentsatz der Indexfragmentierung zu wählen. Wir können verstehen, dass es keine dokumentierten Standards gibt; der Datenbankadministrator folgt jedoch der Standardgleichung je nach Anforderung der Indexgröße und der Art der Informationen.
Übliche Bestimmung der Verwendung der Gleichung:
- Wenn der Fragmentierungsprozentsatz zwischen 15-30 liegt: REORGANIZE
- Wenn die Fragmentierung größer als 30 ist: REBUILD
Die Option REBUILD ist in Verbindung mit der Option ONLINE sinnvoller, wenn die Datenbank außerhalb der Stoßzeiten nicht für die Indexpflege zur Verfügung steht.
Schlussfolgerung
Indexfragmentierung ist eine interne Fragmentierung in der Datendatei. Die wichtigsten Parameter für eine schnelle Leistung Ihrer Datenbank sind die Datenbankarchitektur, das Datenbankdesign und das Schreiben von Abfragen. Ein gutes Indexdesign mit Wartung steigert immer die Abfrageleistung in der Datenbank-Engine.
- Autor
- Recent Posts
Alle Beiträge von Jignesh Raiyani anzeigen
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Automatisieren der Tabellenpartitionierung in SQL Server – 7. Juli 2020
- Konfigurieren von SQL Server Always On Availability Groups auf AWS EC2 – 6. Juli 2020