[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
Next Post
Announcing Quest Database Training Days: Free Live Training with Me, Janis, and Pinal

6 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
  • This script worked for me for quite some time now – but only for ONLINE rebuilds, don’t know whether it works for paused ones as well, build it back in 2010:

    ;WITH PartitionDups AS (
    SELECT
    [object_id]
    , [index_id]
    , [partition_number]
    FROM sys.partitions
    WITH (NOLOCK)
    GROUP BY
    [object_id]
    , [index_id]
    , [partition_number]
    HAVING count(1) > 1
    )
    SELECT DISTINCT
    DB_ID()
    , I.[object_id]
    , I.[index_id]
    , CASE WHEN I.[index_id] = 0 THEN ”{HEAP}”
    ELSE QUOTENAME( I.[name] )
    END
    , P1.[partition_id]
    , P2.[partition_id]
    , CONVERT( DECIMAL(18,15), ( P2.[rows] *100.0 ) /NULLIF( P1.[rows], 0 ) )
    FROM PartitionDups PD
    INNER JOIN sys.partitions P1
    ON P1.[object_id] = PD.[object_id]
    AND P1.[index_id] = PD.[index_id]
    AND P1.[partition_number] = PD.[partition_number]
    INNER JOIN sys.partitions P2
    WITH (NOLOCK)
    ON P2.[object_id] = PD.[object_id]
    AND P2.[index_id] = PD.[index_id]
    AND P2.[partition_number] = PD.[partition_number]
    INNER JOIN sys.indexes I
    WITH( NOLOCK )
    ON I.[object_id] = PD.[object_id]
    AND I.[index_id] = PD.[index_id]
    WHERE
    P1.[partition_id] P2.[partition_id]
    AND P1.[rows] > P2.[rows]

    Reply
    • Dennis – just a thought here, but why not test it before you suggest it as a solution? I show you exactly how to test it in the video. (Techniques like this simply won’t work with paused index builds, as you’ll learn when you test it.)

      Reply
  • I have this SQL2017 DevEdtn with a test database at level 140.
    Still the create index statement does not work !

    CREATE INDEX IX_Stuffing ON dbo.DiningRoomTable(Stuffing)
    WITH (DROP_EXISTING=OFF,ONLINE = ON, RESUMABLE = ON);

    Msg 155, Level 15, State 1, Line 12
    ‘RESUMABLE’ is not a recognized CREATE INDEX option.

    #ScratchedMyHead

    Docs state:
    RESUMABLE = { ON | OFF}
    Applies to: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database

    Reply
    • Right, this blog post is about sys.index_resumable_operations, which appeared in SQL Server 2017. The initial support was just for online index builds. If you want to test it on 2017, you can try something similar with alter index rebuild commands instead of a create.

      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":""}