Real time progress ofSQL index maintenance job

Question

Real time progress ofSQL index maintenance job

Arunkumar Janakiraman 1

Hello,

Is there a query to check the percentage completion of the index maintenance job? Like how we used to check for backup progress % using sys.dm_exec_requests ?

Thanks,
Arun

EchoLiu-MSFT 14,621 Reputation points

2020-12-04T06:15:32.78+00:00

Do you have any updates?
Please also remember to mark the replies as answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

2 answers

Your answer

EchoLiu-MSFT 14,621 Reputation points

2020-12-04T06:15:32.78+00:00

Do you have any updates?
Please also remember to mark the replies as answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 1

Olaf Helper 47,436

Like how we used to check for backup progress % using sys.dm_exec_requests ?

That DMV also returns the progress for index operations as well, see sys.dm_exec_requests (Transact-SQL) => percent_complete, it includes

ALTER INDEX REORGANIZE
DBCC INDEXDEFRAG

Arunkumar Janakiraman 1 Reputation point

2020-12-02T13:59:18.49+00:00

I tried like this, but it didn't gave any results.

Select * from sys.dm_exec_requests
where command in ('ALTER INDEX')
tibor_karaszi@hotmail.com 4,316 Reputation points

2020-12-02T15:34:03.517+00:00

That did indeed return information for me. My guess is that at the very time you were executing the SELECT, the index rebuild command wasn't executing.

Answer 2

Hi @Arunkumar Janakiraman ,

Starting from SQL Server 2008, there is a DMV view sys.dm_exec_requests. There is a field percent_complete representing the percentage of work done by some commands. This includes index reorganization (ALTER INDEX REORGANIZE), which does not include ALTER INDEX REBUILD, which can be viewed The percentage of index reorganization (ALTER INDEX ORGANIZE) completed. In other words, the progress of index reorganization cannot be obtained before SQL Server 2008.

There is a similar case for your reference:
Check progress of alter index reorganize / rebuild

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

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.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-12-03T22:36:17.75+00:00

Small (and largely irrelevant) note; sys.dm_exec_requests made the scene in SQL 2005, but lets hope that Arunkumar is using a or modern version!

Share via

Real time progress ofSQL index maintenance job

2 answers

Your answer