Why is dropping a primary key expensive? I have a table with a clustered PK. No foreign keys reference it. When I drop it I expect it to be more or less instantaneous, but it is instead very slow.

My assumption was that we'd just be removing some metadata and not having a constraint or clustering for future updates and inserts.

What is actually happening internally to make this expensive?
One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:


  The pointer from an index row in a nonclustered index to a data row is
  called a row locator. The structure of the row locator depends on
  whether the data pages are stored in a heap or a clustered table. For
  a heap, a row locator is a pointer to the row. For a clustered table,
  the row locator is the clustered index key.


So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:


  Dropping a clustered index can take time because in addition to
  dropping the clustered index, all nonclustered indexes on the table
  must be rebuilt to replace the clustered index keys with row pointers
  to the heap.