Freigeben über

Richtlinien für Onlineindexvorgänge

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance SQL-Datenbank in Microsoft Fabric

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 von dort fortgesetzt werden, wo nach einem unerwarteten Fehler, einem Datenbank-Failover oder einem PAUSE-Befehl angehalten wurde. Weitere Informationen finden Sie unter Indexerstellung und Alter Index.

Hinweis

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.

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 des ALL-Schlüsselworts kann bewirken, dass die Operation einen Fehler erzeugt, 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		 Es können nicht mehrere Indizes in einer einzigen 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 PRIMARY KEY- oder UNIQUE-Einschränkungen in derselben ALTER TABLE Anweisung hinzufügen und löschen.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY oder UNIQUE) Gruppierter Index

Die zugrunde liegende Tabelle kann nicht modifiziert, abgeschnitten oder gelöscht werden, während ein Onlineindexvorgang ausgeführt wird.

Die beim Erstellen oder Löschen eines gruppierten Indexes angegebene Einstellung für die Onlineoption (ON oder OFF) wird auf alle nicht gruppierten Indizes angewendet, die neu erstellt werden müssen. Wenn der gruppierte Index beispielsweise online mithilfe von CREATE INDEX WITH DROP_EXISTING, ONLINE=ON erstellt wird, werden alle zugeordneten nicht gruppierten Indizes ebenfalls online neu erstellt.

Wenn Sie einen UNIQUE-Index online erstellen oder neu erstellen, versuchen die Indexerstellung und eine gleichzeitige Benutzertransaktion möglicherweise, den gleichen Schlüssel einzufügen. Dies verletzt die Eindeutigkeit. Wenn eine von einem Benutzer in den neuen Index (Ziel) eingegebene Zeile 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 die SQL Server-Datenbank-Engine die Benutzer- oder Anwendungsaktivität als Deadlockopfer aus.

Sie können gleichzeitige Onlineindex-DDL-Operationen für die gleiche Tabelle oder Sicht nur dann 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 für die gleiche Tabelle online neu erstellen.

Ein Onlinevorgang kann nicht ausgeführt werden, wenn ein Index eine Spalte des Datentyps für große Objekte enthält und wenn dieselbe Transaktion vor diesem Onlinevorgang Updatevorgänge enthält. 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

Zwar ermöglichen Onlineindexvorgänge gleichzeitige Benutzerupdateaktivitäten, die Indexvorgänge benötigen jedoch mehr Zeit, wenn die Updateaktivitäten umfangreich sind. In der Regel sind Onlineindexvorgänge langsamer als die entsprechenden Offlineindexvorgänge, und zwar unabhängig davon, in welchem Umfang gleichzeitige Updateaktivitäten ausgeführt werden.

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 vorteilhafter sein, Indexvorgänge offline auszuführen. Dabei besitzen Benutzer während der Operation nur eingeschränkten Zugriff auf die Daten, der Vorgang wird jedoch schneller abgeschlossen und verwendet weniger Ressourcen.

Auf Mehrprozessorcomputern, auf denen SQL Server 2016 (13.x) ausgeführt wird, verwenden Indexanweisungen möglicherweise mehrere Prozessoren, um die Scan- und Sortiervorgänge auszuführen, die mit der Indexanweisung verknüpft sind, genau so, wie andere Abfragen dies tun. Sie können die MAXDOP-Indexoption verwenden, um die Anzahl der Prozessoren für den Onlineindexvorgang zu steuern. Auf diese Weise können Sie die Ressourcen, die vom Indexvorgang verwendet werden, mit den Ressourcen gleichzeitiger Benutzer ausgleichen. 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 Von den SQL Server 2022-Editionen unterstützte Funktionen.

Da eine Sperre des Typs S- oder Sch-M in der Abschlussphase des Indexvorgangs aktiviert wird, sollten Sie Vorsicht walten lassen, wenn Sie einen Onlineindexvorgang innerhalb einer expliziten Benutzertransaktion ausführen, z. B. in einem BEGIN TRANSACTION ... COMMIT-Block. 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 größer als 1 und ALLOW_PAGE_LOCKS=OFF ausgefü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, die das Transaktionsprotokoll schnell füllen können. Dies liegt daran, dass sowohl Offline- als auch Onlinevorgänge für die Neuerstellung von Indizes vollständig protokolliert werden. Damit sichergestellt wird, dass für den Indexvorgang ein Rollback ausgeführt werden kann, kann das Transaktionsprotokoll erst abgeschnitten werden, nachdem der Indexvorgang abgeschlossen wurde; das Protokoll kann jedoch während des Indexvorgangs gesichert werden.

Aus diesem Grund muss das Transaktionsprotokoll während des Indexvorgangs genügend Speicherplatz zum Speichern der Transaktionen des Indexvorgangs sowie ggf. der gleichzeitigen Benutzertransaktionen aufweisen. Weitere Informationen finden Sie unter Transaction Log Disk Space for Index Operations.

Überlegungen zu fortsetzbaren Indizes

Die fortsetzbare Indexoption für CREATE INDEX und INDEX REBUILD gilt für SQL Server (INDEX REBUILD ab SQL Server 2017 (14.x), CREATE INDEX wird in SQL Server 2019 (15.x)) unterstützt) und Azure SQL-Datenbank. Weitere Informationen zu Indizes finden Sie unter Erstellen eines Index und 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

Für die Erstellung oder Neuerstellung fortsetzbarer Indizes muss keine Transaktion mit langer Ausführungsdauer geöffnet bleiben. Deswegen kann das Protokoll während dieses Vorgangs gekürzt werden, was eine bessere Verwaltung des Protokollspeicherplatzes gestattet. 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 besteht kein Leistungsunterschied zwischen fortsetzbaren und nicht fortsetzbaren Neuerstellungen von Onlineindizes. Für die Erstellung von fortsetzbaren Indizes fällt fortwährend Mehraufwand an, weshalb ein geringfügiger Leistungsunterschied zwischen der Erstellung von fortsetzbaren und nicht fortsetzbaren Indizes besteht. 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 Arbeitsauslastungen, die meistens nur gelesen werden, ist die Leistungsauswirkung unbedeutend.
  • Bei Arbeitsauslastungen, die oft aktualisiert werden, tritt möglicherweise eine Minderung des Durchsatzes auf (bei unseren Tests ergab sich eine Minderung von unter 10 %).

Im Allgemeinen besteht kein Unterschied bei der Defragmentierungsqualität zwischen der Erstellung oder Neuerstellung von fortsetzbaren und nicht fortsetzbaren Onlineindizes.

Hinweis

Während ein Onlineindexvorgang angehalten wird, schlägt jede Operation fehl, der eine exklusive Sperre auf Tabellenebene für die Tabelle erfordert, die den angehaltenen Index enthält. Das tritt am häufigsten bei INSERT ... WITH (TABLOCK)-Operationen auf. 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 der Index-Operation auf, und warten Sie, bis sie abgeschlossen ist, bevor Sie die Transaktion erneut versuchen.

Standardonlineoptionen

Sie können Standardoptionen für online oder fortsetzbar auf Datenbankebene festlegen, indem Sie die datenbankbezogenen Konfigurationsoptionen ELEVATE_ONLINE und ELEVATE_RESUMABLE festlegen. Mit diesen Standardoptionen können Sie versehentliches Ausführen eines Vorgangs verhindern, der die Datenbanktabelle offline schalten würde. Beide Optionen bewirken, dass die Engine bestimmte Vorgänge automatisch in Online- oder fortsetzbare Ausführung erhöht.
Sie können die Option über den Befehl ALTER DATABASE SCOPED CONFIGURATIONentweder auf FAIL_UNSUPPORTED, WHEN_SUPPORTED oder OFF festlegen. Sie können verschiedene Werte für online und fortsetzbar festlegen.

Sowohl ELEVATE_ONLINE als auch ELEVATE_RESUMABLE gelten nur für DDL-Anweisungen, die die Syntax für online bzw. fortsetzbar unterstützen. Wenn Sie beispielsweise versuchen, einen XML-Index mit ELEVATE_ONLINE=FAIL_UNSUPORTED zu erstellen, wird der Vorgang offline ausgeführt, weil die ONLINE=-Syntax nicht für XML-Indizes unterstützt wird. Die Optionen wirken sich nur auf DDL-Anweisungen aus, die ohne Angabe einer ONLINE- oder RESUMABLE-Option gesendet werden. Wird z. B. eine Anweisung ONLINE=OFF oder RESUMABLE=OFF gesendet, kann der Benutzer eine FAIL_UNSUPPORTED-Einstellung außer Kraft setzen und eine Anweisung offline und/oder nicht fortsetzbar ausführen.

Hinweis

ELEVATE_ONLINE und ELEVATE_RESUMABLE gelten nicht für XML-Index-Operationen.

Zugehöriger Inhalt