SQL Server / ALTER INDEX / CREATE INDEX Progress

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 *