ALTER INDEX (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

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

Verwenden des Schlüsselworts ALL mit diesem Vorgang	Erzeugt einen Fehler, wenn mindestens einer dieser Indextypen in der Tabelle enthalten ist
REBUILD WITH ONLINE = ON	XML-Index Räumlicher Index Columnstore-Index in SQL Server 2017 (14.x) und älteren Versionen. Spätere Versionen unterstützen die Online-Neuerstellung von Columnstore-Indizes.
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	Resumable indexes not supported with the ALL keyword

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.

Weitere Informationen zu Onlineindexvorgängen finden Sie in 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 von Indexdetails für eine Tabelle oder Ansicht die sys.indexes Katalogansicht.

Azure SQL-Datenbank unterstützt das dreiteilige Namensformat <database_name>.<schema_name>.<object_name>, wenn <database_name> der aktuelle Datenbankname ist, oder <database_name>tempdb ist und <object_name> mit # oder ##beginnt. Wenn der Schemaname dboist, kann <schema_name> weggelassen werden.

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

gilt für: SQL Server 2012 (11.x) und höhere Versionen, Azure SQL-Datenbank und azure SQL Managed Instance

Gibt an, dass der Index mithilfe der gleichen Spalten, des Indextyps, des Eindeutigkeitsattributs und der Sortierreihenfolge neu erstellt wird. 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 indexoptionen nicht angegeben werden, werden die vorhandenen Indexoptionswerte in sys.indexes angewendet. Bei einer Indexoption, deren Wert nicht in sys.indexesangezeigt wird, gilt die in der Argumentdefinition der Option angegebene Standard.

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

Der REBUILD-Vorgang kann minimal protokolliert werden, wenn das Datenbankwiederherstellungsmodell entweder massenprotokolliert oder einfach ist.

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 der Neuerstellungsvorgang ausgeführt:

• Komprimiert alle Daten im Columnstore neu. Es sind zwei Kopien des Columnstore-Index vorhanden, während der Neuerstellungsvorgang ausgeführt wird. Wenn die Neuerstellung abgeschlossen ist, löscht das Datenbankmodul den ursprünglichen Spaltenspeicherindex.
• Behält die Sortierreihenfolge ggf. nicht bei. Wenn Sie einen Columnstore-Index neu erstellen und eine Sortierreihenfolge beibehalten oder einführen möchten, verwenden Sie die CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON)-Anweisung.

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, nur ausgerichtete Indizes zu verwenden, wenn die Anzahl der Partitionen 1.000 überschreitet.

• 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> )

  SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSIONund XML_COMPRESSION sind die Optionen, die beim Neuerstellen einer einzelnen Partition mithilfe der (PARTITION = partition_number)-Syntax angegeben werden können. XML-Indizes können nicht in einem einzigen Partitions-Neuerstellungsvorgang 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 Deaktivieren von Indizes und Einschränkungen und Aktivieren von Indizes und Einschränkungen.

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 langfristige Blockierungstabellensperren nicht gehalten werden, und Abfragen oder Aktualisierungen der Daten in der zugrunde liegenden Tabelle können während der ALTER INDEX REORGANIZE Transaktion fortgesetzt werden.
• 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.

• 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.
  • REORGANIZE ALL führt die LOB-Komprimierung für alle Indizes aus. 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.

• OFF

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

REORGANIZE bei einem Columnstore-Index

Bei Columnstore-Indizes komprimiert REORGANIZE jede geschlossene 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 die geschlossenen Delta-Zeilengruppen in komprimierte Zeilengruppen zu verschieben. Der Hintergrund-Tupel-Mover -Prozess (TM) wird regelmäßig aktiviert, um die geschlossenen Delta-Zeilengruppen zu komprimieren. 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 geöffneten und geschlossenen Zeilengruppen finden Sie im REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS).

Für Columnstore-Indizes in SQL Server 2016 (13.x) und höheren Versionen, Azure SQL-Datenbank und azure SQL Managed Instance, führt REORGANIZE die folgenden zusätzlichen Defragmentierungsoptimierungen online aus:

• Entfernt gelöschte Zeilen physisch aus einer Zeilengruppe, wenn 10% oder mehr der Zeilen logisch gelöscht wurden. Die gelöschten Bytes werden auf den physischen Medien freigegeben. Wenn beispielsweise eine komprimierte Zeilengruppe von 1 Millionen Zeilen 100.000 Zeilen gelöscht hat, entfernt das Datenbankmodul die gelöschten Zeilen und komprimiert die Zeilengruppe mit 900.000 Zeilen erneut.

• 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 REORGANIZEausführen, werden diese Zeilengruppen mit 1 komprimierter Zeilengruppe mit 512.000 Zeilen zusammengeführt. Es wird davon ausgegangen, dass keine Wörterbuchgrößen- oder Speicherbeschränkungen vorhanden sind.

• Bei Zeilengruppen, in denen 10% oder mehr zeilen logisch gelöscht wurden, versucht das Datenbankmodul, 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. Das Datenbankmodul bevorzugt die Kombination dieser beiden Zeilengruppen, um eine neue Zeilengruppe mit 909.830 Zeilen zu komprimieren.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Gilt für Columnstore-Indizes.

Gilt für: SQL Server 2016 (13.x) und höhere Versionen, Azure SQL-Datenbank und azure SQL Managed Instance

COMPRESS_ALL_ROW_GROUPS bietet eine Möglichkeit, geöffnete oder geschlossene Delta-Zeilengruppen in den Columnstore zu erzwingen. Mit dieser Option ist es nicht notwendig, den Columnstore-Index zum Leeren der Delta-Zeilengruppe neu zu erstellen. In Kombination mit den anderen Features zum Entfernen und Zusammenführen der Defragmentierung ist es in den meisten Situationen nicht mehr erforderlich, einen Spaltenspeicherindex neu zu erstellen.

• ON

  Erzwingt alle Zeilengruppen im Spaltenspeicher, unabhängig von Größe und Zustand (geschlossen oder geöffnet).

• OFF

  Erzwingt alle geschlossenen 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 ] )

Ändert Indexoptionen, ohne den Index neu zu erstellen oder neu zu organisieren. 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 freien Speicherplatzes, der durch den Füllfaktor angegeben wird, wird auf die Seiten der Mittleren Ebene des Indexes angewendet. Wenn FILLFACTOR nicht gleichzeitig angegeben wird, PAD_INDEX auf ONfestgelegt ist, wird der Füllfaktorwert in sys.indexes verwendet.

• OFF

  Die Seiten auf mittlerer Ebene werden in der Nähe der Kapazität gefüllt, sodass ausreichend Platz für mindestens eine Zeile der maximalen Größe des Indexes vorhanden sein kann, wobei der Satz von Schlüsseln auf den Zwischenseiten berücksichtigt werden kann. Dies tritt auch auf, wenn PAD_INDEX auf ON festgelegt ist, aber der Füllfaktor nicht angegeben ist.

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 eines Indexes mit einer FILLFACTOR kleiner als 100 erhöht den Speicherplatz, den die Daten belegen, da das Datenbankmodul die Daten entsprechend dem Füllfaktor neu verteilt, wenn ein Index erstellt oder neu erstellt wird.

SORT_IN_TEMPDB = { ON | OFF }

Gibt an, ob temporäre Sortierergebnisse in tempdbgespeichert werden sollen. Der Standardwert ist OFF mit Ausnahme von Azure SQL-Datenbank Hyperscale. Für alle Indexbuildvorgänge in Hyperscale wird SORT_IN_TEMPDB immer ON, es sei denn, ein reaktivierbarer Indexbuild wird verwendet. Für reaktivierbare Indexbuilds ist SORT_IN_TEMPDB immer OFF.

• ON

  Die Zwischensortierungsergebnisse, die zum Erstellen des Indexes verwendet werden, werden in tempdbgespeichert. Dies kann die Zeit verringern, die zum Erstellen eines Indexes erforderlich ist. 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, werden nicht eingefügt.

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

Zum Anzeigen der IGNORE_DUP_KEY Einstellung für einen Index verwenden Sie die ignore_dup_key Spalte in der sys.indexes Katalogansicht.

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

STATISTICS_NORECOMPUTE = { ON | OFF }

Deaktivieren oder aktivieren Sie die Option "Automatische Statistikaktualisierung" AUTO_STATISTICS_UPDATEfür die Statistiken im Index. 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 UPDATE STATISTICS Klausel ausgeführtNORECOMPUTE.

Warnung

Wenn Sie die automatische Neukompilierung von Statistiken deaktivieren, indem Sie STATISTICS_NORECOMPUTE = ONfestlegen, können Sie verhindern, dass der Abfrageoptimierer optimale Ausführungspläne für Abfragen mit der Tabelle auswählt.

Das Festlegen STATISTICS_NORECOMPUTE auf ON verhindert nicht die Aktualisierung von Indexstatistiken, die während des Indexerstellungsvorgangs auftreten.

STATISTICS_INCREMENTAL = { ON | OFF }

gilt für: SQL Server 2014 (12.x) und höhere Versionen, Azure SQL-Datenbank und azure SQL Managed Instance

Bei ONsind die im Index erstellten Statistiken pro Partitionsstatistik. Wenn OFF, werden die vorhandenen Statistiken verworfen, und das Datenbankmodul erstellt die Statistiken neu. Der Standardwert ist OFF.

Wenn statistiken pro Partition nicht unterstützt werden, wird die Option ignoriert und eine Warnung generiert. Inkrementelle Statistiken werden in den folgenden Fällen 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 }

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

  Langfristige Tabellensperren werden für die Dauer des Indexvorgangs nicht gehalten. Während der Hauptphase des Indexvorgangs wird nur eine Absichtssperre (IS) in der Quelltabelle gespeichert. Dadurch können Abfragen oder Aktualisierungen der zugrunde liegenden Tabelle und Indizes fortgesetzt werden. Zu Beginn des Vorgangs wird eine freigegebene (S) Sperre für das Quellobjekt für einen kurzen Zeitraum aufbewahrt. Am Ende des Vorgangs wird für einen kurzen Zeitraum eine freigegebene (S) Sperre für das Objekt abgerufen, wenn ein nicht gruppierter Index erstellt wird. Eine Schemaänderung (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.

  Hinweis

  Sie können die Option WAIT_AT_LOW_PRIORITY verwenden, um die Blockierung während Onlineindexvorgängen zu reduzieren oder zu vermeiden. Weitere Informationen finden Sie unter WAIT_AT_LOW_PRIORITY mit Onlineindexvorgängen.

• OFF

  Tabellensperren werden für die Dauer des Indexvorgangs angewendet. Ein Offlineindexvorgang, der einen gruppierten, räumlichen oder XML-Index erstellt, neu erstellt, neu erstellt oder abbricht, einen nicht gruppierten Index erstellt, neu erstellt oder abbricht, erhält eine Schemaänderung (Sch-M) Sperre für die Tabelle. Dadurch wird verhindert, dass der gesamte Benutzer für die Dauer des Vorgangs auf die zugrunde liegende Tabelle zugreifen kann. Ein Offlineindexvorgang, der einen nicht gruppierten Index erstellt, erwirbt zunächst eine freigegebene (S) Sperre für die Tabelle. Dies verhindert Änderungen der zugrunde liegenden Tabellendefinition, ermöglicht jedoch das Lesen und Ändern der Daten in der Tabelle, während der Indexbuild ausgeführt wird.

Weitere Informationen finden Sie unter Ausführen von Indexvorgängen online und Richtlinien für Onlineindexvorgänge.

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
• Deaktivierte gruppierte Indizes
• Gruppierte Spaltenspeicherindizes in SQL Server 2017 (14.x)) und früheren Versionen
• Nicht gruppierte Columnstore-Indizes in SQL Server 2016 (13.x)) und früheren Versionen
• Gruppierter Index, wenn die zugrunde liegende Tabelle LOB-Datentypen (image, ntext, text) und räumliche Datentypen enthält.
• varchar(max) und varbinary(max) Spalten nicht Teil eines Indexschlüssels sein können. In SQL Server (beginnend mit SQL Server 2012 (11.x)) in Azure SQL-Datenbank und in azure SQL Managed Instance, wenn eine Tabelle varchar(max) oder varbinary(max) Spalten enthält, kann ein gruppierter Index mit anderen Spalten mithilfe der Option ONLINE erstellt oder neu erstellt werden.

Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen.

RESUMABLE = { ON | OFF}

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

Gibt an, ob ein Onlineindexvorgang fortsetzbar ist.

• ON

  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, Azure SQL-Datenbank und azure SQL Managed Instance

Gibt an, wie lange ein resumabler Indexvorgang in ganzzahligen Minuten ausgeführt wird, bevor er angehalten wird.

ALLOW_ROW_LOCKS = { ON | OFF }

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

• ON

  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.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

gilt für: SQL Server 2019 (15.x) und höhere Versionen, Azure SQL-Datenbank und azure SQL Managed Instance

Gibt an, ob eine Optimierung erforderlich ist, um den Inhalt der letzten Seite zu vermeiden. Der Standardwert ist OFF. Weitere Informationen finden Sie unter Sequenzielle Schlüssel.

MAXDOP = max_degree_of_parallelism

Überschreibt den maximalen Grad an Parallelität Konfigurationsoption für den Indexvorgang. Weitere Informationen finden Sie unter Konfigurieren des maximalen Grads der Serverkonfigurationsoption. Verwenden Sie MAXDOP, um den Grad der Parallelität und den resultierenden Ressourcenverbrauch für einen Indexbuildvorgang zu begrenzen.

Obwohl die option MAXDOP syntaktisch für alle XML-Indizes und räumlichen Indizes unterstützt wird, verwendet ALTER INDEX derzeit nur einen einzelnen Prozessor.

max_degree_of_parallelism kann folgende Werte haben:

• 1

  Unterdrückt die parallele Plangenerierung.

• >1

  Schränkt den maximalen Grad der Parallelität ein, der in einem parallelen Indexvorgang auf die angegebene Zahl oder weniger basierend auf der aktuellen Systemauslastung verwendet wird.

• 0 (Standard)

  Verwendet den Grad der Parallelität, die auf Server-, Datenbank- oder Workloadgruppenebene angegeben ist, es sei denn, dies ist auf der Grundlage der aktuellen Systemauslastung reduziert.

Weitere Informationen finden Sie unter Konfigurieren von parallelen Indexvorgä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 ] }

Gilt für: SQL Server (beginnend mit SQL Server 2016 (13.x)), Azure SQL-Datenbank und azure SQL Managed Instance

Gibt für eine datenträgerbasierte Tabelle mit einem Spaltenspeicherindex die Mindestanzahl von Minuten an, die eine Delta-Zeilengruppe im geschlossenen Zustand im Deltaspeicher verbleiben muss, bevor das Datenbankmodul sie in eine komprimierte Zeilengruppe komprimieren kann. Da datenträgerbasierte Tabellen keine Einfüge- und Aktualisierungszeiten für einzelne Zeilen nachverfolgen, wendet das Datenbankmodul diese Verzögerung nur auf Deltaspeicherzeilengruppen im geschlossenen Zustand an.

Die Standardeinstellung beträgt 0 Minuten.

Empfehlungen zur Verwendung von 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, Azure SQL-Datenbank und azure SQL Managed Instance

  Gilt nur für columnstore-Indizes, einschließlich nicht gruppierter und gruppierter columnstore-Indizes. Wenn Sie COLUMNSTORE angeben, werden alle anderen Datenkomprimierung einschließlich COLUMNSTORE_ARCHIVEentfernt.

• COLUMNSTORE_ARCHIVE

  gilt für: SQL Server 2014 (12.x) und höhere Versionen, Azure SQL-Datenbank und azure SQL Managed Instance

  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öhere Versionen, Azure SQL-Datenbank und 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:

• ON

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

<range> können als Partitionsnummern angegeben werden, die durch das Wort TOgetrennt sind, 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)
);

RESUME

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

Setzt einen Indexvorgang fort, der manuell angehalten wird, da die maximale Dauer erreicht ist oder aufgrund eines Fehlers.

• MAX_DURATION

  Gibt an, wie lange in ganzzahligen Minuten ein reaktivierbarer Indexvorgang ausgeführt wird, nachdem er fortgesetzt wurde, bevor er erneut angehalten wird.

• WAIT_AT_LOW_PRIORITY

  Das Fortsetzen eines Indexbuildvorgangs nach einer Pause muss die erforderlichen Sperren abrufen. WAIT_AT_LOW_PRIORITY gibt an, dass der Indexbuildvorgang Sperrungen mit niedriger Priorität erhält, wodurch andere Vorgänge fortgesetzt werden können, während der Indexbuildvorgang 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, Azure SQL-Datenbank und azure SQL Managed Instance

Hält einen reaktivierbaren Indexbuildvorgang an.

ABORT

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

Bricht einen ausgeführten oder angehaltenen Indexbuildvorgang ab, der als fortsetzungsfähig gestartet wurde. Sie müssen explizit einen ABORT Befehl ausführen, um einen reaktivierbaren Indexbuildvorgang zu beenden. Ein Fehler oder eine Pause in einem reaktivierbaren Indexvorgang beendet die Ausführung nicht; stattdessen wird der Vorgang in einem unbefristeten Pausenzustand verlassen.

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. Umgekehrt ist ALTER INDEX REORGANIZE ein einzelner Threadvorgang. Weitere Informationen finden Sie unter Konfigurieren von parallelen Indexvorgängen.

In der SQL-Datenbank in Microsoft Fabric ALTER INDEX ALL wird dies nicht unterstützt, sondern ALTER INDEX <index name> ist.

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

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. Einige Einschränkungen beim Angeben von ALLgelten.

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

Hinweis

Bei einer Tabelle mit einem sortierten Spaltenspeicherindex werden die Daten von ALTER INDEX REORGANIZE 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. Um festzustellen, ob ein Index deaktiviert ist, verwenden Sie die is_disabled Spalte in der sys.indexes Katalogansicht.

Hinweis

In der Dokumentation wird der Begriff „B-Struktur“ im Allgemeinen in Bezug auf Indizes verwendet. In Zeilenspeicherindizes implementiert die Datenbank-Engine 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.

Wenn sich eine Tabelle in einer Transaktionsreplikationsveröffentlichung befindet, können Sie keinen Index deaktivieren, der einer Primärschlüsseleinschränkung zugeordnet ist. weil diese Indizes von der Replikation benötigt werden. Um einen solchen Index zu deaktivieren, müssen Sie zuerst die Tabelle aus der Publikation ablegen. 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 ONLINEdie ON 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	Gilt für
ALLOW_ROW_LOCKS = ON oder OFF	Der Heap und alle zugeordneten nicht gruppierten Indizes.
ALLOW_PAGE_LOCKS = ON	Der Heap und alle zugeordneten nicht gruppierten Indizes.
ALLOW_PAGE_LOCKS = OFF	Die nicht gruppierten Indizes, bei denen alle Seitensperren nicht zulässig sind. Für den Heap sind nur die freigegebenen (S), Update (U) und exklusive (X) Seitensperren nicht zulässig. Das Datenbankmodul kann weiterhin Absichtsseitensperren (IS, IUoder IX) für interne Zwecke abrufen.

Warnung

Es wird nicht empfohlen, Zeilen- oder Seitensperren für einen Index zu deaktivieren. Parallelitätsprobleme können auftreten, und bestimmte Funktionen sind möglicherweise nicht verfügbar. Beispielsweise kann ein Index nicht neu organisiert werden, wenn ALLOW_PAGE_LOCKS auf OFFfestgelegt ist.

Online-Indexvorgänge

Beim Neuerstellen eines Indexes und der Option ONLINE auf ON, Daten im Index, die zugehörige Tabelle und andere Indizes in derselben Tabelle sind für Abfragen und Änderungen verfügbar. Sie können auch einen Teil eines Indexes online neu erstellen, der sich in einer einzelnen Partition befindet. Exklusive Tabellensperren werden nur für kurze Zeit am Ende der Indexneuerstellung gehalten.

Das Neuorganisieren eines Indexes wird stets online durchgeführt. Der Prozess enthält Sperren nur für kurze Zeiträume und ist unwahrscheinlich, dass Abfragen oder Updates blockiert werden.

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, Azure SQL-Datenbank und azure SQL Managed Instance

Sie können einen Onlineindex neu erstellen, der fortgesetzt werden kann. Dies bedeutet, dass die Indexneuerstellung angehalten und später neu gestartet werden kann, von dem Punkt, an dem sie beendet wurde. Wenn Sie eine Indexneuerstellung als fortsetzungsfähig ausführen möchten, geben Sie die Option RESUMABLE = ON an.

Die folgenden Richtlinien gelten für reaktivierbare Indexvorgänge:

• Um die option RESUMABLE zu verwenden, müssen Sie auch die Option ONLINE verwenden.
• Die option RESUMABLE wird in den Metadaten für einen bestimmten Index nicht 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 option MAX_DURATION kann in zwei Kontexten angegeben werden:
  • MAX_DURATION für die Option RESUMABLE gibt das Zeitintervall für einen zu erstellenden Index an. Nach ablauf dieser Zeit und wenn der Indexbuild noch ausgeführt wird, wird er angehalten. Sie entscheiden, wann der Build für einen angehaltenen Index fortgesetzt werden kann. Die Zeit in Minuten für MAX_DURATION muss größer als 0 Minuten und kleiner oder gleich einer Woche sein (7 * 24 * 60 = 10080 Minuten). Eine lange Pause in einem Indexvorgang wirkt sich möglicherweise spürbar auf die DML-Leistung einer bestimmten Tabelle sowie die Kapazität des Datenbankdatenträgers aus, da sowohl der ursprüngliche Index als auch der neu erstellte Index Speicherplatz erfordern und von DML-Vorgängen aktualisiert werden müssen. Wenn MAX_DURATION Option nicht angegeben wird, wird der Indexvorgang bis zum Abschluss oder bis zum Auftreten eines Fehlers fortgesetzt.
  • MAX_DURATION für die Option WAIT_AT_LOW_PRIORITY gibt die Wartezeit mit Sperren mit niedriger Priorität an, wenn der Indexvorgang blockiert wird, bevor Eine Aktion ausgeführt wird. Weitere Informationen finden Sie unter WAIT_AT_LOW_PRIORITY mit Onlineindexvorgängen.
• Um den Indexvorgang sofort anzuhalten, können Sie den befehl ALTER INDEX PAUSE ausführen oder den befehl KILL <session_id> ausführen.
• 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.
• Der Befehl ABORT beendet die Sitzung, in der ein Indexbuild ausgeführt wird, und bricht den Indexvorgang ab. Sie können keinen Indexvorgang fortsetzen, der abgebrochen wurde.
• Wenn Sie einen angehaltenen Indexneuerstellungsvorgang fortsetzen, können Sie den MAXDOP Wert in einen neuen Wert ändern. Wenn MAXDOP beim Fortsetzen eines angehaltenen Indexvorgangs nicht angegeben wird, wird der für den letzten Lebenslauf verwendete MAXDOP Wert verwendet. Wenn die Option MAXDOP für einen Indexneuerstellungsvorgang überhaupt nicht angegeben ist, wird der Standardwert verwendet.

Ein reaktivierbarer Indexvorgang wird ausgeführt, bis er abgeschlossen, angehalten oder fehlschlägt. Falls der Vorgang angehalten wird, wird ein Fehler ausgegeben, der angibt, dass der Vorgang angehalten wurde und dass die Indexneuerstellung nicht abgeschlossen wurde. Falls der Vorgang fehlschlägt, wird auch ein Fehler ausgegeben.

Um festzustellen, ob ein Indexvorgang als reaktivierbarer Vorgang ausgeführt wird und um den aktuellen Ausführungsstatus zu überprüfen, verwenden Sie die sys.index_resumable_operations Katalogansicht.

Betriebsmittel

Die folgenden Ressourcen sind für reaktivierbare Indexvorgänge erforderlich:

• Zusätzlicher Speicherplatz, der erforderlich ist, um den zu erstellenden Index beizubehalten, einschließlich der Zeit, zu der der Build angehalten wird.
• Zusätzlicher Protokolldurchsatz während der Sortierphase. Die Gesamtnutzung des Protokollspeicherplatzes für den reaktivierbaren Index ist weniger im Vergleich zur regulären Onlineindexerstellung und ermöglicht das Abschneiden von Protokollen während dieses Vorgangs.
• DDL-Anweisungen, die versuchen, einen Index zu ändern, der neu erstellt wird, oder die zugehörige Tabelle, während der Indexvorgang angehalten wird, sind nicht zulässig.
• Die Ghost-Bereinigung wird für die Dauer des Vorgangs sowohl während der Pause als auch während der Ausführung des Vorgangs im In-Build-Index blockiert.
• Wenn die Tabelle BRANCHENspalten enthält, erfordert ein resumierbarer Clusterindexbuild eine Schemaänderung (Sch-M) am Anfang des Vorgangs.

Aktuelle funktionale Einschränkungen

Resumable Index-Neuerstellungsvorgänge weisen die folgenden Einschränkungen auf:

• Die Option SORT_IN_TEMPDB = ON wird für reaktivierbare Indexvorgänge nicht unterstützt.
• Der DDL-Befehl mit RESUMABLE = ON kann nicht innerhalb einer expliziten Transaktion ausgeführt werden.
• Sie können keinen reaktivierbaren Index erstellen, der Folgendes enthält:
  • Berechneter oder Zeitstempel/Zeilenversion Spalten als Schlüsselspalten.
  • Lob-Spalte als eingeschlossene Spalte.
• Reaktivierbare Indexvorgänge werden nicht unterstützt für:
  • Der Befehl ALTER INDEX REBUILD ALL
  • Der Befehl ALTER TABLE REBUILD
  • Columnstore-Indizes
  • Gefilterte Indizes
  • Deaktivierte Indizes

WAIT_AT_LOW_PRIORITY bei Onlineindexvorgängen

gilt für: SQL Server 2014 (12.x) und höhere Versionen, Azure SQL-Datenbank und azure SQL Managed Instance

Wenn Sie die Option WAIT_AT_LOW_PRIORITY nicht verwenden, müssen alle aktiven blockierenden Transaktionen, die Sperren für die Tabelle oder den Index enthalten, abgeschlossen sein, damit der Indexneuerstellungsvorgang gestartet und abgeschlossen werden kann. Wenn der Onlineindexvorgang gestartet wird und bevor er abgeschlossen wird, muss er eine freigegebene (S) oder eine Schemaänderung (Sch-M) auf der Tabelle erwerben und diese für kurze Zeit halten. Auch wenn die Sperre nur für kurze Zeit gehalten wird, kann sie sich erheblich auf den Workloaddurchsatz auswirken, die Abfragelatenz erhöhen oder Ausführungstimeouts verursachen.

Um diese Probleme zu vermeiden, können Sie mit der Option WAIT_AT_LOW_PRIORITY das Verhalten von S oder Sch-M Sperren verwalten, die für den Start und Abschluss eines Onlineindexvorgangs erforderlich sind, und wählen Sie aus drei Optionen aus. Wenn während der durch MAX_DURATION = n [minutes] angegebenen Wartezeit in allen Fällen keine Blockierung vorhanden ist, die den Indexvorgang umfasst, wird der Indexvorgang sofort fortgesetzt.

WAIT_AT_LOW_PRIORITY den Onlineindexvorgang mit Sperren mit niedriger Priorität warten lässt, sodass andere Vorgänge mit normalen Prioritätssperren in der Zwischenzeit fortgesetzt werden können. Das Weglassen der WAIT_AT_LOW_PRIORITY-Option entspricht WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = Zeit [MINUTES]

Die Wartezeit (ein ganzzahliger Wert, der in Minuten angegeben ist), die der Onlineindexvorgang mit Sperren mit niedriger Priorität wartet. 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 | BLOCKERS ]

• NONE: Warten Sie weiterhin auf die Sperre mit normaler Priorität.
• SELF: Beenden Sie den derzeit ausgeführten Onlineindexvorgang, ohne eine Aktion auszuführen. Die Option SELF kann nicht verwendet werden, wenn MAX_DURATION 0 ist.
• BLOCKERS: Beenden Sie alle Benutzertransaktionen, die den Onlineindexvorgang blockieren, damit der Vorgang fortgesetzt werden kann. Für die option BLOCKERS muss der Prinzipal die CREATE INDEX- oder ALTER INDEX-Anweisung ausführen, um über die ALTER ANY CONNECTION-Berechtigung zu verfügen.

Sie können die folgenden erweiterten Ereignisse verwenden, um Indexvorgänge zu überwachen, die auf Sperren mit niedriger Priorität warten:

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

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.

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 jedoch räumliche Indizes in einer Tabellenerstellung 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".

Nachfolgend sind die wichtigsten Punkte aufgeführt, die im Kontext von Indexbuildvorgängen beim Verwenden der Datenkomprimierung berücksichtigt werden sollten:

• Die Komprimierung kann es ermöglichen, dass mehr Zeilen auf einer Seite gespeichert werden, aber die maximale Zeilengröße wird nicht geändert.
• Nicht blattfreie Seiten eines Indexes werden nicht komprimiert, können jedoch zeilenkomprimiert werden.
• Jeder nicht gruppierte Index verfügt über eine einzelne Komprimierungseinstellung und erbt nicht die Komprimierungseinstellung der zugrunde liegenden Tabelle.
• Wenn ein gruppierter Index für einen Heap erstellt wird, erbt der gruppierte Index den Komprimierungszustand des Heaps, es sei denn, es wird ein alternativer Komprimierungszustand angegeben.

Die folgenden Überlegungen gelten für die Neuerstellung von partitionierten Indizes:

• Sie können die Komprimierungseinstellung einer einzelnen Partition nicht ändern, wenn die Tabelle nicht ausgerichtete Indizes aufweist.
• Die ALTER INDEX <index> ... REBUILD PARTITION ... WITH DATA_COMPRESSION = ... Syntax erstellt die angegebene Partition des Indexes mit der angegebenen Komprimierungsoption neu. Wenn die WITH DATA_COMPRESSION-Klausel weggelassen wird, wird die vorhandene Komprimierungsoption verwendet.
• Die ALTER INDEX <index> ... REBUILD PARTITION = ALL Syntax erstellt alle Partitionen des Indexes mithilfe der vorhandenen Komprimierungsoptionen neu.
• Mit der Syntax ALTER INDEX <index> ... REBUILD PARTITION = ALL (WITH ...) werden alle Partitionen des Index neu erstellt. Sie können verschiedene Komprimierung für verschiedene Partitionen mithilfe der DATA_COMPRESSION = ... ON PARTITIONS ( ...)-Klausel auswählen.

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.

Statistik

Wenn Sie einen Index neu erstellen, werden die Statistiken für den Index mit der vollständigen Überprüfung auf nicht partitionierte Indizes und mit dem Standard-Sampling-Verhältnis für partitionierte Indizes aktualisiert. Im Rahmen der Indexerstellung werden keine anderen Statistiken zur Tabelle aktualisiert.

Berechtigungen

Die ALTER Berechtigung für die Tabelle oder Ansicht ist erforderlich.

Versionshinweise

• Azure SQL-Datenbank unterstützt keine anderen Dateigruppen als PRIMARY.
• Azure SQL-Datenbank und azure SQL Managed Instance unterstützen keine FILESTREAM Optionen.
• Columnstore-Indizes sind vor SQL Server 2012 (11.x) nicht verfügbar.
• Resumable index operations are available in SQL Server 2017 (14.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.

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

Gilt für: SQL Server 2016 (13.x) und höhere Versionen, Azure SQL-Datenbank und azure SQL Managed Instance

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, Azure SQL-Datenbank und Azure SQL Managed Instance

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, Azure SQL-Datenbank und azure SQL Managed Instance

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, Azure SQL-Datenbank und azure SQL Managed Instance

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 PRIMARY KEY Einschränkungen aktiviert, die FOREIGN 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, Azure SQL-Datenbank und azure SQL Managed Instance

-- 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, Azure SQL-Datenbank und azure SQL Managed Instance

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 Constraints
• 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)