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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT session_id, start_time, [status], wait_type, wait_time, is_resumable FROM sys.dm_exec_requests WHERE session_id > 50 |
Result:
Criando a massa de dados
Para iniciar nossos testes, vamos criar uma pequena massa de dados.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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, Name VARCHAR(50) NOT NULL ) WITH(DATA_COMPRESSION=PAGE) GO INSERT INTO dbo.Teste (Name) SELECT NEWID() GO 50000 CREATE NONCLUSTERED INDEX SK01_Teste ON dbo.Teste(Name) WITH(DATA_COMPRESSION=PAGE) |
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:
|
1 2 |
ALTER INDEX SK01_Teste ON dbo.Teste REBUILD WITH(ONLINE=ON, MAXDOP=4, MAX_DURATION=1, 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:
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:
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:
|
1 2 |
SELECT * FROM sys.index_resumable_operations |
Result:
Como resumir o rebuild de índices RESUMABLE
Para continuar o rebuild do índice, basta utilizar o comando:
|
1 |
ALTER INDEX SK01_Teste ON dbo.Teste RESUME |
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:
|
1 |
ALTER INDEX SK01_Teste ON dbo.Teste RESUME WITH(MAXDOP=6, MAX_DURATION=2) |
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:
|
1 |
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)) |
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:
|
1 |
ALTER INDEX SK01_Teste ON dbo.Teste PAUSE |
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:
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:
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:
|
1 |
ALTER INDEX SK01_Teste ON dbo.Teste ABORT |
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:
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.
Portuguese 
English