Hey guys!
In this article, I would like to share with you a very simple but very interesting script that helps you estimate the progress of creating an index through the sys.dm_exec_query_profiles DMV, available from SQL Server 2014 onwards.

If you want to estimate the progress and how long until your BACKUP, RESTORE, or DBCC command ends, take a look at my article How to estimate how long until the backup in SQL Server ends?.

Starting with SQL Server 2017, you can REBUILD indexes with the RESUMABLE=ON parameter, being able to pause and resume this operation and monitor the progress of this process using the DMV sys.index_resumable_operations. To learn more about this feature, read my article SQL Server 2017 – How to pause the rebuild of an index using the Resumable Online Index Rebuilds feature, remembering that as of SQL Server 2019, you can even create indexes using the RESUMABLE parameter, as I mentioned in the article SQL Server 2019 – List of new features and features.

How to estimate the progress of creating an index

Given the above messages, let's now find out how to estimate the progress of creating an index in SQL Server 2014 onwards, using the DMV sys.dm_exec_query_profiles and the row_count and estimate_row_count columns (base view of Live Query Statistics), which are generated based on database statistics, which, in order to generate an estimated value close to the real one, must always be as up to date as possible.

To be able to monitor the progress of index creation, you must use one of the two ways below:

  • Session level (recommended): To activate monitoring in a given session, simply use the SET STATISTICS PROFILE ON command and execute the CREATE INDEX command, looking like this: 
    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
  • Instance level: To enable monitoring on all sessions of the instance, you can activate traceflag 7412 by using the command below:
    DBCC TRACEON (7412, -1);  
GO

Now that we have activated monitoring in the session that is executing the CREATE INDEX command, let's start monitoring the progress of creating this index, which can take a long time depending on the size of the table. To facilitate your analysis, I will make the script available below, already consolidated with some very interesting additional information:

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

Well guys, I hope you liked this post, which is a really cool tip to use in your everyday life. Who has never needed to create an urgent index in production and was agonized over the delay in creating this index, without even having an idea of ​​how much time was left? lol

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