Neuorganisieren und Neuerstellen von Indizes
In diesem Thema wird beschrieben, wie Sie einen fragmentierten Index in SQL Server 2014 mithilfe von SQL Server Management Studio oder Transact-SQL neu organisieren oder neu erstellen. Die SQL Server-Datenbank-Engine verwaltet automatisch Indizes, wenn Einfüge-, Aktualisierungs- oder Löschvorgänge für die zugrunde liegenden Daten ausgeführt 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.
Sie können die Indexfragmentierung durch Neuorganisieren oder Neuerstellen eines Indexes beheben. Für partitionierte Indizes, die auf Grundlage eines Partitionsschemas erstellt wurden, können beide Methoden für einen vollständigen Index oder für eine einzelne Partition eines Indexes verwendet werden. Beim Neuerstellen eines Indexes wird der Index gelöscht und neu erstellt. Bei diesem Vorgang wird die Fragmentierung entfernt, Speicherplatz wird freigegeben, indem die Seiten auf der Grundlage der angegebenen oder vorhandenen Füllfaktoreinstellung komprimiert werden, und die Indexzeilen werden in aufeinanderfolgenden Seiten neu geordnet. Wenn ALL angegeben ist, werden alle Indizes der Tabelle in einer einzelnen Transaktion gelöscht und neu erstellt. Das Neuorganisieren eines Indexes beansprucht minimale Systemressourcen. Dabei wird die Blattebene von gruppierten und nicht gruppierten Indizes in Tabellen und Sichten defragmentiert, indem die Blattebenenseiten physisch neu geordnet werden, damit sie mit der logischen Reihenfolge der Blattknoten von links nach rechts übereinstimmen. Durch das Neuorganisieren werden die Indexseiten auch komprimiert. Die Komprimierung basiert auf dem vorhandenen Füllfaktorwert.
In diesem Thema
Vorbereitungen:
Überprüfen der Fragmentierung eines Indexes mit:
Neuorganisieren oder Neuerstellen eines Indexes mit:
Vorbereitungen
Erkennen der 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 in einer Tabelle oder indizierten 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) 1 |
1 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.
Tipp
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. Wird ein bestimmter Index beispielsweise hauptsächlich für Überprüfungsvorgänge verwendet, kann ein Entfernen der Fragmentierung die Leistung dieser Vorgänge verbessern. Für Indizes, die in erster Linie für Suchvorgänge verwendet werden, fällt der Leistungsvorteil weniger auf. Ähnliches gilt für das Entfernen der Fragmentierung in einem Heap (einer Tabelle ohne gruppierten Index). Auch dies ist besonders nützlich für Überprüfungsvorgänge für nicht gruppierte Indizes, wirkt sich aber kaum auf Suchvorgänge aus.
Bei sehr niedrigen Fragmentierungsniveaus (unter 5 Prozent) sollten diese Befehle normalerweise 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
Durch das erneute Erstellen oder Organisieren kleiner Indizes lässt sich die Fragmentierung häufig nicht verringern. Die Seiten kleiner Indizes werden manchmal in gemischten Blöcken gespeichert. Da gemischte Blöcke von bis zu acht Objekten gemeinsam genutzt werden, lässt sich die Fragmentierung in einem kleinen Index durch die erneute Erstellung oder Organisation des Indexes möglicherweise nicht verringern.
Überlegungen zur Indexdefragmentierung
Bei Zutreffen bestimmter Bedingungen führt eine Neuerstellung eines gruppierten Indexes dazu, dass alle nicht gruppierten Indizes, in denen auf den Gruppierungsschlüssel verwiesen wird, automatisch neu erstellt werden, wenn die physischen oder logischen IDs geändert werden müssen, die sich in den nicht gruppierten Indexdatensätzen befinden.
In diesen Szenarien wird erzwungen, dass alle nicht gruppierten Indizes automatisch für eine Tabelle neu erstellt werden:
- Erstellen eines gruppierten Indexes für eine Tabelle
- Entfernen eines gruppierten Indexes, was zur Folge hat, dass die Tabelle als Heap gespeichert wird
- Ändern des Gruppierungsschlüssels, um Spalten einzubeziehen oder auszuschließen
In diesen Szenarien ist es nicht erforderlich, dass alle nicht gruppierten Indizes automatisch für eine Tabelle neu erstellt werden:
- Neuerstellen eines eindeutigen gruppierten Indexes
- Neuerstellen eines nicht eindeutigen gruppierten Indexes
- Ändern des Indexschemas, beispielsweise Anwenden eines Partitionierungsschemas auf einen gruppierten Index oder Verschieben des gruppierten Indexes in eine andere Dateigruppe
Einschränkungen
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 zu Blöcken finden Sie im Handbuch zur Architektur von Seiten und Blöcken.
Die ALTER INDEX REORGANIZE -Anweisung erfordert, dass die Datendatei mit dem Index über Platz verfügt, da der Vorgang temporäre Arbeitsseiten nur in der gleichen Datei zuordnen kann, nicht in einer anderen Datei der Dateigruppe. Selbst wenn in einer Dateigruppe leere Seiten verfügbar sind, kann für den Benutzer daher trotzdem Fehler 1105 auftreten: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Das Erstellen bzw. Neuerstellen von nicht ausgerichteten Indizes für eine Tabelle mit mehr als 1.000 Partitionen ist möglich, wird aber nicht empfohlen. Dies hätte Leistungseinbußen oder eine zu hohe Speicherauslastung während der Vorgänge zur Folge.
Ein Index kann nicht neu organisiert oder neu erstellt werden, wenn die Dateigruppe, in der er enthalten ist, eine Offline- oder schreibgeschützte Dateigruppe ist. Wenn das Schlüsselwort ALL angegeben ist und mindestens ein Index in einer Offline- oder schreibgeschützten Dateigruppe enthalten ist, erzeugt die Anweisung einen Fehler.
Sicherheit
Berechtigungen
Erfordert die ALTER-Berechtigung für die Tabelle oder Sicht. Der Benutzer muss ein Mitglied der festen Serverrolle sysadmin bzw. der festen Datenbankrollen db_ddladmin und db_owner sein.
Verwendung von SQL Server Management Studio
So überprüfen Sie die Fragmentierung eines Indexes
Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie die Fragmentierung eines Indexes überprüfen möchten.
Erweitern Sie den Ordner Tabellen .
Erweitern Sie die Tabelle, in der Sie die Fragmentierung eines Indexes überprüfen möchten.
Erweitern Sie den Ordner Indizes .
Klicken Sie mit der rechten Maustaste auf den Index, für den Sie die Fragmentierung überprüfen möchten, und wählen Sie Eigenschaftenaus.
Wählen Sie unter Seite auswählendie Option Fragmentierungaus.
Die folgenden Informationen sind auf der Seite Fragmentierung verfügbar:
Seitenfüllgrad
Gibt den durchschnittlichen Füllgrad der Indexseiten als Prozentwert an. 100 % bedeutet, dass die Indexseiten vollständig gefüllt sind. 50 % heißt, dass jede Indexseite im Durchschnitt zur Hälfte gefüllt ist.Fragmentierung gesamt
Prozentwert der logischen Fragmentierung. Dieser Wert gibt die Anzahl der Seiten in einem Index an, die nicht in Reihenfolge gespeichert sind.Durchschnittliche Zeilengröße
Durchschnittliche Größe einer Zeile auf Blattebene.Tiefe
Anzahl der Ebenen im Index, einschließlich der Blattebene.Weitergeleitete Datensätze
Anzahl der Datensätze in einem Heap, die Weiterleitungszeiger auf einen anderen Datenspeicherort besitzen. (Dieser Status tritt während eines Updates auf, wenn nicht genügend Speicherplatz vorhanden ist, um die neue Zeile am ursprünglichen Speicherort zu speichern.)Inaktive Zeilen
Anzahl der Zeilen, die als gelöscht markiert sind, aber noch nicht entfernt wurden. Diese Zeilen werden von einem Bereinigungsthread entfernt, wenn der Server nicht ausgelastet ist. Dieser Wert schließt keine Zeilen ein, die aufgrund einer ausstehenden Momentaufnahme-Isolationstransaktion beibehalten werden.Indextyp
Der Indextyp. Mögliche Werte sind Gruppierter Index, Nicht gruppierter Indexund Primär-XML. Tabellen können auch als Heap gespeichert werden (ohne Indizes). Dann kann aber diese Seite Indexeigenschaften nicht geöffnet werden.Zeilen auf Blattebene
Anzahl der Zeilen auf Blattebene.Maximale Zeilengröße
Maximale Größe von Zeilen auf Blattebene.Minimale Zeilengröße
Minimale Größe von Zeilen auf Blattebene.Seiten
Gesamtanzahl der Datenseiten.Partitions-ID
Partitions-ID der B-Struktur, die den Index enthält.Inaktive Zeilen (Version)
Die Anzahl inaktiver Datensätze, die aufgrund einer ausstehenden Momentaufnahme-Isolationstransaktion beibehalten werden.
Verwenden von Transact-SQL
So überprüfen Sie die Fragmentierung eines Indexes
Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen.
USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GODas von der obigen Anweisung zurückgegebene Resultset kann in etwa wie folgt aussehen.
index_id name avg_fragmentation_in_percent ----------- ----------------------------------------------------- ---------------------------- 1 PK_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)
Weitere Informationen finden Sie unter sys.dm_db_index_physical_stats (Transact-SQL).
Verwendung von SQL Server Management Studio
So organisieren oder erstellen Sie einen Index neu
Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie einen Index neu organisieren möchten.
Erweitern Sie den Ordner Tabellen .
Erweitern Sie die Tabelle, in der Sie einen Index neu organisieren möchten.
Erweitern Sie den Ordner Indizes .
Klicken Sie mit der rechten Maustaste auf den Index, den Sie neu organisieren möchten, und wählen Sie Neu organisierenaus.
Vergewissern Sie sich im Dialogfeld Indizes neu organisieren , dass der richtige Index im Raster Neu zu organisierende Indizes ausgewählt ist, und klicken Sie auf OK.
Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren , um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (Large Object, LOB) komprimiert werden sollen.
Klicken Sie auf OK.
So organisieren Sie alle Indizes in einer Tabelle neu
Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie die Indizes neu organisieren möchten.
Erweitern Sie den Ordner Tabellen .
Erweitern Sie die Tabelle, in der Sie die Indizes neu organisieren möchten.
Klicken Sie mit der rechten Maustaste auf den Ordner Indizes , und wählen Sie Alle neu organisierenaus.
Vergewissern Sie sich im Dialogfeld Index neu organisieren , dass die richtigen Indizes im Raster Neu zu organisierende Indizesausgewählt sind. Um einen Index aus dem Raster Neu zu organisierende Indizes zu entfernen, wählen Sie den Index aus, und drücken Sie die ENTF-Taste.
Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren , um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (Large Object, LOB) komprimiert werden sollen.
Klicken Sie auf OK.
So erstellen Sie einen Index neu
Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie einen Index neu organisieren möchten.
Erweitern Sie den Ordner Tabellen .
Erweitern Sie die Tabelle, in der Sie einen Index neu organisieren möchten.
Erweitern Sie den Ordner Indizes .
Klicken Sie mit der rechten Maustaste auf den Index, den Sie neu organisieren möchten, und wählen Sie Neu organisierenaus.
Vergewissern Sie sich im Dialogfeld Indizes neu erstellen , dass der richtige Index im Raster Erneut zu erstellende Indizes ausgewählt ist, und klicken Sie auf OK.
Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren , um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (Large Object, LOB) komprimiert werden sollen.
Klicken Sie auf OK.
Verwenden von Transact-SQL
So organisieren Sie einen defragmentierten Index neu
Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen.
USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE ; GO
So organisieren Sie alle Indizes in einer Tabelle neu
Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen.
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
So erstellen Sie einen defragmentierten Index neu
Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen. Im folgenden Beispiel wird ein einzelner Index in der
Employee-Tabelle neu erstellt.USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
So erstellen Sie alle Indizes in einer Tabelle neu
Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
Kopieren Sie das folgende Beispiel in das Abfragefenster. In diesem Beispiel wird das
ALL-Schlüsselwort verwendet. Hier werden alle der Tabelle zugeordneten Indizes neu erstellt. Es werden drei Optionen angegeben.USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
Weitere Informationen finden Sie unter ALTER INDEX (Transact-SQL).
Weitere Informationen
Bewährte Methoden für die Indexdefragmentierung in Microsoft SQL Server 2000