Neuorganisieren und Neuerstellen von Indizes

In diesem Thema wird beschrieben, wie Sie einen fragmentierten Index in SQL Server 2012 mithilfe SQL Server Management Studio oder Transact-SQL neu organisieren oder neu erstellen. SQL Server Database Engine (Datenbankmodul) verwaltet Indizes automatisch, wenn Einfüge-, Update- 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.

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 anschließend 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 aufeinander folgende Seiten geordnet. Wenn ALL angegeben ist, werden alle Indizes der Tabelle gelöscht und in einer einzelnen Transaktion neu erstellt. Beim Neuorganisieren eines Indexes werden minimale Systemressourcen verwendet. Dabei wird die Blattebene von gruppierten und nicht gruppierten Indizes in Tabellen und Sichten defragmentiert, indem die Blattebenenseiten physisch neu geordnet werden, um mit der logischen Reihenfolge der Blattknoten von links nach rechts übereinzustimmen. Durch das Neuorganisieren werden die Indexseiten außerdem komprimiert. Die Komprimierung basiert auf dem vorhandenen Füllfaktorwert.

In diesem Thema

• Vorbereitungen:

  Erkennen der Fragmentierung

  Einschränkungen

  Sicherheit

• Überprüfen der Fragmentierung eines Indexes mit:

  SQL Server Management Studio

  Transact-SQL

• Neuorganisieren oder Neuerstellen eines Indexes mit:

  SQL Server Management Studio

  Transact-SQL

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 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 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.

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 Indexes möglicherweise nicht verringert wird.

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.

• Indexoptionen können beim Neuorganisieren eines Indexes nicht angegeben werden.

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.

[Nach oben]

Mit SQL Server Management Studio

So überprüfen Sie die Fragmentierung eines Indexes

1. Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie die Fragmentierung eines Indexes überprüfen möchten.

2. Erweitern Sie den Ordner Tabellen.

3. Erweitern Sie die Tabelle, in der Sie die Fragmentierung eines Indexes überprüfen möchten.

4. Erweitern Sie den Ordner Indizes.

5. Klicken Sie mit der rechten Maustaste auf den Index, für den Sie die Fragmentierung überprüfen möchten, und wählen Sie Eigenschaften aus.

6. Wählen Sie unter Seite auswählen die Option Fragmentierung aus.

   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
     Typ des Indexes. Mögliche Werte sind Gruppierter Index, Nicht gruppierter Index und 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)
     Anzahl der inaktiv gesetzten Zeilen, die aufgrund einer ausstehenden Momentaufnahme-Isolationstransaktion beibehalten werden.

[Nach oben]

Mit Transact-SQL

So überprüfen Sie die Fragmentierung eines Indexes

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Database Engine (Datenbankmodul)-Instanz her.

2. Klicken Sie in der Standardleiste auf Neue Abfrage.

3. 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; 
   GO
   

   Das 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).

[Nach oben]

Mit SQL Server Management Studio

So organisieren oder erstellen Sie einen Index neu

1. Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie einen Index neu organisieren möchten.

2. Erweitern Sie den Ordner Tabellen.

3. Erweitern Sie die Tabelle, in der Sie einen Index neu organisieren möchten.

4. Erweitern Sie den Ordner Indizes.

5. Klicken Sie mit der rechten Maustaste auf den Index, den Sie neu organisieren möchten, und wählen Sie Neu organisieren aus.

6. 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.

7. Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren, um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (LOB) komprimiert werden sollen.

8. Klicken Sie auf OK.

So organisieren Sie alle Indizes in einer Tabelle neu

1. Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie die Indizes neu organisieren möchten.

2. Erweitern Sie den Ordner Tabellen.

3. Erweitern Sie die Tabelle, in der Sie die Indizes neu organisieren möchten.

4. Klicken Sie mit der rechten Maustaste auf den Ordner Indizes, und wählen Sie Alle neu organisieren aus.

5. Vergewissern Sie sich im Dialogfeld Index neu organisieren, dass die richtigen Indizes im Raster Neu zu organisierende Indizes ausgewä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.

6. Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren, um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (LOB) komprimiert werden sollen.

7. Klicken Sie auf OK.

So erstellen Sie einen Index neu

1. Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie einen Index neu organisieren möchten.

2. Erweitern Sie den Ordner Tabellen.

3. Erweitern Sie die Tabelle, in der Sie einen Index neu organisieren möchten.

4. Erweitern Sie den Ordner Indizes.

5. Klicken Sie mit der rechten Maustaste auf den Index, den Sie neu organisieren möchten, und wählen Sie Neu organisieren aus.

6. 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.

7. Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren, um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (LOB) komprimiert werden sollen.

8. Klicken Sie auf OK.

[Nach oben]

Mit Transact-SQL

So organisieren Sie einen defragmentierten Index neu

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Database Engine (Datenbankmodul)-Instanz her.

2. Klicken Sie in der Standardleiste auf Neue Abfrage.

3. 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

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Instanz von Database Engine (Datenbankmodul) her.

2. Klicken Sie in der Standardleiste auf Neue Abfrage.

3. 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

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Instanz von Database Engine (Datenbankmodul) her.

2. Klicken Sie in der Standardleiste auf Neue Abfrage.

3. 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

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Instanz von Database Engine (Datenbankmodul) her.

2. Klicken Sie in der Standardleiste auf Neue Abfrage.

3. 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).

[Nach oben]

Siehe auch

Andere Ressourcen

Bewährte Methoden für die Indexdefragmentierung in Microsoft SQL Server 2000