Neuorganisieren und Neuerstellen von Indizes

Artikel
12/15/2008

Aktualisiert: 17. Juli 2006

SQL Server 2005-Datenbankmodul verwaltet Indizes automatisch, wenn Einfüge-, Aktualisierungs- oder Löschvorgänge an den zugrunde liegenden Daten vorgenommen werden. Im Lauf der Zeit können diese Änderungen dazu führen, dass die Informationen im Index in der Datenbank verstreut (fragmentiert) werden. Fragmentierung liegt vor, wenn Indizes über Seiten verfügen, in denen die logische Reihenfolge (basierend auf dem Schlüsselwert) nicht der physikalischen Reihenfolge in der Datendatei entspricht. Hochgradig fragmentierte Indizes können die Abfrageleistung beeinträchtigen und dazu führen, dass Ihre Anwendung nur langsam reagiert. Weitere Informationen finden Sie auf dieser Microsoft-Website.

In SQL Server 2005 können Sie die Indexfragmentierung durch Neuorganisieren des Indexes oder Neuerstellen des Indexes beheben. Für partitionierte Indizes, die auf der Grundlage eines Partitionsschemas erstellt wurden, können Sie eine dieser Methoden für einen vollständigen Index oder für eine einzelne Partition eines Indexes verwenden.

Erkennen von Fragmentierung

Der erste Schritt bei der Entscheidung für eine Defragmentierungsmethode besteht im Analysieren des Indexes, 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.

Der Algorithmus zur Berechnung der Fragmentierung ist in SQL Server 2005 präziser als in SQL Server 2000. Daher scheinen die Fragmentierungswerte höher zu sein. So wird eine Tabelle zum Beispiel in SQL Server 2000 nicht als fragmentiert angesehen, wenn sich Seite 11 und Seite 13, jedoch nicht Seite 12, im gleichen Block befinden. Das Zugreifen auf diese beiden Seiten erfordert jedoch zwei physische E/A-Vorgänge, sodass dies in SQL Server 2005 als Fragmentierung gewertet wird.

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

Spalte	Beschreibung
avg_fragmentation_in_percent	Der Prozentsatz der logischen Fragmentierung (falsche Reihenfolge der Seiten in einem Index).
fragment_count	Die Anzahl der Fragmente (physikalisch aufeinanderfolgende Blattseiten) im Index.
avg_fragment_size_in_pages	Durchschnittliche Anzahl der Seiten in einem Fragment in einem Index.

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

avg_fragmentation_in_percent-Wert	Korrigierende Anweisung
> 5 % und < = 30 %	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Das Neuerstellen eines Indexes kann online oder offline erfolgen. Das Neuorganisieren eines Indexes 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 tatsächlichen Werte 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 fünf 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 Indexes nicht aufwiegen.

Hinweis:
Das erneute Erstellen oder Reorganisieren von kleinen Indizes verringert oft nicht die Fragmentierung. Die Seiten kleiner Indizes werden in gemischten Blöcken gespeichert. Gemischte Erweiterungen 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 Erweiterungen finden Sie unter Seiten und Blöcke.

Das erneute Erstellen oder Reorganisieren von kleinen Indizes verringert oft nicht die Fragmentierung. Die Seiten kleiner Indizes werden in gemischten Blöcken gespeichert. Gemischte Erweiterungen 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 Erweiterungen finden Sie unter Seiten und Blöcke.

Beispiel

Im folgenden Beispiel wird die dynamische Verwaltungsfunktion sys.dm_db_index_physical_stats abgefragt, um die durchschnittliche Fragmentierung für alle Indizes der Production.Product-Tabelle zurückzugeben. Die empfohlene Lösung besteht darin, mithilfe der obigen Tabelle PK_Product_ProductID neu zu organisieren und die anderen Indizes neu zu erstellen.

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

Diese Anweisung gibt möglicherweise ein Resultset zurück, das dem folgenden Resultset ähnelt.

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 Indexes

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 Indexes neu organisieren möchten, verwenden Sie die PARTITION-Klausel von ALTER INDEX.

Durch das Neuorganisieren eines Indexes wird die Blattebene von gruppierten und nicht gruppierten Indizes für Tabellen und Sichten durch physikalisches Neuanordnen der Seiten auf Blattebene entsprechend der logischen Reihenfolge (von links nach rechts) der Endknoten 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 Indexes erzielt.

Large Object-Datentypkomprimierung

Bei der Neuorganisation eines oder mehrerer Indizes werden außerdem Large Object-Datentypen (LOBs), die im gruppierten Index oder der zugrunde liegenden Tabelle enthalten sind, bei der Neuorganisation des Indexes standardmäßig komprimiert. Die Datentypen image, text, ntext, varchar(max), nvarchar(max), varbinary(max) und xml sind Large Object-Datentypen. Durch das Komprimieren dieser Daten kann eine bessere Verwendung des Speicherplatzes erzielt werden:

Wenn Sie einen angegebenen gruppierten Index neu organisieren, werden alle LOB-Spalten, die auf der Blattebene (Datenzeilen) des gruppierten Indexes enthalten sind, komprimiert.
Bei der Neuorganisation eines nicht gruppierten Indexes werden alle LOB-Spalten komprimiert, die Nichtschlüsselspalten (eingeschlossene Spalten) im Index sind.
Wenn ALL angegeben wird, werden alle Indizes, die der angegebenen Tabelle oder Sicht zugeordnet sind, neu organisiert, und alle LOB-Spalten, die dem gruppierten Index, der zugrunde liegenden Tabelle oder dem nicht gruppierten Index mit eingeschlossenen Spalten zugeordnet sind, komprimiert.
Die LOB_COMPACTION-Klausel wird ignoriert, wenn keine LOB-Spalten vorhanden sind.

Neuerstellen eines Indexes

Beim Neuerstellen eines Indexes 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.

Funktionalität	ALTER INDEX REBUILD	CREATE INDEX WITH DROP_EXISTING
Die Indexdefinition kann durch Hinzufügen oder Entfernen von Schlüsselspalten, Ändern der Spaltenreihenfolge oder Ändern der Spaltensortierreihenfolge geändert werden.*	Nein	Ja**
Indexoptionen können festgelegt oder geändert werden.	Ja	Ja
Mehrere Indizes können in einer einzigen Transaktion neu erstellt werden.	Ja	Nein
Die meisten Indextypen können online neu erstellt werden, ohne dass ausgeführte Abfragen oder Aktualisierungen blockiert werden.	Ja	Ja
Ein partitionierter Index kann neu partitioniert werden.	Nein	Ja
Der Index kann in eine andere Dateigruppe verschoben werden.	Nein	Ja
Zusätzlicher temporärer Speicherplatz ist erforderlich.	Ja	Ja
Durch das Neuerstellen eines gruppierten Indexes werden die zugeordneten nicht gruppierten Indizes ebenfalls neu erstellt.	Nein Nur wenn das Schlüsselwort ALL angegeben wird.	Nein Nur wenn sich die Indexdefinition geändert hat.
Indizes, die die PRIMARY KEY- und UNIQUE-Einschränkungen erzwingen, können neu erstellt werden, ohne dass die Einschränkungen gelöscht und erneut erstellt werden.	Ja	Ja
Einzelne Indexpartition kann neu erstellt werden.	Ja	Nein

* 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 Indexes 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 Indexes. Wenn ein nicht gruppierter Index jedoch in separaten Transaktionen deaktiviert und neu erstellt werden, kann der durch das Deaktivieren des Indexes 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 Indexes 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 physikalischen 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 physikalischen Phase werden die zuvor für die Aufhebung der Zuordnung markierten Zuordnungseinheiten in kurzen Transaktionen physikalisch 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 Indexes nicht angegeben werden. Die folgenden Indexoptionen können jedoch beim Neuerstellen eines Indexes mithilfe von ALTER INDEX REBUILD oder CREATE INDEX WITH DROP_EXISTING festgelegt werden:

PAD_INDEX	DROP_EXISTING (nur CREATE INDEX)
FILLFACTOR	ONLINE
SORT_IN_TEMPDB	ALLOW_ROW_LOCKS
IGNORE_DUP_KEY	ALLOW_PAGE_LOCKS
STATISTICS_NORECOMPUTE	MAXDOP

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:

ALLOW_PAGE_LOCKS	IGNORE_DUP_KEY
ALLOW_ROW_LOCKS	STATISTICS_NORECOMPUTE

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 Indexes

Das folgende Beispiel erstellt einen einzelnen Index neu.

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

Das folgende Beispiel gibt das ALL-Schlüsselwort an. Auf diese Weise werden alle Indizes neu erstellt, die der Tabelle zugeordnet sind. Drei Optionen werden angegeben.

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

C. Neuorganisieren eines Indexes mit LOB-Komprimierung

Das folgende Beispiel organisiert einen einzelnen gruppierten Index neu. Da der Index einen LOB-Datentyp auf Blattebene enthält, komprimiert die Anweisung außerdem alle Seiten, die die Large Object-Daten enthalten. Beachten Sie, dass Sie die Option WITH (LOB_Compaction) nicht angeben müssen, weil der Standardwert ON lautet.

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

Siehe auch

Änderungsverlauf

Version	Verlauf
17. Juli 2006	Neuer Inhalt: Im Abschnitt "Erkennen von Fragmentierung" wurde die Information zum Algorithmus zur Berechnung der Fragmentierung hinzugefügt.
05. Dezember 2005	Neuer Inhalt: Im Abschnitt "Erkennen von Fragmentierung" wurden Hinweise zum Umgang mit niedrigen Fragmentierungsniveaus hinzugefügt.

Freigeben über