Aggiunta di vincoli di tabella ripristinabili

Si applica a: SQL Server 2022 (16.x) e versioni successive database SQL di AzureIstanza gestita di SQL di AzureDatabase SQL in Microsoft Fabric

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 ricreazione dell'indice in modo da adattarsi 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, il database SQL e Istanza gestita di SQL consentono un'operazione ripristinabile per il comando DDL (Data Definition Language) ADD CONSTRAINT e l'aggiunta di una chiave primaria o univoca.ALTER TABLE Per altre informazioni sull'aggiunta di una chiave primaria o univoca, vedere ALTER TABLE table_constraint.

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à di ripresa per ALTER TABLE ADD CONSTRAINT supporta i seguenti scenari per i 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.

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

Sintassi T-SQL per ALTER TABLE

Per informazioni sulla sintassi usata per abilitare le operazioni ripristinabili 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 (Transact-SQL) esistenteALTER TABLE.

• 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 ripristinabile per ALTER TABLE ADD CONSTRAINT, usare la sintassi ALTER INDEX T-SQL (Transact-SQL).

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

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 online e ripristinabile di aggiunta di un vincolo di tabella in esecuzione

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

• Riprendi un'operazione di aggiunta di un vincolo di tabella sospesa manualmente o in seguito a un errore.

MAX_DURATION usato con RESUMABLE=ON

• L'intervallo di tempo (un valore intero specificato in minuti) dopo il quale viene eseguita l'operazione ripristinabile di aggiunta di un vincolo di tabella, dopo essere stata 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 di un vincolo di tabella attenderà i blocchi a bassa priorità, consentendo ad altre operazioni di continuare mentre l'operazione ripristinabile è 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

• Annullare un'operazione in esecuzione o sospesa di aggiunta di un vincolo di tabella dichiarata come ripristinabile. 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 PAUSEopzioni , RESUMEe ABORT disponibili per le operazioni ripristinabili, vedere ALTER INDEX (Transact-SQL).

Visualizza lo stato dell'operazione ripristinabile

Per visualizzare lo stato dell'operazione di vincolo di tabella ripristinabile, utilizzare la vista sys.index_resumable_operations.

Autorizzazioni

Richiede l'autorizzazione ALTER per la tabella.

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

Esempi

Ecco alcuni esempi di operazioni ripristinabili di aggiunta di vincoli di tabella.

Esempio 1

Operazione ALTER TABLE riprendibile per l'aggiunta di una chiave primaria clusterizzata 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 riprendibile per aggiungere 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 verifica lo stato di esecuzione del vincolo in esecuzione. Dopo un po' di tempo, sospende l'operazione riutilizzabile. Session #2 verifica lo stato del vincolo in pausa. Session #1, infine, riprende il vincolo sospeso e Session #2 controlla nuovamente lo stato.

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:

Vedi anche

• Linee guida per le operazioni sugli indici online
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• sys.index_resumable_operations
• WAIT_AT_LOW_PRIORITY
• ALTER TABLE (Transact-SQL)
• ALTER TABLE index_option