Richtlinien für Onlineindexvorgänge

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Für das Ausführen von Onlineindexvorgängen gelten die folgenden Richtlinien:

  • Gruppierte Indizes müssen offline erstellt, neu erstellt oder gelöscht werden, wenn die zugrunde liegende Tabelle die folgenden LOB-Datentypen (Large Object) enthält: image, ntextund text.
  • Nicht eindeutige, nicht gruppierte Indizes können online erstellt werden, wenn die Tabelle LOB-Datentypen enthält, keine dieser Spalten jedoch in der Indexdefinition als Schlüssel- oder Nichtschlüsselspalte (eingeschlossene Spalte) verwendet wird.
  • Indizes für lokale temporäre Tabellen können online nicht erstellt, neu erstellt oder gelöscht werden. Diese Einschränkung gilt nicht für Indizes globaler temporärer Tabellen.
  • Indizes können nach einem unerwarteten Fehler, einem Datenbankfailover oder einem PAUSE Befehl angehalten werden. Weitere Informationen finden Sie unter Indexerstellung und Alter Index.

Hinweis

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.

Die folgende Tabelle enthält eine Auflistung der Indexvorgänge, die online ausgeführt werden können, der Indizes, die von diesen Onlinevorgängen ausgeschlossen sind und fortsetzbaren Indexrestriktionen. Zusätzliche Einschränkungen werden ebenfalls aufgeführt.

Onlineindexvorgang Ausgeschlossene Indizes Weitere Einschränkungen
ALTER INDEX REBUILD Deaktivierter gruppierter Index oder deaktivierte indizierte Sicht

XML-Index

Columnstore-Index

Index für eine lokale temp-Tabelle		 Die Angabe der Schlüsselwort (keyword) ALL kann dazu führen, dass der Vorgang fehlschlägt, wenn die Tabelle einen ausgeschlossenen Index enthält.

Weitere Einschränkungen zum Neuerstellen deaktivierter Indizes gelten ebenfalls. Weitere Informationen finden Sie unter Deaktivieren von Indizes und Einschränkungen.
CREATE INDEX XML-Index

Eindeutiger gruppierter Ausgangsindex für eine Sicht

Index für eine lokale temp-Tabelle
CREATE INDEX WITH DROP_EXISTING Deaktivierter gruppierter Index oder deaktivierte indizierte Sicht

Index für eine lokale temp-Tabelle

XML-Index
DROP INDEX Deaktivierter Index

XML-Index

Nicht gruppierter Index

Index für eine lokale temp-Tabelle		 Mehrere Indizes können nicht innerhalb einer einzelnen Anweisung angegeben werden.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY oder UNIQUE) Index für eine lokale temp-Tabelle

Gruppierter Index		 Es ist nur jeweils eine Unterklausel gleichzeitig zulässig. Sie können z. B. keine Einschränkungen in derselben ALTER TABLE Anweisung hinzufügen und ablegen oder UNIQUE löschenPRIMARY KEY.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY oder UNIQUE) Gruppierter Index

Die zugrunde liegende Tabelle kann nicht geändert, abgeschnitten oder verworfen werden, während ein Onlineindexvorgang ausgeführt wird.

Die Beim Erstellen oder Ablegen eines gruppierten Indexes angegebene Onlineoptionseinstellung (ON oder OFF) wird auf nicht gruppierte Indizes angewendet, die neu erstellt werden müssen. Wenn der gruppierte Index beispielsweise online erstellt CREATE INDEX WITH DROP_EXISTING, ONLINE=ONwird, werden alle zugeordneten nicht gruppierten Indizes ebenfalls online neu erstellt.

Wenn Sie einen UNIQUE Index online erstellen oder neu erstellen, kann der Index-Generator und eine gleichzeitige Benutzertransaktion versuchen, denselben Schlüssel einzufügen, wodurch die Eindeutigkeit verletzt wird. Wenn eine zeile, die von einem Benutzer eingegeben wird, in den neuen Index (Ziel) eingefügt wird, bevor die ursprüngliche Zeile aus der Quelltabelle in den neuen Index verschoben wird, schlägt der Onlineindexvorgang fehl.

Obwohl der Fall nicht häufig auftritt, kann der Onlineindexvorgang aufgrund von Benutzer- oder Anwendungsaktivitäten einen Deadlock bewirken, wenn sie mit den Datenbankupdates interagiert. In diesen seltenen Fällen wählt der SQL Server Datenbank-Engine die Benutzer- oder Anwendungsaktivität als Deadlock-Opfer aus.

Sie können gleichzeitige DDL-Vorgänge im Onlineindex nur für dieselbe Tabelle oder Ansicht ausführen, wenn Sie mehrere neue nicht gruppierte Indizes erstellen oder nicht gruppierte Indizes neu organisieren. Alle anderen gleichzeitig durchgeführten Onlineindexvorgänge erzeugen einen Fehler. Sie können z. B. keinen neuen Index online erstellen, während Sie einen vorhandenen Index online in derselben Tabelle neu erstellen.

Ein Onlinevorgang kann nicht ausgeführt werden, wenn ein Index eine Spalte des großen Objekttyps enthält, und in derselben Transaktion gibt es Aktualisierungsvorgänge vor diesem Onlinevorgang. Um dieses Problem zu umgehen, platzieren Sie den Onlinevorgang außerhalb der Transaktion oder vor den Updates in der Transaktion.

Überlegungen zum Speicherplatz auf dem Datenträger

Onlineindexvorgänge erfordern mehr Speicherplatz als Offlineindexvorgänge.

  • Beim Erstellen und Wiederherstellen von Indizes ist zusätzlicher Speicherplatz erforderlich, damit der Index erstellt (oder wiederhergestellt) werden kann.
  • Darüber hinaus ist Speicherplatz für den temporären Zuordnungsindex erforderlich. Dieser temporäre Index wird in Onlineindexvorgängen verwendet, die einen gruppierten Index erstellen, neu erstellen oder löschen.
  • Das Löschen eines gruppierten Indexes online benötigt den gleichen Speicherplatz wie das Erstellen (oder Wiederherstellen) eines gruppierten Indexes online.

Weitere Informationen finden Sie unter Disk Space Requirements for Index DDL Operations.

Überlegungen zur Leistung

Obwohl Onlineindexvorgänge gleichzeitige Benutzeraktualisierungsaktivitäten zulassen, können die Indexvorgänge länger dauern, wenn die Aktualisierungsaktivität sehr stark ist. In der Regel sind Onlineindexvorgänge langsamer als gleichwertige Offlineindexvorgänge, unabhängig von der gleichzeitigen Aktualisierungsaktivitätsebene.

Da sowohl die Quell- als auch die Zielstrukturen während des Onlineindexvorgangs verwaltet werden, kann die Ressourcenverwendung für Einfüge-, Update- und Löschtransaktionen bis um das Doppelte zunehmen. Dieser Vorgang kann einen Leistungsabfall und erhöhte Ressourcenverwendung (insbesondere CPU-Zeit) während des Indexvorgangs bewirken. Onlineindexvorgänge werden vollständig protokolliert.

In der Regel werden Onlinevorgänge empfohlen, Sie sollten jedoch Ihre Umgebung sowie besondere Anforderungen berücksichtigen. Es kann optimal sein, Indexvorgänge offline auszuführen. Dabei ist der Benutzerzugriff auf die Daten während des Vorgangs eingeschränkt, der Vorgang wird jedoch schneller abgeschlossen und verwendet weniger Ressourcen.

Auf Multiprozessorcomputern, auf denen SQL Server 2016 (13.x) ausgeführt wird, können Indexanweisungen weitere Prozessoren verwenden, um die Überprüfungs- und Sortiervorgänge auszuführen, die der Index-Anweisung zugeordnet sind, genau wie bei anderen Abfragen. Sie können die MAXDOP Indexoption verwenden, um die Anzahl der Prozessoren zu steuern, die dem Onlineindexvorgang zugeordnet sind. Auf diese Weise können Sie die Ressourcen ausgleichen, die vom Indexvorgang mit Ressourcen der gleichzeitigen Benutzer verwendet werden. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen. Weitere Informationen zu den Editionen von SQL Server, die parallele Indexvorgänge unterstützen, finden Sie unter Editionen und unterstützte Features von SQL Server 2022.

Da eine S-Sperre oder sch-M-Sperre in der letzten Phase des Indexvorgangs gehalten wird, achten Sie darauf, wenn Sie einen Onlineindexvorgang innerhalb einer expliziten Benutzertransaktion ausführen, z BEGIN TRANSACTION ... COMMIT . B. "Blockieren". In diesem Fall bleibt die Sperre aktiviert, bis die Transaktion beendet ist, und beeinträchtigt daher die Benutzerparallelität.

Die Neuerstellung von Onlineindexen kann die Fragmentierung erhöhen, wenn sie mit MAXDOP mehr als 1und mehr ALLOW_PAGE_LOCKS=OFFausgeführt wird. Weitere Informationen finden Sie unter Vorgehensweise: Onlineneuerstellung von Indizes kann zu erhöhter Fragmentierung führen.

Überlegungen zum Transaktionsprotokoll

Umfangreiche Indexvorgänge, die offline oder online ausgeführt werden, können große Datenlasten generieren, was dazu führt, dass das Transaktionsprotokoll schnell ausgefüllt wird. Dies liegt daran, dass sowohl Offline- als auch Onlineindex-Neuerstellungsvorgänge vollständig protokolliert werden. Um sicherzustellen, dass der Indexvorgang rückgängig gemacht werden kann, kann das Transaktionsprotokoll erst abgeschnitten werden, wenn der Indexvorgang abgeschlossen ist. Das Protokoll kann jedoch während des Indexvorgangs gesichert werden.

Daher muss im Transaktionsprotokoll ausreichend Speicherplatz vorhanden sein, um sowohl die Indexvorgangstransaktionen als auch alle gleichzeitigen Benutzertransaktionen während des Indexvorgangs zu speichern. Weitere Informationen finden Sie unter Transaction Log Disk Space for Index Operations.

Überlegungen zu fortsetzbaren Indizes

Die reaktivierbare Indexoption zum Erstellen von Index und Indexneuerstellung gilt für SQL Server (Indexneuerstellung ab SQL Server 2017 (14.x) und Erstellen von Index, der in SQL Server 2019 (15.x) unterstützt wird, und Azure SQL-Datenbank. Weitere Informationen finden Sie unter Create Index and Alter Index.

Für die Erstellung oder Neuerstellung von fortsetzbaren Onlineindizes gelten die folgenden Richtlinien:

  • Verwalten, Planen und Erweitern von Indexwartungsfenstern. Sie können einen Vorgang zur Indexerstellung oder -neuerstellung mehrmals anhalten und neu starten, um Ihre Wartungsfenster anzupassen.
  • Wiederherstellen nach Fehlern bei der Indexerstellung oder -neuerstellung (z.B. Datenbankfailover oder wenn kein Speicherplatz auf dem Datenträger mehr verfügbar war).
  • Wenn ein Indexvorgang angehalten wird, wird sowohl für den ursprünglichen Index als auch für den neu erstellten Index Speicherplatz benötigt, und beide müssen während des DML-Vorgangs aktualisiert werden.
  • Ermöglicht das Abschneiden von Transaktionsprotokollen während des Vorgangs einer Indexerstellung oder -neuerstellung.
  • Die Option SORT_IN_TEMPDB=ON wird nicht unterstützt.
  • Deaktivierte Indizes werden nicht unterstützt.

Wichtig

Das Erstellen oder Neuerstellen eines reaktivierbaren Index erfordert nicht, dass Sie eine lange ausgeführte Transaktion öffnen, sodass die Protokollabkürzung während dieses Vorgangs und eine bessere Verwaltung des Protokollraums möglich ist. Mit dem neuen Entwurf haben wir es geschafft, dass notwendige Daten zusammen mit allen erforderlichen Verweisen für den Neustart des fortsetzbaren Vorgangs in einer Datenbank gehalten werden.

Im Allgemeinen gibt es keinen Leistungsunterschied zwischen resumabler und nicht reresumabler Onlineindex-Neuerstellung. Für das Erstellen eines reaktivierbaren Indexes gibt es einen konstanten Overhead, der einen kleinen Leistungsunterschied zwischen resumablen und nicht umsetzbaren Indexerstellung verursacht. Dieser Unterschied ist in erster Linie nur bei kleineren Tabellen spürbar.

Wenn Sie einen fortsetzbaren Index aktualisieren, während ein Indexvorgang angehalten ist, gilt Folgendes:

  • Bei schreibgeschützten Arbeitslasten ist der Leistungseffekt unbedeutend.
  • Bei updatelastigen Workloads können Sie eine Durchsatzbeeinträchtigung erleben (unsere Tests zeigen weniger als 10 % Beeinträchtigung).

Im Allgemeinen gibt es keinen Unterschied bei der Defragmentierungsqualität zwischen resumablen und nicht umsetzbaren Onlineindexerstellung oder -neuerstellung.

Hinweis

Während ein Onlineindexvorgang angehalten wird, schlägt jeder Vorgang, der eine exklusive Sperre auf Tabellenebene für die Tabelle erfordert, die den angehaltenen Index enthält, fehl. Dies ist am häufigsten bei INSERT ... WITH (TABLOCK) Vorgängen aufgetreten. Unter Umständen tritt der folgende Fehler auf:

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Um Fehler 10637 zu beheben, entfernen Sie den TABLOCK Hinweis aus Ihrer Transaktion, oder heben Sie die Unterbrechung des Indexvorgangs auf, und warten Sie, bis sie abgeschlossen ist, bevor Sie die Transaktion erneut versuchen.

Standardonlineoptionen

Sie können Standardoptionen für online festlegen oder auf Datenbankebene fortsetzen, indem Sie die Konfigurationsoptionen für den ELEVATE_ONLINE Bereich "Datenbank" oder ELEVATE_RESUMABLE "Datenbankbereich" festlegen. Mit diesen Standardoptionen können Sie versehentliches Ausführen eines Vorgangs verhindern, der die Datenbanktabelle offline schalten würde. Beide Optionen führen dazu, dass das Modul bestimmte Vorgänge automatisch online erhöht oder die Ausführung fortsetzen kann.
Sie können die Option entweder als FAIL_UNSUPPORTED, WHEN_SUPPORTEDoder OFF mit dem BEFEHL ALTER DATABASE SCOPED CONFIGURATION festlegen. Sie können verschiedene Werte für online und fortsetzbar festlegen.

Beides gilt ELEVATE_RESUMABLE nur ELEVATE_ONLINE für DDL-Anweisungen, die die online- bzw. resumable Syntax unterstützen. Wenn Sie z. B. versuchen, einen XML-Index zu erstellen, ELEVATE_ONLINE=FAIL_UNSUPORTEDwird der Vorgang offline ausgeführt, da XML-Indizes die ONLINE= Syntax nicht unterstützen. Die Optionen wirken sich nur auf DDL-Anweisungen aus, die ohne Angabe einer ONLINE- oder RESUMABLE-Option übermittelt werden. Beispielsweise kann der Benutzer eine Einstellung ONLINE=OFFRESUMABLE=OFFaußer FAIL_UNSUPPORTED Kraft setzen und eine Anweisung offline und/oder nicht mehrumsumsieren.

Hinweis

ELEVATE_ONLINE und ELEVATE_RESUMABLE gelten nicht für XML-Indexvorgänge.

Zugehöriger Inhalt