Fala, galera!
Neste artigo, eu quero compartilhar com vocês um script bem simples, mas bem interessante e 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 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: “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 meu outro 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 com base nas estatísticas do banco, que para gerar um valor estimado próximo do real, devem estar 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:
USE [dirceuresende]
GO
-- Ativa o recurso de monitoramento nesta sessão
SET STATISTICS PROFILE ON
GO
-- Cria o índice normalmente
CREATE NONCLUSTERED INDEX SK01_Senhas ON dbo.Senhas(Senha) WITH(DATA_COMPRESSION=PAGE)
GO
-- Desativa o recurso de monitoramento nesta sessão
SET STATISTICS PROFILE OFF
GO
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:
DBCC 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:
;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
Resultado:
Bom, pessoal, espero que vocês tenham gostado dessa 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?