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!
Answer (1)
Dropping foreign keys can sometimes take a noticeable amount of time due to several factors:
Referential Integrity Checks: When you drop a foreign key constraint, the database needs to check all existing data to ensure that no referential integrity violations will occur. This involves scanning through potentially large tables to verify that there are no child records referencing non-existent parent records.
Locking: Depending on the database system and the specific implementation, dropping a foreign key constraint might require obtaining locks on the affected tables. This could lead to contention with other transactions trying to access or modify the same tables, causing delays.
Index Maintenance: Foreign key constraints often rely on indexes for efficient enforcement. Dropping a foreign key constraint may involve updating or removing these indexes, which can be time-consuming for large tables.
Transaction Overhead: Dropping a foreign key constraint is usually performed within a transaction to ensure data consistency. Transaction management overhead can contribute to the overall time taken, especially if there are other concurrent transactions.
Database Load: If the database server is under heavy load or experiencing resource contention, dropping foreign keys may take longer due to competing demands for CPU, memory, and I/O.
Cascade Actions: If the foreign key constraint has CASCADE actions specified (e.g., CASCADE DELETE), dropping the constraint may involve cascading deletes or updates to maintain data integrity, which can add to the processing time.
Constraints on Foreign Keys: If there are additional constraints on the foreign key column(s), such as unique constraints or check constraints, dropping the foreign key may involve additional checks or modifications to these constraints.
In summary, dropping foreign keys involves various checks, updates, and potentially resource-intensive operations, especially on large datasets or in busy database environments, which can contribute to longer execution times.