Why does dropping foreign keys take long?
I have made a script that, one at a time, deletes all the foreign keys from a database, just like this:
ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1 ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col1 ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col2
What surprises me is that the script takes a long time: on average, 20 seconds for each DROP FK. Now, I understand that creating an FK may be a big deal because the server has to go and check that the FK constraint is not infringed from the beginning, but dropping? What does a server do when dropping FKs that take so long? This is both for my own curiosity, and to understand if there is a way to make things faster. Being able to remove FK (not just disable them) would allow me to be much faster during migration, and therefore minimize downtime.
Drop foreign key SQL server requires a Schema Modification lock that will block others to query the table during the modification. You are probably waiting to get that lock and have to wait until all currently running queries against that table are finished. A running query has a Schema Stability lock on the table and that lock is incompatible with an Sch-M lock. From Lock Modes, Schema Locks
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released. Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.
The Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table. Hope this helps you drop foreign key SQL server!