Monitorar Index Online com extended events

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…

Maníaco, entusiasta, fascinado, fanático por SQL Server e nas horas vagas um DBA que adora o que faz! Também possui certificações como: MCT, MCSE - Data Management and Analystics, MCSE - Data Plataform e MCSA - SQL Server 2016/2014/2012

Responda