First published on MSDN on Dec 22, 2016
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:
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) GOWhile 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
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
– 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;
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 .
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.