Track progress of create index command

My normal SQL for checking long running procedures do not work with the CREATE INDEX command, I usually run:

SELECT     db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
         SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
         (CASE WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
         ELSE 
                 r.statement_end_offset
         END -r.statement_start_offset)/2) AS stmt_executing,
     s.login_name, r.percent_complete, r.start_time, 
     CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                   + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                   + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
             dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
     ,CASE s.transaction_isolation_level
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncomitted'
         WHEN 2 THEN 'ReadComitted'
         WHEN 3 THEN 'Repeatable'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
     END as 'Isolation level'
     ,   s.HOST_NAME,    s.PROGRAM_NAME, s.host_process_id, 
     r.status,   r.wait_time,    wait_type,  r.wait_resource,
     r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
     --Comment out below if you don't need the plan
     , (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
 FROM sys.dm_exec_requests r
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
             sys.dm_exec_sessions s
 WHERE r.session_id > 50 
 and r.session_id=s.session_id
 and r.session_id <> @@SPID
 ORDER BY r.cpu_time desc

But this will not print out the percent complete value we want, need to pick it up in another way. What you need to do is the following, in the same SSMS Query window that you later will run CREATE INDEX you start with finding your SPID by running:

SELECT @@SPID

Save that SPID, we’ll use it later.

To be able to pick up the CREATE INDEX progress we need to add:

SET STATISTICS PROFILE ON;
SET STATISTICS XML ON;

So the full CREATE INDEX command would be something like:

SET STATISTICS PROFILE ON;
SET STATISTICS XML ON;

CREATE NONCLUSTERED INDEX [IX_mycolumn] ON [dbo].[mycolumn]
(
    [mycolumn] ASC
)
GO

Then in another window we run we run some code checking that SPID we found earlier, say that the SPID was 42, then it would be:

DECLARE @SPID INT = 42;
 ;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'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;

Now we get an ETA and stuff, neat huh? 🙂

None of this was my original work, I’m just building upon giants here, that’s what’s so nice about the SQL Server community!

Leave a Reply

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