Sql-server – Is it possible to track the progress of when I drop a the primary key clustered index on a table

clustered-indexnonclustered-indexprimary-keysql serversql-server-2016

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?

Best Answer

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:

progress_report_online_index_operation event fields

Related Question