Search code examples
sql-server-2012primary-keydatabase-performance

Why does it take so long to drop a constraint?


SQL Server 2012 SP3

It's a primary key backed by a clustered index. If it matters, there are 62M rows in the table, with 24GB of data, 6.5GB of indexes (there are 3 in total).

(EDIT: the statement eventually fails, saying that dbo.SORT temporary run storage has filled PRIMARY (and thus the disk).


Solution

  • Dropping the clustered index (doesn't have to be PK) turns the table into a heap and rebuilds all non-clustered indexes because the row locator has changed from the clustered index key to a RID.

    You can avoid the overhead of rebuilding the non-clustered twice by fist dropping the non-clustered indexes so they are untouched when the table becomes a heap. Then recreate the new clustered primary key followed by non-clustered indexes.

    An alternative to dropping and recreating the non-clustered indexes is to disable them (ALTER INDEX...DISABLE) before dropping the clustered index. When the new clustered index is subsequently created, the disabled non-clustered indexes are automatically enabled (rebuilt). The benefit of this disable method is disabled index meta-data is leveraged, which avoids cluttering the script with non-clustered index create DDL.

    I'll add that tables should generally have a clustered index, primary key or not. The implication is you need to have free space of roughly 120% of the table size to support a recreate/rebuild the clustered index.