Neuorganisieren und Neuerstellen von Indizes

Erkennen von Fragmentierung

Der erste Schritt bei der Entscheidung für eine Defragmentierungsmethode besteht im Analysieren des Index, um den Fragmentierungsgrad zu ermitteln. Mithilfe der Systemfunktion sys.dm_db_index_physical_stats können Sie die Fragmentierung in einem bestimmten Index, allen Indizes für eine Tabelle oder indizierte Sicht, allen Indizes in einer Datenbank oder allen Indizes in allen Datenbanken erkennen. Für partitionierte Indizes stellt sys.dm_db_index_physical_stats außerdem Fragmentierungsinformationen für jede Partition bereit.

Das durch die Funktion sys.dm_db_index_physical_stats zurückgegebene Resultset enthält die folgenden Spalten.

Nachdem der Grad der Fragmentierung bekannt ist, verwenden Sie die folgenden Tabelle, um die beste Methode zum Beheben der Fragmentierung zu ermitteln.

* Das Neuerstellen eines Index kann online oder offline erfolgen. Das Neuorganisieren eines Index erfolgt immer online. Damit eine Verfügbarkeit ähnlich der Neuorganisierungsoption erreicht wird, sollten Indizes online neu erstellt werden.

Diese Werte dienen als grobe Richtlinie, um den Punkt zu bestimmen, an dem Sie zwischen ALTER INDEX REORGANIZE und ALTER INDEX REBUILD wechseln sollten. Die Istwerte können jedoch von Fall zu Fall unterschiedlich sein. Es ist wichtig, dass Sie experimentieren, um den besten Schwellenwert für Ihre Umgebung zu bestimmen.

Bei sehr niedrigen Fragmentierungsniveaus (unter 5 Prozent) sollten diese Befehle nicht eingesetzt werden, da die Vorteile des Entfernens einer so geringen Fragmentierung die Kosten für das Neuorganisieren und Neuerstellen des Index nicht aufwiegen.

Hinweis

Im Allgemeinen ist die Fragmentierung bei kleinen Indizes oft nicht steuerbar. Die Seiten kleiner Indizes werden in gemischten Blöcken gespeichert. Gemischte Blöcke sind für bis zu acht Objekte freigegeben, sodass die Fragmentierung in einem kleinen Index durch die Reorganisation oder das erneute Erstellen des Index möglicherweise nicht verringert wird. Weitere Informationen zu gemischten Blöcken finden Sie unter Grundlegendes zu Seiten und Blöcken.

Beispiel

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Neuorganisieren eines Index

Verwenden Sie zum Neuorganisieren eines oder mehrerer Indizes die ALTER INDEX-Anweisung mit der REORGANIZE-Klausel. Diese Anweisung ersetzt die DBCC INDEXDEFRAG-Anweisung. Wenn Sie eine einzelne Partition eines partitionierten Index neu organisieren möchten, verwenden Sie die PARTITION-Klausel von ALTER INDEX.

Durch das Neuorganisieren eines Index wird die Blattebene von gruppierten und nicht gruppierten Indizes für Tabellen und Sichten durch physisches Neuanordnen der Seiten auf Blattebene entsprechend der logischen Reihenfolge (von links nach rechts) der Blattknoten defragmentiert. Wenn die Seiten die richtige Reihenfolge aufweisen, wird die Leistung beim Durchsuchen von Indizes verbessert. Der Index wird innerhalb der im zugewiesenen vorhandenen Seiten neu organisiert; es werden keine neuen Seiten zugewiesen. Wenn sich ein Index über mehrere Dateien erstreckt, werden die Dateien nacheinander neu organisiert. Seiten werden nicht zwischen Dateien migriert.

Durch das Neuorganisieren werden die Indexseiten außerdem komprimiert. Alle leeren Seiten, die durch diese Komprimierung erstellt werden, werden entfernt; auf diese Weise wird zusätzlicher Speicherplatz verfügbar. Die Komprimierung basiert auf dem Füllfaktorwert in der sys.indexes-Katalogsicht.

Der Vorgang der Neuorganisation verwendet nur minimale Systemressourcen. Außerdem wird die Neuorganisation automatisch online ausgeführt. Der Prozess errichtet keine Sperren über längere Zeit, daher werden keine ausgeführten Abfragen oder Aktualisierungen blockiert.

Organisieren Sie einen Index neu, wenn der Index nicht übermäßig fragmentiert ist. Fragmentierungsrichtlinien finden Sie in der obigen Tabelle. Sollte der Index jedoch hochgradig fragmentiert sein, werden bessere Ergebnisse durch Neuerstellen des Index erzielt.

Large Object-Datentypkomprimierung

Neuerstellen eines Index

Beim Neuerstellen eines Index wird der Index gelöscht und ein neuer Index erstellt. Auf diese Weise wird die Fragmentierung entfernt, Speicherplatz durch Komprimieren der Seiten mithilfe der angegebenen oder vorhandenen Füllfaktoreinstellung freigegeben, und die Indexzeilen werden auf zusammenhängenden Seiten (unter Zuweisung neuer Seiten bei Bedarf) neu angeordnet. Auf diese Weise kann die Datenträgerleistung verbessert werden, da die Anzahl der Seitenlesevorgänge verringert wird, die erforderlich ist, um die angeforderten Daten abzurufen.

Die folgenden Methoden können zum Neuerstellen gruppierter und nicht gruppierter Indizes verwendet werden:

• ALTER INDEX mit der REBUILD-Klausel. Diese Anweisung ersetzt die DBCC DBREINDEX-Anweisung.

• CREATE INDEX mit der DROP_EXISTING-Klausel.

Jede Methode führt die gleiche Funktion aus; die folgende Tabelle führt jedoch die Vor- und Nachteile auf, die berücksichtigt werden sollten.

* Ein nicht gruppierter Index kann in einen gruppierten Indextyp konvertiert werden, indem CLUSTERED in der Indexdefinition angegeben wird. Bei der Ausführung dieses Vorgangs muss die Option ONLINE auf OFF festgelegt werden. Die Konvertierung eines gruppierten in einen nicht gruppierten Index wird unabhängig von der ONLINE-Einstellung nicht unterstützt.

** Wenn der Index unter dem gleichen Namen, mit den gleichen Spalten und der gleichen Sortierreihenfolge neu erstellt wird, kann der Sortiervorgang möglicherweise ausgelassen werden. Der Neuerstellungsvorgang überprüft, ob die Zeilen sortiert sind, während der Index erstellt wird.

Sie können einen Index neu erstellen, indem Sie den Index zuerst mit der DROP INDEX-Anweisung löschen und dann mit einer separaten CREATE INDEX-Anweisung neu erstellen. Das Ausführen dieser Operationen als separate Anweisungen besitzt mehrere Nachteile und wird nicht empfohlen.

Deaktivieren nicht gruppierter Indizes zur Einsparung von Speicherplatz während Neuerstellungsvorgängen

Wenn ein nicht gruppierter Index deaktiviert wird, werden die Datenzeilen des Index gelöscht, die Indexdefinition verbleibt jedoch in den Metadaten. Der Index wird aktiviert, wenn er neu erstellt wird. Wenn der nicht gruppierte Index nicht deaktiviert wird, benötigt der Neuerstellungsvorgang ausreichenden temporären Speicherplatz zum Speichern des alten und des neuen Index. Wenn ein nicht gruppierter Index jedoch in separaten Transaktionen deaktiviert und neu erstellt werden, kann der durch das Deaktivieren des Index freigegebene Speicherplatz bei der anschließenden Neuerstellung oder einer beliebigen anderen Operation erneut verwendet werden. Mit Ausnahme des temporären Speicherplatzes für die Sortierung (normalerweise 20 Prozent der Indexgröße) ist kein zusätzlicher Speicherplatz erforderlich. Wenn sich der nicht gruppierte Index auf den Primärschlüssel bezieht, werden alle aktiven, verweisenden FOREIGN KEY-Einschränkungen automatisch deaktiviert. Diese Einschränkungen müssen nach dem Neuerstellen des Index manuell aktiviert werden. Weitere Informationen finden Sie unter Deaktivieren von Indizes und Richtlinien für das Aktivieren von Indizes und Einschränkungen.

Neuerstellen großer Indizes

Indizes mit mehr als 128 Blöcken werden in zwei getrennten Phasen neu erstellt: der logischen und der physischen Phase. In der logischen Phase werden die vorhandenen Zuordnungseinheiten, die vom Index verwendet werden, für die Aufhebung der Zuordnung markiert, die Datenzeilen werden kopiert und sortiert und dann in neue Zuordnungseinheiten verschoben, die erstellt werden, um den neu erstellten Index zu speichern. In der physischen Phase werden die zuvor für die Aufhebung der Zuordnung markierten Zuordnungseinheiten in kurzen Transaktionen physisch gelöscht, die im Hintergrund ausgeführt werden und nicht viele Sperren benötigen. Weitere Informationen finden Sie unter Löschen und Neuerstellen großer Objekte.

Festlegen von Indexoptionen

Indexoptionen können beim Neuorganisieren eines Index nicht angegeben werden. Die folgenden Indexoptionen können jedoch beim Neuerstellen eines Index mithilfe von ALTER INDEX REBUILD oder CREATE INDEX WITH DROP_EXISTING festgelegt werden:

Hinweis
Wenn keine Sortiervorgänge erforderlich ist oder die Sortierung im Arbeitsspeicher erfolgen kann, wird die Option SORT_IN_TEMPDB ignoriert.

Außerdem können Sie mithilfe der SET-Klausel in der ALTER INDEX-Anweisung die folgenden Indexoptionen festlegen, ohne den Index neu erstellen zu müssen:

Weitere Informationen finden Sie unter Festlegen von Indexoptionen.

So erstellen und organisieren Sie einen Index neu

ALTER INDEX (Transact-SQL)

So erstellen Sie einen Index neu, indem Sie den Index in einem Schritt löschen und neu erstellen

CREATE INDEX (Transact-SQL)

Beispiele:

A. Neuerstellen eines Index

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. Neuerstellen aller Indizes für eine Tabelle und Angeben von Optionen

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Neuorganisieren eines Index mit LOB-Komprimierung

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Siehe auch

Verweis

Konzepte

Andere Ressourcen