Adición reanudable de restricciones de tabla
Se aplica a: SQL Server 2022 (16.x) Azure SQL Database Azure 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 conRESUMABLE = ON
(requiereONLINE = 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ónWAIT_AT_LOW_PRIORITY
es equivalente aWAIT_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 reanudableALTER 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ónSELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Salida que muestra la operación
|
|||||||
Pausar la restricción reanudableALTER 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ónSELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Salida que muestra la operación
|
|||||||
ALTER INDEX ALL ON TestConstraint RESUME; |
|||||||
Comprobar el estado de la restricciónSELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations; |
|||||||
Salida que muestra la operación
|
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 |