CREATE INDEX Progress

Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks on Oct 12th, 2018 | 1 comment

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

SET STATISTICS PROFILE ON
GO
CREATE NONCLUSTERED INDEX [IX_Table02_Column01_Column02 ON [dbo].[Table02]
([Column01] ASC)
INCLUDE ([Column02], [Column03])
GO

ALTER Index statement example

SET STATISTICS PROFILE ON
GO
ALTER INDEX [IX_Table01_Column01_Column02] ON [dbo].[Table01] REBUILD
GO

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

SELECT session_id,
sp.cmd,
sp.hostname,
db.name,
sp.last_batch,
node_id,
physical_operator_name,
SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count) as EST_COMPLETE_PERCENT
FROM sys.dm_exec_query_profiles eqp
join sys.sysprocesses sp on sp.spid=eqp.session_id
join sys.databases db on db.database_id=sp.dbid
WHERE session_id in (select spid from sys.sysprocesses sp where sp.cmd like '%INDEX%')
GROUP BY session_id, node_id, physical_operator_name, sp.cmd, sp.hostname, db.name, sp.last_batch
ORDER BY session_id, node_id desc;

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

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

vitor says:

March 8, 2019 at 11:40 pm

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

Reply

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / ALTER INDEX / CREATE INDEX Progress

Similar Posts:

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

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud