ALTER INDEX (Transact-SQL)
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 }
}
Hinweis
Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) und früher finden Sie unter Dokumentation zu früheren Versionen.
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 1 |
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 1 |
ONLINE = ON |
XML-Index Räumlicher Index Columnstore-Index 1 |
RESUMABLE = ON 2 |
Resumable indexes not supported with ALL keyword |
1 Gilt für SQL Server 2012 (11.x) und höhere Versionen und Azure SQL-Datenbank.
2 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 derREBUILD
, auch bei VerwendungNOLOCK
, 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 desREBUILD
-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 OptionenSORT_IN_TEMPDB
,MAXDOP
,DATA_COMPRESSION
undXML_COMPRESSION
angegeben werden. XML-Indizes können bei einemREBUILD
-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 aufOFF
. - 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 verschiebenCLOSED
. Der Hintergrund-Tupel-Mover (TM)-Prozess wird regelmäßig aktiviert, um Delta-Zeilengruppen zu komprimierenCLOSED
. Wir empfehlen, die Verwendung zu verwendenREORGANIZE
, wenn der Tupel-Mover hinter sich fällt.REORGANIZE
zeilengruppen aggressiver komprimieren können.- Informationen zum Komprimieren aller
OPEN
undCLOSED
Zeilengruppen finden Sie in derREORGANIZE 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_GROUPS
bietet 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
oderOPEN
).OFF
erzwingt alleCLOSED
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. WennFILLFACTOR
nicht gleichzeitigPAD_INDEX
angegebenON
wird, 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 tempdb
werden 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
tempdb
gespeichert. Wenntempdb
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_KEY
WITH IGNORE_DUP_KEY = ON
.
STATISTICS_NORECOMPUTE = { ON | OFF }
Deaktivieren oder aktivieren Sie die Option AUTO_STATISTICS_UPDATE
fü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 OFF
oder 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 ON
die erstellten Statistiken pro Partitionsstatistik vorliegen. Wenn OFF
die 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 in jeder Edition von Microsoft SQL Server nicht 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 festgelegtON
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 dieONLINE
-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_DELAY
finden 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 derCOLUMNSTORE_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 mitRESUMABLE = ON
verwendetDie 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 mitRESUMABLE = ON
undONLINE = 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 derWAIT_AT_LOW_PRIORITY
-Option entsprichtWAIT_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 SQL Server-Dokumentation wird der Begriff „B-Struktur“ im Allgemeinen in Bezug auf Indizes verwendet. In Zeilenspeicherindizes implementiert SQL Server eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder In-Memory-Datenspeicher. 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 ON
die 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_KEY
und 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 ON
festgelegt 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 dieRESUMABLE = ON
-Klausel explizit angegeben werden, wenn Fortsetzbarkeit aktiviert werden soll.Die
MAX_DURATION
-Option wird für dieRESUMABLE = ON
- oder dielow_priority_lock_wait
-Option unterstützt.MAX_DURATION
fürRESUMABLE
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ürMAX_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. WennMAX_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 derALTER 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 desBEGIN 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. WennMAXDOP
beim Fortsetzen eines angehaltenen Indexvorgangs nicht angegeben wird, wird der letzteMAXDOP
Wert verwendet. Wenn die Option für denMAXDOP
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
oderKILL <session_id>
ausführen. Sobald der Befehl angehalten wurde, kann er mithilfeRESUME
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
-BefehlALTER TABLE
bei der Indexneuerstellung- DDL-Befehl mit
RESUMABLE = ON
kann nicht innerhalb einer expliziten Transaktion ausgeführt werden (kann nicht Teil desBEGIN TRAN ... COMMIT
-Blocks sein) - Erstellen Sie einen Index neu, der berechnet wurde oder
TIMESTAMP
Spalten als Schlüsselspalten enthält.
- Neuerstellen eines deaktivierten Index wird mit
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 mitMAX_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 BerechtigungALTER 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 ALL
angegeben. 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;
Zugehöriger Inhalt
- Leitfaden zur Architektur und zum Design von SQL Server-Indizes
- Ausführen von Onlineindexvorgängen
- CREATE INDEX (Transact-SQL)
- CREATE SPATIAL INDEX (Transact-SQL)
- CREATE XML INDEX (Transact-SQL)
- DROP INDEX (Transact-SQL)
- Deaktivieren von Indizes und Einschränkungen
- XML-Indizes (SQL Server)
- Optimale Wartung von Indizes zum Verbessern der Leistung und Verringern der Ressourcenauslastung
- sys.dm_db_index_physical_stats (Transact-SQL)
- EVENTDATA (Transact-SQL)