Neuorganisieren und Neuerstellen von Indizes
SQL Server Database Engine (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 physischen 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.
Sie können 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.
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 (physisch 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 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 Indexes nicht aufwiegen.
.gif "Hinweis") 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
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 AdventureWorks2008R2;
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 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 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, standardmäßig komprimiert, wenn der Index neu organisiert wird. 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 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 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
So erstellen Sie einen Index neu, indem Sie den Index in einem Schritt löschen und neu erstellen
Beispiele:
A. Neuerstellen eines Indexes
Das folgende Beispiel erstellt einen einzelnen Index neu.
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
B. Neuerstellen aller Indizes für eine Tabelle und Angeben von Optionen
Im folgenden Beispiel wird das Schlüsselwort ALL angegeben. Hier werden alle der Tabelle zugeordneten Indizes neu erstellt. Drei Optionen werden angegeben.
USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
Siehe auch