[bing_translator]
Recentemente eu precisei realizar uma movimentação de dados fisicamente, ou seja, retirar dados do arquivo1 e passar esses dados para o arquivo2.
A estratégia adotada foi a recriação do índice clustered em um novo filegroup;
CREATE CLUSTERED INDEX [IX_01] ON [dbo].[TEST]
(
[ID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [NovoFileGroup](ID)
GO
Esse procedimento é bem simples e já tinha executado muitas vezes. Porem dessa vez tinha algumas variáveis que levei em consideração no meu ambiente de teste:
Primeira: quantidade de registros envolvidos nessa tabela, aproximadamente 2 bilhões.
Segunda: Essa mesma tabela possuía 3 índices non-clustered no qual no ambiente de teste eu pude apagar antes de recriar o meu índice non-clustered.
Realizei todos os meus testes e obtive um tempo de 5 horas para execução do comando. A minha janela de manutenção era de 8 horas. A criação dos outros dois índices non-clustered levaram em torno de 3h e meia conversei com a equipe de negócio e eles não viram problema em invadir por um pouco tempo, ou seja, eu estava tudo certo para executar a minha manutenção.
Antes de iniciar a manutenção em produção, eu decidi não apagar os índices non-clustered (meu erro fatal).
Iniciei a manutenção sem nenhuma preocupação e fiquei acompanhando através de algumas DMV (sys.dm_exec_requests, sys.dm_os_tasks, sys.dm_os_waiting_tasks entre outras) a execução do comando, porém não sei se existe, eu desconheço, uma forma de saber exatamente quanto tempo falta para o comando de CREATE INDEX terminar. No meu acompanhamento eu podia ver o que estava em execução porem muito lento!
Após 10 horas a criação do índice não havia terminado e a operação do dia-a-dia já havia iniciado e nesse momento a preocupação era em não impactar o negócio com possíveis bloqueios que poderiam gerar lentidão no sistema.
Nesse momento os ânimos estavam exaltados na área de negócio e na minha gerencia uma vez que a manutenção não terminou, porem eu estava “super” tranquilo porque não haveria impacto simplesmente pelo fato de eu ter tomado o cuidado e ter utilizado a clausura ONLINE = ON.
Como estávamos usando a versão Enterprise do SQL Server 2012 essa opção é uma das principais funcionalidades que tenho utilizado quando faço manutenção em índices.
A partir desse momento a área de negócio percebeu que eles não estavam sendo afetados pela minha manutenção e que o sistema estava sendo operado normalmente (Essa criação de índice era uma das principais tabelas do sistema)
Com os ânimos mais calmos agora vinham as perguntas: Quando irá terminar? Quanto tempo falta? O que realmente está fazendo? Está travado? Posso realizar rollback? Quanto tempo para o rollback terminar? E muitas outras perguntas referentes ao processo.
Para me auxiliar em relação a algumas dessas perguntas abri um caso na Microsoft e algumas das respostas eu já tinha:
P: Quando irá terminar?
R: Não tem como estimar
P: O que realmente está fazendo?
R: Está em execução realizando a criação do índice lentamente.
P: Posso realizar rollback? Quanto tempo para o rollback terminar?
R: Pode, como está sendo realizado a criação com a clausura ONLINE = ON e DROP_EXISTING = ON o rollback é para ser rápido, mas não é possível precisar o tempo
Basead0s nessas respostas e que não havia impacto para o negócio decidimos deixar a execução rodando e monitorar o que estava fazendo. Mas agora a pergunta era como monitorar!!??
Resolvi tentar achar algum evento via XE (extended events) e existe um REPORT com informações que realmente são valiosas para monitorar o processo.
O que a opção ONLINE = ON faz exatamente? Aqui você entra nos detalhes e sabe o que está acontecendo.
https://msdn.microsoft.com/en-us/library/ms191261(v=sql.110).aspx
Através desse evento podemos monitorar todas as fases que o índice passa:
- Fase 1: Preparation
- Fase 2: Build
- Fase 3: Final
Após saber como monitorar passamos a tentar estimar um tempo de finalização, o qual demorou 38 horas. Esse tempo todo é completamente justificado pelo comportamento da opção ONLINE.
Usar XE é sempre uma boa opção…