SQL Server: 2020

Wednesday, August 12, 2020

Get index progress in SQL 2004 and above

Copy from answer from Solomon Rutzky on topic SQL Server : HOw To trace progress of Create index command. This query is very useful to trace the progress. Pam Lahoud also mention in SQL 2019, that is new dmv called sys.index_resumable_operations which trace resume index operation.

I copy script to here for reference and reference URL as below
https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command

DECLARE @SPID INT = 51;

;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'<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] = @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;

SQL Server

Pages

Monday, November 09, 2020

Window Cluster resource

Wednesday, August 12, 2020

Get index progress in SQL 2004 and above

Friday, January 03, 2020

SQL 2019 what new

Database Reliability Engineering

Popular Posts