Dlaczego upuszczanie kluczy obcych trwa długo?

Stworzyłem skrypt, który usuwa wszystkie klucze obce z bazy danych, tak jak poniżej:

ALTER TABLE MyTable1 DROP CONSTRAINT FK_MyTable1_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col1
ALTER TABLE MyTable2 DROP CONSTRAINT FK_MyTable2_col2

Zaskakuje mnie to, że skrypt zajmuje dużo czasu: średnio 20 sekund na każde DROP FK. Rozumiem teraz, że utworzenie FK może być dużym problemem, ponieważ serwer musi przejść i sprawdzić, czy ograniczenie FK nie jest naruszane od samego początku, ale spada? Co robi serwer, upuszczając FK tak długo? Dotyczy to zarówno mojej własnej ciekawości, jak i zrozumienia, czy istnieje sposób na przyspieszenie. Możliwość usunięcia FK (a nie tylko ich wyłączenia) pozwoliłaby mi być znacznie szybsza podczas migracji, a tym samym zminimalizować przestoje.

Usunięcie ograniczenia wymaga blokady Sch-M (Modyfikacja schematu), która blokuje innym możliwość zapytania do tabeli podczas modyfikacji. Prawdopodobnie czekasz na tę blokadę i musisz poczekać, aż wszystkie aktualnie uruchomione zapytania dotyczące tej tabeli zostaną zakończone.
Uruchomione zapytanie ma blokadę Sch-S (Schema Stability) na stole i ta blokada jest niezgodna z blokadą Sch-M.

Z trybów blokady, blokad schematów

Aparat baz danych używa blokad modyfikacji schematu (Sch-M) podczas operacji języka definicji danych tabeli (DDL), takich jak dodawanie kolumny lub upuszczanie tabeli. Podczas trzymania zamek Sch-M uniemożliwia równoczesny dostęp do stołu. Oznacza to, że zamek Sch-M blokuje wszystkie operacje zewnętrzne, dopóki zamek nie zostanie zwolniony.

Niektóre operacje języka manipulacji danymi (DML), takie jak obcinanie tabel, używają blokad Sch-M, aby uniemożliwić dostęp do tabel podlegających usterce przez jednoczesne operacje.

Aparat baz danych używa blokad stabilności schematu (Sch-S) podczas kompilowania i wykonywania zapytań. Blokady Sch-S nie blokują żadnych blokad transakcyjnych, w tym blokad wyłącznych (X). Dlatego inne transakcje, w tym transakcje z blokadami X w tabeli, są kontynuowane podczas kompilowania zapytania. Jednak współbieżne operacje DDL i współbieżne operacje DML, które uzyskują blokady Sch-M, nie mogą być wykonywane na stole.

Przedstawię ci przykład, abyś mógł zobaczyć, dlaczego zajęło to dużo czasu. Tworzenie pustej bazy danych dla tego testu.

CREATE DATABASE [TestFK]
GO

Tworzenie 2 tabel.

 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

Tworzenie ograniczenia klucza obcego w tabeli Person.

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

Wstaw niektóre dane do obu tabel.

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

Otwórz nowe okno zapytania i uruchom je (nie zamykaj okna po zakończeniu zapytania).

   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

Otwórz kolejne okno zapytania i uruchom to.

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

Zobaczysz, że upuszczenie ograniczenia będzie nadal działać (czeka), a teraz uruchom zapytanie, aby zobaczyć, dlaczego działa dłużej i na jakie blokady czeka.

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

Po zatwierdzeniu operacji wstawiania ograniczenie upuszczania zostanie natychmiast zakończone, ponieważ teraz instrukcja drop może uzyskać wymaganą blokadę.

W twoim przypadku musisz upewnić się, że żadna sesja nie posiada kompatybilnej blokady, która zapobiegnie ograniczeniu upuszczenia w celu uzyskania niezbędnej blokady / blokad.