How can I check the progress / status when I submit an alter index reorganize / rebuild ?

  • 1
    By progress you mean how much rows of indexes it has checked and how much remains? I dont think you can do it. Your best bet is to monitor is using DMV sys.dm_exec_requests
    – Shanky
    Commented Sep 12, 2014 at 8:41

3 Answers 3


It's really hard to say how long your rebuild will take, as SQL itself doesn't really know in advance and cant give you an estimate.

You can use the following query to use the dm_exec_requests dmv to view how long your index rebuild has been going on for, and to verify that SQL doesn't really have an estimate:

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')

However when it comes to a real estimate on time required you can read this nice blog post from sqlmunkee, which summarises this by saying "..it depends. ":

And since we're not all on the same hardware, using the same software, or looking at the same data, the answer has to be… ..it depends

Frustrating, but true, sadly.

  • 2
    Thanks for the nice script, I had to adjust the where clause to 'DBCC' but then I got some information back, saying that percent complete is 6.42 and the ETA Min are around 707 (which is for one partition). I will keep an eye on it on how far of this will be. Will check the post as well.
    – nojetlag
    Commented Sep 12, 2014 at 8:54
  • does this work for ONLINE rebuilds too? Commented May 22, 2018 at 7:26
  • 1
    @Simon_Weaver Should do yes.
    – Reaces
    Commented May 22, 2018 at 11:25
  • This only works for REORGANIZE. It does not work for REBUILD. Please see the "percent_complete" column at the following URL for a complete list of where it works. The estimated_completion_time column falls into the same category but is not documented as such because it's "Internal Only". learn.microsoft.com/en-us/sql/relational-databases/…
    – Jeff Moden
    Commented Jul 24, 2019 at 3:43

I managed to find this blog post with a magick script which allegedly does the task, can't check because this seems to not work for SQL Server 2014 which I am running, query blocks waiting for a Shared Lock. Maybe someone will find it useful though so I will just leave it here.

;WITH cte AS
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
   object_name(cur.object_id) as TableName,
   PrecentDone =
         WHEN pre.rows = 0 THEN 0
         ((cur.rows * 100.0) / pre.rows)
   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)

I found the accepted answer above good, but missing a crucial thing: command status (e.g. is the command blocked)

This simple select shows status front and center:

SELECT percent_complete, *
FROM sys.dm_exec_requests
WHERE session_id = <session id of alter index>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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