SQL Server 2014 – Como estimar o andamento e quanto tempo falta para a criação de um índice

Visualizações: 798
Tempo de Leitura: 3 minutos

Fala galera!
Nesse artigo, eu gostaria de compartilhar com vocês um script bem simples, mas bem interessante que ajuda a estimar o andamento da criação de um índice através da DMV sys.dm_exec_query_profiles, disponível a partir do SQL Server 2014.

Caso você queira estimar o andamento e quanto tempo falta para acabar o seu BACKUP, RESTORE, ou comando DBCC, dê uma olhada no meu artigo Como estimar quanto tempo falta para acabar o backup no SQL Server?.

A partir do SQL Server 2017, você pode fazer o REBUILD de índices com o parâmetro RESUMABLE=ON, podendo pausar e resumir essa operação e acompanhar o andamento desse processo utilizando a DMV sys.index_resumable_operations. Para saber mais sobre esse recurso, leia o meu artigo SQL Server 2017 – Como pausar o rebuild de um índice utilizando o recurso Resumable Online Index Rebuilds, lembrando que a partir do SQL Server 2019, você já pode até criar índices utilizando o parâmetro RESUMABLE, conforme comentei no artigo SQL Server 2019 – Lista de novidades e novos recursos.

Como estimar o andamento da criação de um índice

Dados os recados acima, vamos agora descobrir como estimar o andamento da criação de um índice no SQL Server 2014 em diante, utilizando a DMV sys.dm_exec_query_profiles e as colunas row_count e estimate_row_count (view base do Live Query Statistics), que são geradas baseado nas estatísticas do banco, que para gerar um valor estimado próximo do real, devem ser sempre o mais atualizadas possível.

Para que seja possível monitorar o andamento da criação do índice, você deve utilizar uma das duas formas abaixo:

  • Nível de sessão (recomendada): Para ativar o monitoramento em uma determinada sessão, basta utilizar o comando SET STATISTICS PROFILE ON e executar o comando de CREATE INDEX, ficando desta forma:
  • Nível de instância: Para ativar o monitoramento em todas as sessões da instância, você pode ativar a traceflag 7412 ao utilizar o comando abaixo:

Agora que ativamos o monitoramento na sessão que está executando o comando de CREATE INDEX, vamos começar a acompanhar como está o andamento da criação desse índice, que pode demorar bastante tempo dependendo do tamanho da tabela. Para facilitar a sua análise, vou disponibilizar o script abaixo, já consolidado com algumas informações adicionais bem interessantes:

Resultado:

Bom pessoal, espero que vocês tenham gostado desse post, que é uma dica bem legal para utilizar no dia a dia de vocês. Quem nunca precisou criar um índice urgente em produção e ficou agoniado com a demora da criação desse índice, sem ter nem uma ideia de quanto tempo faltava ? rs

Referências
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-profiles-transact-sql?view=sql-server-2017
https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/12/22/create-index-monitoring-progress/
https://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Utilizando-a-e2600ca6
https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command