ALTER INDEX (Transact-SQL)

Artikel
05/23/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Ändert einen vorhandenen Tabellen- oder Sichtindex (Rowstore, Columnstore oder XML), indem der Index deaktiviert, neu erstellt oder neu organisiert wird oder indem Optionen für den Index festgelegt werden.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server, Azure SQL-Datenbank und Azure SQL verwaltete Instanz.

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Syntax für Azure Synapse Analytics and Analytics Platform System (PDW).

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Argumente

index_name

Der Name des Index. Indexnamen müssen für eine Tabelle oder Sicht eindeutig sein, können aber innerhalb einer Datenbank mehrfach vorkommen. Indexnamen müssen den Regeln für Bezeichner entsprechen.

ALL

Gibt alle Indizes an, die unabhängig vom Indextyp der Tabelle oder Sicht zugeordnet sind. Die Angabe ALL bewirkt, dass die Anweisung fehlschlägt, wenn sich ein oder mehrere Indizes in einer Offline- oder schreibgeschützten Dateigruppe befinden oder der angegebene Vorgang für einen oder mehrere Indextypen nicht zulässig ist. In der folgenden Tabelle werden die Indexvorgänge und die nicht zulässigen Indextypen aufgelistet.

Verwendet bei diesem Vorgang das Schlüsselwort ALL	Erzeugt einen Fehler, wenn mindestens einer dieser Indextypen in der Tabelle enthalten ist
`REBUILD WITH ONLINE = ON`	XML-Index Räumlicher Index Columnstore-Index ¹
`REBUILD PARTITION = <partition_number>`	Nicht partitionierter Index, XML-Index, räumlicher Index oder deaktivierter Index
`REORGANIZE`	Indizes mit `ALLOW_PAGE_LOCKS` Festgelegt auf `OFF`
`REORGANIZE PARTITION = <partition_number>`	Nicht partitionierter Index, XML-Index, räumlicher Index oder deaktivierter Index
`IGNORE_DUP_KEY = ON`	XML-Index Räumlicher Index Columnstore-Index ¹
`ONLINE = ON`	XML-Index Räumlicher Index Columnstore-Index ¹
`RESUMABLE = ON` ²	Resumable indexes not supported with `ALL` keyword

¹ Gilt für SQL Server 2012 (11.x) und höhere Versionen und Azure SQL-Datenbank.

² Gilt für SQL Server 2017 (14.x) und höhere Versionen und Azure SQL-Datenbank

Wenn ALL angegeben PARTITION = <partition_number>ist, müssen alle Indizes ausgerichtet werden. Das bedeutet, dass sie auf der Grundlage der entsprechenden Partitionsfunktionen partitioniert sind. Die Verwendung ALL bewirkt PARTITION , dass alle Indexpartitionen mit demselben <partition_number> neu erstellt oder neu organisiert werden. Weitere Informationen zu partitionierten Indizes finden Sie unter Partitionierte Tabellen und Indizes.

Ausführlichere Informationen zu Indexvorgängen, die online ausgeführt werden können, finden Sie unter Richtlinien für Onlineindexvorgänge.

database_name

Der Name der Datenbank.

schema_name

Der Name des Schemas, zu dem die Tabelle oder Sicht gehören.

table_or_view_name

Der Name der Tabelle oder Sicht, die dem Index zugeordnet ist. Verwenden Sie zum Anzeigen eines Berichts über die Indizes zu einem Objekt die sys.indexes-Katalogsicht.

SQL-Datenbank unterstützt das dreiteilige Namensformat<database_name>.[schema_name].<table_or_view_name>, wenn die database_name die aktuelle Datenbank oder die database_name ist tempdb und die table_or_view_name beginnt mit #.

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ]

Gilt für: SQL Server 2012 (11.x) und höhere Versionen sowie Azure SQL-Datenbank

Gibt an, dass der Index mithilfe der gleichen Spalten, des Indextyps, des Eindeutigkeitsattributs und der Sortierreihenfolge neu erstellt wird. Diese Klausel entspricht DBCC DBREINDEX. Mit REBUILD wird ein deaktivierter Index aktiviert. Durch die Neuerstellung eines gruppierten Indexes werden zugeordnete nicht gruppierte Indizes neu erstellt, es sei denn, das Schlüsselwort ALL ist angegeben. Wenn keine Indexoptionen angegeben sind, werden die vorhandenen Werte der Indexoptionen angewandt, die in sys.indexes gespeichert sind. Für alle Indexoptionen, deren Werte nicht in sys.indexes gespeichert sind, wird der Standardwert angewandt, der in der Argumentdefinition der Option angegeben ist.

Wenn ALL angegeben und die zugrunde liegende Tabelle ein Heap ist, hat der REBUILD Vorgang keine Auswirkungen auf die Tabelle. Alle nicht gruppierten Indizes, die der Tabelle zugeordnet sind, werden neu erstellt.

Der REBUILD-Vorgang kann minimal protokolliert werden, wenn für die Datenbank das massenprotokollierte oder einfache Wiederherstellungsmodell festgelegt ist.

Hinweis

Wenn Sie einen primären XML-Index neu erstellen, ist die zugrunde liegende Benutzertabelle während des Indexvorgangs nicht verfügbar.

Für Columnstore-Indizes wird durch den REBUILD-Vorgang Folgendes ausgelöst:

Die Sortierreihenfolge wird nicht verwendet.
Abrufen einer exklusiven Sperre für die Tabelle oder Partition, während der REBUILD-Vorgang ausgeführt wird. Die Daten sind "offline" und nicht verfügbar während der REBUILD, auch bei Verwendung NOLOCK, Read Commit Snapshot Isolation (RCSI) oder Snapshot Isolation (SI).
Komprimiert alle Daten im Columnstore neu. Während der REBUILD-Vorgang ausgeführt wird, sind zwei Kopien des Columnstore-Indexes vorhanden. Nach Abschluss des REBUILD-Vorgangs wird der ursprüngliche Columnstore-Index von SQL Server gelöscht.

Weitere Informationen finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

PARTITION

Gibt an, dass nur eine Partition eines Index neu erstellt oder neu organisiert wird. PARTITION kann nicht angegeben werden, wenn index_name kein partitioniertes Index ist.

PARTITION = ALL Erstellt alle Partitionen neu.

Warnung

Das Erstellen und Neuerstellen nicht ausgerichteter Indizes in einer Tabelle mit mehr als 1.000 Partitionen ist möglich, wird jedoch nicht unterstützt. Dies kann zu einer beeinträchtigten Leistung oder zu übermäßigem Arbeitsspeicherverbrauch während dieser Vorgänge führen. Microsoft empfiehlt, bei mehr als 1.000 Partitionen nur ausgerichtete Indizes zu verwenden.

partition_number

Die Partitionsnummer eines partitionierten Index, der neu erstellt oder neu organisiert werden soll. partition_number ist ein konstanter Ausdruck, der auf Variablen verweisen kann. Hierbei kann es sich um Funktionen oder Variablen mit benutzerdefiniertem Typ sowie um benutzerdefinierte Funktionen handeln, die jedoch nicht auf eine Transact-SQL-Anweisung verweisen können. partition_number muss vorhanden sein. Andernfalls schlägt die Anweisung fehl.
WITH ( <single_partition_rebuild_index_option> )

Beim REBUILD-Vorgang für eine einzelne Partition (PARTITION = partition_number) können die Optionen SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSION und XML_COMPRESSION angegeben werden. XML-Indizes können bei einem REBUILD-Vorgang für eine einzelne Partition nicht angegeben werden.

DISABLE

Markiert den Index als deaktiviert und als nicht verfügbar für das Datenbank-Engine. Jeder Index kann deaktiviert werden. Die Indexdefinition eines deaktivierten Indexes bleibt weiterhin im Systemkatalog ohne zugrunde liegende Indexdaten bestehen. Durch das Deaktivieren eines gruppierten Indexes wird der Benutzerzugriff auf die zugrunde liegenden Tabellendaten verhindert. Verwenden Sie ALTER INDEX REBUILD oder CREATE INDEX WITH DROP_EXISTING, um einen Index zu aktivieren. Weitere Informationen finden Sie unter "Indizes und Einschränkungen deaktivieren" und "Indizes und Einschränkungen aktivieren".

REORGANIZE bei einem Rowstore-Index

Gibt für Rowstore-Indizes an, REORGANIZE die Indexblattebene neu zu organisieren. Der REORGANIZE Vorgang lautet:

Wird immer online ausgeführt. Dies bedeutet, dass keine blockierenden Langzeitsperren für Tabellen aufrechterhalten werden und dass während der ALTER INDEX REORGANIZE-Transaktion Abfragen oder Updates der zugrunde liegenden Tabelle fortgesetzt werden können.
Nicht zulässig für einen deaktivierten Index
Nicht zulässig, wenn ALLOW_PAGE_LOCKS festgelegt auf OFF.
Es wurde kein Rollback ausgeführt, wenn es innerhalb einer Transaktion ausgeführt wird und die Transaktion zurückgesetzt wird.

Hinweis

Wenn ALTER INDEX REORGANIZE anstelle des impliziten Standardtransaktionsmodus explizite Transaktionen verwendet (z. B. ALTER INDEX innerhalb von BEGIN TRAN ... COMMIT/ROLLBACK), ist das Sperrverhalten von REORGANIZE einschränkender, was möglicherweise zu Blockierungen führt. Weitere Informationen zu impliziten Transaktionen finden Sie unter SET IMPLICIT_TRANSACTIONS.

Weitere Informationen finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

Gilt für Rowstore-Indizes.

LOB_COMPACTION = ON

Gibt an, dass alle Seiten komprimiert werden, die Daten der folgenden LOB-Datentypen (Large Objects) enthalten: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) und xml. Durch das Komprimieren dieser Daten kann die Datenmenge auf der Festplatte verringert werden.
Bei einem gruppierten Index werden dadurch alle LOB-Spalten komprimiert, die in der Tabelle enthalten sind.
Bei einem nicht gruppierten Index werden alle LOB-Spalten komprimiert, die (eingeschlossene) Nichtschlüsselspalten im Index sind.
Mit REORGANIZE ALL wird LOB_COMPACTION für alle Indizes ausgeführt. Bei jedem Index werden alle LOB-Spalten im gruppierten Index, in der zugrunde liegenden Tabelle oder in eingeschlossenen Spalten in einem nicht gruppierten Index komprimiert.

LOB_COMPACTION = OFF

Seiten, die LOB-Daten enthalten, werden nicht komprimiert.
OFF hat keine Auswirkung auf einen Heap.

REORGANIZE bei einem Columnstore-Index

Komprimiert bei Columnstore-Indizes REORGANIZE jede CLOSED Delta-Zeilengruppe als komprimierte Zeilengruppe in den Columnstore. Der REORGANIZE-Vorgang wird immer online durchgeführt. Dies bedeutet, dass keine blockierenden Langzeitsperren für Tabellen aufrechterhalten werden und dass während der ALTER INDEX REORGANIZE-Transaktion Abfragen oder Updates der zugrunde liegenden Tabelle fortgesetzt werden können. Weitere Informationen finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

REORGANIZE ist nicht erforderlich, um Delta-Zeilengruppen in komprimierte Zeilengruppen zu verschieben CLOSED . Der Hintergrund-Tupel-Mover (TM)-Prozess wird regelmäßig aktiviert, um Delta-Zeilengruppen zu komprimieren CLOSED . Wir empfehlen, die Verwendung zu verwenden REORGANIZE , wenn der Tupel-Mover hinter sich fällt. REORGANIZE zeilengruppen aggressiver komprimieren können.
Informationen zum Komprimieren aller OPEN und CLOSED Zeilengruppen finden Sie in der REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS) Option in diesem Abschnitt.

Für Spaltenspeicherindizes in SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank REORGANIZE werden die folgenden zusätzlichen Defragmentierungsoptimierungen online ausgeführt:

Es werden Zeilen physisch aus der Zeilengruppe entfernt, wenn mindestens 10 % der Zeilen logisch gelöscht wurden. Die gelöschten Bytes werden auf den physischen Medien freigegeben. Wenn beispielsweise eine komprimierte Zeilengruppe von 1 Million Zeilen 100.000 Zeilen gelöscht hat, entfernt SQL Server die gelöschten Zeilen und komprimiert die Zeilengruppe mit 900k Zeilen erneut. Durch das Entfernen gelöschter Zeilen wird Speicherplatz eingespart.
Eine oder mehrere komprimierte Zeilengruppen werden kombiniert, um die Anzahl der Zeilen pro Zeilengruppe auf maximal 1.048.576 Zeilen zu erhöhen. Wenn Sie beispielsweise 5 Batches mit 102.400 Zeilen massenweise importieren, erhalten Sie 5 komprimierte Zeilengruppen. Wenn Sie ausführen REORGANIZE, werden diese Zeilengruppen mit einer komprimierten Zeilengruppe von 512.000 Zeilen zusammengeführt. Dies setzt voraus, dass keine Wörterbuchumfangsbegrenzungen oder Arbeitsspeichereinschränkungen vorhanden sind.
Bei Zeilengruppen, in denen 10 % oder mehr zeilen logisch gelöscht wurden, versucht SQL Server, diese Zeilengruppe mit einer oder mehreren Zeilengruppen zu kombinieren. Beispiel: Zeilengruppe 1 ist mit 500.000 Zeilen komprimiert und Zeilengruppe 21 mit dem Maximalwert von 1.048.576 Zeilen. In Zeilengruppe 21 wurden 60 % der Zeilen gelöscht, wodurch noch 409.830 Zeilen vorhanden sind. In SQL Server werden diese beiden Zeilengruppen vorzugsweise kombiniert, um eine neue Zeilengruppe mit 909.830 Zeilen zu komprimieren.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Gilt für Columnstore-Indizes.

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank

COMPRESS_ALL_ROW_GROUPSbietet eine Möglichkeit, Zeilengruppen im Columnstore zu erzwingen oder CLOSED Delta-Zeilengruppen zu erzwingenOPEN. Mit dieser Option ist es nicht notwendig, den Columnstore-Index zum Leeren der Delta-Zeilengruppe neu zu erstellen. Damit sowie durch die anderen Defragmentierungsfeatures zum Entfernen und Zusammenfügen von Zeilengruppen ist es in den meisten Fällen nicht mehr erforderlich, den Index neu zu erstellen.

ON erzwingt alle Zeilengruppen in den Columnstore, unabhängig von Größe und Zustand (CLOSED oder OPEN).
OFF erzwingt alle CLOSED Zeilengruppen in den Columnstore.

Weitere Informationen finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

SET ( <set_index Option> [ ,... n ] )

Gibt Indexoptionen ohne das Neuerstellen oder Neuorganisieren des Indexes an. SET kann für einen deaktivierten Index nicht angegeben werden.

PAD_INDEX = { ON | OFF }

Gibt die Auffüllung von Indizes an. Der Standardwert ist OFF.

ON

Der Prozentsatz des mit FILLFACTOR angegebenen freien Speicherplatzes wird für die Zwischenebenenseiten des Index angewendet. Wenn FILLFACTOR nicht gleichzeitig PAD_INDEX angegeben ONwird, wird der in sys.indexes gespeicherte Füllfaktorwert verwendet.
OFF oder fillfactor ist nicht angegeben.

Die Zwischenebenenseiten werden nahezu vollständig gefüllt. Dabei bleibt genügend Platz für mindestens eine Zeile der maximal zulässigen Größe eines Indexes erhalten. Dies erfolgt auf der Grundlage des Schlüsselsatzes in den Zwischenseiten.

Weitere Informationen finden Sie unter CREATE INDEX.

FILLFACTOR = fillfactor

Gibt einen Prozentwert an, der dem Füllfaktor entspricht. Dieser Faktor legt fest, wie weit die Datenbank-Engine die Blattebene jeder Indexseite während der Indexerstellung oder -änderung auffüllen soll. Der Wert für fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Die Standardeinstellung ist 0. Die Füllfaktorwerte 0 und 100 sind in jeder Hinsicht identisch.

Eine explizite FILLFACTOR-Einstellung gilt nur bei der erstmaligen Erstellung oder bei der Neuerstellung des Index. Die Datenbank-Engine hält den angegebenen Prozentsatz des Speicherplatzes auf den Seiten nicht dynamisch frei. Weitere Informationen finden Sie unter CREATE INDEX.

Verwenden Sie zum Anzeigen der Füllfaktoreinstellung fill_factor in sys.indexes.

Wichtig

Das Erstellen oder Ändern eines gruppierten Indexes mit einem FILLFACTOR-Wert wirkt sich auf den Speicherplatz aus, den die Daten belegen, da Datenbank-Engine die Daten beim Erstellen des gruppierten Indexes neu verteilt.

SORT_IN_TEMPDB = { ON | OFF }

Gibt an, ob die Sortierergebnisse gespeichert tempdbwerden sollen. Der Standardwert ist OFF mit Ausnahme von Azure SQL-Datenbank Hyperscale. Für alle Indexerstellungsvorgänge in Hyperscale ist SORT_IN_TEMPDB unabhängig von der angegebenen Option immer auf ON festgelegt, sofern nicht die fortsetzbare Indexneuerstellung verwendet wird.

ON

Die Zwischensortierungsergebnisse, die zum Erstellen des Indexes verwendet werden, werden in tempdbgespeichert. Wenn tempdb sich eine andere Gruppe von Datenträgern als die Benutzerdatenbank befindet, kann dies die Zum Erstellen eines Indexes erforderliche Zeit verringern. Sie erhöht jedoch den Betrag an Speicherplatz, der während der Indexerstellung verwendet wird.
OFF

Die Zwischenergebnisse der Sortierung werden in derselben Datenbank gespeichert wie der Index.

Wenn ein Sortiervorgang nicht erforderlich ist oder im Arbeitsspeicher ausgeführt werden kann, wird die SORT_IN_TEMPDB-Option ignoriert.

Weitere Informationen finden Sie unter SORT_IN_TEMPDB-Option für Indizes.

IGNORE_DUP_KEY = { ON | OFF }

Gibt die Fehlermeldung an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die IGNORE_DUP_KEY-Option gilt nur für Einfügevorgänge nach dem Erstellen oder Neuerstellen des Index. Der Standardwert ist OFF.

ON

Eine Warnmeldung wird ausgegeben, wenn doppelte Schlüsselwerte in einen eindeutigen Index eingefügt werden. Nur die Zeilen, die die Eindeutigkeitseinschränkung verletzen, schlagen fehl.
OFF

Eine Fehlermeldung wird ausgegeben, wenn doppelte Schlüsselwerte in einen eindeutigen Index eingefügt werden. Der gesamte INSERT Vorgang wird zurückgesetzt.

IGNORE_DUP_KEY kann nicht für Indizes festgelegt ON werden, die in einer Ansicht, nicht eindeutigen Indizes, XML-Indizes, räumlichen Indizes und gefilterten Indizes erstellt wurden.

Verwenden Sie sys.indexes, um IGNORE_DUP_KEY anzuzeigen.

In abwärtskompatibler Syntax entspricht WITH IGNORE_DUP_KEYWITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF }

Deaktivieren oder aktivieren Sie die Option AUTO_STATISTICS_UPDATEfür die automatische Statistikaktualisierung für die Statistiken im Zusammenhang mit den angegebenen Indizes. Der Standardwert ist OFF.

ON

Automatische Statistikaktualisierungen werden deaktiviert, nachdem der Index neu erstellt wurde.
OFF

Automatische Statistikaktualisierungen werden aktiviert, nachdem der Index neu erstellt wurde.

Um die automatische Aktualisierung von Statistiken wiederherzustellen, legen Sie den STATISTICS_NORECOMPUTE Befehl auf OFFoder wird ohne die NORECOMPUTE Klausel ausgeführtUPDATE STATISTICS.

Warnung

Wenn Sie die automatische Aktualisierung von Statistiken deaktivieren, wird möglicherweise verhindert, dass der Abfrageoptimierer optimale Ausführungspläne für Abfragen auswählt, die die Tabelle umfassen. Sie sollten diese Option sparsam und nur von einem qualifizierten Datenbankadministrator verwenden.

Diese Einstellung verhindert nicht, dass während des Neuerstellungsvorgangs ein automatisches Update mit vollständigem Scan der indexbezogenen Statistiken ausgeführt wird.

STATISTICS_INCREMENTAL = { ON | OFF }

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank

Wenn ONdie erstellten Statistiken pro Partitionsstatistik vorliegen. Wenn OFFdie Statistikstruktur verworfen wird und SQL Server die Statistiken neu komputet. Der Standardwert ist OFF.

Wenn statistiken pro Partition nicht unterstützt werden, wird die Option ignoriert und eine Warnung generiert. Inkrementelle Statistiken werden für folgende Statistiktypen nicht unterstützt:

Statistiken, die mit Indizes erstellt wurden, die über keine Partitionsausrichtung mit der Basistabelle verfügen
Statistiken, die für lesbare sekundäre Datenbanken von Verfügbarkeitsgruppen erstellt wurden
Statistiken, die für schreibgeschützte Datenbanken erstellt wurden
Statistiken, die für gefilterte Indizes erstellt wurden
Statistiken, die für Sichten erstellt wurden
Statistiken, die für interne Tabellen erstellt wurden
Statistiken, die mit räumlichen Indizes oder XML-Indizes erstellt wurden

ONLINE = { ON | OFF } <wie für rebuild_index_option>

Gibt an, ob die zugrunde liegenden Tabellen und zugeordneten Indizes für Abfragen und Datenänderungen während des Indexvorgangs verfügbar sind. Der Standardwert ist OFF.

Für einen XML-Index oder einen räumlichen Index wird nur ONLINE = OFF unterstützt, und wenn ONLINE dieser auf ON einen Fehler festgelegt ist, wird ausgelöst.

Wichtig

Onlineindexvorgänge sind nicht in jeder Edition von Microsoft SQL Server verfügbar. Eine Liste der Funktionen, die von den SQL Server-Editionen unterstützt werden, finden Sie unter Editionen und unterstützte Funktionen von SQL Server 2022.

ON

Lang andauernde Tabellensperren werden während des Indexvorgangs nicht aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte freigegebene Sperre für die Quelltabelle aufrechterhalten. Auf diese Weise können Abfragen oder Updates der zugrunde liegenden Tabelle und Indizes fortgesetzt werden. Am Anfang des Vorgangs wird eine Freigegebene (S)-Sperre kurz auf dem Quellobjekt gehalten. Am Ende des Vorgangs wird eine S-Sperre kurz auf der Quelle gehalten, wenn ein nicht gruppierter Index erstellt wird. Eine Schemaänderungssperre (Sch-M) wird abgerufen, wenn ein gruppierter Index online erstellt oder abgelegt wird und ein gruppierter oder nicht gruppierter Index neu erstellt wird. ONLINE kann nicht festgelegt ON werden, wenn ein Index in einer lokalen temporären Tabelle erstellt wird.
OFF

Tabellensperren werden während des Indexvorgangs angewandt. Ein Offlineindexvorgang, durch den ein gruppierter, räumlicher oder XML-Index erstellt, neu erstellt oder gelöscht wird bzw. ein nicht gruppierter Index neu erstellt oder gelöscht wird, aktiviert eine Sch-M-Sperre für die Tabelle. Dadurch wird verhindert, dass Benutzer während des Vorgangs auf die zugrunde liegende Tabelle zugreifen können. Ein Offlineindexvorgang, bei dem ein nicht gruppierter Index erstellt wird, aktiviert eine freigegebene Sperre (S) für die Tabelle. Dadurch werden Aktualisierungen der zugrunde liegenden Tabelle verhindert, aber Lesevorgänge wie SELECT Anweisungen erlaubt.

Weitere Informationen finden Sie unter "Online ausführen von Indexvorgängen".

Indizes, einschließlich Indizes globaler temporärer Tabellen, können online neu erstellt werden. Es gelten folgende Ausnahmen:

XML-Index
Index für eine lokale temp-Tabelle
Eindeutiger gruppierter Ausgangsindex für eine Sicht
Columnstore-Indizes
Gruppierter Index, wenn die zugrunde liegende Tabelle LOB-Datentypen (image, ntext, text) und räumliche Datentypen enthält.
varchar(max)- und varbinary(max)-Spalten können nicht Teil eines Index sein. In SQL Server (ab SQL Server 2012 (11.x)) und Azure SQL-Datenbank kann ein gruppierter Index mit der Option ONLINE erstellt oder neu erstellt werden, wenn eine Tabelle Spalten vom Typ varchar(max) oder varbinary(max) enthält. Azure SQL-Datenbank lässt die ONLINE-Option nicht zu, wenn die Basistabelle Spalten vom Typ varchar(max) oder varbinary(max) enthält.

Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen.

Die folgenden XEvents beziehen sich auf ALTER TABLE ... SWITCH PARTITION und die Neuerstellung von Onlineindizes.

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

Das vorhandene XEvent progress_report_online_index_operation für Onlineindexvorgänge umfasst partition_number und partition_id.

RESUMABLE = { ON | OFF}

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank

Gibt an, ob ein Onlineindexvorgang fortsetzbar ist.

EIN

Der Indexvorgang ist fortsetzbar.
OFF

Der Indexvorgang ist nicht fortsetzbar.

MAX_DURATION = Zeit [ MINUTEN ] verwendet mit `RESUMABLE = ON` (erfordert `ONLINE = ON`)

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank

Gibt die Zeitspanne an (als ganzzahligen Wert in Minuten), in der ein fortsetzbarer Onlineindexvorgang ausgeführt wird, bevor er angehalten wird.

Wichtig

Ausführlichere Informationen zu Indexvorgängen, die online ausgeführt werden können, finden Sie unter Richtlinien für Onlineindexvorgänge.

Hinweis

Resumable Online index rebuilds aren't supported on columnstore indexes.

ALLOW_ROW_LOCKS = { ON | OFF }

Gibt an, ob Zeilensperren zulässig sind. Der Standardwert ist ON.

EIN

Zeilensperren sind beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Zeilensperren verwendet werden.
OFF

Zeilensperren werden nicht verwendet.

ALLOW_PAGE_LOCKS = { ON | OFF }

Gibt an, ob Seitensperren zulässig sind. Der Standardwert ist ON.

ON

Seitensperren sind beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Seitensperren verwendet werden.
OFF

Seitensperren werden nicht verwendet.

Hinweis

Ein Index kann nicht neu organisiert werden, wenn ALLOW_PAGE_LOCKS er auf OFF".

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Gilt für: SQL Server 2019 (15.x) und höhere Versionen sowie Azure SQL-Datenbank

Gibt an, ob der Konflikt beim Einfügen der letzten Seite optimiert werden soll. Der Standardwert ist OFF. Weitere Informationen finden Sie unter Sequenzielle Schlüssel.

MAXDOP = max_degree_of_parallelism

Überschreibt die Konfigurationsoption Max. Grad an Parallelität während des Indexvorgangs. Weitere Informationen finden Sie unter Konfigurieren des maximalen Parallelitätsgrads (Serverkonfigurationsoption). Wird verwendet MAXDOP , um die Anzahl der Prozessoren zu begrenzen, die in einer parallelen Planausführung verwendet werden. Maximal sind 64 Prozessoren zulässig.

Wichtig

Obwohl die MAXDOP Option für alle XML-Indizes syntaktisch unterstützt wird, wird für einen räumlichen Index oder einen primären XML-Index ALTER INDEX derzeit nur ein einzelner Prozessor verwendet.

max_degree_of_parallelism kann folgende Werte haben:

1: Unterdrückt die parallele Plangenerierung.
>1: Beschränkt die maximale Anzahl von Prozessoren, die in einem parallelen Indexvorgang verwendet werden, auf die angegebene Zahl.
0 (Standard): Verwendet die tatsächliche Anzahl von Prozessoren oder weniger basierend auf der aktuellen Systemauslastung.

Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Hinweis

Parallele Indexvorgänge sind nicht in jeder Edition von SQL Server verfügbar. Eine Liste der Funktionen, die von den SQL Server-Editionen unterstützt werden, finden Sie unter Editionen und unterstützte Funktionen von SQL Server 2022.

COMPRESSION_DELAY = { 0 | Dauer [ Minuten ] }

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x))

Bei einer datenträgerbasierten Tabelle gibt die Verzögerung die Mindestanzahl von Minuten an, die eine Delta-Zeilengruppe im CLOSED Status in der Delta-Zeilengruppe verbleiben muss, bevor SQL Server sie in die komprimierte Zeilengruppe komprimieren kann. Da datenträgerbasierte Tabellen keine Einfüge- und Aktualisierungszeiten für einzelne Zeilen nachverfolgen, wendet SQL Server die Verzögerung auf Delta-Zeilengruppen im CLOSED Zustand an.

Die Standardeinstellung beträgt 0 Minuten.

Empfehlungen für die Verwendung COMPRESSION_DELAYfinden Sie unter "Erste Schritte mit Columnstore" für Echtzeit-Betriebsanalysen.

DATA_COMPRESSION

Gibt die Datenkomprimierungsoption für den angegebenen Index, die Partitionsnummer oder den Bereich von Partitionen an. Die folgenden Optionen sind verfügbar:

Keine

Der Index oder die angegebenen Partitionen werden nicht komprimiert. Dies gilt nicht für Columnstore-Indizes.
ROW

Der Index oder die angegebenen Partitionen werden mit Zeilenkomprimierung komprimiert. Dies gilt nicht für Columnstore-Indizes.
PAGE

Der Index oder die angegebenen Partitionen werden mit Seitenkomprimierung komprimiert. Dies gilt nicht für Columnstore-Indizes.
COLUMNSTORE

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank

Gilt nur für columnstore-Indizes, einschließlich nicht gruppierter und gruppierter columnstore-Indizes. COLUMNSTORE Gibt an, den Index oder die angegebenen Partitionen zu dekomprimieren, die mit der COLUMNSTORE_ARCHIVE Option komprimiert werden. Wenn die Daten wiederhergestellt werden, wird sie weiterhin mit der Columnstore-Komprimierung komprimiert, die für alle Spaltenspeicherindizes verwendet wird.
COLUMNSTORE_ARCHIVE

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank

Gilt nur für columnstore-Indizes, einschließlich nicht gruppierter und gruppierter columnstore-Indizes. COLUMNSTORE_ARCHIVE komprimiert die angegebene Partition weiter auf eine kleinere Größe. Dies empfiehlt sich bei der Archivierung und in Situationen, in denen es auf eine geringere Speichergröße und nicht auf den zusätzlichen Zeitaufwand für das Speichern und Abrufen ankommt.

Weitere Informationen zur Komprimierung finden Sie unter Datenkomprimierung.

XML_COMPRESSION

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

Gibt die XML-Komprimierungsoption für den angegebenen Index an, der mindestens eine Spalte vom XML-Datentyp enthält. Die folgenden Optionen sind verfügbar:

EIN

Der Index oder die angegebenen Partitionen werden mit der XML-Komprimierung komprimiert.
OFF

Der Index oder die angegebenen Partitionen werden nicht komprimiert.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

Gibt die Partitionen an, für die die Einstellung DATA_COMPRESSION oder XML_COMPRESSION gilt. Wenn der Index nicht partitioniert ist, generiert das ON PARTITIONS Argument einen Fehler. Wenn die ON PARTITIONS-Klausel nicht angegeben wird, gilt die DATA_COMPRESSION- oder XML_COMPRESSION-Option für alle Partitionen eines partitionierten Index.

<partition_number_expression> kann wie folgt angegeben werden:

Geben Sie die Nummer für eine Partition an, beispielsweise: ON PARTITIONS (2).
Geben Sie die Partitionsnummern für mehrere einzelne Partitionen durch Kommas getrennt an, beispielsweise: ON PARTITIONS (1, 5).
Geben Sie sowohl Bereiche als auch einzelne Partitionen an: ON PARTITIONS (2, 4, 6 TO 8).

Für <range> können durch das Wort „TO“ getrennte Partitionsnummern angegeben werden, z. B. ON PARTITIONS (6 TO 8).

Wenn Sie für verschiedene Partitionen unterschiedliche Datenkomprimierungstypen festlegen möchten, geben Sie die Option DATA_COMPRESSION mehrmals an, beispielsweise:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Sie können die Option XML_COMPRESSION auch mehrmals angeben. Beispiel:

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { ON | OFF } <wie für single_partition_rebuild_index_option>

Gibt an, ob ein Index oder eine Indexpartition einer zugrunde liegenden Tabelle online oder offline neu erstellt werden kann. Wenn REBUILD ... ONLINE = ON ausgeführt wird, sind die Daten in dieser Tabelle für Abfragen und Datenänderungen während des Indexvorgangs verfügbar. Der Standardwert ist OFF.

ON

Lang andauernde Tabellensperren werden während des Indexvorgangs nicht aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte freigegebene Sperre für die Quelltabelle aufrechterhalten. Eine Schemastabilitätssperre (Schema Stability, Sch-S) für die Tabelle ist erforderlich, wenn die Indexneuerstellung gestartet wird, und eine Schemaänderungssperre (Schema Modification, Sch-M) für die Tabelle ist am Ende der Onlineneuerstellung des Index erforderlich. Obwohl beide Metadatensperren von kurzer Dauer sind, muss insbesondere die Sch-M-Sperre auf den Abschluss aller blockierenden Transaktionen warten. Während der Wartezeit sperrt die Sch-M-Sperre alle anderen Transaktionen, die an dieser Sperre warten, wenn sie auf die gleiche Tabelle zugreifen.

Hinweis

Bei der Onlineneuerstellung des Index können die low_priority_lock_wait-Optionen festgelegt werden. Weitere Informationen finden Sie unter WAIT_AT_LOW_PRIORITY mit Onlineindexvorgängen.
OFF

Tabellensperren werden während des Indexvorgangs angewandt. Dadurch wird verhindert, dass Benutzer während des Vorgangs auf die zugrunde liegende Tabelle zugreifen können.

RESUME

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank

Fortsetzen eines Indexvorgangs, der manuell oder aufgrund eines Fehlers angehalten wurde.

MAX_DURATION wird mit RESUMABLE = ON verwendet

Die Zeitspanne (als ganzzahliger Wert in Minuten), die ein fortsetzbarer Onlineindexvorgang ausgeführt wird, nachdem er fortgesetzt wurde. Nach Ablauf dieser Zeitspanne wird der fortsetzbare Vorgang angehalten, falls er noch ausgeführt wird.
WAIT_AT_LOW_PRIORITY verwendet mit RESUMABLE = ON und ONLINE = ON.

Beim Fortsetzen einer Onlineindexneuerstellung nach dem Anhalten muss auf blockierende Vorgänge für diese Tabelle gewartet werden. WAIT_AT_LOW_PRIORITY gibt an, dass der Onlineindex-Neuerstellungsvorgang auf Sperrungen mit niedriger Priorität wartet, sodass andere Vorgänge fortgesetzt werden können, während der Onlineindexbuildvorgang wartet. Das Weglassen der WAIT_AT_LOW_PRIORITY-Option entspricht WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Weitere Informationen finden Sie unter WAIT_AT_LOW_PRIORITY.

PAUSE

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank

Anhalten eines fortsetzbaren Onlineneuerstellungsvorgangs für einen Index.

ABORT

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank

Abbrechen eines ausgeführten oder angehaltenen Indexvorgangs, der als fortsetzbar deklariert wurde. Zum Beenden eines fortsetzbaren Indexneuerstellungsvorgangs müssen Sie explizit einen ABORT-Befehl ausführen. Durch das Auftreten eines Fehlers oder durch Anhalten eines fortsetzbaren Indexvorgangs wird dessen Ausführung nicht beendet. Der Vorgang befindet sich stattdessen in einem unbestimmten Pausenzustand.

Bemerkungen

ALTER INDEX kann nicht verwendet werden, um einen Index neu zu partitionieren oder ihn in eine andere Dateigruppe zu verschieben. Das Ändern der Indexdefinition, z. B. das Hinzufügen oder Löschen von Spalten oder das Ändern der Spaltenreihenfolge, ist mit dieser Anweisung nicht möglich. Verwenden Sie CREATE INDEX mit der DROP_EXISTING-Klausel, um diese Vorgänge auszuführen.

Wenn eine Option nicht explizit angegeben ist, wird die aktuelle Einstellung angewandt. Wenn beispielsweise eine FILLFACTOR Einstellung nicht in der REBUILD Klausel angegeben ist, wird der im Systemkatalog gespeicherte Füllfaktorwert während des Neuerstellungsprozesses verwendet. Verwenden Sie zum Anzeigen der aktuellen Indexoptionseinstellungen sys.indexes.

Die Werte für ONLINE, MAXDOP und SORT_IN_TEMPDB werden nicht im Systemkatalog gespeichert. Der Standardwert der Option wird verwendet, sofern die Option nicht in der Indexanweisung angegeben ist.

Auf Mehrprozessorcomputern werden für ALTER INDEX REBUILD, wie bei anderen Abfragen auch, automatisch weitere Prozessoren verwendet, um die Scan- und Sortierungsvorgänge auszuführen, die mit einem Ändern des Index verbunden sind. Wenn Sie ALTER INDEX REORGANIZE mit oder ohne LOB_COMPACTION ausführen, entspricht der Wert von Max. Grad an Parallelität einem einzelnen Threadvorgang. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Wichtig

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 ist und mindestens ein Index in einer Offline- oder schreibgeschützten Dateigruppe enthalten ist, erzeugt die Anweisung einen Fehler.

Neuerstellen von Indizes

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. Fremdschlüsseleinschränkungen müssen nicht im Voraus gelöscht werden. Wenn Indizes mit mindestens 128 Blöcken neu erstellt werden, verzögert das Datenbank-Engine die tatsächlichen aufgehobenen Seitenzuordnungen sowie deren zugeordnete Sperren, bis für die Transaktion ein Commit ausgeführt wird.

Weitere Informationen finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

Neuorganisieren von Indizes

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.

Wenn ALL angegeben ist, werden relationale Indizes, sowohl gruppierte als auch nicht gruppierte, und XML-Indizes der Tabelle neu organisiert. Bei Angabe von ALL gelten einige Einschränkungen; diese finden Sie in der Definition für ALL in diesem Artikel im Abschnitt „Argumente“.

Weitere Informationen finden Sie unter Optimieren der Indexwartung, um die Abfrageleistung zu verbessern und den Ressourcenverbrauch zu verringern.

Wichtig

Bei einer Azure Synapse Analytics-Tabelle mit einem sortierten gruppierten Spaltenspeicherindex ALTER INDEX REORGANIZE werden die Daten nicht neu sortiert. Verwenden Sie ALTER INDEX REBUILD zum Neusortieren der Daten.

Indizes deaktivieren

Durch das Deaktivieren eines Indexes wird der Benutzerzugriff auf den Index sowie auf die zugrunde liegenden Tabellendaten gruppierter Indizes verhindert. Die Indexdefinition bleibt im Systemkatalog erhalten. Beim Deaktivieren eines nicht gruppierten oder gruppierten Indexes in einer Sicht werden die Indexdaten physisch gelöscht. Durch das Deaktivieren eines gruppierten Indexes wird der Benutzerzugriff auf die Daten verhindert; die Daten bleiben jedoch in der B-Struktur unverwaltet, bis der Index gelöscht oder neu erstellt wird. Führen Sie eine Abfrage für die is_disabled-Spalte in der sys.indexes-Katalogsicht aus, um den Status eines aktivierten oder deaktivierten Index anzuzeigen.

Hinweis

In der Dokumentation wird der Begriff B-Struktur im Allgemeinen in Bezug auf Indizes verwendet. In Rowstore-Indizes implementiert die Datenbank-Engine eine B+-Struktur. Dies gilt nicht für Spaltenspeicherindizes oder Indizes für speicheroptimierte Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

Befindet sich eine Tabelle in einer Transaktionsreplikationsveröffentlichung, können die Indizes, die mit Primärschlüsselspalten verknüpft sind, nicht deaktiviert werden, weil diese Indizes von der Replikation benötigt werden. Wenn Sie einen Index deaktivieren möchten, müssen Sie zuerst die Tabelle aus der Veröffentlichung löschen. Weitere Informationen finden Sie unter Veröffentlichen von Daten und Datenbankobjekten.

Verwenden Sie die ALTER INDEX REBUILD-Anweisung oder die CREATE INDEX WITH DROP_EXISTING-Anweisung, um den Index zu aktivieren. Das Neuerstellen eines deaktivierten gruppierten Indexes kann nicht mit der Option ausgeführt werden, auf ONdie ONLINE festgelegt ist. Weitere Informationen finden Sie unter Deaktivieren von Indizes und Einschränkungen.

Optionen festlegen

Sie können die Optionen ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, , OPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEYund STATISTICS_NORECOMPUTE für einen angegebenen Index festlegen, ohne diesen Index neu zu erstellen oder neu zu organisieren. Die geänderten Werte werden sofort auf den Index angewendet. Verwenden Sie zum Anzeigen dieser Einstellungen sys.indexes. Weitere Informationen finden Sie unter Festlegen von Indexoptionen.

Zeilen- und Seitensperren (Optionen)

Wenn ALLOW_ROW_LOCKS = ON und ALLOW_PAGE_LOCK = ON angegeben sind, sind Sperren auf Zeilen-, Seiten- und Tabellenebene zulässig, wenn auf den Index zugegriffen wird. Das Datenbank-Engine wählt die geeignete Sperre aus und kann die Sperre von einer Zeilen- oder Seitensperre auf eine Tabellensperre ausweiten.

Wenn ALLOW_ROW_LOCKS = OFF und ALLOW_PAGE_LOCK = OFF angegeben sind, ist nur eine Sperre auf Tabellenebene zulässig, wenn auf den Index zugegriffen wird.

Wenn beim Festlegen der Optionen für Zeilen- oder Seitensperren ALL angegeben ist, werden die Einstellungen auf alle Indizes angewendet. Wenn es sich bei der zugrunde liegenden Tabelle um einen Heap handelt, werden die Einstellungen folgendermaßen angewendet:

Option	Details
`ALLOW_ROW_LOCKS = ON or OFF`	Für den Heap und alle zugeordneten nicht gruppierten Indizes.
`ALLOW_PAGE_LOCKS = ON`	Für den Heap und alle zugeordneten nicht gruppierten Indizes.
`ALLOW_PAGE_LOCKS = OFF`	Vollständig für die nicht gruppierten Indizes. Dies bedeutet, dass für die nicht gruppierten Indizes keine Seitensperren zulässig sind. Beim Heap sind nur gemeinsame Sperren (S, Shared), Updatesperren (U, Update) und exklusive Sperren (X, Exclusive) für die Seite unzulässig. Das Datenbank-Engine kann weiterhin eine beabsichtigte Seitensperre (IS, IU oder IX) für interne Zwecke abrufen.

Online-Indexvorgänge

Wenn Sie einen Index neu erstellen und die ONLINE Option auf ONfestgelegt ist, stehen die zugrunde liegenden Objekte, die Tabellen und zugeordneten Indizes für Abfragen und Datenänderungen zur Verfügung. Sie können auch einen Teil eines Indexes online neu erstellen, der sich in einer einzelnen Partition befindet. Exklusive Tischsperren werden nur für kurze Zeit während des Änderungsprozesses gehalten.

Das Neuorganisieren eines Indexes wird stets online durchgeführt. Bei dem Prozess werden Sperren nicht dauerhaft aufrechterhalten, daher werden laufende Abfragen oder Updates nicht blockiert.

Sie können gleichzeitige Onlineindexvorgänge nur für dieselbe Tabelle oder Tabellenpartition ausführen, wenn Sie die folgenden Vorgänge ausführen:

Erstellen mehrerer nicht gruppierter Indizes.
Neuorganisieren unterschiedlicher Indizes in derselben Tabelle.
Neuorganisieren unterschiedlicher Indizes während der Neuerstellung von nicht überlappenden Indizes derselben Tabelle.

Alle anderen gleichzeitig durchgeführten Onlineindexvorgänge erzeugen einen Fehler. Sie können beispielsweise nicht zwei oder mehr Indizes zur gleichen Zeit für dieselbe Tabelle neu erstellen bzw. beim Neuerstellen eines vorhandenen Index keinen neuen Index für dieselbe Tabelle erstellen.

Weitere Informationen finden Sie unter "Online ausführen von Indexvorgängen".

Fortsetzbare Indexvorgänge

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank

Eine Onlineneuerstellung eines Indexes wird mit der RESUMABLE = ON-Option als fortsetzbar angegeben.

Die RESUMABLE-Option wird in den Metadaten nicht für einen bestimmten Index beibehalten und gilt nur für die Dauer der aktuellen DDL-Anweisung. Daher muss die RESUMABLE = ON-Klausel explizit angegeben werden, wenn Fortsetzbarkeit aktiviert werden soll.
Die MAX_DURATION-Option wird für die RESUMABLE = ON- oder die low_priority_lock_wait-Option unterstützt.
- MAX_DURATION für RESUMABLE option specifies the time interval for an index being rebuild. Sobald diese Zeit verwendet wird, wird die Indexneuerstellung entweder angehalten oder die Ausführung abgeschlossen. Der Benutzer entscheidet, wann die Neuerstellung eines angehaltenen Index fortgesetzt werden kann. Die Zeitspanne (in Minuten) für MAX_DURATION muss größer als 0 Minuten und kleiner oder gleich einer Woche (7 × 24 × 60 = 10080 Minuten) sein. Eine lange Pause für einen Indexvorgang kann sich auf die DML-Leistung in einer bestimmten Tabelle sowie auf die Kapazität des Datenbankdatenträgers auswirken, da sowohl Indizes (der ursprüngliche als auch der neu erstellte) Speicherplatz erfordern und während DML-Vorgängen aktualisiert werden müssen. Wenn MAX_DURATION die Option nicht angegeben wird, wird der Indexvorgang bis zum Abschluss oder bis zum Auftreten eines Fehlers fortgesetzt.
- Mit der Argumentoption low_priority_lock_wait können Sie entscheiden, wie der Indexvorgang fortgesetzt werden kann, wenn er für die Sch-M-Sperre blockiert wird.
Durch das erneute Ausführen der ursprünglichen ALTER INDEX REBUILD-Anweisung mit denselben Parametern wird ein angehaltener Indexneuerstellungsvorgang fortgesetzt. Auch durch Ausführen der ALTER INDEX RESUME-Anweisung kann ein angehaltener Indexneuerstellungsvorgang fortgesetzt werden.
Die Option SORT_IN_TEMPDB = ON wird für einen fortsetzbaren Index nicht unterstützt.
Der DDL-Befehl mit RESUMABLE = ON kann nicht innerhalb einer expliziten Transaktion ausgeführt werden (kann nicht Teil des BEGIN TRAN ... COMMIT-Blocks sein).
Nur Indexvorgänge, die angehalten wurden, sind fortsetzbar.
Wenn Sie einen angehaltenen Indexvorgang fortsetzen, können Sie den MAXDOP Wert in einen neuen Wert ändern. Wenn MAXDOP beim Fortsetzen eines angehaltenen Indexvorgangs nicht angegeben wird, wird der letzte MAXDOP Wert verwendet. Wenn die Option für den MAXDOP Index-Neuerstellungsvorgang überhaupt nicht angegeben ist, wird der Standardwert übernommen.
Wenn Sie den Indexvorgang sofort anhalten möchten, können Sie den laufenden Befehl beenden (STRG+C) oder den Befehl ALTER INDEX PAUSE oder KILL <session_id> ausführen. Sobald der Befehl angehalten wurde, kann er mithilfe RESUME der Option fortgesetzt werden.
Mit dem ABORT-Befehl wird die Sitzung beendet, die die ursprüngliche Indexneuerstellung gehostet hat, und der Indexvorgang wird abgebrochen.
Für die fortsetzbare Indexneuerstellung werden keine zusätzlichen Ressourcen benötigt, mit Ausnahme von:
- Zusätzlicher Speicherplatz, der erforderlich ist, um den zu erstellenden Index beizubehalten, einschließlich der Zeit, zu der index angehalten wird
- DDL-Status zur Verhinderung von DDL-Änderungen
Die Ghostbereinigung wird während der Index-Pause-Phase ausgeführt, wird aber während der Indexausführung angehalten. Die folgenden Funktionen sind für Indexneuerstellungsvorgänge deaktiviert:
- Neuerstellen eines deaktivierten Index wird mit RESUMABLE = ON nicht unterstützt
- ALTER INDEX REBUILD ALL-Befehl
- ALTER TABLE bei der Indexneuerstellung
- DDL-Befehl mit RESUMABLE = ON kann nicht innerhalb einer expliziten Transaktion ausgeführt werden (kann nicht Teil des BEGIN TRAN ... COMMIT-Blocks sein)
- Erstellen Sie einen Index neu, der berechnet wurde oder TIMESTAMP Spalten als Schlüsselspalten enthält.
Für den Fall, dass die Basistabelle lob-Spalten enthält, die gruppierten Index neu erstellt werden können, erfordert eine Sch-M-Sperre beim Starten dieses Vorgangs.

Hinweis

Der DDL-Befehl wird so lange ausgeführt, bis er entweder abgeschlossen ist, angehalten wird oder ein Fehler auftritt. Falls der Befehl angehalten wird, wird ein Fehler ausgegeben, der angibt, dass der Vorgang angehalten wurde und dass die Indexerstellung nicht abgeschlossen wurde. Weitere Informationen zum aktuellen Indexstatus finden Sie unter sys.index_resumable_operations. Tritt ein Fehler auf, wird auch hier eine Fehlermeldung ausgegeben.

WAIT_AT_LOW_PRIORITY bei Onlineindexvorgängen

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank

Die Syntax low_priority_lock_wait ermöglicht die Angabe des Verhaltens WAIT_AT_LOW_PRIORITY. Die Verwendung von WAIT_AT_LOW_PRIORITY ist nur mit ONLINE = ON möglich.

Um die DDL-Anweisung für eine Onlineindexneuerstellung auszuführen, müssen alle aktiven blockierenden Transaktionen, die für eine bestimmte Tabelle ausgeführt werden, abgeschlossen sein. Wenn die Onlineindexneuerstellung ausgeführt wird, werden alle neuen Transaktionen, die zur Ausführung in dieser Tabelle bereit sind, blockiert. Obwohl die Dauer der Sperre für die Onlineindexerstellung kurz ist, warten sie auf alle geöffneten Transaktionen in einer bestimmten Tabelle, um die neuen Transaktionen zu starten und zu blockieren, können den Durchsatz erheblich beeinträchtigen, was zu einer Verlangsamung oder einem Timeout führt und den Zugriff auf die zugrunde liegende Tabelle erheblich einschränken kann.

Mit der WAIT_AT_LOW_PRIORITY-Option können Datenbankadministrator*innen die Schemastabilitätssperren (Sch-S) und die Schemaänderungssperren (Sch-M), die für die Onlineneuerstellung von Indizes erforderlich sind, verwalten und eine von zwei Optionen auswählen. In beiden Fällen gilt: Sind während der Wartezeit (MAX_DURATION = n [minutes]) keine blockierenden Aktivitäten vorhanden, wird die Onlineindexneuerstellung ohne Wartezeit sofort ausgeführt, und die DDL-Anweisung wird abgeschlossen.

WAIT_AT_LOW_PRIORITY gibt an, dass der Onlineindex-Neuerstellungsvorgang auf Sperrungen mit niedriger Priorität wartet, sodass andere Vorgänge fortgesetzt werden können, während der Onlineindexbuildvorgang wartet. Das Weglassen der WAIT AT LOW PRIORITY-Option ist gleichwertig mit WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = Zeit [ MINUTEN ]

Die Wartezeit (ein ganzzahliger Wert in Minuten), den der Onlineindex neu erstellt, warten bei der Ausführung des DDL-Befehls mit niedriger Priorität. Wenn der Vorgang für die MAX_DURATION Zeit blockiert wird, wird die angegebene ABORT_AFTER_WAIT Aktion ausgeführt. MAX_DURATION Die Zeit ist immer in Minuten, und das Wort MINUTES kann weggelassen werden.

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKER ]

NONE

Es wird weiterhin mit normaler (regulärer) Priorität auf die Sperre gewartet.
SELF

Beendet den DDL-Vorgang zur Onlineindexneuerstellung, der derzeit ausgeführt wird, ohne weitere Aktionen auszuführen. Die Option SELF kann nicht mit MAX_DURATION 0 verwendet werden.
BLOCKERS

Bricht alle Benutzertransaktionen ab, die den DDL-Vorgang zur Onlineindexneuerstellung blockieren, sodass der Vorgang fortgesetzt werden kann. Die Option BLOCKERS setzt voraus, dass bei der Anmeldung die Berechtigung ALTER ANY CONNECTION vorliegt.

Einschränkungen für räumliche Indizes

Wenn Sie einen räumlichen Index neu erstellen, ist die zugrunde liegende Benutzertabelle während des Indexvorgangs nicht verfügbar, da für den räumlichen Index eine Schemasperre gilt.

Die PRIMARY KEY Einschränkung in der Benutzertabelle kann nicht geändert werden, während ein räumlicher Index in einer Spalte dieser Tabelle definiert ist. Um die PRIMARY KEY Einschränkung zu ändern, legen Sie zuerst jeden räumlichen Index der Tabelle ab. Nachdem Sie die PRIMARY KEY Einschränkung geändert haben, können Sie die einzelnen räumlichen Indizes neu erstellen.

Räumliche Indizes können in einem Neuerstellungsvorgang einer einzelnen Partition nicht angegeben werden. Sie können räumliche Indizes jedoch bei einer vollständigen Neuerstellung der Partition angeben.

Wenn Sie Optionen ändern möchten, die für einen räumlichen Index spezifisch sind (z. B. BOUNDING_BOX oder GRID), können Sie entweder eine CREATE SPATIAL INDEX-Anweisung verwenden, die DROP_EXISTING = ON angibt, oder den räumlichen Index verwerfen und einen neuen Index erstellen. Ein Beispiel finden Sie unter CREATE SPATIAL INDEX.

Datenkomprimierung

Weitere Informationen zur Datenkomprimierung finden Sie unter "Datenkomprimierung".

Verwenden Sie zum Auswerten der Auswirkungen von Änderung PAGE und ROW Komprimierung auf eine Tabelle, einen Index oder eine Partition die sp_estimate_data_compression_savings gespeicherten Prozedur.

Für partitionierte Indizes gelten die folgenden Einschränkungen:

Mit ALTER INDEX ALL ... können Sie die Komprimierungseinstellung einer einzelnen Partition nicht ändern, wenn die Tabelle nicht ausgerichtete Indizes aufweist.
Mit der Syntax ALTER INDEX <index> ... REBUILD PARTITION ... wird die angegebene Partition des Index neu erstellt.
Mit der Syntax ALTER INDEX <index> ... REBUILD WITH ... werden alle Partitionen des Index neu erstellt.

Statistik

Wenn Sie ALTER INDEX ALL ... für eine Tabelle ausführen, werden nur die Statistiken mit zugeordneten Indizes aktualisiert. Automatische oder manuelle Statistiken, die anstelle eines Index in der Tabelle erstellt wurden, werden nicht aktualisiert.

Berechtigungen

Zum Ausführen von ALTER INDEX benötigen Sie mindestens die ALTER-Berechtigung für die Tabelle oder Ansicht.

Versionshinweise

SQL-Datenbank verwendet die Optionen „Dateigruppe“ und „Filestream“ nicht.
Columnstore-Indizes sind vor SQL Server 2012 (11.x) nicht verfügbar.
Fortsetzbare Indexvorgänge sind verfügbar ab SQL Server 2017 (14.x) und Azure SQL-Datenbank.

Einfaches Syntaxbeispiel

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

Beispiele: Columnstore-Indizes

Diese Beispiele gelten für Columnstore-Indizes.

A. REORGANIZE-Demo

In diesem Beispiel wird veranschaulicht, wie der Befehl ALTER INDEX REORGANIZE funktioniert. Es wird eine Tabelle mit mehreren Zeilengruppen erstellt, die anschließend mithilfe von REORGANIZE zusammengeführt werden.

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Mit der TABLOCK-Option können Sie Zeilen parallel einfügen. Ab SQL Server 2016 (13.x) kann der INSERT INTO Vorgang bei Verwendung parallel TABLOCK ausgeführt werden.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Führen Sie diesen Befehl aus, um die OPEN Delta-Zeilengruppen anzuzeigen. Die Anzahl der Zeilengruppen hängt vom Grad der Parallelität ab.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Führen Sie diesen Befehl aus, um alle CLOSED OPEN Zeilengruppen in den Columnstore zu erzwingen.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Führen Sie diesen Befehl erneut aus, und Sie sehen, dass kleinere Zeilengruppen mit einer komprimierten Zeilengruppe zusammengeführt werden.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Komprimieren von CLOSED-Delta-Zeilengruppen im Columnstore

In diesem Beispiel wird die REORGANIZE Option verwendet, jede Delta-Zeilengruppe als komprimierte Zeilengruppe in den Columnstore zu komprimieren CLOSED . Dies ist nicht erforderlich, ist aber nützlich, wenn der Tupel-Mover nicht schnell genug Zeilengruppen komprimiert CLOSED .

Sie können beide Beispiele in der Beispieldatenbank AdventureWorksDW2022 ausführen.

Dieses Beispiel wird auf allen Partitionen ausgeführt REORGANIZE .

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Dieses Beispiel wird auf einer bestimmten Partition ausgeführt REORGANIZE .

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. Komprimieren aller OPEN- und CLOSED-Delta-Zeilengruppen im Columnstore

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank

Der Befehl REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) komprimiert jede OPEN und CLOSED jede Delta-Zeilengruppe als komprimierte Zeilengruppe in den Columnstore. Dadurch wird der Deltastore geleert, und es werden alle Zeilen im Columnstore komprimiert. Dies ist insbesondere nach dem Ausführen einer Vielzahl von Einfügevorgängen nützlich, da die Zeilen bei diesen Vorgängen in einer oder mehreren Delta-Zeilengruppen gespeichert werden.

REORGANIZE kombiniert Zeilengruppen zum Ausfüllen von Zeilengruppen bis zu einer maximalen Anzahl von Zeilen <= 1.024.576. Wenn Sie daher alle OPEN Und CLOSED Zeilengruppen komprimieren, haben Sie nicht viele komprimierte Zeilengruppen, die nur wenige Zeilen enthalten. Bestmöglich gefüllte Zeilengruppen verringern die komprimierte Größe und verbessern die Abfrageleistung.

In den folgenden Beispielen wird die AdventureWorksDW2022-Datenbank verwendet.

In diesem Beispiel werden alle OPEN Und CLOSED Delta-Zeilengruppen in den Spaltenspeicherindex verschoben.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

In diesem Beispiel werden alle OPEN Und CLOSED Delta-Zeilengruppen in den Spaltenspeicherindex für eine bestimmte Partition verschoben.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D: Online-Defragmentieren eines Columnstore-Index

Gilt nicht für: SQL Server 2012 (11.x) und SQL Server 2014 (12.x).

Ab SQL Server 2016 (13.x) REORGANIZE werden Delta-Zeilengruppen im Columnstore mehr komprimiert. Sie können auch eine Onlinedefragmentierung durchführen. Zunächst wird die Größe des Columnstores verringert, indem gelöschte Zeilen physisch entfernt werden, wenn mindestens 10 % der Zeilen in einer Zeilengruppe gelöscht wurden. Anschließend werden Zeilengruppen zu größeren Zeilengruppen bis maximal 1.024.576 Zeilen pro Zeilengruppe zusammengeführt. Alle geänderten Zeilengruppen werden erneut komprimiert.

Hinweis

Ab SQL Server 2016 (13.x) ist die Neuerstellung eines Spaltenspeicherindex in den meisten Situationen nicht mehr erforderlich, da REORGANIZE gelöschte Zeilen entfernt und Zeilengruppen zusammengeführt werden. Die COMPRESS_ALL_ROW_GROUPS Option erzwingt alle OPEN oder CLOSED Delta-Zeilengruppen in den Columnstore, die zuvor nur mit einer Neuerstellung durchgeführt werden konnten. REORGANIZE ist online und tritt im Hintergrund auf, damit Abfragen fortgesetzt werden können, wenn der Vorgang erfolgt.

Das folgende Beispiel führt eine REORGANIZE Defragmentierung des Index durch physisches Entfernen von Zeilen aus, die logisch aus der Tabelle gelöscht wurden, und das Zusammenführen von Zeilengruppen.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Neuerstellen eines gruppierten Columnstore-Index im Offline-Modus

Gilt für: SQL Server (ab SQL Server 2012 (11.x))

Tipp

Ab SQL Server 2016 (13.x) und in Azure SQL-Datenbank wird die Verwendung von ALTER INDEX REORGANIZE anstelle von ALTER INDEX REBUILD für Columnstore-Indizes empfohlen.

Hinweis

In SQL Server 2012 (11.x) und SQL Server 2014 (12.x) REORGANIZE wird nur zum Komprimieren CLOSED von Zeilengruppen in den Columnstore verwendet. Die einzige Möglichkeit, Defragmentierungsvorgänge auszuführen und die Übernahme aller Delta-Zeilengruppen in den Columnstore zu erzwingen, ist das Neuerstellen des Index.

In diesem Beispiel wird veranschaulicht, wie Sie einen gruppierten Columnstore-Index neu erstellen und die Übernahme aller Delta-Zeilengruppen in den Columnstore erzwingen. Im ersten Schritt wird eine Tabelle FactInternetSales2 in der Datenbank AdventureWorksDW2022 mit einem gruppierten Columnstore-Index vorbereitet, und es werden Daten aus den ersten vier Spalten eingefügt.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

Die Ergebnisse zeigen eine OPEN Zeilengruppe, was bedeutet, dass SQL Server wartet, bis weitere Zeilen hinzugefügt werden, bevor sie die Zeilengruppe schließt und die Daten in den Spaltenspeicher verschiebt. Mit der nächsten Anweisung wird der gruppierte Columnstore-Index neu erstellt und die Übernahme aller Zeilen in den Columnstore erzwungen.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Die Ergebnisse der SELECT Anweisung zeigen, dass die Zeilengruppe lautet COMPRESSED, was bedeutet, dass die Spaltensegmente der Zeilengruppe jetzt komprimiert und im Columnstore gespeichert sind.

F. Neuerstellen einer Partition eines gruppierten Columnstore-Index im Offline-Modus

Gilt für: SQL Server 2012 (11.x) und höhere Versionen

Verwenden Sie ALTER INDEX REBUILD mit der Partitionsoption, um eine Partition eines großen gruppierten Columnstore-Index neu zu erstellen. In diesem Beispiel wird die Partition 12 neu erstellt. Ab SQL Server 2016 (13.x) sollten Sie REBUILD durch REORGANIZE ersetzen.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Ändern eines gruppierten Columnstore-Index zur Verwendung der Archivierungskomprimierung

Gilt nicht für: SQL Server 2012 (11.x)

Sie können die Größe eines gruppierten Spaltenspeicherindex noch weiter reduzieren, indem Sie die COLUMNSTORE_ARCHIVE Datenkomprimierungsoption verwenden. Dies kann bei älteren Daten nützlich sein, die Sie kostengünstiger speichern möchten. Es wird empfohlen, dies nur für Daten zu verwenden, auf die nicht häufig zugegriffen wird, da die Dekomprimierung langsamer ist als bei der normalen COLUMNSTORE Komprimierung.

Im folgenden Beispiel wird ein gruppierter columnstore-Index für die Verwendung der Archivierungskomprimierung neu erstellt. Anschließend wird gezeigt, wie die Archivierungskomprimierung entfernt wird. Das Endergebnis verwendet nur die Columnstore-Komprimierung.

Bereiten Sie zunächst das Beispiel vor, indem Sie eine Tabelle mit einem gruppierten Columnstore-Index erstellen. Komprimieren Sie die Tabelle dann weiter, indem Sie die Archivierungskomprimierung verwenden.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

In diesem Beispiel wird die Archivierungskomprimierung entfernt und nur die Columnstore-Komprimierung verwendet.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Beispiele: Rowstore-Indizes

A. Neuerstellen eines Indexes

Im folgenden Beispiel wird ein einzelner Index für die Employee-Tabelle der AdventureWorks2022-Datenbank neu erstellt.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Neuerstellen aller Indizes einer Tabelle und Angeben von Optionen

Im folgenden Beispiel wird das Schlüsselwort ALLangegeben. Dadurch werden alle Indizes neu erstellt, die der Tabelle Production.Product in der Datenbank AdventureWorks2022 zugeordnet sind. Es werden drei Optionen angegeben.

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

Im folgenden Beispiel werden die ONLINE-Option einschließlich der Option für Sperren mit niedriger Priorität sowie die Zeilenkomprimierungsoption hinzugefügt.

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. Neuorganisieren eines Index mit LOB-Komprimierung

Im folgenden Beispiel wird ein einzelner gruppierter Index in der AdventureWorks2022-Datenbank neu organisiert. Da der Index einen LOB-Datentyp in der Blattebene enthält, komprimiert die Anweisung auch alle Seiten, die die LOB-Daten enthalten. Die Angabe der WITH (LOB_COMPACTION = ON)-Option ist nicht erforderlich, da der Standardwert auf ON festgelegt ist.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D: Festlegen von Optionen für einen Index

Im folgenden Beispiel werden mehrere Optionen für den AK_SalesOrderHeader_SalesOrderNumber-Index in der AdventureWorks2022-Datenbank festgelegt.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Deaktivieren eines Index

Im folgenden Beispiel wird ein nicht gruppierter Index für die Employee-Tabelle der AdventureWorks2022-Datenbank deaktiviert.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Deaktivieren von Einschränkungen

Im folgenden Beispiel wird eine PRIMARY KEY Einschränkung deaktiviert, indem der PRIMARY KEY Index in der AdventureWorks2022 Datenbank deaktiviert wird. Die FOREIGN KEY Einschränkung für die zugrunde liegende Tabelle wird automatisch deaktiviert, und die Warnmeldung wird angezeigt.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Das Resultset gibt diese Warnmeldung zurück.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Aktivieren von Einschränkungen

Im folgenden Beispiel werden die Einschränkungen und FOREIGN KEY Einschränkungen aktiviert, die PRIMARY KEY in Beispiel F deaktiviert wurden.

Die PRIMARY KEY Einschränkung wird durch neuerstellen des PRIMARY KEY Indexes aktiviert.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

Die FOREIGN KEY Einschränkung wird dann aktiviert.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Neuerstellen eines partitionierten Index

Im folgenden Beispiel wird eine einzelne Partition mit der Partitionsnummer 5 des partitionierten IX_TransactionHistory_TransactionDate-Indexes in der AdventureWorks2022-Datenbank neu erstellt. Partition 5 wird mit ONLINE=ON neu erstellt, und die zehnminütige Wartezeit für die Sperre mit niedriger Priorität gilt für jede einzelne Sperre, die durch die Indexneuerstellung abgerufen wird. Wenn die Sperre während dieser Zeit nicht für die vollständige Neuerstellung des Indexes reicht, wird die Neuerstellungsanweisung selbst aufgrund von ABORT_AFTER_WAIT = SELF abgebrochen.

Gilt für: SQL Server 2014 (12.x) und höhere Versionen und Azure SQL-Datenbank

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

I. Ändern der Komprimierungseinstellung eines Index

Im folgenden Beispiel wird ein Index für eine nicht partitionierte rowstore-Tabelle neu erstellt.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. Ändern der Einstellung eines Indexes mit XML-Komprimierung

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

Im folgenden Beispiel wird ein Index für eine nicht partitionierte rowstore-Tabelle neu erstellt.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Weitere Datenkomprimierungsbeispiele finden Sie unter "Datenkomprimierung".

K. Onlineneuerstellung von fortsetzbaren Indizes

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank

In den folgenden Beispielen wird veranschaulicht, wie fortsetzbare Onlineindizes neu erstellt werden.

Führen Sie eine Onlineindexerstellung als reaktivierbaren Vorgang mit MAXDOP = 1. Wenn Sie denselben Befehl erneut ausführen, nachdem ein Indexvorgang angehalten wurde, wird der Indexneuerstellungsvorgang automatisch fortgesetzt.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

Führen Sie eine Onlineindexerstellung als reaktivierbaren Vorgang aus, der MAX_DURATION auf 240 Minuten festgelegt ist.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

Halten Sie einen fortsetzbaren Onlineneuerstellungsvorgangs für einen Index an.

ALTER INDEX test_idx on test_table PAUSE;

Fortsetzen einer Onlineindexneuerstellung für eine Indexneuerstellung, die als reaktivierbarer Vorgang ausgeführt wurde, der einen neuen Wert für MAXDOP den Wert 4 angibt.

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

Setzen Sie einen Vorgang zur Onlineindexneuerstellung für eine fortsetzbar ausgeführte Onlineindexneuerstellung fort. Legen Sie MAXDOP die Ausführungszeit für den Index auf 240 Minuten fest, und wenn ein Index für die Sperre blockiert wird, warten Sie 10 Minuten und danach alle Blocker.

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

Abbrechen des fortsetzungsfähigen Indexneuerstellungsvorgangs, der ausgeführt oder angehalten wird.

ALTER INDEX test_idx on test_table ABORT;

Teilen über

ALTER INDEX (Transact-SQL)

Syntax

Argumente

index_name

ALL

database_name

schema_name

table_or_view_name

REBUILD [ WITH ( <rebuild_index_option> [ ,... n ] ]

PARTITION

DISABLE

REORGANIZE bei einem Rowstore-Index

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

REORGANIZE bei einem Columnstore-Index

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

SET ( <set_index Option> [ ,... n ] )

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { ON | OFF }

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF }

STATISTICS_INCREMENTAL = { ON | OFF }

ONLINE = { ON | OFF } <wie für rebuild_index_option>

RESUMABLE = { ON | OFF}

MAX_DURATION = Zeit [ MINUTEN ] verwendet mit RESUMABLE = ON (erfordert ONLINE = ON)

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

MAXDOP = max_degree_of_parallelism

COMPRESSION_DELAY = { 0 | Dauer [ Minuten ] }

DATA_COMPRESSION

XML_COMPRESSION

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

ONLINE = { ON | OFF } <wie für single_partition_rebuild_index_option>

RESUME

PAUSE

ABORT

Bemerkungen

Neuerstellen von Indizes

Neuorganisieren von Indizes

Indizes deaktivieren

Optionen festlegen

Zeilen- und Seitensperren (Optionen)

Online-Indexvorgänge

Fortsetzbare Indexvorgänge

WAIT_AT_LOW_PRIORITY bei Onlineindexvorgängen

MAX_DURATION = Zeit [ MINUTEN ]

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKER ]

Einschränkungen für räumliche Indizes

Datenkomprimierung

Statistik

Berechtigungen

Versionshinweise

Einfaches Syntaxbeispiel

Beispiele: Columnstore-Indizes

A. REORGANIZE-Demo

B. Komprimieren von CLOSED-Delta-Zeilengruppen im Columnstore

C. Komprimieren aller OPEN- und CLOSED-Delta-Zeilengruppen im Columnstore

D: Online-Defragmentieren eines Columnstore-Index

E. Neuerstellen eines gruppierten Columnstore-Index im Offline-Modus

F. Neuerstellen einer Partition eines gruppierten Columnstore-Index im Offline-Modus

G. Ändern eines gruppierten Columnstore-Index zur Verwendung der Archivierungskomprimierung

Beispiele: Rowstore-Indizes

A. Neuerstellen eines Indexes

B. Neuerstellen aller Indizes einer Tabelle und Angeben von Optionen

C. Neuorganisieren eines Index mit LOB-Komprimierung

D: Festlegen von Optionen für einen Index

E. Deaktivieren eines Index

F. Deaktivieren von Einschränkungen

G. Aktivieren von Einschränkungen

H. Neuerstellen eines partitionierten Index

I. Ändern der Komprimierungseinstellung eines Index

J. Ändern der Einstellung eines Indexes mit XML-Komprimierung

K. Onlineneuerstellung von fortsetzbaren Indizes

Zugehöriger Inhalt

Feedback

Zusätzliche Ressourcen

MAX_DURATION = Zeit [ MINUTEN ] verwendet mit `RESUMABLE = ON` (erfordert `ONLINE = ON`)