Queries do Dia a Dia – Como Monitorar o Percentual de Execução na Criação ou Rebuild de um Índice?
Fala Pessoal,
Quantas vezes vocês já criaram um índice em produção e ficaram torcendo, apertando F5 toda hora, rezando, fazendo promessa para essa criação acabar mais rápido e resolver seu problema crítico?
Ficamos naquela dúvida, será que cancelo ou não? Será que já está acabando ou ainda vai demorar muito para criar esse índice?
Já fiz isso várias vezes para resolver problemas que não poderiam esperar à noite para criar um índice.
Outra situação do dia a dia:
Você precisa criar um índice em uma tabela gigante ou fazer um rebuild nessa tabela à noite ou no fim de semana. O comando passa de uma hora de execução e você não tem ideia se vai acabar em mais um minuto ou em mais 3 horas.
Seus problemas acabaram…
Esse ano atendi um cliente junto com o mestre Fabiano Amorim e na criação de um índice gigante ele usou uma query para saber o status em tempo real da criação desse índice.
Eu falei: Tá de brincadeira que existe um comando desse e eu não sabia disso ainda???
Pois é, existia…. E eu que acompanho um monte de sites de SQL, deixei passar essa dica por um bom tempo… Thanks Amorim!!!
Tudo isso é possível através da DMV dm_exec_query_profiles que funciona a partir da versão 2014 do SQL Server.
Antes de criar um índice habilite o STATISTICS PROFILE:
|
1 2 |
SET STATISTICS PROFILE ON create nonclustered index NOME_INDEX on NOME_TABELA(Colunas...) WITH(...) |
O mesmo vale para desfragmentar um índice:
|
1 2 |
SET STATISTICS PROFILE ON alter index NOME_INDEX on NOME_TABELA REBUILD |
Após iniciar o comando de criação ou desfragmentação do índice, em outra conexão, execute a query abaixo alterando o SPID no WHERE:
|
1 2 3 4 5 6 7 8 |
--Acompanhar a criação ou desfragmentação do índice SELECT node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count, CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count) percent_completed FROM sys.dm_exec_query_profiles WHERE session_id= (colocar o SPID da conexão que quer monitorar) GROUP BY node_id,physical_operator_name ORDER BY node_id; |
Segue exemplo do status da criação de um índice em uma tabela Heap:
Repare que essa tabela tem 431 milhões de linhas (coluna estimate_row_count), row_count mostra quantas linhas já foram processadas pelo operador (nesse caso 7,3 milhões de linhas) e a última coluna mostra o percentual de execução desse operador (nessa caso só tinha feito 1,7%).
Segue um exemplo da execução de um REBUILD em um índice clustered:
Repare que essa tabela tem 21 milhões de linhas (coluna estimate_row_count), row_count mostra quantas linhas já foram processadas pelo operador (nesse caso 225 mil linhas) e a última coluna mostra o percentual de execução desse operador (nessa caso só tinha feito 1,0 %).
Fica ai o aprendizado de um comando que já está diponível para nós há algum tempo.
Link do Script no GitHub: Script Post
Gostou da dica?
Curta, comente, compartilhe com os coleguinhas…
Assine meu canal no Youtube e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.
Abraços,
Fabrício Lima
Microsoft Data Platform MVP
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Fabrício, não tenho como agradecer por compartilhar tanto conhecimento! SENSACIONAL essa dica!
Muito obrigada!
TOP Mariana.
Obrigado!!!