gotchasqlModerate
Why does dropping foreign keys take long?
Viewed 0 times
whytakedroppingforeignlongkeysdoes
Problem
I have made a script that, one at a time, deletes all the foreign keys from a database, just like this:
What surprises me is that the script takes long time: on average, 20 seconds for each DROP FK.
Now, I understand that creating a FK may be a big deal, because 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 takes 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 a migration, and therefore minimize downtime.
ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col2What surprises me is that the script takes long time: on average, 20 seconds for each DROP FK.
Now, I understand that creating a FK may be a big deal, because 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 takes 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 a migration, and therefore minimize downtime.
Solution
Dropping a constraint requires a Sch-M (Schema Modification) lock that will block others to query the table during the modification. You are probably waiting to get that lock and has to wait until all currently running queries against that table are finished.
A running query has a Sch-S (Schema Stability) lock on the table and that lock is incompatible with a 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.
A running query has a Sch-S (Schema Stability) lock on the table and that lock is incompatible with a 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.
Context
StackExchange Database Administrators Q#170803, answer score: 17
Revisions (0)
No revisions yet.