Resolve index fragmentation by reorganizing or rebuilding indexes

• 03/19/2020
• 21 minutes to read
• • p
  • j
  • M
  • M
  • d
  • +11

Applies to: SQL Server (alle unterstützten Versionen) Azure SQL-Datenbank Verwaltete Azure SQL-Instanz Azure Synapse Analytics Parallele Data Warehouse

In diesem Artikel wird beschrieben, wie die Indexdefragmentierung erfolgt, und ihre Auswirkungen auf die Abfrageleistung erläutert. Nachdem Sie den Umfang der Fragmentierung für einen Index ermittelt haben, können Sie einen Index defragmentieren, indem Sie entweder einen Index reorganisieren oder einen Index neu erstellen, indem Sie Transact-SQL-Befehle in dem Tool Ihrer Wahl ausführen oder SQL Server Management Studio verwenden.

Indexfragmentierung Übersicht

Was ist Indexfragmentierung und warum sollte ich mich darum kümmern:

• Fragmentierung liegt vor, wenn Indizes Seiten haben, in denen die logische Reihenfolge innerhalb des Index, basierend auf dem Schlüsselwert des Index, nicht mit der physischen Reihenfolge innerhalb der Indexseiten übereinstimmt.
• Das Datenbankmodul ändert Indizes automatisch, wenn Einfüge-, Aktualisierungs- oder Löschvorgänge an den zugrunde liegenden Daten vorgenommen werden. Beispielsweise kann das Hinzufügen von Zeilen in einer Tabelle dazu führen, dass vorhandene Seiten in Rowstore-Indizes aufgeteilt werden, um Platz für das Einfügen neuer Schlüsselwerte zu schaffen. Im Laufe 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 Seiten haben, in denen die logische Reihenfolge, basierend auf dem Schlüsselwert, nicht mit der physischen Reihenfolge in der Datendatei übereinstimmt.
• Stark fragmentierte Indizes können die Abfrageleistung beeinträchtigen, da zusätzliche E/ A erforderlich sind, um Daten zu finden, auf die der Index verweist. Mehr E / A führt dazu, dass Ihre Anwendung langsam reagiert, insbesondere wenn Scanvorgänge erforderlich sind.

Ermitteln des Fragmentierungsgrads

Der erste Schritt bei der Entscheidung, welche Indexdefragmentierungsmethode verwendet werden soll, besteht darin, den Index zu analysieren, um den Fragmentierungsgrad zu bestimmen. Sie erkennen die Fragmentierung für Rowstore-Indizes und Columnstore-Indizes unterschiedlich.

Erkennung der Fragmentierung von Zeilenspeicherindizes

Mithilfe von sys.dm_db_index_physical_stats, können Sie Fragmentierung in einem bestimmten Index, alle Indizes in einer Tabelle oder indizierte Ansicht, alle Indizes in einer Datenbank oder alle Indizes in allen Datenbanken zu erkennen. Für partitionierte Indizes stellt sys.dm_db_index_physical_stats auch Fragmentierungsinformationen für jede Partition bereit.

Die von sys.dm_db_index_physical_stats zurückgegebene Ergebnismenge enthält die folgenden Spalten:

Nachdem der Grad der Fragmentierung bekannt ist, verwenden Sie die folgende Tabelle, um die beste Methode zum Entfernen der Fragmentierung zu bestimmen: INDEX REORGANISIEREN oder INDEXIEREN.

Spalte	Beschreibung
avg_fragmentation_in_percent	Der Prozentsatz der logischen Fragmentierung (nicht geordnete Seiten im 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.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30% 1	ALTER INDEX REORGANIZE
> 30% 1	ALTER INDEX REBUILD WITH (ONLINE = ON) 2

1 These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE und ALTER INDEX REBUILD. Die tatsächlichen Werte können jedoch von Fall zu Fall variieren. Es ist wichtig, dass Sie experimentieren, um den besten Schwellenwert für Ihre Umgebung zu ermitteln.

2 Der Wiederaufbau eines Index kann online oder offline ausgeführt werden. Die Reorganisation eines Index wird immer online ausgeführt. Um eine ähnliche Verfügbarkeit wie bei der Option Reorganisieren zu erreichen, sollten Sie Indizes online neu erstellen. Weitere Informationen finden Sie unter INDEXIEREN und Indexoperationen online ausführen.

Indizes mit einer Fragmentierung von weniger als 5 Prozent müssen nicht defragmentiert werden, da der Nutzen aus dem Entfernen einer so geringen Fragmentierung fast immer durch die CPU-Kosten für die Reorganisation oder den Wiederaufbau des Index aufgewogen wird. Durch das Neu Erstellen oder Reorganisieren kleiner Rowstore-Indizes wird die tatsächliche Fragmentierung im Allgemeinen nicht verringert.Bis einschließlich SQL Server 2014 (12.x), weist das SQL Server-Datenbankmodul Speicherplatz mit gemischten Erweiterungen zu. Daher werden Seiten mit kleinen Indizes manchmal in gemischten Ausmaßen gespeichert. Gemischte Extents werden von bis zu acht Objekten gemeinsam genutzt, sodass die Fragmentierung in einem kleinen Index nach dem Reorganisieren oder Neuaufbau möglicherweise nicht verringert wird. Siehe auch Überlegungen zum Neuaufbau von Rowstore-Indizes. Weitere Informationen zu Extents finden Sie in den Seiten und im Extents Architecture Guide.

Erkennen der Fragmentierung von Columnstore-Indizes

Mithilfe von sys.dm_db_column_store_row_group_physical_stats können Sie den Prozentsatz gelöschter Zeilen in einem Index ermitteln. Verwenden Sie diese Informationen, um die Fragmentierung in einem bestimmten Index, allen Indizes in einer Tabelle, allen Indizes in einer Datenbank oder allen Indizes in allen Datenbanken zu berechnen.

Die von sys.dm_db_column_store_row_group_physical_stats zurückgegebene Ergebnismenge enthält die folgenden Spalten:

Verwenden Sie diese zurückgegebenen Informationen, um die Indexfragmentierung mithilfe der folgenden Formel zu berechnen:

100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Nachdem der Grad der Indexfragmentierung bekannt ist, ermitteln Sie anhand der folgenden Tabelle die beste Methode zum Entfernen der Fragmentierung: INDEX REORGANISIEREN oder INDEXIEREN.

Spalte	Beschreibung
total_rows	Anzahl der tatsächlich in der Zeilengruppe gespeicherten Zeilen. Bei komprimierten Zeilengruppen umfasst dies die Zeilen, die als gelöscht markiert sind.
deleted_rows	Anzahl der physisch in einer komprimierten Zeilengruppe gespeicherten Zeilen, die zum Löschen markiert sind. 0 für Zeilengruppen, die sich im Delta-Speicher befinden.

So überprüfen Sie die Fragmentierung eines Rowstore-Index mit Transact-SQL

Das folgende Beispiel ermittelt den durchschnittlichen Fragmentierungsprozentsatz aller Indizes in der HumanResources.Employee -Tabelle im AdventureWorks2016 Datenbank.

SELECT a.object_id, object_name(a.object_id) AS TableName, a.index_id, name AS IndedxName, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID (N'AdventureWorks2016_EXT') , OBJECT_ID(N'HumanResources.Employee') , NULL , NULL , NULL) AS aINNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;GO

Die vorherige Anweisung gibt eine Ergebnismenge ähnlich der folgenden zurück.

object_id TableName index_id IndexName avg_fragmentation_in_percent----------- ------------ ----------- ----------------------------------------------------- ------------------------------1557580587 Employee 1 PK_Employee_BusinessEntityID 01557580587 Employee 2 IX_Employee_OrganizationalNode 01557580587 Employee 3 IX_Employee_OrganizationalLevel_OrganizationalNode 01557580587 Employee 5 AK_Employee_LoginID 66.66666666666671557580587 Employee 6 AK_Employee_NationalIDNumber 501557580587 Employee 7 AK_Employee_rowguid 0(6 row(s) affected)

Weitere Informationen finden Sie unter sys.dm_db_index_physical_stats.

So überprüfen Sie die Fragmentierung eines Columnstore-Index mit Transact-SQL

Das folgende Beispiel ermittelt den durchschnittlichen Fragmentierungsprozentsatz aller Indizes in der dbo.FactResellerSalesXL_CCI -Tabelle in der AdventureWorksDW2016 -Datenbank.

SELECT i.object_id, object_name(i.object_id) AS TableName, i.index_id, i.name AS IndexName, 100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'FROM sys.indexes AS i INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups ON i.object_id = CSRowGroups.object_id AND i.index_id = CSRowGroups.index_idWHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'GROUP BY i.object_id, i.index_id, i.nameORDER BY object_name(i.object_id), i.name;

Die vorherige Anweisung gibt eine Ergebnismenge ähnlich der folgenden zurück.

object_id TableName index_id IndexName Fragmentation----------- --------------------------- ----------- ------------------------------- ---------------114099447 FactResellerSalesXL_CCI 1 IndFactResellerSalesXL_CCI 0(1 row(s) affected)

Überprüfen der Indexfragmentierung mit SQL Server Management Studio

1. Erweitern Sie im Objekt-Explorer die Datenbank, die die Tabelle enthält, für die Sie die Fragmentierung eines Index überprüfen möchten.
2. Erweitern Sie den Tabellenordner.
3. Erweitern Sie die Tabelle, in der Sie die Fragmentierung eines Index überprüfen möchten.
4. Erweitern Sie den Indexordner.
5. Klicken Sie mit der rechten Maustaste auf den Index, dessen Fragmentierung Sie überprüfen möchten, und wählen Sie Eigenschaften.
6. Wählen Sie unter Seite auswählen die Option Fragmentierung aus.

Die folgenden Informationen sind auf der Fragmentierungsseite verfügbar:

computed fragmentation in percent value	Applies to version	Corrective statement
> = 20%	SQL Server 2012 (11.x) and SQL Server 2014 (12.x)	ALTER INDEX REBUILD
> = 20%	Starting with SQL Server 2016 (13.x)	ALTER INDEX REORGANIZE

Defragmentieren von Indizes durch Neuaufbau oder Reorganisation des Index

Sie defragmentieren einen fragmentierten Index mit einer der folgenden Methoden:

• Indexreorganisation
• Indexwiederherstellung

Index reorganisieren

Das Reorganisieren eines Index verbraucht minimale Systemressourcen und ist ein Online-Vorgang. Dies bedeutet, dass langfristige blockierende Tabellensperren nicht gehalten werden und Abfragen oder Aktualisierungen der zugrunde liegenden Tabelle während der ALTER INDEX REORGANIZE -Transaktion fortgesetzt werden können.

• Bei Rowstore-Indizes defragmentiert das Datenbankmodul die Blattebene von gruppierten und nicht gruppierten Indizes in Tabellen und Ansichten, indem die Seiten auf Blattebene physisch so angeordnet werden, dass sie der logischen Reihenfolge der Blattknoten entsprechen (von links nach rechts). Durch die Neuorganisation werden die Indexseiten auch basierend auf dem Füllfaktorwert des Index komprimiert. Verwenden Sie sys, um die Einstellung für den Füllfaktor anzuzeigen.Index. Syntaxbeispiele finden Sie unter Examples: Rowstore reorganize.

• Bei Verwendung von Columnstore-Indizes kann der Delta-Speicher nach dem Einfügen, Aktualisieren und Löschen von Daten im Laufe der Zeit mehrere kleine Zeilengruppen aufweisen. Das Reorganisieren eines Columnstore-Index erzwingt alle Zeilengruppen in den Columnstore und kombiniert dann die Zeilengruppen in weniger Zeilengruppen mit mehr Zeilen. Der Vorgang Reorganisieren entfernt auch Zeilen, die aus dem Spaltenspeicher gelöscht wurden. Die Reorganisation erfordert zunächst zusätzliche CPU-Ressourcen, um die Daten zu komprimieren, was die Gesamtsystemleistung verlangsamen kann. Sobald die Daten jedoch komprimiert sind, verbessert sich die Abfrageleistung. Syntaxbeispiele finden Sie unter Examples: ColumnStore reorganize.

Index neu erstellen

Beim Neu Erstellen eines Index wird der Index gelöscht und neu erstellt. Je nach Indextyp und Datenbankmodulversion kann ein Neuerstellungsvorgang online oder offline ausgeführt werden. Informationen zur T-SQL-Syntax finden Sie unter ALTER INDEX REBUILD

• Bei Rowstore-Indizes wird durch die Neuerstellung die Fragmentierung entfernt, Speicherplatz durch Komprimieren der Seiten basierend auf der angegebenen oder vorhandenen Füllfaktoreinstellung zurückgewonnen und die Indexzeilen in zusammenhängenden Seiten neu angeordnet. Wenn ALL angegeben wird, werden alle Indizes in der Tabelle gelöscht und in einer einzigen Transaktion neu erstellt. Fremdschlüsseleinschränkungen müssen nicht im Voraus aufgehoben werden. Wenn Indizes mit 128 oder mehr Extents neu erstellt werden, verschiebt das Datenbankmodul die tatsächlichen Seitenentlastungen und die zugehörigen Sperren bis nach dem Festschreiben der Transaktion. Syntaxbeispiele finden Sie unter Examples: Rowstore reorganize.

• Bei Columnstore-Indizes wird durch die Neuerstellung die Fragmentierung entfernt, alle Zeilen in den Columnstore verschoben und Speicherplatz zurückgewonnen, indem Zeilen physisch gelöscht werden, die logisch aus der Tabelle gelöscht wurden.

  Tipp

  Beginnend mit SQL Server 2016 (13.x), ist der Wiederaufbau des Columnstore-Index in der Regel nicht erforderlich, da REORGANIZE die Grundlagen eines Wiederaufbaus im Hintergrund als Online-Operation ausführt.

  Syntaxbeispiele finden Sie unter Examples: ColumnStore rebuild.

Berechtigungen

Erfordert ALTER Berechtigung für die Tabelle oder Ansicht. Der Benutzer muss Mitglied mindestens einer der folgenden Rollen sein:

• db_ddladmin Datenbankrolle 1
• db_owner Datenbankrolle
• sysadmin Serverrolle

1db_ddladmin Datenbankrolle ist die am wenigsten privilegierte.

Entfernen der Fragmentierung mit SQL Server Management Studio

Um einen Index neu zu organisieren oder neu zu erstellen

1. Erweitern Sie im Objekt-Explorer die Datenbank, die die Tabelle enthält, für die Sie einen Index neu organisieren möchten.
2. Erweitern Sie den Tabellenordner.
3. Erweitern Sie die Tabelle, für die Sie einen Index reorganisieren möchten.
4. Erweitern Sie den Indexordner.Klicken Sie mit der rechten Maustaste auf den Index, den Sie reorganisieren möchten, und wählen Sie Reorganisieren.
5. Überprüfen Sie im Dialogfeld Indizes neu organisieren, ob sich der richtige Index im Raster zu reorganisierende Indizes befindet, und klicken Sie auf OK.
6. Aktivieren Sie das Kontrollkästchen Große Objektspaltendaten komprimieren, um anzugeben, dass alle Seiten, die LOB-Daten (Large Object) enthalten, ebenfalls komprimiert werden.
7. Klicken Sie auf OK.

Um alle Indizes in einer Tabelle zu reorganisieren

1. Erweitern Sie im Objekt-Explorer die Datenbank, die die Tabelle enthält, für die Sie die Indizes reorganisieren möchten.
2. Erweitern Sie den Tabellenordner.
3. Erweitern Sie die Tabelle, für die Sie die Indizes reorganisieren möchten.
4. Klicken Sie mit der rechten Maustaste auf den Ordner Indizes und wählen Sie Alle neu organisieren.
5. Überprüfen Sie im Dialogfeld Indizes neu organisieren, ob sich die richtigen Indizes in den zu reorganisierenden Indizes befinden. Um einen Index aus dem Raster zu reorganisierende Indizes zu entfernen, wählen Sie den Index aus und drücken Sie die Entf-Taste.
6. Aktivieren Sie das Kontrollkästchen Große Objektspaltendaten komprimieren, um anzugeben, dass alle Seiten, die LOB-Daten (Large Object) enthalten, ebenfalls komprimiert werden.
7. Klicken Sie auf OK.

Um einen Index neu zu erstellen

1. Erweitern Sie im Objekt-Explorer die Datenbank, die die Tabelle enthält, für die Sie einen Index neu organisieren möchten.
2. Erweitern Sie den Tabellenordner.
3. Erweitern Sie die Tabelle, für die Sie einen Index reorganisieren möchten.
4. Erweitern Sie den Indexordner.
5. Klicken Sie mit der rechten Maustaste auf den Index, den Sie reorganisieren möchten, und wählen Sie Neu erstellen.
6. Überprüfen Sie im Dialogfeld Indizes neu erstellen, ob sich der richtige Index im Raster Neu zu erstellende Indizes befindet, und klicken Sie auf OK.
7. Aktivieren Sie das Kontrollkästchen Große Objektspaltendaten komprimieren, um anzugeben, dass alle Seiten, die LOB-Daten (Large Object) enthalten, ebenfalls komprimiert werden.
8. Klicken Sie auf OK.

Entfernen der Fragmentierung mit Transact-SQL

So reorganisieren Sie einen fragmentierten Index

Das folgende Beispiel reorganisiert den IX_Employee_OrganizationalLevel_OrganizationalNode Index in der HumanResources.Employee Tabelle in der AdventureWorks2016 Datenbank.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE;

Im folgenden Beispiel wird der IndFactResellerSalesXL_CCI Columnstore-Index der dbo.FactResellerSalesXL_CCI -Tabelle in der AdventureWorksDW2016 -Datenbank neu organisiert.

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.ALTER INDEX IndFactResellerSalesXL_CCI ON FactResellerSalesXL_CCI REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

So reorganisieren Sie alle Indizes in einer Tabelle

Das folgende Beispiel reorganisiert alle Indizes in der HumanResources.Employee -Tabelle in der AdventureWorks2016-Datenbank.

ALTER INDEX ALL ON HumanResources.Employee REORGANIZE;

So erstellen Sie einen fragmentierten Index neu

Im folgenden Beispiel wird ein einzelner Index in der Employee -Tabelle in der AdventureWorks2016 -Datenbank neu erstellt.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.EmployeeREBUILD;

So erstellen Sie alle Indizes in einer Tabelle neu

Das folgende Beispiel erstellt alle Indizes, die der Tabelle in der AdventureWorks2016 Datenbank zugeordnet sind, mithilfe des Schlüsselworts ALL. Es werden drei Optionen angegeben.

ALTER INDEX ALL ON Production.ProductREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

Weitere Informationen finden Sie unter ALTER INDEX (Transact-SQL).

Automatische Index- und Statistikverwaltung

Nutzen Sie Lösungen wie Adaptive Index Defrag, um die Indexdefragmentierung und Statistikaktualisierungen für eine oder mehrere Datenbanken automatisch zu verwalten. Dieses Verfahren wählt automatisch aus, ob ein Index unter anderem nach seinem Fragmentierungsgrad neu erstellt oder reorganisiert werden soll, und aktualisiert Statistiken mit einem linearen Schwellenwert.

Spezifische Überlegungen zum Neuaufbau von Zeilenspeicherindizes

Beim Neuaufbau eines Clusterindex werden automatisch alle nicht gruppierten Indizes neu erstellt, die auf den Clusterschlüssel verweisen, wenn die physischen oder logischen Bezeichner in den nicht gruppierten Indexdatensätzen geändert werden müssen.

Die folgenden Szenarien erzwingen, dass alle nicht gruppierten Rowstore-Indizes in einer Tabelle automatisch neu erstellt werden:

• Erstellen eines Clusterindex für eine Tabelle
• Entfernen eines Clusterindex, wodurch die Tabelle als Heap gespeichert wird
• Ändern des Clusterschlüssels, um Spalten einzuschließen oder auszuschließen

In den folgenden Szenarien müssen nicht alle nicht gruppierten Rowstore-Indizes für eine Tabelle automatisch neu erstellt werden:

• Erstellen eines eindeutigen Clusterindex
• Erstellen eines nicht eindeutigen Clusterindex
• Ändern des Indexschemas, z. B. Anwenden eines Partitionsschemas auf einen Clusterindex oder Verschieben des Clusterindex in eine andere Dateigruppe

Wenn ALL mit der Anweisung ALTER INDEX angegeben wird, werden relationale Indizes, sowohl gruppierte als auch nicht gruppierte, und XML-Indizes für die Tabelle neu organisiert.

Spezifische Überlegungen zum Neuaufbau eines Columnstore-Index

Beim Neuaufbau eines Columnstore-Index liest das Datenbankmodul alle Daten aus dem ursprünglichen Columnstore-Index, einschließlich des Delta-Speichers. Es kombiniert die Daten zu neuen Zeilengruppen und komprimiert die Zeilengruppen in den Columnstore. Das Datenbankmodul defragmentiert den Spaltenspeicher, indem Zeilen, die logisch aus der Tabelle gelöscht wurden, physisch gelöscht werden. Die gelöschten Bytes werden auf der Festplatte zurückgewonnen.

Erstellen Sie eine Partition anstelle der gesamten Tabelle neu

• Das Erstellen der gesamten Tabelle dauert lange, wenn der Index groß ist, und es wird genügend Speicherplatz benötigt, um eine zusätzliche Kopie des Index während der Neuerstellung zu speichern. Normalerweise ist es nur notwendig, die zuletzt verwendete Partition neu zu erstellen.
• Bei partitionierten Tabellen müssen Sie nicht den gesamten Columnstore-Index neu erstellen, da die Fragmentierung wahrscheinlich nur in den Partitionen auftritt, die kürzlich geändert wurden. Faktentabellen und große Dimensionstabellen werden normalerweise partitioniert, um Sicherungs- und Verwaltungsvorgänge für Teile der Tabelle durchzuführen.

Rebuild a partition after heavy DML operations

Das Rebuild einer Partition defragmentiert die Partition und reduziert den Festplattenspeicher. Beim Neuaufbau werden alle Zeilen aus dem Spaltenspeicher gelöscht, die zum Löschen markiert sind, und alle Zeilengruppen aus dem Delta-Speicher in den Spaltenspeicher verschoben. Im Delta-Speicher können mehrere Zeilengruppen mit weniger als einer Million Zeilen vorhanden sein.

Partition nach dem Laden von Daten neu erstellen

Durch das Neu Erstellen einer Partition nach dem Ladedatum wird sichergestellt, dass alle Daten im Columnstore gespeichert werden. Wenn gleichzeitige Prozesse jeweils weniger als 100.000 Zeilen gleichzeitig in dieselbe Partition laden, kann die Partition mit mehreren Delta-Speichern enden. Beim Neuaufbau werden alle Delta Store-Zeilen in den Columnstore verschoben.

Spezifische Überlegungen zur Reorganisation eines Columnstore-Index

Bei der Reorganisation eines Columnstore-Index komprimiert das Datenbankmodul jede GESCHLOSSENE Delta-Zeilengruppe als komprimierte Zeilengruppe in den Columnstore. Beginnend mit SQL Server 2016 (13.x) und in der Azure SQL-Datenbank führt der Befehl REORGANIZE die folgenden zusätzlichen Defragmentierungsoptimierungen online aus:

• Entfernt Zeilen physisch aus einer Zeilengruppe, wenn 10% oder mehr der Zeilen logisch gelöscht wurden. Die gelöschten Bytes werden auf dem physischen Medium zurückgewonnen. Wenn beispielsweise in einer komprimierten Zeilengruppe von 1 Million Zeilen 100 KB Zeilen gelöscht wurden, entfernt SQL Server die gelöschten Zeilen und komprimiert die Zeilengruppe mit 900 KB Zeilen erneut. Es spart den Speicher, indem gelöschte Zeilen entfernt werden.

• Kombiniert eine oder mehrere komprimierte Zeilengruppen, um die Zeilen pro Zeilengruppe auf maximal 1.048.576 Zeilen zu erhöhen. Wenn Sie beispielsweise 5 Stapel mit 102.400 Zeilen importieren, erhalten Sie 5 komprimierte Zeilengruppen. Wenn Sie REORGANIZE ausführen, werden diese Zeilengruppen zu 1 komprimierten Zeilengruppe der Größe 512.000 Zeilen zusammengeführt. Dies setzt voraus, dass es keine Wörterbuchgröße oder Speicherbeschränkungen gab.

• Bei Zeilengruppen, in denen mindestens 10% der Zeilen logisch gelöscht wurden, versucht das Datenbankmodul, diese Zeilengruppe mit einer oder mehreren Zeilengruppen zu kombinieren. Beispielsweise wird Zeilengruppe 1 mit 500.000 Zeilen und Zeilengruppe 21 mit maximal 1.048.576 Zeilen komprimiert. In der Zeilengruppe 21 wurden 60% der Zeilen gelöscht, sodass 409.830 Zeilen übrig bleiben. Das Datenbankmodul bevorzugt die Kombination dieser beiden Zeilengruppen, um eine neue Zeilengruppe mit 909.830 Zeilen zu komprimieren.

Nach dem Laden von Daten können Sie mehrere kleine Zeilengruppen im Delta-Speicher haben. Sie können ALTER INDEX REORGANIZE , um alle Zeilengruppen in den columnstore zu zwingen und dann die Zeilengruppen in weniger Zeilengruppen mit mehr Zeilen zu kombinieren. Der Vorgang Reorganisieren entfernt auch Zeilen, die aus dem columnstore gelöscht wurden.

Einschränkungen und Einschränkungen

Rowstore-Indizes mit mehr als 128 Extents werden in zwei getrennten Phasen neu erstellt: logisch und physisch. In der logischen Phase werden die vorhandenen Zuordnungseinheiten, die vom Index verwendet werden, für die Freigabe 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 Freigabe markierten Zuordnungseinheiten in kurzen Transaktionen, die im Hintergrund ablaufen und nicht viele Sperren erfordern, physisch gelöscht. Weitere Informationen zu Extents finden Sie unter Seiten und Extents Architecture Guide.

Die ALTER INDEX REORGANIZE Anweisung erfordert, dass die Datendatei, die den Index enthält, Speicherplatz zur Verfügung hat, da die Operation nur temporäre Arbeitsseiten derselben Datei zuweisen kann, nicht in einer anderen Datei innerhalb der Dateigruppe. Obwohl die Dateigruppe möglicherweise freie Seiten zur Verfügung hat, kann der Benutzer dennoch auf Fehler 1105 stoßen: 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.

Ein Index kann nicht reorganisiert oder neu erstellt werden, wenn die Dateigruppe, in der er sich befindet, offline oder schreibgeschützt ist. Wenn das Schlüsselwort ALL angegeben ist und sich ein oder mehrere Indizes in einer offline- oder schreibgeschützten Dateigruppe befinden, schlägt die Anweisung fehl.

Statistik:

• Wenn ein Index erstellt oder neu erstellt wird, werden Statistiken erstellt oder aktualisiert, indem alle Zeilen in der Tabelle durchsucht werden. Beginnend mit SQL Server 2012 (11.x) werden Statistiken nicht erstellt oder aktualisiert, indem alle Zeilen in der Tabelle durchsucht werden, wenn ein partitionierter Index erstellt oder neu erstellt wird. Stattdessen verwendet der Abfrageoptimierer den Standard-Stichprobenalgorithmus, um diese Statistiken zu generieren. Um Statistiken über partitionierte Indizes zu erhalten, indem Sie alle Zeilen in der Tabelle scannen, verwenden Sie CREATE STATISTICS oder UPDATE STATISTICS mit der FULLSCAN-Klausel.

• Wenn ein Index reorganisiert wird, werden die Statistiken nicht aktualisiert.

Ein Index kann nicht reorganisiert werden, wenn ALLOW_PAGE_LOCKS auf OFF gesetzt ist.

Bis SQL Server 2017 (14.x) ist das Neu Erstellen eines Clustered Columnstore-Index ein Offline-Vorgang. Das Datenbankmodul muss während der Neuerstellung eine exklusive Sperre für die Tabelle oder Partition erwerben. Die Daten sind offline und während des Neuaufbaus nicht verfügbar, selbst wenn NOLOCK, Read-Committed Snapshot Isolation (RCSI) oder Snapshot Isolation verwendet werden.Beginnend mit SQL Server 2019 (15.x) kann ein gruppierter Columnstore-Index mit der Option ONLINE = ON neu erstellt werden.

Für eine Azure Synapse Analytics-Tabelle (ehemals Azure Synapse Analytics) mit einem geordneten Clustered Columnstore-Index sortiert ALTER INDEX REBUILD die Daten mithilfe von TempDB neu. Überwachen Sie TempDB während der Wiederherstellungsvorgänge. Wenn Sie mehr TempDB-Speicherplatz benötigen, skalieren Sie das Data Warehouse. Skalieren Sie wieder nach unten, sobald die Indexwiederherstellung abgeschlossen ist.

Für eine Azure Synapse Analytics-Tabelle (ehemals Azure Synapse Analytics) mit einem geordneten Clustered Columnstore-Index werden die Daten ALTER INDEX REORGANIZE nicht neu sortiert. Um auf die Daten zuzugreifen, verwenden Sie ALTER INDEX REBUILD .

Verwenden der INDEXWIEDERHERSTELLUNG zur Wiederherstellung nach Hardwarefehlern

In früheren Versionen von SQL Server konnten Sie manchmal einen nicht gruppierten Rowstore-Index neu erstellen, um Inkonsistenzen zu korrigieren, die durch Hardwarefehler verursacht wurden.Ab SQL Server 2008 können Sie solche Inkonsistenzen zwischen dem Index und dem gruppierten Index möglicherweise weiterhin beheben, indem Sie einen nicht gruppierten Index offline neu erstellen. Sie können nicht gruppierte Indexinkonsistenzen jedoch nicht reparieren, indem Sie den Index online neu erstellen, da der Online-Wiederherstellungsmechanismus den vorhandenen nicht gruppierten Index als Grundlage für die Neuerstellung verwendet und somit die Inkonsistenz beibehält. Das Offline-Erstellen des Index kann manchmal einen Scan des Clusterindex (oder Heaps) erzwingen und so die Inkonsistenz beseitigen. Um eine Neuerstellung aus dem gruppierten Index zu gewährleisten, löschen und erstellen Sie den nicht gruppierten Index neu. Wie bei früheren Versionen empfehlen wir die Wiederherstellung von Inkonsistenzen durch Wiederherstellen der betroffenen Daten aus einer Sicherung; Sie können die Indexinkonsistenzen jedoch möglicherweise reparieren, indem Sie den nicht gruppierten Index offline neu erstellen. Weitere Informationen finden Sie unter DBCC CHECKDB (Transact-SQL).

Siehe auch

• SQL Server Index Architecture and Design Guide
• Indexoperationen online durchführen
• INDEX ÄNDERN (Transact-SQL)
• Adaptive Index Defrag
• STATISTIKEN ERSTELLEN (Transact-SQL)
• STATISTIKEN AKTUALISIEREN (Transact-SQL)
• Columnstore Indiziert Abfrageleistung
• Erste Schritte mit Columnstore für operational Analytics
• Columnstore-Indizes für Data Warehousing
• Columnstore-Indizes und die Merge-Richtlinie für Zeilengruppen

Wert	Beschreibung
Page fullness	Gibt die durchschnittliche Fülle der Indexseiten in Prozent an. 100% bedeutet, dass die Indexseiten vollständig gefüllt sind. 50% bedeutet, dass im Durchschnitt jede Indexseite halb voll ist.
Gesamtfragmentierung	Der Prozentsatz der logischen Fragmentierung. Dies gibt die Anzahl der Seiten in einem Index an, die nicht in der richtigen Reihenfolge gespeichert sind.
Durchschnittliche Zeilengröße	Die durchschnittliche Größe einer Zeile auf Blattebene.
Depth	Die Anzahl der Ebenen im Index, einschließlich der Blattebene.
Weitergeleitete Datensätze	Die Anzahl der Datensätze in einem Heap, die Weiterleitungszeiger auf einen anderen Datenspeicherort haben. (Dieser Zustand tritt während einer Aktualisierung auf, wenn nicht genügend Platz zum Speichern der neuen Zeile am ursprünglichen Speicherort vorhanden ist.)
Ghost rows	Die Anzahl der Zeilen, die als gelöscht markiert, aber noch nicht entfernt wurden. Diese Zeilen werden von einem Bereinigungsthread entfernt, wenn der Server nicht ausgelastet ist. Dieser Wert enthält keine Zeilen, die aufgrund einer ausstehenden Snapshot-Isolationstransaktion beibehalten werden.
Index type	Der Typ des Index. Mögliche Werte sind Clustered Index, Nonclustered Index und Primary XML. Tabellen können auch als Heap (ohne Indizes) gespeichert werden, aber dann kann diese Indexeigenschaftenseite nicht geöffnet werden.
Zeilen auf Blattebene	Die Anzahl der Zeilen auf Blattebene.
Maximale Zeilengröße	Die maximale Zeilengröße auf Blattebene.
Minimale Zeilengröße	Die minimale Zeilengröße auf Blattebene.
Seiten	Die Gesamtzahl der Datenseiten.
Partitions-ID	Die Partitions-ID des b-Baums, der den Index enthält.
Version Geisterzeilen	Die Anzahl der Geisterdatensätze, die aufgrund einer ausstehenden Snapshot-Isolationstransaktion beibehalten werden.