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. Das SQL Server-Datenbankmodul verwaltet automatisch Indizes, wenn Einfüge-, Aktualisierungs- oder Löschvorgänge an den zugrunde liegenden Daten vorgenommen werden. Im Laufe der Zeit können diese Änderungen dazu führen, dass die Informationen im Index in der Datenbank (fragmentiert) verteilt werden. Fragmentierung ist vorhanden, wenn Indizes Seiten aufweisen, auf denen die logische Sortierung basierend auf dem Schlüsselwert nicht mit der physischen Reihenfolge innerhalb der Datendatei übereinstimmt. Stark fragmentierte Indizes können die Abfrageleistung beeinträchtigen und dazu führen, dass Ihre Anwendung langsam reagiert.

Sie können die Indexfragmentierung beheben, indem Sie einen Index neu organisieren oder neu erstellen. Für partitionierte Indizes, die auf einem Partitionsschema basieren, können Sie eine dieser Methoden für einen vollständigen Index oder eine einzelne Partition eines Indexes verwenden. 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 gelöscht und in einer einzelnen Transaktion 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 Themenbereich

• Bevor Sie beginnen:

  Erkennen der Fragmentierung

  Einschränkungen und Beschränkungen

  Sicherheit

• So überprüfen Sie die Fragmentierung eines Indexes mit:

  SQL Server Management Studio

  Transact-SQL

• Um einen Index neu zu organisieren oder neu zu erstellen, verwenden Sie Folgendes:

  SQL Server Management Studio

  Transact-SQL

Bevor Sie beginnen

Erkennen der Fragmentierung

Der erste Schritt bei der Entscheidung, welche Defragmentierungsmethode verwendet werden soll, besteht darin, den Index zu analysieren, um den Grad der Fragmentierung zu bestimmen. Mithilfe der Systemfunktion sys.dm_db_index_physical_stats können Sie Fragmentierung in einem bestimmten Index, alle Indizes in einer Tabelle oder indizierten Ansicht, alle Indizes in einer Datenbank oder alle Indizes in allen Datenbanken erkennen. Für partitionierte Indizes stellt sys.dm_db_index_physical_stats auch Fragmentierungsinformationen für jede Partition bereit.

Das von der sys.dm_db_index_physical_stats-Funktion zurückgegebene Resultset enthält die folgenden Spalten.

Nachdem der Grad der Fragmentierung bekannt ist, verwenden Sie die folgende Tabelle, um die beste Methode zu ermitteln, um die Fragmentierung zu korrigieren.

¹ Das Neuerstellen eines Indexes kann online oder offline ausgeführt werden. Die Neuorganisation eines Indexes wird immer online ausgeführt. Um die Verfügbarkeit ähnlich der Neuorganisationsoption zu erreichen, sollten Sie Indizes online neu erstellen.

Sehr niedrige Fragmentierungsebenen (weniger als 5 Prozent) sollten in der Regel nicht von einem dieser Befehle angesprochen werden, da der Vorteil von der Entfernung einer solchen geringen Fragmentierungsmenge fast immer stark durch die Kosten der Neuorganisation oder Neuerstellung des Indexes überwiegt wird.

Überlegungen zur Index defragmentierung

Unter bestimmten Bedingungen erstellt die Neuerstellung eines gruppierten Indexes automatisch alle nicht gruppierten Indexe, die auf den Clusterschlüssel verweisen, neu, wenn sich die physischen oder logischen Bezeichner, die in den nicht gruppierten Indexdatensätzen enthalten sind, ändern müssen.

Szenarien, die erzwingen, dass alle nicht gruppierten Indizes automatisch in einer Tabelle neu erstellt werden:

• Erstellen eines gruppierten Indexes in einer Tabelle
• Entfernen eines gruppierten Indexes, wodurch die Tabelle als Heap gespeichert wird
• Ändern des Clusterschlüssels zum Einschließen oder Ausschließen von Spalten

Szenarien, für die nicht gruppierte Indizes nicht automatisch in einer Tabelle neu erstellt werden müssen:

• Neuerstellen eines eindeutigen gruppierten Indexes
• Neuerstellen eines nicht eindeutigen gruppierten Indexes
• Ändern des Indexschemas, z. B. Anwenden eines Partitionierungsschemas auf einen gruppierten Index oder Verschieben des gruppierten Indexes auf eine andere Dateigruppe

Einschränkungen und Beschränkungen

Indizes mit mehr als 128 Extents werden in zwei separaten Phasen rekonstruiert: logisch und physisch. In der logischen Phase werden die vorhandenen Zuordnungseinheiten, die vom Index verwendet werden, für den Deallocation markiert, die Datenzeilen kopiert und sortiert und dann in neue Zuordnungseinheiten verschoben, die zum Speichern des neu erstellten Index erstellt wurden. In der physischen Phase werden die zuvor für die Deallokation markierten Zuordnungseinheiten in kurzen Transaktionen, die im Hintergrund ablaufen, physisch entfernt und benötigen nicht viele Sperren. Weitere Informationen zu Extents finden Sie im Handbuch zur Architektur von Seiten und Extents.

Die ALTER INDEX REORGANIZE-Anweisung erfordert, dass die Datendatei, die den Index enthält, über freien Speicherplatz verfügt, da der Vorgang nur temporäre Arbeitsseiten in derselben Datei und nicht in einer anderen Datei innerhalb der Dateigruppe zuordnen kann. Obwohl die Dateigruppe möglicherweise freie Seiten zur Verfügung hat, kann der Benutzer weiterhin den Fehler 1105 erhalten: 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 und Neuerstellen nicht ausgerichteter Indizes in einer Tabelle mit mehr als 1.000 Partitionen ist möglich, wird jedoch 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 sie sich befindet, offline ist oder schreibgeschützt festgelegt ist. Wenn das Schlüsselwort ALL angegeben wird und sich mindestens ein Index in einer Offline- oder schreibgeschützten Dateigruppe befindet, schlägt die Anweisung fehl.

Sicherheit

Erlaubnisse

Erfordert die ALTER-Berechtigung für die Tabelle oder Sicht. Der Benutzer muss Mitglied der festen Serverrolle "sysadmin " oder der db_ddladmin und db_owner festen Datenbankrollen sein.

Verwendung von SQL Server Management Studio

So überprüfen Sie die Fragmentierung eines Indexes

1. Erweitern Sie im Objekt-Explorer die Datenbank, die die Tabelle enthält, auf der Sie die Fragmentierung eines Indexes überprüfen möchten.

2. Erweitern Sie den Ordner Tabellen .

3. Erweitern Sie die Tabelle, auf 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, dessen Fragmentierung Sie ü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 Fragmentierungsseite verfügbar:

   Seitenfülle
   Gibt die durchschnittliche Füllzahl der Indexseiten als Prozentsatz an. 100% bedeutet, dass die Indexseiten vollständig vollständig sind. 50% bedeutet, dass jede Indexseite im Durchschnitt halb voll ist.

   Gesamtfragmentierung
   Der prozentsatz der logischen Fragmentierung. Dies gibt die Anzahl der Seiten in einem Index an, die nicht in der reihenfolge gespeichert sind.

   Durchschnittliche Zeilengröße
   Die durchschnittliche Größe einer Blattebenenzeile.

   Tiefe
   Die Anzahl der Ebenen im Index, einschließlich der Blattebene.

   Weitergeleitete Datensätze
   Die Anzahl der Datensätze in einem Heap, die über Weiterleitungszeiger zu einem anderen Datenort verfügen. (Dieser Zustand tritt während einer Aktualisierung auf, wenn nicht genügend Platz zum Speichern der neuen Zeile am ursprünglichen Speicherort vorhanden ist.)

   Geisterzeilen
   Die Anzahl der Zeilen, die als gelöscht markiert, aber noch nicht entfernt wurden. Diese Zeilen werden durch einen Bereinigungsthread entfernt, wenn der Server nicht ausgelastet ist. Dieser Wert enthält keine Zeilen, die aufgrund einer ausstehenden Momentaufnahme-Isolationstransaktion zurückgehalten werden.

   Indextyp
   Der Indextyp. Mögliche Werte sind "Clustered Index", "Nonclustered Index" und "Primary XML". Tabellen können auch als Heap (ohne Indizes) gespeichert werden, diese Seite mit den Indexeigenschaften kann jedoch nicht geöffnet werden.

   Zeilen auf Blattebene
   Die Anzahl der Zeilen auf Blattebene.

   Maximale Zeilengröße
   Die maximale Zeilengröße auf Blattebene.

   Mindestzeilengröße
   Die minimale Zeilengröße auf Blattebene.

   Seiten
   Die Gesamtanzahl der Datenseiten.

   Partitions-ID
   Die Partitions-ID des B-Baums, der den Index enthält.

   Versionen-Geisterzeilen
   Die Anzahl der Ghost-Datensätze, die aufgrund einer ausstehenden Snapshot-Isolationstransaktion aufbewahrt werden.

Verwenden von Transact-SQL

So überprüfen Sie die Fragmentierung eines Indexes

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-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  
   

   Die obige Anweisung kann ein Resultset ähnlich wie folgt zurückgeben.

   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

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

2. Erweitern Sie den Ordner Tabellen .

3. Erweitern Sie die Tabelle, auf 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. Überprüfen Sie im Dialogfeld " Indizes neu organisieren ", ob sich der richtige Index in den Indizes befindet, um das Raster neu zu organisieren , und klicken Sie auf "OK".

7. Aktivieren Sie das Kontrollkästchen "Daten für große Objekte komprimieren ", um anzugeben, dass alle Seiten, die große Objektdaten (LOB) enthalten, ebenfalls komprimiert werden.

8. Klicken Sie auf OK.

So ordnen Sie alle Indizes in einer Tabelle neu an

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

2. Erweitern Sie den Ordner Tabellen .

3. Erweitern Sie die Tabelle, auf 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. Überprüfen Sie im Dialogfeld "Indizes neu organisieren", ob sich die richtigen Indizes in den Indizes befinden , die neu organisiert werden sollen. Um einen Index aus dem Gitter der Indizes, die reorganisiert werden sollen zu entfernen, wählen Sie den Index aus und drücken Sie dann die ENTF-TASTE.

6. Aktivieren Sie das Kontrollkästchen "Daten für große Objekte komprimieren ", um anzugeben, dass alle Seiten, die große Objektdaten (LOB) enthalten, ebenfalls komprimiert werden.

7. Klicken Sie auf OK.

So erstellen Sie einen Index neu

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

2. Erweitern Sie den Ordner Tabellen .

3. Erweitern Sie die Tabelle, auf 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. Überprüfen Sie im Dialogfeld "Indizes neu erstellen", ob sich der richtige Index im Raster "Indizes, die neu erstellt werden sollen" befindet, und klicken Sie auf "OK".

7. Aktivieren Sie das Kontrollkästchen "Daten für große Objekte komprimieren ", um anzugeben, dass alle Seiten, die große Objektdaten (LOB) enthalten, ebenfalls komprimiert werden.

8. Klicken Sie auf OK.

Verwenden von Transact-SQL

So ordnen Sie einen defragmentierten Index neu an

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-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 ordnen Sie alle Indizes in einer Tabelle neu an

1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-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 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 Datenbank-Engine-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. Im 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 Datenbank-Engine-Instanz her.

2. Klicken Sie in der Standardleiste auf Neue Abfrage.

3. Kopieren Sie das folgende Beispiel, und fügen Sie es in die Abfrage Ein Beispiel gibt das Schlüsselwort ALLan. Dadurch werden alle Indizes neu erstellt, die der Tabelle zugeordnet sind. 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).

Siehe auch

Bewährte Methoden für die Defragmentierung von Microsoft SQL Server 2000-Index