How to query the progress of SQL Reindex

Question

Re-index of the database has been made according to the Task of SQL, but 48 hours have passed and it is still in the active state. However, the progress of creating the index can not be seen ，How to query progress of SQL re-index. thank you

Answer 1

There are lot of online threads with similar question and answer. I will quote one of the Stackexchange Thread. You can use below query to fetch the status of index rebuild. if you have multiple indexes the percent_complete column will keep refreshing for each alter index operation.

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('Alter Index')

Answer 2

Hi @Ping Wang ,

Please refer a similar query from below:

Select r.command  
, s.text  
, r.start_time  
, r.percent_complete  
, cast(((datediff(second, r.start_time, getdate())) / 3600) As varchar) + ' hour(s), '  
+ cast((datediff(second, r.start_time, getdate()) % 3600) / 60 As varchar) + 'min, '  
+ cast((datediff(second, r.start_time, getdate()) % 60) As varchar) + ' sec' As running_time  
, cast((r.estimated_completion_time / 3600000) As varchar) + ' hour(s), '  
+ cast((r.estimated_completion_time % 3600000) / 60000 As varchar) + 'min, '  
+ cast((r.estimated_completion_time % 60000) / 1000 As varchar) + ' sec' As est_time_to_go  
, dateadd(second, r.estimated_completion_time / 1000, getdate()) As est_completion_time  
   From sys.dm_exec_requests     r                  
  Cross Apply sys.dm_exec_sql_text(r.sql_handle) s  
  Where r.command IN ('Alter Index')  
  ----   Or r.command Like 'DBCC%'  
  ----   Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');  

Using above query, you could also query the progress of backup,restore,dbcc command and so on.

You could also refer another query which may be helpful to you.

;WITH cte AS  
(  
SELECT  
object_id,  
index_id,  
partition_number,  
rows,  
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn  
FROM sys.partitions  
)  
SELECT  
   object_name(cur.object_id) as TableName,  
   cur.index_id,  
   cur.partition_number,  
   PrecentDone =  
      CASE  
         WHEN pre.rows = 0 THEN 0  
      ELSE  
         ((cur.rows * 100.0) / pre.rows)  
      END,  
   pre.rows - cur.rows as MissingRows  
FROM cte as cur  
INNER JOIN cte as pre on (cur.object_id = pre.object_id) AND (cur.index_id = pre.index_id) AND (cur.partition_number = pre.partition_number) AND (cur.rn = pre.rn +1)  
ORDER BY 4  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.