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:
1234567891011121314USE [dirceuresende]GO-- Ativa o recurso de monitoramento nesta sessãoSET STATISTICS PROFILE ONGO-- Cria o índice normalmenteCREATE NONCLUSTERED INDEX SK01_Senhas ON dbo.Senhas(Senha) WITH(DATA_COMPRESSION=PAGE)GO-- Desativa o recurso de monitoramento nesta sessãoSET STATISTICS PROFILE OFFGO
- 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:
12DBCC TRACEON (7412, -1);GO
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
;WITH dadosSessao AS ( SELECT B.session_id, B.login_time, B.[host_name], B.[program_name], B.nt_user_name, B.original_login_name, D.[text], MIN(C.start_time) AS start_time, SUM(A.[row_count]) AS Qt_Linhas_Processadas, SUM(A.[estimate_row_count]) AS [Qt_Linhas_Total], MAX(A.last_active_time) - MIN(A.first_active_time) AS [Qt_Tempo_Decorrido_MS], MAX(IIF(A.[close_time] = 0 AND A.[first_row_time] > 0, [physical_operator_name], N'<Transition>')) AS [Ds_Operador_Atual] FROM sys.dm_exec_query_profiles A JOIN sys.dm_exec_sessions B ON B.session_id = A.session_id JOIN sys.dm_exec_requests AS C WITH (NOLOCK) ON B.session_id = C.session_id CROSS APPLY sys.dm_exec_sql_text(C.[sql_handle]) D WHERE C.command = 'CREATE INDEX' GROUP BY B.session_id, B.login_time, B.[host_name], B.[program_name], B.nt_user_name, B.original_login_name, D.[text] ), contabilizacao AS ( SELECT *, ( [Qt_Linhas_Total] - Qt_Linhas_Processadas ) AS [Qt_Linhas_Restantes], ( [Qt_Tempo_Decorrido_MS] / 1000.0 ) AS [Qt_Segundos_Decorridos] FROM dadosSessao ) SELECT session_id, start_time, [text], [Ds_Operador_Atual], [Qt_Linhas_Total], Qt_Linhas_Processadas, [Qt_Linhas_Restantes], CONVERT(DECIMAL(5, 2), (( Qt_Linhas_Processadas * 1.0 ) / [Qt_Linhas_Total] ) * 100) AS [Vl_Percentual_Completado], [Qt_Segundos_Decorridos], (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ) AS [Qt_Segundos_Restantes], DATEADD(SECOND, (( [Qt_Segundos_Decorridos] / Qt_Linhas_Processadas ) * [Qt_Linhas_Restantes] ), GETDATE()) AS [Dt_Prevista], [host_name], [program_name], nt_user_name, original_login_name FROM contabilizacao |
Result:
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
References
– 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
Portuguese 
English
Comecei a usar aqui na empresa, pensa num sofrimento antes de conhecer essa rotina
Kkkkkkkkkk fico feliz em saber que você gostou e que está sendo útil no seu dia a dia
Legal! 1-Dirceu tem como estimar com query normal tmb? Se sim, como? 2-Outra dúvida, o que você sugere para monitorar a execução de jobs que não seja o SQL agent. Um que seja mais detalhado. Algum programa externo. Pergunto isso Pq meu ambiente tem muitos jobs. 3-Tem outros blogs no estilo do seu nacional ou internacional que vc recomenda tmb? Obrigado!