How many rows are progressed while doing operations like below from SSMS?

 

How many rows are progressed while doing operations like below from SSMS?

I would like to share one more very useful technique believe me this is very much needed when you want to estimate how many rows are progressed while doing operations like below

1) insert into select * from 

2) select * into 

3) NonClustered Indexes on a Heap

4) Clustered Index (no NonClustered Indexes exist)

5) NonClustered Indexes on the Clustered Index/Table

6) Clustered Index when NonClustered Indexes already exist

7) Unique NonClustered Indexes on the Clustered Index/Table

8) ALTER TABLE [schema_name].[table_name] REBUILD; (only Clustered Index shows up when using this method)

9) ALTER INDEX ALL ON [schema_name].[table_name] REBUILD;

10)ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD;

What you need to do is just enable set statistics profile on in the window where you want to execute above commands

Now to track the progress you can use the below query(credits to Solomon Rutzky) and the output would be like 

DECLARE @SPID INT = 127;

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

     FROM sys.dm_exec_query_profiles qp

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

     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;

I hope you will start utilizing above query from now on.