SQL Server: How to track progress of CREATE INDEX command?

Thanks to “Solomon Rutzky”, for sharing the script online. I have modified it slightly to work in my scenarios as well.

This will help you to find the estimate each operator index creation will take

You have to just mention the SPID which is running rebuild index

/**************************************/

DECLARE @SPID INT = 56;

;WITH agg AS

(

     SELECT SUM(qp.[row_count]) AS [RowsProcessed],

            SUM(qp.[estimate_row_count]) AS [TotalRows],

            MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],

            MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,

                    [physical_operator_name],

                    N'')) AS [CurrentStep]

     FROMsys.dm_exec_query_profiles qp

     WHEREqp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort', 'Index Insert')

     AND   qp.[session_id] = @SPID

), comp AS

(

     SELECT *,

            ([TotalRows] - [RowsProcessed]) AS [RowsLeft],

            ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]

     FROM   agg

)

SELECT [CurrentStep],

       [TotalRows],

       [RowsProcessed],

       [RowsLeft],

       CONVERT(DECIMAL(5, 2),

               (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],

       [ElapsedSeconds],

       (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],

       DATEADD(SECOND,

               (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),

               GETDATE()) AS [EstimatedCompletionTime]

FROM   comp;

/**************************************/

Output Format: