Adición reanudable de restricciones de tabla

  • Artículo

Se aplica a: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

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

  • Recuperarse de errores de recompilación o creación de índice (por ejemplo, conmutaciones por error de base de datos o quedarse sin espacio en disco)

  • Truncar registros de transacciones durante la operación de recompilación o creación de índice

  • 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 de SQL Server 2022, SQL Database y SQL Managed Instance permiten una operación reanudable del comando de lenguaje de definición de datos (DDL)ALTER TABLE ADD CONSTRAINT y agregar una clave principal o única. Para obtener más información sobre cómo agregar una clave principal o única, vea ALTER TABLE table_constraint.

Nota:

Las restricciones de tabla de adición reanudable solo se aplican a las restricciones CLAVE PRINCIPAL y CLAVE ÚNICA. No se admiten restricciones de tabla de adición reanudables para las restricciones CLAVE EXTRANJERA.

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.

Nota:

La operación reanudable de ALTER TABLE ADD CONSTRAINT requiere ejecutar el comando ALTER en línea (WITH ONLINE = ON).

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 utilizada para habilitar operaciones reanudables en una restricción de tabla, consulte la sintaxis y las opciones en ALTER TABLE (Transact-SQL).

Comentarios sobre ALTER TABLE

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

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

  • 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 de ALTER TABLE ADD CONSTRAINT, use la sintaxis de T-SQL ALTER INDEX (Transact-SQL).

Para las restricciones reanudables, se usa el comando ALTER INDEX ALL existente.

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

Comentarios sobre ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Pause una operación de adición de restricción de tabla reanudable en línea que se está ejecutando.

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

  • Reanude una operación de adición de restricción de tabla que se haya pausado manualmente o debido a un error.

MAX_DURATION usado con RESUMABLE=ON

  • El tiempo (valor entero especificado en minutos) durante el cual se ejecuta la operación de adición de restricción de tabla en línea reanudable después de reanudarse. Cuando este tiempo expira, se pone en pausa la operación reanudable si todavía se está ejecutando.

WAIT_AT_LOW_PRIORITY usado con RESUMABLE=ON y ONLINE = ON

  • Para reanudar una operación de adición de restricción de tabla en línea tras una pausa es necesario esperar a las operaciones de bloqueo de esta tabla. WAIT_AT_LOW_PRIORITY indica que la operación de adición de restricción de tabla esperará a los bloqueos de prioridad baja, de forma que otras operaciones puedan continuar mientras la operación reanudable está 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

  • Anule una operación de adición de restricción de tabla que está ejecutándose o en pausa y que se había 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, sino que se deja en un estado de pausa indefinido.

Para obtener más información sobre las opciones PAUSE, RESUME y ABORT disponibles en 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

Debe tener un permiso de ALTER sobre la tabla.

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

Ejemplos

Estos son algunos ejemplos sobre cómo usar operaciones de adición de restricción de tabla reanudables.

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. Transcurrido un tiempo, pausa la operación reanudable. Session #2 comprueba el estado de la restricción en pausa. Por último, Session #1 reanuda la restricción en pausa y Session #2 vuelve a comprobar el estado.

Sesión 1 Sesión 2
Ejecutar la operación de adición de restricción reanudable

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
Comprobar el estado de la restricción

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Salida que muestra la operación

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43.552
Pausar la restricción reanudable

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.
Comprobar el estado de la restricción

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Salida que muestra la operación

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)PAUSED65.339
ALTER INDEX ALL ON TestConstraint RESUME;
Comprobar el estado de la restricción

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Salida que muestra la operación

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

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:

constraint_name table_name constraint_type
PK_Constraint TestConstraint PRIMARY KEY

Consulte también