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


;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,
                    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],
       CONVERT(DECIMAL(5, 2),
               (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
       (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
               (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
               GETDATE()) AS [EstimatedCompletionTime]
FROM   comp;

Output Format:

No comments:
Write comments

Please do not enter spam links


More Services