Wiederaufnehmbares Hinzufügen von Tabelleneinschränkungen

Gilt für: SQL Server 2022 (16.x) und höhere Versionen Azure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

Der fortsetzbare Vorgang für die Onlineindexerstellung und -neuerstellung wird bereits für SQL Server 2019, Azure SQL-Datenbank und Azure SQL Managed Instance unterstützt. Die fortsetzbaren Vorgänge ermöglichen die Ausführung von Indexvorgängen, während die Tabelle online ist (ONLINE=ON), und zusätzlich Folgendes:

• Vorgang zum Erstellen oder Neuerstellen eines Index mehrmals anhalten und neu starten, damit er in ein Wartungsfenster passt

• Wiederherstellen nach Fehlern bei der Indexerstellung oder -neuerstellung (z. B. Datenbankfailover oder wenn kein Speicherplatz auf dem Datenträger mehr verfügbar war)

• Trunkierung von Transaktionsprotokollen während der Erstellung oder Neuerstellung eines Index ermöglichen

• 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 DML-Vorgängen (Datenbearbeitungssprache) aktualisiert werden.

Die neuen Erweiterungen für SQL Server 2022, SQL-Datenbank und SQL Managed Instance ermöglichen einen reaktivierbaren Vorgang für den Befehl DATA Definition Language (DDL)ALTER TABLE ADD CONSTRAINT und Hinzufügen eines primären oder eindeutigen Schlüssels. Weitere Informationen zum Hinzufügen eines Primärschlüssels oder eines eindeutigen Schlüssels finden Sie unterALTER TABLE table_constraint.

Fortsetzbare Vorgänge

In früheren Versionen von SQL Server kann der ALTER TABLE ADD CONSTRAINT-Vorgang mit der ONLINE=ON-Option ausgeführt werden. Der Vorgang kann jedoch viele Stunden dauern, bis eine große Tabelle abgeschlossen ist, und kann eine große Anzahl von Ressourcen verbrauchen. Während dieser Ausführung kann es auch zu Fehlern oder Unterbrechungen kommen. Wir haben für ALTER TABLE ADD CONSTRAINT fortsetzbare Vorgänge eingeführt, damit Benutzer den Vorgang während eines Wartungsfensters anhalten oder von dem Punkt aus neu starten können, an dem er während eines Ausführungsfehlers unterbrochen wurde, ohne den Vorgang von Anfang an neu zu starten.

Unterstützte Szenarios

Die neue fortsetzbare Funktion für ALTER TABLE ADD CONSTRAINT unterstützt die folgenden Kundenszenarios:

• Anhalten oder Fortsetzen des laufenden ALTER TABLE ADD CONSTRAINT-Vorgangs, z. B. Anhalten des Vorgangs für ein Wartungsfenster und Fortsetzen des Vorgangs, sobald das Wartungsfenster abgeschlossen ist.

• Fortsetzen des ALTER TABLE ADD CONSTRAINT-Vorgangs nach Failovern und Systemfehlern.

• Ausführen des ALTER TABLE ADD CONSTRAINT-Vorgangs für eine große Tabelle trotz der geringen verfügbaren Protokollgröße.

Dieses Feature ist besonders nützlich für große Tabellen.

T-SQL-Syntax für ALTER TABLE

Informationen zur Syntax, die zum Aktivieren resumabler Vorgänge für eine Tabelleneinschränkung verwendet wird, finden Sie in der Syntax und den Optionen in ALTER TABLE (Transact-SQL).

Bemerkungen für ALTER TABLE

• Der aktuellen T-SQL-Syntax in ALTER TABLE (Transact-SQL) wurde die neue Klausel WITH <resumable_options hinzugefügt.

• Die Option RESUMABLE ist neu und wurde der vorhandenen ALTER TABLE Syntax (Transact-SQL) hinzugefügt.

• MAX_DURATION = time [MINUTES] wird mit RESUMABLE = ON verwendet (erfordert ONLINE = ON). MAX_DURATION gibt die Zeitspanne an (als ganzzahligen Wert in Minuten), in der ein fortsetzbarer Onlinevorgang zum Hinzufügen einer Einschränkung ausgeführt wird, bevor er angehalten wird. Wenn nicht angegeben, wird der Vorgang bis zum Abschluss fortgesetzt.

T-SQL-Syntax für ALTER INDEX

Verwenden Sie die T-SQL-Syntax ALTER INDEX (Transact-SQL), um den fortsetzbaren Tabelleneinschränkungsvorgang für ALTER TABLE ADD CONSTRAINT anzuhalten, fortzusetzen oder abzubrechen.

Für fortsetzbare Einschränkungen wird der vorhandene ALTER INDEX ALL-Befehl verwendet.

ALTER INDEX ALL ON <table_name>  
      { RESUME [WITH (<resumable_index_options>,[...n])]
        | PAUSE
        | ABORT
      }
<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 } )  
}  

Bemerkungen für ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

• Anhalten eines wiederaufnehmbaren Onlinevorgangs zum Hinzufügen einer Tabelleneinschränkung, der gerade ausgeführt wird

ALTER INDEX ALL ON <Table> RESUME [WITH (<resumable_index_options>,[...n])]

• Setzen Sie einen Vorgang zum Hinzufügen eines Tabellenconstraints, der manuell oder aufgrund eines Fehlers angehalten wurde, fort.

MAX_DURATION wird mit RESUMABLE=ON verwendet

• Die Zeit (ein in Minuten angegebener Ganzzahlwert), für die der fortsetzbare Vorgang zum Hinzufügen einer Tabelleneinschränkung nach seiner Fortsetzung ausgeführt wird. Nach Ablauf dieser Zeitspanne wird der fortsetzbare Vorgang angehalten, falls er noch ausgeführt wird.

WAIT_AT_LOW_PRIORITY wird mit RESUMABLE=ON und ONLINE = ON verwendet

• Beim Fortsetzen eines Onlinevorgangs zum Hinzufügen einer Tabelleneinschränkung nach einer Pause muss gewartet werden, bis blockierende Vorgänge für diese Tabelle abgeschlossen sind. WAIT_AT_LOW_PRIORITY gibt an, dass der Vorgang zum Hinzufügen einer Tabelleneinschränkung auf Sperren mit niedriger Priorität wartet, sodass andere Vorgänge fortgesetzt werden können, während der fortsetzbare Vorgang 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.

ALTER INDEX ALL ON <Table> ABORT

• Brechen Sie einen laufenden oder angehaltenen Vorgang zum Hinzufügen einer Tabelleneinschränkung ab, der als fortsetzbar deklariert wurde. Der Abbruchvorgang muss explizit als ABORT-Befehl ausgeführt werden, um einen fortsetzbaren Einschränkungsvorgang zu beenden. Durch das Auftreten eines Fehlers oder durch Anhalten eines fortsetzbaren Tabellenconstraintvorgangs wird dessen Ausführung nicht beendet. Vielmehr lässt es den Vorgang auf unbestimmte Zeit in einem Pausenzustand.

Weitere Informationen zu den für fortsetzbare Vorgänge verfügbaren Optionen PAUSE, RESUME und ABORT finden Sie unter ALTER INDEX (Transact-SQL).

Status des wiederaufnehmbaren Vorgangs anzeigen

Verwenden Sie die Sicht sys.index_resumable_operations, um den Status des Vorgangs für eine fortsetzbare Tabelleneinschränkung anzuzeigen.

Berechtigungen

Erfordert die ALTER-Berechtigung auf der Tabelle.

Es sind keine neuen Berechtigungen für den fortsetzbaren ALTER TABLE ADD CONSTRAINT-Vorgang erforderlich.

Beispiele

Im Folgenden einige Beispiele für die Verwendung von fortsetzbaren Vorgängen zum Hinzufügen von Tabellenconstraints.

Beispiel 1

Fortsetzbarer ALTER TABLE Vorgang zum Hinzufügen eines auf Spalte (a) geclusterten Primärschlüssels mit einer MAX_DURATION von 240 Minuten.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Beispiel 2

Fortsetzbarer ALTER TABLE-Vorgang zum Hinzufügen einer eindeutigen Einschränkung für zwei Spalten (a und b) mit MAX_DURATION von 240 Minuten.

ALTER TABLE table2
ADD CONSTRAINT PK_Constrain UNIQUE CLUSTERED (a,b)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Beispiel 3

ALTER TABLE-Vorgang zum Hinzufügen eines gruppierten Primärschlüssels, der angehalten und fortgesetzt wird.

Die folgende Tabelle zeigt zwei Sitzungen (Session #1 und Session #2), die chronologisch mit den folgenden T-SQL-Anweisungen ausgeführt werden. Session #1 führt einen fortsetzbaren ALTER TABLE ADD CONSTRAINT-Vorgang aus, der einen Primärschlüssel für Spalte Col1erstellt. Session #2 überprüft den Ausführungsstatus für die ausgeführte Einschränkung. Nach einiger Zeit pausiert es die wiederholbare Operation. Session #2 überprüft den Status der angehaltenen Beschränkung. Zum Schluss setzt Session #1 die angehaltene Einschränkung fort, und Session #2 prüft noch mal den Status.

Führen Sie nach Abschluss des Vorgangs die folgende T-SQL-Anweisung aus, um die Einschränkung zu überprüfen:

SELECT constraint_name, table_name, constraint_type 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY';
GO

Das Ergebnis lautet wie folgt:

Siehe auch

• Richtlinien für Onlineindexvorgänge
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• sys.index_resumable_operations
• WAIT_AT_LOW_PRIORITY
• ALTER TABLE (Transact-SQL)
• ALTER TABLE index_option