Why does dropping foreign keys take long?

Question

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 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.

Answer 1

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.

Answer 2

I will walk you through an example so, you can see why it was taking long time. Creating an empty database for this test.

CREATE DATABASE [TestFK]
GO

Creating 2 tables.

 USE [TestFK]
 GO
CREATE TABLE dbo.[Address] (
      ADDRESSID   INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
       Address1    VARCHAR(50),
      City        VARCHAR(50),
      [State]     VARCHAR(10),
      ZIP     VARCHAR(10));
GO

CREATE TABLE dbo.Person (
       PersonID    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
       LastName    VARCHAR(50) NOT NULL,
     FirstName   VARCHAR(50),
      AddressID   INT);
GO

Creating a Foreign Key constraint on Person table.

 USE [TestFK]
 GO
ALTER TABLE dbo.Person ADD CONSTRAINT FK_Person_AddressID FOREIGN KEY (AddressID)
REFERENCES dbo.Address(AddressID)
GO

Insert some data into both tables.

USE [TestFK]
GO
INSERT dbo.Address (Address1,City,[State],Zip)
  SELECT '123 Easy St','Austin','TX','78701'
    UNION
 SELECT '456 Lakeview','Sunrise Beach','TX','78643'
GO
INSERT dbo.Person (LastName,FirstName,AddressID)
    SELECT 'Smith','John',1
   UNION
 SELECT 'Smith','Mary',1
   UNION
 SELECT 'Jones','Max',2
GO

Open a new query window and run this (do not close the window once the query is completed).

   USE [TestFK]
   GO
   BEGIN TRAN
   INSERT dbo.Person (LastName,FirstName,AddressID)
    SELECT 'Smith1','John1',1
    UNION
    SELECT 'Smith1','Mary1',1
    UNION
    SELECT 'Jones1','Max1',2

Open another query window and run this.

USE [TestFK]
GO
ALTER TABLE dbo.person DROP CONSTRAINT FK_Person_AddressID

You will see you drop constraint will keep on running (waiting) and now run the query to see why it is running longer and what locks it is waiting for.

SELECT * FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id IS NOT NULL; 

Once you commit your insert operation, drop constraint will complete immediately because now drop statement can acquire required lock.

For your case you need to make sure no session is holding a compatible lock which will prevent drop constraint to acquire necessary lock/locks.