27

SQL Server 2014, Std Ed

I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help.

I currently use the method below, which at least shows me movement (that the index creation is not blocked). But I have zero idea if I am %10 through the process or %99.

I tried the method described here: https://dba.stackexchange.com/a/102545/6229 but it shows a clearly wrong est completion time (it basically shows 'now' for a 60+ minute process that I am 10 min into)

How can I get a clue?

SELECT percent_complete, estimated_completion_time, reads, writes, logical_reads, text_size, *
FROM
sys.dm_exec_requests AS r
WHERE
r.session_id <> @@SPID
AND r.session_id = 58
35

I think the following query will at least get you quite close. It makes use of a DMV that was introduced in SQL Server 2014: sys.dm_exec_query_profiles (and thanks to Martin Smith for introducing it to me via this related DBA.StackExchange Answer: Progress of SELECT INTO statement :-).

Please note:

  • !! You will need to add SET STATISTICS PROFILE ON; or SET STATISTICS XML ON; in the query batch that is doing the CREATE INDEX (and placed before the CREATE INDEX statement, if that wasn't obvious), else no rows will show up in this DMV for that SPID / session_id !!

  • The IN operator is used to filter out the Index Insert row that, if included, will increase the TotalRows values, which will skew the calculations since that row never shows any rows processed.

  • The row count displayed here (i.e. TotalRows) is double the row count of the table due to the operation taking two steps, each one operating on all of the rows: first is a "Table Scan" or "Clustered Index Scan", and second is the "Sort". You will see "Table Scan" when creating a Clustered Index or creating a NonClustered Index on a Heap. You will see "Clustered Index Scan" when creating a NonClustered Index on a Clustered Index.

  • This query does not seem to work when creating Filtered Indexes. For some reason, Filtered Indexes a) do not have the "Sort" step, and b) the row_count field never increases from 0.

  • When creating a Clustered Index on a Heap that already has NonClustered Indexes on it, the NonClustered Indexes need to be rebuilt (to swap out the RID -- RowID -- for the Clustered Index Key(s)), and each NonClustered Index rebuild will be a separate operation and hence not reflected in the stats returned by this query during the creation of the Clustered Index.

  • This query has been tested against:

    • Creating NonClustered Indexes on a Heap
    • Creating a Clustered Index (no NonClustered Indexes exist)
    • Creating NonClustered Indexes on the Clustered Index/Table
    • Creating a Clustered Index when NonClustered Indexes already exist
    • Creating Unique NonClustered Indexes on the Clustered Index/Table
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'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;

Sample output:

                        Rows                 Percent   Elapsed  Estimated    Estimated
CurrentStep  TotalRows  Processed  RowsLeft  Complete  Seconds  SecondsLeft  CompletionTime
-----------  ---------  ---------  --------  --------  -------  -----------  --------------
Clustered    11248640   4786937    6461703   42.56     4.89400  6.606223     2016-05-23
Index Scan                                                                   14:32:40.547
  • If you create a non-clustered index on a heap and the new index has the same key as an existing index, the query will make use of an operator with physical_operator_name set to N'Index Scan', rather than N'Table Scan' or N'Clustered Index Scan'. Also, it will be very slow, since it will perform a bunch of RID lookups. – Brian Jan 22 '18 at 21:36
  • Now if only that would work on ALTER INDEX ALL ON dbo.table REBUILD ..... <g> – samsmith Mar 21 '18 at 5:32
  • BTW, this also works well to monitor the progress of implementing page compression too. sys.dm_exec_query_profiles is pretty cool. – Todd Kleinhans Aug 2 '18 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.