Is it possible to track the progress of when I drop the primary key clustered index on a table?

Question

I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine the progress of these processes?

Answer 1

If you are using Enterprise Edition or equivalent, you can drop the primary key ONLINE:

ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH (ONLINE = ON);

This will allow you to monitor progress using the Profiler Event Class Progress Report: Online Index Operation or the Extended Event:

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. Use this event to monitor online index builds.

It provides the following event fields:

Answer 2

Starting in SQL Server 2014, you can use Query Profiles to track the progress of CREATE INDEX statements. You can also use this to get some progress information on the DROP INDEX process.

Specifically, the below query will show the progress of the updates to the non-clustered indexes when you drop a clustered index as the HEAP then has to be scanned so the RID pointers in the non-clustered index can be updated after the clustered index is dropped.

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=62 --update to session id of session dropping the index
GROUP BY node_id,physical_operator_name
ORDER BY node_id desc;

Note that you will see the "estimate_percent_complete" column hit 100% multiple times if you have multiple non-clustered indexes. The script also won't return any rows initially while the clustered index is being dropped as the query profile doesn't return any information for this process.

Answer 3

Starting with SQL Server 2016, you can track the progress of in-flight requests using:

sys.dm_exec_query_statistics_xml:

Returns query execution plan for in-flight requests. Use this DMV to retrieve showplan XML with transient statistics.

Syntax

sys.dm_exec_query_statistics_xml(session_id)  

Arguments

session_id Is the session id executing the batch to be looked up. session_id is smallint. session_id can be obtained from the following dynamic management objects:

• sys.dm_exec_requests
• sys.dm_exec_sessions
• sys.dm_exec_connections

Note that one of the profiling infrastructures must be enabled:

Remarks

This system function is available starting with SQL Server 2016 (13.x) SP1. See KB 3190871

This system function works under both standard and lightweight query execution statistics profiling infrastructure. For more information, see Query Profiling Infrastructure.