Aggiunta di vincoli di tabella ripristinabili

  • Articolo

Si applica a: SQL Server 2022 (16.x) Database Azure SQL Istanza gestita di SQL di Azure

L'operazione resumable per la creazione e la ricompilazione degli indici online è già supportata per SQL Server 2019, Database SQL di Azure e Istanza gestita di SQL di Azure. Le operazioni resumable consentono di eseguire operazioni sugli indici mentre la tabella è online (ONLINE=ON) e anche:

  • Sospendere e riavviare più volte un'operazione di creazione o ricompilazione dell'indice in base a una finestra di manutenzione

  • Eseguire un ripristino a seguito di errori di creazione o ricompilazione dell'indice, ad esempio failover del database o esaurimento dello spazio su disco.

  • Abilitare il troncamento dei log delle transazioni durante un'operazione di creazione o ricompilazione dell'indice.

  • Quando un'operazione sull'indice viene sospesa, sia l'indice originario sia quello appena creato richiedono spazio su disco e devono essere aggiornati durante le operazioni Data Manipulation Language (DML).

Le nuove estensioni per SQL Server 2022, Database SQL e Istanza gestita di SQL consentono un'operazione resumable per il comando Data Definition Language (DDL) ALTER TABLE ADD CONSTRAINT e l'aggiunta di una chiave primaria o univoca. Per altre informazioni sull'aggiunta di una chiave primaria o univoca, vedere ALTER TABLE table_constraint.

Nota

L’aggiunta di vincoli di tabella resumable si applica solo ai vincoli PRIMARY KEY e UNIQUE KEY. L’agginta di vincoli di tabella resumable non è supportata per i vincoli FOREIGN KEY.

Operazioni ripristinabili

Nelle versioni precedenti di SQL Server, l'operazione ALTER TABLE ADD CONSTRAINT può essere eseguita con l'opzione ONLINE=ON. L'operazione, tuttavia, può richiedere molte ore per il completamento di una tabella di grandi dimensioni e può consumare un elevato numero di risorse. È possibile anche che si verifichino errori o interruzioni durante tale esecuzione. Sono state introdotte funzionalità resumable per ALTER TABLE ADD CONSTRAINT per consentire agli utenti di sospendere l'operazione durante una finestra di manutenzione o di riavviarla da dove è stata interrotta durante un errore di esecuzione, senza riavviare l'operazione dall'inizio.

Scenari supportati

La nuova funzionalità resumable per ALTER TABLE ADD CONSTRAINT supporta i seguenti scenari dei clienti:

  • Sospendere o riprendere l'esecuzione di un’operazione ALTER TABLE ADD CONSTRAINT, ad esempio sospenderla per una finestra di manutenzione e riprendere l'operazione al termine della finestra di manutenzione.

  • Riprendere l'operazione ALTER TABLE ADD CONSTRAINT a seguito di failover ed errori di sistema.

  • Esecuzione dell’operazione ALTER TABLE ADD CONSTRAINT su una tabella di grandi dimensioni nonostante le piccole dimensioni del log disponibili.

Nota

L'operazione resumable per ALTER TABLE ADD CONSTRAINT richiede l’esecuzione del comando ALTER online (WITH ONLINE = ON).

Questa funzionalità è particolarmente utile per tabelle di grandi dimensioni.

Sintassi T-SQL per ALTER TABLE

Per informazioni sulla sintassi usata per abilitare operazioni resumable su un vincolo di tabella, vedere la sintassi e le opzioni in ALTER TABLE (Transact-SQL).

Osservazioni per ALTER TABLE

  • È stata aggiunta una nuova clausola WITH <resumable_options alla sintassi T-SQL corrente in ALTER TABLE (Transact-SQL).

  • L'opzione RESUMABLE è nuova ed è stata aggiunta alla sintassi di ALTER TABLE (Transact-SQL) esistente.

  • MAX_DURATION = time [MINUTES] usato con RESUMABLE = ON (richiede ONLINE = ON). MAX_DURATION indica il tempo (un valore intero specificato in minuti) per cui viene eseguita un’operazione di aggiunta di un vincolo online resumable prima che venga sospesa. Se l'opzione non è specificata, l'operazione continua fino al completamento.

Sintassi T-SQL per ALTER INDEX

Per sospendere, riprendere o interrompere l'operazione di vincolo di tabella resumable per ALTER TABLE ADD CONSTRAINT, usare la sintassi T-SQL ALTER INDEX (Transact-SQL).

Per i vincoli resumable viene usato il comando ALTER INDEX ALL esistente.

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

Osservazioni per ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Sospendere un'operazione di aggiunta vincolo di tabella resumable e online in esecuzione

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

  • Riprendere un'operazione di aggiunta vincolo di tabella che è stata sospesa manualmente o a causa di un errore.

MAX_DURATION usato con RESUMABLE=ON

  • Il tempo di esecuzione (un valore intero specificato in minuti) di un'operazione di aggiunta vincolo di tabella resumable dopo la ripresa. Allo scadere del tempo, l'operazione ripristinabile viene sospesa se è ancora in esecuzione.

WAIT_AT_LOW_PRIORITY usato con RESUMABLE=ON e ONLINE = ON

  • La ripresa di un'operazione di aggiunta vincolo di tabella online dopo una pausa deve attendere le operazioni di blocco in questa tabella. WAIT_AT_LOW_PRIORITY indica che l'operazione di aggiunta vincolo di tabella attenderà blocchi con priorità bassa, consentendo la continuazione delle altre operazioni mentre l’operazione resumable è in attesa. L'omissione dell'opzione WAIT_AT_LOW_PRIORITY equivale a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Per altre informazioni, vedere WAIT_AT_LOW_PRIORITY.

ALTER INDEX ALL ON <Table> ABORT

  • Interrompere un'operazione di aggiunta vincolo di tabella in esecuzione o sospesa che è stata dichiarata resumable. L'operazione di interruzione deve essere eseguita in modo esplicito come comando ABORT per terminare un'operazione di vincolo resumable. Un errore o la sospensione di un'operazione di vincolo di tabella resumable non termina l'esecuzione. Lascia, invece, l'operazione in uno stato sospeso indefinito.

Per altre informazioni sulle opzioni PAUSE, RESUME e ABORT disponibili per operazioni resumable, vedere ALTER INDEX (Transact-SQL).

Visualizzare lo stato per l'operazione resumable

Per visualizzare lo stato per l'operazione di vincolo di tabella resumable, usare la vista sys.index_resumable_operations.

Autorizzazioni

È richiesta l'autorizzazione ALTER per la tabella.

Non sono necessarie nuove autorizzazioni per ALTER TABLE ADD CONSTRAINT resumable.

Esempi

Ecco alcuni esempi sull'uso di operazioni di aggiunta vincolo di tabella resumable.

Esempio 1

Operazione ALTER TABLE ripristinabile per l'aggiunta di una chiave primaria in cluster nella colonna (a) con MAX_DURATION di 240 minuti.

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

Esempio 2

Operazione ALTER TABLE resumable per l'aggiunta di un vincolo univoco su due colonne (a e b) con MAX_DURATION di 240 minuti.

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

Esempio 3

Operazione ALTER TABLE per l'aggiunta di una chiave primaria in cluster sospesa e ripresa.

La tabella seguente mostra due sessioni (Session #1 e Session #2) eseguite in ordine cronologico usando le istruzioni T-SQL seguenti. Session #1 esegue un'operazione ALTER TABLE ADD CONSTRAINT resumable creando una chiave primaria sulla colonna Col1. Session #2 controlla lo stato di esecuzione per il vincolo in esecuzione. Dopo un po’ di tempo, sospende l'operazione resumable. Session #2 controlla lo stato per il vincolo sospeso. Session #1, infine, riprende il vincolo sospeso e Session #2 controlla nuovamente lo stato.

Sessione n. 1 Sessione n. 2
Eseguire un’aggiunta vincolo resumable

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
Controllare lo stato del vincolo

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Output che mostra l'operazione

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43,552
Sospendere il vincolo resumable

ALTER INDEX ALL ON TestConstraint PAUSE;
Errore

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.
Controllare lo stato del vincolo

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Output che mostra l'operazione

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)PAUSED65,339
ALTER INDEX ALL ON TestConstraint RESUME;
Controllare lo stato del vincolo

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Output che mostra l'operazione

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

Al termine dell'operazione, eseguire l'istruzione T-SQL seguente per controllare il vincolo:

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

Il set di risultati è il seguente:

constraint_name table_name constraint_type
PK_Constraint TestConstraint PRIMARY KEY

Vedi anche