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: 
    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 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