Creating a new and altering existing indexes on the big tables can be time-consuming. There is a simple way to see the progress of the CREATE / ALTER INDEX command.

Since SQL Server 2014 we can use sys.dm_exec_query_profiles DMV to monitor real-time query progress.

sys.dm_exec_query_profiles (SQL SERVER 2014 and above)

“Monitors real-time query progress while the query is in execution. For example, use this DMV to determine which part of the query is running slow. Join this DMV with other system DMVs using the columns identified in the description field”


The first thing we need to do is to enable SET STATISTICS PROFILE ON
CREATE Index statement example

ALTER Index statement example


To see the real-time progress after running ALTER/CREATE index statement just run folowing query


For creating one index at the time you can get something similar to


If you run multiple CREATE index commands on different tables and different sessions you will get something similar to this


So you will have two different Session IDs. If we run sp_who2 stored procedure to lists all current processes connected to a SQL Server we are getting  following for 54,57 session IDs



Similar Posts:

One Response to “ “SQL Server / ALTER INDEX / CREATE INDEX Progress”

  1. vitor says:

    This work fine : SELECT physical_operator_name, sum(row_count)*100/ (select max(rowcnt) from sys.sysindexes
    where object_name (id) = ‘tablename’
    ) Percent_complete
    FROM sys.dm_exec_query_profiles
    group by physical_operator_name

Leave a Reply

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