Adición reanudable de restricciones a tablas

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores De Azure SQL DatabaseAzureSQL Managed InstanceSQL Database en Microsoft Fabric

La operación reanudable para la creación y recompilación de índices en línea ya se admite para SQL Server 2019, Azure SQL Database y Azure SQL Managed Instance. Las operaciones reanudables permiten ejecutar operaciones de índice mientras la tabla está en línea (ONLINE=ON), así como hacer lo siguiente:

• Pausar y reiniciar una operación de creación o recompilación de índice varias veces para adaptarse a una ventana de mantenimiento

• Recupérese tras errores en la creación o reconstrucción de índices, como conmutaciones por error en la base de datos o falta de espacio en disco.

• Habilitar el truncamiento de los registros de transacciones durante una operación de creación o recompilación de índices

• Cuando una operación de índice está en pausa, tanto el índice original como el recién creado requieren espacio en disco y deben actualizarse durante las operaciones de lenguaje de manipulación de datos (DML).

Las nuevas extensiones para SQL Server 2022, SQL Database y SQL Managed Instance permiten una operación reanudable para el comando ADD CONSTRAINT del lenguaje ALTER TABLE de definición de datos (DDL) y agregar una clave principal o única. Para obtener más información sobre cómo agregar una clave principal o única, consulte ALTER TABLE table_constraint.

Operaciones reanudables

En versiones anteriores de SQL Server, la operación ALTER TABLE ADD CONSTRAINT se puede ejecutar con la opción ONLINE=ON, pero esta operación puede tardar muchas horas en completarse si la tabla es grande, y consumir un gran número de recursos. También existe la posibilidad de que se produzcan errores o interrupciones mientras dicha operación se ejecuta. Hemos incluido capacidades reanudables en ALTER TABLE ADD CONSTRAINT para que los usuarios puedan detener la operación durante una ventana de mantenimiento o para reiniciarla desde donde se interrumpió durante un error de ejecución, sin que haya que reiniciarla desde el principio.

Escenarios admitidos

La nueva capacidad reanudable de ALTER TABLE ADD CONSTRAINT admite los siguientes escenarios de cliente:

• Pausar o reanudar una operación ALTER TABLE ADD CONSTRAINT en ejecución, como pausarla durante una ventana de mantenimiento, y reanudarla una vez completada esa ventana de mantenimiento.

• Reanudar una operación ALTER TABLE ADD CONSTRAINT después de conmutaciones por error y errores del sistema.

• Ejecutar una operación ALTER TABLE ADD CONSTRAINT en una tabla grande, pese al tamaño de registro escaso disponible.

Esta característica es especialmente útil con tablas de gran tamaño.

Sintaxis de T-SQL para ALTER TABLE

Para obtener información sobre la sintaxis usada para habilitar las operaciones reanudables en una restricción de tabla, vea la sintaxis y las opciones de ALTER TABLE (Transact-SQL).

Comentarios para ALTER TABLE

• Se ha agregado una nueva cláusula WITH <resumable_options a la sintaxis T-SQL actual en ALTER TABLE (Transact-SQL).

• La opción RESUMABLE es nueva y se ha agregado a la sintaxis existente ALTER TABLE (Transact-SQL).

• MAX_DURATION = time [MINUTES] usado con RESUMABLE = ON (requiere ONLINE = ON). MAX_DURATION indica el tiempo (valor entero especificado en minutos) durante el cual se ejecuta una operación de adición de restricción en línea reanudable antes de ponerse en pausa. Si no se especifica, la operación continúa hasta acabar.

Sintaxis de T-SQL para ALTER INDEX

Para pausar, reanudar o anular la operación de restricción de tabla reanudable para ALTER TABLE ADD CONSTRAINT, use la sintaxis ALTER INDEX T-SQL (Transact-SQL).

Para las restricciones reanudables, se usa el comando ALL existente 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 } )  
}  

Observaciones para ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

• Pausar una operación reanudable y en línea para agregar una restricción de tabla en ejecución.

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

• Reanudar una operación para agregar una restricción a una tabla que se haya pausado manualmente o debido a un fallo.

MAX_DURATION usado con RESUMABLE=ON

• El tiempo (un valor entero especificado en minutos) tras el cual se ejecuta la operación reanudable de agregar una restricción de tabla después de reanudarse. Una vez transcurrido el tiempo, la operación reanudable se pausa si aún sigue en ejecución.

WAIT_AT_LOW_PRIORITY usado con RESUMABLE=ON y ONLINE = ON

• Al reanudar una operación en línea para agregar una restricción de tabla tras una pausa, es necesario esperar a que finalicen las operaciones que bloquean esta tabla. WAIT_AT_LOW_PRIORITY indica que la operación para agregar una restricción a una tabla esperará a los bloqueos de baja prioridad, lo que permite que otras operaciones continúen mientras la operación resumible permanece en espera. La omisión de la opción WAIT_AT_LOW_PRIORITY es equivalente a WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Para más información, vea WAIT_AT_LOW_PRIORITY.

ALTER INDEX ALL ON <Table> ABORT

• Cancele una operación para agregar una restricción a una tabla que esté en ejecución o en pausa y que se haya declarado como reanudable. La operación de anulación se debe ejecutar explícitamente como un comando ABORT para finalizar una operación de restricción reanudable. Si una operación de restricción de tabla reanudable genera un error o se pausa, su ejecución no finaliza, Más bien, deja la operación en un estado de pausa indefinida.

Para obtener más información sobre PAUSElas opciones , RESUMEy ABORT disponibles para las operaciones reanudables, vea ALTER INDEX (Transact-SQL).

Visualización del estado de una operación reanudable

Para ver el estado de una operación de restricción de tabla reanudable, use la vista sys.index_resumable_operations.

Permisos

Se requiere permiso ALTER en la tabla.

Las operaciones ALTER TABLE ADD CONSTRAINT reanudables no requieren nuevos permisos.

Ejemplos

Aquí se muestran algunos ejemplos de operaciones reanudables para agregar restricciones de tabla.

Ejemplo 1

La operación ALTER TABLE reanudable para agregar una clave principal agrupada en la columna (a) con una duración MAX_DURATION de 240 minutos.

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

Ejemplo 2

Operación ALTER TABLE reanudable para agregar una restricción única en dos columnas (a y b) con una duración MAX_DURATION de 240 minutos.

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

Ejemplo 3

Operación ALTER TABLE para agregar una clave principal agrupada que está en pausa y reanudándose.

En la tabla siguiente se muestran dos sesiones (Session #1 y Session #2) que se ejecutan cronológicamente mediante las siguientes instrucciones T-SQL. Session #1 ejecuta una operación ALTER TABLE ADD CONSTRAINT reanudable que crea una clave principal en la columna Col1. Session #2 comprueba el estado de ejecución de la restricción que está ejecutándose. Después de un tiempo, pausa la operación reutilizable. Session #2 comprueba el estado de la restricción pausada. Por último, Session #1 reanuda la restricción en pausa y Session #2 vuelve a comprobar el estado.

Una vez completada la operación, ejecute la siguiente instrucción T-SQL para comprobar la restricción:

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

Este es el conjunto de resultados:

Consulte también

• Directrices para operaciones de índices en línea
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• sys.index_resumable_operations
• WAIT_AT_LOW_PRIORITY
• ALTER TABLE (Transact-SQL)
• ALTER TABLE index_option