Início / dba / Perguntas / 170803
Accepted
carlo.borreo
Asked: 2017-04-13 02:28:29 +0800 CST

Por que a eliminação de chaves estrangeiras demora muito?

  • 772

Eu fiz um script que, uma de cada vez, exclui todas as chaves estrangeiras de um banco de dados, assim:

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

O que me surpreende é que o script leva muito tempo: em média, 20 segundos para cada DROP FK. Agora, eu entendo que criar um FK pode ser um grande negócio, porque o servidor tem que ir e verificar se a restrição FK não foi infringida desde o início, mas descartando? O que um servidor faz ao descartar FKs que demora tanto? Isso é tanto para minha própria curiosidade quanto para entender se existe uma maneira de tornar as coisas mais rápidas. Ser capaz de remover o FK (não apenas desativá-lo) me permitiria ser muito mais rápido durante uma migração e, portanto, minimizar o tempo de inatividade.

sql-server foreign-key

2 respostas

  1. Best Answer

    A eliminação de uma restrição requer um bloqueio Sch-M (Modificação de esquema) que bloqueará outras pessoas a consultar a tabela durante a modificação. Você provavelmente está esperando para obter esse bloqueio e precisa esperar até que todas as consultas atualmente em execução nessa tabela sejam concluídas.
    Uma consulta em execução tem um bloqueio Sch-S (estabilidade de esquema) na tabela e esse bloqueio é incompatível com um bloqueio Sch-M.

    Dos modos de bloqueio, bloqueios de esquema

    O Mecanismo de Banco de Dados usa bloqueios de modificação de esquema (Sch-M) durante uma operação de linguagem de definição de dados de tabela (DDL), como adicionar uma coluna ou descartar uma tabela. Durante o tempo em que é retido, o bloqueio Sch-M impede o acesso simultâneo à tabela. Isso significa que o bloqueio Sch-M bloqueia todas as operações externas até que o bloqueio seja liberado.

    Algumas operações de linguagem de manipulação de dados (DML), como truncamento de tabela, usam bloqueios Sch-M para impedir o acesso às tabelas afetadas por operações simultâneas.

    O Mecanismo de Banco de Dados usa bloqueios de estabilidade de esquema (Sch-S) ao compilar e executar consultas. Os bloqueios Sch-S não bloqueiam nenhum bloqueio transacional, incluindo bloqueios exclusivos (X). Portanto, outras transações, incluindo aquelas com bloqueios X em uma tabela, continuam sendo executadas enquanto uma consulta está sendo compilada. No entanto, operações DDL simultâneas e operações DML simultâneas que adquirem bloqueios Sch-M não podem ser executadas na tabela.

    • 14

  2. Vou orientá-lo através de um exemplo para que você possa ver por que estava demorando muito. Criando um banco de dados vazio para este teste.

    CREATE DATABASE [TestFK]
GO

    Criando 2 tabelas.

     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

    Criando uma restrição de chave estrangeira na tabela Person.

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

    Insira alguns dados em ambas as tabelas.

    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

    Abra uma nova janela de consulta e execute-a (não feche a janela depois que a consulta for concluída).

       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

    Abra outra janela de consulta e execute isso.

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

    Você verá que a restrição de descarte continuará em execução (aguardando) e agora execute a consulta para ver por que ela está sendo executada por mais tempo e quais bloqueios ela está esperando.

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

    Depois de confirmar sua operação de inserção, a restrição de descarte será concluída imediatamente porque agora a instrução de descarte pode adquirir o bloqueio necessário.

    Para o seu caso, você precisa ter certeza de que nenhuma sessão está mantendo um bloqueio compatível, o que impedirá a restrição de soltar para adquirir os bloqueios/bloqueios necessários.

    • 6

relate perguntas