26

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

nojetlag
  • 2,877
  • 9
  • 33
  • 42
  • 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 Sep 12 '14 at 08:41

3 Answers3

24

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.

Retne
  • 3
  • 3
Reaces
  • 2,601
  • 4
  • 25
  • 38
  • 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 Sep 12 '14 at 08:54
  • does this work for ONLINE rebuilds too? – Simon_Weaver May 22 '18 at 07:26
  • 1
    @Simon_Weaver Should do yes. – Reaces May 22 '18 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". https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql – Jeff Moden Jul 24 '19 at 03:43
4

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
(
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
evictednoise
  • 301
  • 2
  • 8
2

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>
splattne
  • 242
  • 3
  • 10
Jonesome Reinstate Monica
  • 3,379
  • 10
  • 37
  • 55