Create Index Status Monitor Progress

Starting with SQL Server 2014 a new troubleshooting capability is to monitors real time query progress with the DMV sys.dm_exec_query_profiles which is the base for Live Query Statistics new functionality for SQL Server 2016, there are two ways to enable real time query monitoring:

• Session scope: By enabling SET STATISTICS XML ON; or SET STATISTICS PROFILE ON;
• Instance scope: By enabling query_post_execution_showplan extended event.

1. Change the session behavior by enabling SET STATISTICS PROFILE ON

2. Execute the CREATE INDEX command

SET STATISTICS PROFILE ON
GO

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

While this session is executing the CREATE INDEX command you can monitor the execution with sys.dm_exec_requests and sys.dm_os_waiting_tasks and the progress with sys.dm_exec_query_profiles

3. On a different session execute the DMV sys.dm_exec_query_profiles
sys.dm_exec_query_profiles will give you statistical information of the progress of every operator in the execution plan and if the plan executes in parallel then it will give you the statistical information by thread.

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;

Sources:

SQL Server / ALTER INDEX / CREATE INDEX Progress

https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/create-index-monitoring-progress/ba-p/371166