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

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?

Referências

• sys.dm_exec_query_profiles (Transact-SQL)
• CREATE INDEX Monitoring Progress
• Microsoft SQL Server – Utilizando a DMV – sys.dm_exec_query_profiles
• SQL Server: How to track progress of CREATE INDEX command?