Contraintes d’ajout à une table pouvant être repris

  • Article

S’applique à : SQL Server 2022 (16.x)base de données Azure SQL Azure SQL Managed Instance

L’opération pouvant être reprise dans le cadre de la création et de la régénération d’index en ligne est déjà prise en charge pour SQL Server 2019, la base de données Azure SQL et Azure SQL Managed Instance. Les opérations pouvant être reprises permettent l’exécution des opérations d’index pendant que la table est en ligne (ONLINE=ON) et aussi :

  • Mettre en suspens et redémarrer une opération de création ou de reconstruction d’index à plusieurs reprises en fonction d’une fenêtre de maintenance

  • Récupérer après des échecs de création ou de reconstruction d’index, par exemple des basculements de base de données ou un manque d’espace disque.

  • Activer la troncation des journaux des transactions au cours d’une opération de création ou de reconstruction d’index.

  • Quand une opération d’index est mise en suspens, l’index d’origine et celui qui vient d’être créé nécessitent de l’espace disque et doivent être mis à jour lors d’opérations DML (Data Manipulation Language).

Les nouvelles extensions sur SQL Server 2022, SQL Database et SQL Managed Instance autorisent une opération pouvant être reprise pour la commande DDL (langage de définition de données) ALTER TABLE ADD CONSTRAINT, et l’ajout d’une clé primaire ou unique. Pour plus d’informations sur l’ajout d’une clé primaire ou unique, consultez ALTER TABLE contrainte_table.

Remarque

Les contraintes d’ajout à une table pouvant être reprise ne s’appliquent qu’aux contraintes de PRIMARY KEY et de UNIQUE KEY. Les contraintes d’ajout à une table pouvant être reprise ne sont pas prises en charge dans le cas de contraintes de type FOREIGN KEY.

Opérations pouvant être reprises

Dans les versions précédentes de SQL Server, l’opération ALTER TABLE ADD CONSTRAINT peut être exécutée avec l’option ONLINE=ON. Cependant, l’opération peut prendre plusieurs heures pour une grande table et consommer un grand nombre de ressources. Il peut également y avoir des défaillances ou des interruptions pendant cette exécution. Nous avons introduit des fonctionnalités pouvant être reprises dans ALTER TABLE ADD CONSTRAINT pour permettre aux utilisateurs de suspendre l’opération pendant une fenêtre de maintenance ou de la redémarrer là où elle a été interrompue lors d’un échec d’exécution, sans redémarrer l’opération à partir du début.

Scénarios pris en charge

La nouvelle fonctionnalité pouvant être reprise pour ALTER TABLE ADD CONSTRAINT prendre en charge les scénarios suivants des clients :

  • Suspendre ou reprendre l’opération ALTER TABLE ADD CONSTRAINT en cours d’exécution, comme la suspendre pour une fenêtre de maintenance et reprendre l’opération une fois la fenêtre de maintenance terminée.

  • Reprendre l’opération ALTER TABLE ADD CONSTRAINT après des basculements et des défaillances système.

  • Exécuter ALTER TABLE ADD CONSTRAINT sur une grande table malgré la petite taille de journal disponible.

Remarque

L’opération pouvant être reprise pour ALTER TABLE ADD CONSTRAINT nécessite l’exécution en ligne de la ALTER commande (WITH ONLINE = ON).

Cette fonctionnalité est particulièrement utile pour les grandes tables.

Syntaxe T-SQL pour ALTER TABLE

Pour plus d’informations sur la syntaxe utilisée pour activer les opérations pouvant être reprises sur une contrainte de table, consultez la syntaxe et les options dans ALTER TABLE (Transact-SQL).

Remarques pour ALTER TABLE

  • Une nouvelle clause WITH <options_reprise a été ajoutée à la syntaxe T-SQL actuelle dans ALTER TABLE (Transact-SQL).

  • L’option RESUMABLE est nouvelle et a été ajoutée à la syntaxe ALTER TABLE (Transact-SQL) existante.

  • MAX_DURATION = durée [MINUTES] utilisée avec RESUMABLE = ON (nécessite ONLINE = ON). MAX_DURATION indique la durée (une valeur entière spécifiée en minutes) pendant laquelle une opération d’ajout de contrainte en ligne pouvant être reprise est exécutée avant d’être mise en suspens. Si elle n’est pas spécifiée, l’opération continue jusqu’à ce qu’elle soit terminée.

Syntaxe T-SQL pour ALTER INDEX

Pour suspendre, reprendre ou abandonner l’opération de contrainte de table pouvant être reprise pour ALTER TABLE ADD CONSTRAINT, utilisez la syntaxe T-SQL ALTER INDEX (Transact-SQL).

Pour les contraintes pouvant être reprises, la commande ALTER INDEX ALL existante est utilisée.

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

Remarques pour ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Suspendre une opération de contrainte d’ajout de table pouvant être reprise et en ligne en cours d’exécution

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

  • Reprendre une opération d’ajout de contrainte de table mise en pause manuellement ou suite à une défaillance.

MAX_DURATION utilisé avec RESUMABLE=ON

  • Durée (une valeur entière spécifiée en minutes) pendant laquelle l’opération d’ajout de contrainte de table pouvant être reprise est exécutée après avoir été reprise. Une fois que le délai expire, l’opération pouvant être reprise est suspendue si elle est toujours en cours d’exécution.

WAIT_AT_LOW_PRIORITY utilisé avec RESUMABLE=ON et ONLINE = ON

  • La reprise d’une opération de contrainte d’ajout de table en ligne après une pause doit attendre les opérations bloquantes sur cette table. WAIT_AT_LOW_PRIORITY indique que l’opération d’ajout de contrainte de table doit attendre des verrous de basse priorité, en permettant à d’autres opérations de continuer pendant que l’opération pouvant être reprise attend. Omettre l’option WAIT_AT_LOW_PRIORITY équivaut à WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Pour plus d’informations, consultez WAIT_AT_LOW_PRIORITY.

ALTER INDEX ALL ON <Table> ABORT

  • Abandonner une opération d’ajout de contrainte de table en cours d’exécution ou en pause qui a été déclarée comme pouvant être reprise. L’opération d’abandon doit être exécutée explicitement sous la dorme d’une commande ABORT pour arrêter une opération de contrainte pouvant être reprise. L’échec ou la mise en suspens d’une opération de contrainte de table pouvant être reprise n’arrête pas son exécution. Au lieu de cela, elle laisse l’opération dans un état de mise en suspens infinie.

Pour plus d’informations sur les options PAUSE, RESUME et ABORT disponibles pour les opérations pouvant être reprises, consultez ALTER INDEX (Transact-SQL).

Voir l’état d’une opération pouvant être reprise

Pour voir l’état de l’opération de contrainte de table pouvant être reprise, utilisez la vue sys.index_resumable_operations.

autorisations

Nécessite l'autorisation ALTER sur la table.

Aucune nouvelle autorisation n’est nécessaire pour l’opération ALTER TABLE ADD CONSTRAINT pouvant être reprise.

Exemples

Voici quelques exemples d’utilisation des opérations d’ajout de contrainte de table pouvant être reprises.

Exemple 1

Opération ALTER TABLE pouvant être reprise pour l’ajout d’une clé primaire en cluster sur la colonne (a) avec MAX_DURATION égal à 240 minutes.

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

Exemple 2

Opération ALTER TABLE pouvant être reprise pour l’ajout d’une contrainte d’unicité sur deux colonnes (a et b) avec MAX_DURATION égal à 240 minutes.

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

Exemple 3

Opération ALTER TABLE pour ajouter une clé primaire en cluster mise en suspens et reprise.

Le tableau ci-dessous montre deux sessions (Session #1et Session #2) exécutées chronologiquement en utilisant les instructions T-SQL suivantes. Session #1 exécute une opération ALTER TABLE ADD CONSTRAINT pouvant être reprise créant une clé primaire sur la colonne Col1. Session #2 vérifie l’état d’exécution de la contrainte en cours d’exécution. Après un certain temps, elle interrompt l’opération pouvant être reprise. Session #2 vérifie l’état d’exécution de la contrainte mise en suspens. Enfin, Session #1 reprend la contrainte mise en suspens et Session #2 vérifie à nouveau l’état.

Session 1 Session 2
Exécuter une contrainte d’ajout pouvant être reprise

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
Vérifier l’état de la contrainte

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Sortie montrant l’opération

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43,552
Mettre en suspens la contrainte pouvant être reprise

ALTER INDEX ALL ON TestConstraint PAUSE;
Error

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.
Vérifier l’état de la contrainte

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Sortie montrant l’opération

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)EN SUSPENS65,339
ALTER INDEX ALL ON TestConstraint RESUME;
Vérifier l’état de la contrainte

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Sortie montrant l’opération

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

Une fois l’opération terminée, exécutez l’instruction T-SQL suivante pour vérifier la contrainte :

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

Voici le jeu de résultats :

constraint_name table_name constraint_type
PK_Constraint TestConstraint PRIMARY KEY

Voir aussi