How to check how much percentage index operation has been done

DECLARE @Processid INT = 70 ---here mention SPid (session id)

;WITH Summary 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'<Transition>')) AS [CurrentStep]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Index Scan',  N'Sort')
AND   qp.[session_id] = @Processid
), Result AS
(
     SELECT *,
            ([TotalRows] - [RowsProcessed]) AS [RowsLeft],
            ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
     FROM   Summary
)
SELECT [CurrentStep],
       [TotalRows],
       [RowsProcessed],
       [RowsLeft],
       GETDATE() AS [CurrentTime],
       CONVERT(DECIMAL(5, 2),
      (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete]
FROM   Result;

output

CurrentStep TotalRows RowsProcessed RowsLeft CurrentTime PercentComplete
    

Comments