[Video] What Percent Complete Is That Index Build?

SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but…only if the data isn’t changing. But of course your data is changing – that’s the whole point of doing these operations as resumable. If they weren’t changing, we could just let the operations finish.

Let’s see how it works to understand why the numbers aren’t really reliable:

Well, that’s … disappointingly inaccurate.

Here are the demo scripts if you want to give it a shot on your own systems:

Previous Post
Updated First Responder Kit and Consultant Toolkit for March 2020

2 Comments. Leave new

  • This works fairly well

    https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
    — add this line to index or insert tsql statement before running it
    — SET STATISTICS PROFILE ON;

    DECLARE @SPID INT = 76 ;

    ;WITH agg AS
    (
    SELECT SUM(qp.[row_count]) AS [RowsProcessed],
    –SUM(qp.[estimate_row_count])
    10554351100 AS [TotalRows], — change the value here to number of rows from the properties of the AHLDB temp table
    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 Scan’) — most common operation
    –qp.[physical_operator_name] IN (N’Table Scan’, N’Clustered Index Scan’, N’Sort’, ‘Index Scan’,N’Clustered Index Insert’,N’Online Index Insert’,N’Compute Scalar’) — all operations
    –qp.[physical_operator_name] IN ( N’Clustered Index Insert’) — single operation
    –qp.[physical_operator_name] IN ( N’Online Index Insert’) — single operation
    –qp.[physical_operator_name] IN ( N’Index Insert’) — single operation
    –qp.[physical_operator_name] IN ( N’Sort’) — single operation
    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;

    — run this query below to find out what operations are running and adjust where clause above — if you get negative number then it has already gone past one operation to another –
    — if you get a an error with zero divide in the above query, the operation in where clause is not running, choose another line or run the command below to find active operation

    — select * from sys.dm_exec_query_profiles

    Reply
    • Brian – that doesn’t work at all in the scenario described in the post, where I’m checking on a *paused* index operation.

      Thanks for stopping by, though.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}