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…

This is Tiago Balabuch's website, and this is a bit of copy about him. He is enthusiast, fascinated, passionate, fanatic by SQL Server and in the off-hours a Data Engineer who loves what he does and he is traveling in the cloud and surfing on the wave of the moment called Azure! Originally from Brazil and with encouragement from family and friends, Tiago moved to Europe in 2017 where lives in Ireland. In addition to being a data engineer, he is also active speaker in the SQL PASS events and keeps up to date on the key technologies and technical certifications. Tiago hold these certification MCT, MCSE - Data Management and Analystics, MCSA - SQL Server 2016/2014/2012. Simply psychedelic and manic he is just one more freak who likes SQL Server and its new features ...

Responda