CREATE INDEX Monitoring Progress

  • Article

One of the challenges for DBA's are to work on heavy load and critical systems where maintenance windows are usually very short, recently I was asked for a customer if there was any possibility to track or estimate the progress of a CREATE INDEX statement and my response was sys.dm_exec_query_profiles.

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.

 
Let's see the session scope in action.

1. Change the session behavior by enabling SET STATISTICS PROFILE ON
 
2. Execute the CREATE INDEX command

 SET STATISTICS PROFILE ON
GO
CREATE CLUSTERED INDEX cix_SalesOrderDetail_demo_soid ON [SalesOrderDetail_demo] (SalesOrderDetailID)
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, request_id, physical_operator_name, node_id, 
       thread_id, row_count, estimate_row_count
FROM sys.dm_exec_query_profiles
ORDER BY node_id DESC, thread_id

The result is the way on how the CREATE INDEX is being processed.

– TABLE SCAN. Retrieves all rows from the table specified.
– SORT. Sorts all incoming rows.
– INDEX INSERT. Inserts rows from its input into the clustered index
– Parallelism. Parallel query

For example, the TABLE SCAN operator is being processed in 4 threads; the estimated_row_count are the estimated rows to be processed and the row_count is the number of rows that were processed.

If we summarize this information:

 SELECT    
       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 estimate_percent_complete
FROM sys.dm_exec_query_profiles   
WHERE session_id=54  
GROUP BY node_id,physical_operator_name  
ORDER BY node_id desc;

Then you can view the progress of the CREATE INDEX statement and by doing so you can estimate the completion time under an equal base line of operation. Keep in mind that the output of the DMV is based on the execution plan generated by SQL Server, consider that the statistics are very important in the estimation of the retrieved records and that the order of the operations are determined by the execution plan too.

Review the details of the sys.dm_exec_query_profiles and look for other interesting columns such as open_time, logical_read_count, write_page_count, first_active_time, last_active_time and more. In fact, you can troubleshoot almost any query with this functionality, see this great post from by @Fany Carolina Vargas. Troubleshoot Runaway Queries in SQL 2014 Using sys.dm_exec_query_profiles

 

Additional notes for online index operations
Introduced in SQL Server 2012, online index operations [CREATE INDEX] and [ALTER INDEX REBUILD], also could be tracked by the extended event progress_report_online_index_operation.

progress_report_online_index_operation
Occurs during an online index build process to indicate the progress of the build operation. Each stage of an online index build operation generates an event.

Example:

 CREATE CLUSTERED INDEX cix_SalesOrderDetail_demo_soid ON [SalesOrderDetail_demo] (SalesOrderDetailID)
WITH (ONLINE=ON)

 
Introduced in SQL Server 2014, for [ALTER INDEX REBUILD] operations the ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option which permits you to specify how long the rebuild process should wait for the necessary locks. For details check this post from the SQL Server Blog.

Example:

 ALTER INDEX cix_SalesOrderDetail_demo_soid ON [SalesOrderDetail_demo] REBUILD
WITH (ONLINE=ON (WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF )))

References
Guidelines for Online Index Operations
https://technet.microsoft.com/en-us/library/ms190981(v=sql.120).aspx

How Online Index Operations Work
https://msdn.microsoft.com/en-us/library/ms191261.aspx

Managing the Lock Priority of Online Operations
https://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Lock

Twitter @carlos_sfc