¡Hola, chicos!
En este artículo, me gustaría compartir contigo un script muy simple pero muy interesante que te ayuda a estimar el progreso de la creación de un índice a través del DMV sys.dm_exec_query_profiles, disponible desde SQL Server 2014 en adelante.

Si desea estimar el progreso y cuánto tiempo falta para que finalice su comando BACKUP, RESTORE o DBCC, consulte mi artículo. ¿Cómo estimar cuánto falta para que finalice la copia de seguridad en SQL Server?.

A partir de SQL Server 2017, puede RECONSTRUIR índices con el parámetro RESUMABLE=ON, pudiendo pausar y reanudar esta operación y monitorear el progreso de este proceso usando el DMV sys.index_resumable_operaciones. Para obtener más información sobre esta función, lea mi artículo. SQL Server 2017: cómo pausar la reconstrucción de un índice mediante la función Reconstrucciones de índices en línea reanudables, recordando que a partir de SQL Server 2019, incluso puedes crear índices usando el parámetro RESUMABLE, como mencioné en el artículo SQL Server 2019: lista de nuevas funciones y características.

Cómo estimar el progreso de la creación de un índice

Teniendo en cuenta los mensajes anteriores, descubramos ahora cómo estimar el progreso de la creación de un índice en SQL Server 2014 en adelante, utilizando las columnas DMV sys.dm_exec_query_profiles y row_count y estima_row_count (vista base de Live Query Statistics), que se generan en base a las estadísticas de la base de datos, que, para generar un valor estimado cercano al real, debe estar siempre lo más actualizado posible.

Para poder monitorear el progreso de la creación del índice, debe utilizar una de las dos formas siguientes:

  • Nivel de sesión (recomendado): Para activar el monitoreo en una sesión determinada, simplemente use el comando SET STATISTICS PROFILE ON y ejecute el comando CREATE INDEX, con este aspecto: 
    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
  • Nivel de instancia: Para habilitar el monitoreo en todas las sesiones de la instancia, puede activar traceflag 7412 usando el siguiente comando:
    DBCC TRACEON (7412, -1);  
GO

Ahora que hemos activado la monitorización en la sesión que está ejecutando el comando CREATE INDEX, comencemos a monitorizar el progreso de la creación de este índice, que puede tardar mucho tiempo dependiendo del tamaño de la tabla. Para facilitar su análisis, pondré a su disposición el script a continuación, ya consolidado con información adicional muy interesante:

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

Bueno chicos, espero que les haya gustado esta publicación, que es un consejo realmente interesante para usar en su vida diaria. ¿Quién nunca ha necesitado crear un índice urgente en producción y se ha sentido agonizado por el retraso en la creación de este índice, sin siquiera tener idea de cuánto tiempo le quedaba? jajaja

Referencias
– 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