Fortsetzbares Hinzufügen von Tabellenconstraints

  • Artikel

Gilt für: SQL Server 2022 (16.x) Azure SQL-Datenbank Azure SQL Managed Instance

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 zur Indexerstellung oder -neuerstellung mehrmals anhalten und neu starten, um ein 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)

  • Abschneiden von Transaktionsprotokollen während des Vorgangs einer Indexerstellung oder -neuerstellung 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 fortsetzbaren Vorgang für den DDL-Befehl (Datendefinitionssprache) ALTER TABLE ADD CONSTRAINT und das Hinzufügen eines Primärschlüssels oder eindeutigen Schlüssels. Weitere Informationen zum Hinzufügen eines Primärschlüssels oder eindeutigen Schlüssels finden Sie unter ALTER TABLE table_constraint.

Hinweis

Die fortsetzungsfähigen Tabelleneinschränkungen gelten nur für PRIMÄRSCHLÜSSEL- und UNIQUE KEY-Einschränkungen. Fortsetzungsfähige Tabelleneinschränkungen werden für FREMDSCHLÜSSEL-Einschränkungen nicht unterstützt.

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.

Hinweis

Der fortsetzbare Vorgang für ALTER TABLE ADD CONSTRAINT erfordert, dass der ALTER-Befehl online ausgeführt wird (WITH ONLINE = ON).

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

T-SQL-Syntax für ALTER TABLE

Informationen zur Syntax, die verwendet wird, um fortsetzbare Vorgänge für eine Tabelleneinschränkung zu ermöglichen, finden Sie in der Syntax und den Optionen in ALTER TABLE (Transact-SQL).

Hinweise für ALTER TABLE

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

  • Die Option RESUMABLE ist neu und wurde der vorhandenen ALTER TABLE (Transact-SQL)-Syntax 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 zum Anhalten, Fortsetzen oder Abbrechen des fortsetzbaren Tabellenconstraintvorgangs für ALTER TABLE ADD CONSTRAINT die T-SQL-Syntax ALTER INDEX (Transact-SQL).

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

Hinweise für ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Anhalten eines ausgeführten fortsetzbaren Onlinevorgangs zum Hinzufügen eines Tabellenconstraints

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 Zeitspanne (als ganzzahliger Wert in Minuten), die ein fortsetzbarer Onlinevorgang zum Hinzufügen eines Tabellenconstraints ausgeführt wird, nachdem er fortgesetzt wurde. Nach Ablauf dieser Zeitspanne wird der fortsetzbare Vorgang angehalten, falls er noch ausgeführt wird.

WAIT_AT_LOW_PRIORITY wird mit RESUMABLE=ON und ONLINE = ON verwendet

  • Beim Fortsetzen eines Onlinevorgangs zum Hinzufügen eines Tabellenconstraints nach einer Pause muss auf blockierende Vorgänge für diese Tabelle gewartet werden. WAIT_AT_LOW_PRIORITY gibt an, dass der Vorgang zum Hinzufügen eines Tabellenconstraints auf Sperren mit niedriger Priorität wartet und die weitere Ausführung anderer Vorgänge ermöglicht, 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 ausgeführten oder angehaltenen Vorgang zum Hinzufügen eines Tabellenconstraints 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. Der Vorgang befindet sich stattdessen in einem unbestimmten Pausenzustand.

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

Anzeigen des Status für den fortsetzbaren Vorgang

Verwenden Sie die Sicht sys.index_resumable_operations, um den Status des Vorgangs für den fortsetzbaren Tabellenconstraintvorgang anzuzeigen.

Berechtigungen

Erfordert die ALTER-Berechtigung für die 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 Primärschlüssels, gruppiert für Spalte (a) mit 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 Unique-Einschränkung auf 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 wird der wiederverwendbare Vorgang angehalten. Session #2 überprüft den Status für die angehaltene Einschränkung. Zum Schluss setzt Session #1 die angehaltene Einschränkung fort, und Session #2 prüft noch mal den Status.

Sitzung 1 Sitzung 2
Fortsetzbare Add-Einschränkung ausführen

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
Einschränkungsstatus überprüfen

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Ausgabe mit dem Vorgang

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43.552
Fortsetzbare Einschränkung anhalten

ALTER INDEX ALL ON TestConstraint PAUSE;
Fehler

Msg 1219, Level 16, State 1, Line 6
Your session has been disconnected because of a high priority DDL operation.

Msg 1750, Level 16, State 1, Line 6
Could not create constraint or index. See previous errors.

Msg 0, Level 20, State 0, Line 5
A severe error occurred on the current command.
The results, if any, should be discarded.
Einschränkungsstatus überprüfen

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Ausgabe mit dem Vorgang

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)ANGEHALTEN65.339
ALTER INDEX ALL ON TestConstraint RESUME;
Einschränkungsstatus überprüfen

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Ausgabe mit dem Vorgang

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING90.238

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:

constraint_name table_name constraint_type
PK_Constraint TestConstraint PRIMARY KEY

Weitere Informationen