SQL Server 2017 - Como pausar el rebuild de un indice usando Resumable Online Index Rebuilds

¡Hola, chicos!
¿Está todo bien contigo?

En esta publicación, me gustaría demostrarles una característica innovadora de SQL Server 2017 (solo ediciones Enterprise, Trial y Developer), que son las reconstrucciones de índices en línea reanudables, que le permite iniciar un proceso de reconstrucción de índice y poder pausar esta operación en medio del procesamiento y luego continuar donde lo dejó, cuando lo desee.

Esta nueva característica es muy útil, especialmente cuando se trabaja con entornos críticos que tienen una ventana de mantenimiento corta. En muchos casos, no es posible reconstruir ciertos índices en esta ventana y el proceso de mantenimiento termina volviéndose muy complejo para conciliar actividades pendientes y ventanas disponibles.

Otra situación muy común en el día a día de un DBA es comenzar a reconstruir un índice, comenzar a experimentar contención de disco y/o CPU y verse obligado a interrumpir la reconstrucción, perdiendo todo el trabajo ya realizado hasta ahora (y esto es muy frustrante).

Ante estas situaciones, Resumable Online Index Rebuilds termina siendo una solución muy importante en la vida del DBA de SQL Server, ya que puede mejorar drásticamente las rutinas de reconstrucción de índices, que son de gran importancia para un buen mantenimiento de la base de datos.

Vale la pena señalar que, debido a esta nueva característica en SQL Server 2017, el DMV sys.dm_exec_requests se ha cambiado para incluir la columna is_resumable, que indica si la solicitud en cuestión se puede resumir o no, utilizando la función Reconstrucciones de índices en línea reanudables.

SELECT
    session_id,
    start_time,
    [status],
    wait_type,
    wait_time,
    is_resumable
FROM
    sys.dm_exec_requests
WHERE
    session_id > 50

Resultado:

Creando la masa de datos

Para comenzar nuestras pruebas, creemos una pequeña masa de datos.

IF (OBJECT_ID('dbo.Teste') IS NOT NULL) DROP TABLE dbo.Teste
CREATE TABLE dbo.Teste (
    Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Nome VARCHAR(50) NOT NULL
) WITH(DATA_COMPRESSION=PAGE)
GO

INSERT INTO dbo.Teste (Nome)
SELECT NEWID()
GO 50000

CREATE NONCLUSTERED INDEX SK01_Teste ON dbo.Teste(Nome) WITH(DATA_COMPRESSION=PAGE)

Realizando reconstrucción de índice

Ahora que hemos creado nuestra tabla de prueba, reconstruiremos el índice para permitir el comando de pausa, que consiste en agregar los parámetros ONLINE=ON, RESUMABLE=ON:

ALTER INDEX SK01_Teste ON dbo.Teste 
REBUILD WITH(ONLINE=ON, MAXDOP=4, MAX_DURATION=1, RESUMABLE=ON)

Donde los parámetros del comando ALTER INDEX son:

• EN LÍNEA: Define si la reconstrucción se realizará en línea (por páginas) o no. Tenga en cuenta que la reconstrucción del índice reanudable solo admite la reconstrucción en línea, por lo que siempre debemos usar el parámetro ONLINE=ON para utilizar esta función.
• REANUDABLE: Le permite definir si la reconstrucción se realizará admitiendo la opción Pausa/Reanudar o no.
• MAX_DURACIÓN: Parámetro muy interesante, que permite definir, en minutos, la cantidad de tiempo que se ejecutará la reconstrucción antes de suspenderse automáticamente. Este valor debe ser mayor que 0 y menor o igual a 10080 (1 semana).
• PAUSA: Usando este parámetro, la operación de reconstrucción se pausará y esperará a que un nuevo ALTER INDEX continúe el proceso o el comando ABORT para interrumpir la reconstrucción.
• ABORTO PROVOCADO: Parámetro utilizado para detener la reconstrucción del índice.

Si intenta reconstruir el índice con el parámetro RESUMABLE=ON y ONLINE=OFF, encontrará este mensaje de error:

Si se alcanza el límite de tiempo definido en el comando de reconstrucción (en el ejemplo, especifiqué 1 minuto), la reconstrucción se interrumpirá inmediatamente y se devolverá el siguiente mensaje de error:

Ahora, comenzaré la reconstrucción del índice y lo detendré rápidamente en medio del proceso:

Y usando la vista sys.index_resumable_operaciones, podemos identificar cómo avanza esta operación, la cual no se completó y terminó quedando pendiente:

SELECT * 
FROM sys.index_resumable_operations

Resultado:

Cómo resumir la reconstrucción del índice RESUMABLE

Para continuar reconstruyendo el índice, simplemente use el comando:

ALTER INDEX SK01_Teste ON dbo.Teste RESUME

Al utilizar el parámetro RESUME, la sesión que está realizando la reconstrucción recibirá este mensaje de advertencia:
"Advertencia: Se identificó una operación reanudable existente con las mismas opciones para el mismo índice en 'Teste'. En su lugar, se resumirá la operación existente".

El comando RESUME se puede utilizar después de un error de reconstrucción anterior, como quedarse sin espacio o si la instancia se reinicia. También es especialmente útil para rutinas de reconstrucción que tardan horas en completarse y terminan consumiendo mucho espacio de registro, ya que la rutina de reconstrucción se puede pausar, lo que permite que la copia de seguridad del registro reduzca el uso del archivo de registro.

También puede usar el comando RESUME para resumir la reconstrucción del índice y, al mismo tiempo, cambiar los parámetros utilizados inicialmente, como MAXDOP:

ALTER INDEX SK01_Teste ON dbo.Teste RESUME WITH(MAXDOP=6, MAX_DURATION=2)

Todavía es posible utilizar el parámetro WAIT_AT_LOW_PRIORITY para manejar bloqueos y bloqueos que pueden ocurrir durante el proceso de reconstrucción, como se muestra en el siguiente ejemplo:

ALTER INDEX SK01_Teste ON dbo.Teste RESUME WITH(MAXDOP=4, MAX_DURATION=1, WAIT_AT_LOW_PRIORITY(MAX_DURATION=10, ABORT_AFTER_WAIT=BLOCKERS))

Donde, en el ejemplo anterior, el parámetro WAIT_AT_LOW_PRIORITY hará que la reconstrucción espere un máximo de 10 minutos, hasta que se resuelva el problema de bloqueo/bloqueo. Pasado este tiempo, las sesiones que estén bloqueando la reconstrucción serán eliminadas, gracias al parámetro ABORT_AFTER_WAIT.

Las opciones disponibles para el parámetro ABORT_AFTER_WAIT son:
– NINGUNO: Continúa esperando el bloqueo con prioridad normal.
– SER: Detiene la ejecución actual de la reconstrucción del índice.
– BLOQUEADORES: Detiene las sesiones que impiden que continúe la reconstrucción del índice. Esta opción requiere que el usuario que la ejecuta tenga permiso ALTERAR CUALQUIER CONEXIÓN.

Cómo pausar la reconstrucción de índices RESUMABLES

Para pausar una reconstrucción en progreso, use el siguiente comando:

ALTER INDEX SK01_Teste ON dbo.Teste PAUSE

Una vez que pausa la reconstrucción de un índice, la sesión que estaba realizando la operación de reconstrucción recibirá este mensaje de error:

Tenga en cuenta que cuando pausa una reconstrucción, ésta tendrá el estado PAUSADA:

En esta etapa, no es posible eliminar el índice que se estaba actualizando. Debe resumirse o abortarse para permitir su eliminación. De lo contrario, encontrará este mensaje de error:

Cómo dejar de reconstruir índices RESUMABLES

Para interrumpir la reconstrucción que se encuentra en estado PAUSA, perdiendo todo el progreso ya realizado, debes usar el comando:

ALTER INDEX SK01_Teste ON dbo.Teste ABORT

Una vez que detenga la reconstrucción de un índice, la sesión que estaba realizando la operación de reconstrucción recibirá el mismo mensaje de error que cuando se pausa la reconstrucción:

Limitaciones de la reconstrucción de índices RESUMABLES

A continuación, enumeraré las limitaciones de reconstruir índices RESUMABLES:

• Esta característica solo es compatible con índices en formato de almacén de filas.
• No funciona con el parámetro ALTER INDEX SORT_IN_TEMPDB
• No funciona con columnas TIMESTAMP
• No funciona con columnas calculadas (calculadas)
• Esta característica no se puede utilizar en índices deshabilitados.
• Esta característica no se puede utilizar dentro de una transacción de usuario.

¡Eso es todo, muchachos!
Un abrazo y nos vemos en el próximo post.