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:

Leave a Reply

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