Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server 2017 – Como pausar o rebuild de um índice utilizando o recurso Resumable Online Index Rebuilds

Post Views 2,278 views
Reading time 6 minutes

Fala galera!
Tudo tranquilo com vocês ?

Neste post, eu gostaria de demonstrar a vocês um recurso inovador do SQL Server 2017 (apenas nas edições Enterprise, Trial e Developer), que é o Resumable Online Index Rebuilds, que permite iniciar um processo de rebuild de índices e poder pausar essa operação no meio do processamento e depois, continuar de onde parou, quando for da sua vontade.

Essa nova feature é muito útil, especialmente quando se trabalha com ambientes críticos e que possuem uma janela de manutenção curta. Em muitos casos, não é possível realizar o rebuild de determinados índices nessa janela e o processo de manutenção acaba ficando muito complexo para conciliar as atividades pendentes e as janelas disponíveis.

Uma outra situação muito comum no dia a dia do DBA, é iniciar o rebuild de um índice, começar a ter contenção de disco e/ou CPU e ser forçado a ter que interromper o rebuild, perdendo todo o trabalho já realizado até então (e isso é muito frustrante).

Diante dessas situações, o Resumable Online Index Rebuilds acaba sendo uma solução muito importante na vida do DBA SQL Server, uma vez que isso pode melhorar drasticamente as rotinas de rebuild de índices, que possuem uma grande importância para uma boa manutenção do banco de dados.

Vale observar que, devido a essa nova feature do SQL Server 2017, a DMV sys.dm_exec_requests sofreu uma alteração para incluir a coluna is_resumable, indicando se a requisição em questão pode ser resumida ou não, utilizando o recurso Resumable Online Index Rebuilds.

Result:

Criando a massa de dados

Para iniciar nossos testes, vamos criar uma pequena massa de dados.

Realizando o rebuild dos índices

Agora que já criamos a nossa tabela de testes, vamos realizar o rebuild do índice de forma a permitir o comando de pause, que é adicionando os parâmetros ONLINE=ON, RESUMABLE=ON:

Onde os parâmetros do comando ALTER INDEX são:

  • ONLINE: Define se o rebuild será feito de forma online (por páginas) ou não. Observe que o Resumable index rebuild apenas suporta o rebuild online, então devemos sempre utilizar o parâmetro ONLINE=ON para utilizar este recurso.
  • RESUMABLE: Permite definir se o rebuild será feito suportando a opção de Pause/Resume ou não.
  • MAX_DURATION: Parâmetro muito interessante, que permite definir em minutos, a quantidade de tempo que o rebuild irá executar antes de ser suspenso automaticamente. Esse valor deve ser maior que 0 e menor ou igual a 10080 (1 semana).
  • PAUSE: Utilizando esse parâmetro, a operação de rebuild será pausada e ficará aguardando um novo ALTER INDEX para continuar o processo ou o comando ABORT, para interromper o rebuild.
  • ABORT: Parâmetro utilizado para interromper o rebuild do índice.

Caso você tente realizar o rebuild do índice com o parâmetro RESUMABLE=ON e ONLINE=OFF, irá se deparar com essa mensagem de erro:

Msg 11438, Level 15, State 1, Line 2
The RESUMABLE option cannot be set to ‘ON’ when the ONLINE option is set to ‘OFF’

Caso o tempo limite definido no comando de rebuild seja atingido (no exemplo, especifiquei 1 min), o rebuild será imediatamente interrompido, retornando a mensagem de erro abaixo:

Msg 3643, Level 16, State 1, Line 20
The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped.
Msg 596, Level 21, State 1, Line 19
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 19
A severe error occurred on the current command. The results, if any, should be discarded.

Agora, vou iniciar o rebuild do índice e interromper rapidamente no meio do processo:

E utilizando a view sys.index_resumable_operations, podemos identificar como está o andamento dessa operação, que não foi concluída e acabou ficando pendente:

Result:

Como resumir o rebuild de índices RESUMABLE

Para continuar o rebuild do índice, basta utilizar o comando:

Ao utilizar o parâmetro RESUME, a sessão que está fazendo o rebuild irá receber essa mensagem de aviso:
“Warning: An existing resumable operation with the same options was identified for the same index on ‘Teste’. The existing operation will be resumed instead.”

O comando de RESUME pode ser utilizado após uma falha anterior do rebuild, como falta de espaço ou se a instância reiniciar. Ele também é especialmente útil para rotinas de rebuild que demoram horas para serem concluídas e acabam consumindo muito espaço de log, pois a rotina de rebuild pode ser pausada, permitindo que o backup de log diminua a utilização do arquivo de log.

Você também pode utilizar o comando RESUME para resumir o rebuild do índice e ao mesmo tempo, alterar os parâmetros utilizados inicialmente, como MAXDOP:

Ainda é possível utilizar o parâmetro WAIT_AT_LOW_PRIORITY para tratar locks e blocks que podem ocorrer durante o processo de rebuild, conforme exemplo abaixo:

Onde, no exemplo acima, o parâmetro WAIT_AT_LOW_PRIORITY fará com que o rebuild aguarde por no máximo 10 minutos, até que o problema de block/lock seja resolvido. Após esse tempo, as sessões que estão bloqueando o rebuild serão eliminadas, graças ao parâmetro ABORT_AFTER_WAIT.

As opções disponíveis para o parâmetro ABORT_AFTER_WAIT são:
NONE: Continua aguardando o lock com prioridade normal
SELF: Interrompe a execução atual do rebuild do índice
BLOCKERS: Interrompe as sessões que estão impedindo o rebuild do índice de continuar. Essa opção requer que o usuário que esteja executando tenha a permissão ALTER ANY CONNECTION.

Como pausar o rebuild de índices RESUMABLE

Para pausar um rebuild em andamento, utilize o comando abaixo:

Uma vez que você pause o rebuild de um índice, a sessão que estava executando a operação de rebuild irá receber essa mensagem de erro:

Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 596, Level 21, State 1, Line 16
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 16
A severe error occurred on the current command. The results, if any, should be discarded.

Tenha em mente que, ao pausar um rebuild, ele ficará com o status de PAUSED:

Nesse estágio, não é possível apagar o índice que estava sendo atualizado. O mesmo deverá ser resumido ou abortado para permitir que ele seja excluído. Caso contrário, irá se deparar com essa mensagem de erro:

Msg 10637, Level 16, State 1, Line 14
Cannot perform this operation on ‘object’ with ID 703341570 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Como interromper o rebuild de índices RESUMABLE

Para interromper o rebuild que está em estado de PAUSE, perdendo todo o progresso já realizado, você deve utilizar o comando:

Uma vez que você interrompa o rebuild de um índice, a sessão que estava executando a operação de rebuild irá receber a mesma mensagem de erro de quando o rebuild é pausado:

Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 1219, Level 16, State 1, Line 17
Your session has been disconnected because of a high priority DDL operation.
Msg 596, Level 21, State 1, Line 16
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 16
A severe error occurred on the current command. The results, if any, should be discarded.

Limitações do rebuild de índices RESUMABLE

Logo abaixo, vou listar as limitações do rebuild de índices RESUMABLE:

  • Essa feature é apenas suportada para índices no formato rowstore
  • Não funciona com o parâmetro SORT_IN_TEMPDB do ALTER INDEX
  • Não funciona com colunas do tipo TIMESTAMP
  • Não funciona com colunas calculadas (computadas)
  • Não é possível utilizar esse recurso em índices desativados
  • Essa feature não pode ser utilizada dentro de uma transação de usuário

É isso aí, galera!
Um abraço e até o próximo post.