Foreign Keys, confiança e planos de execução

  • Articolo

Vamos falar sobre FKs e como elas influenciam os seu plano de execução.

Primeiramente vamos criar algumas tabelas, ainda sem FKs e inserir alguns dados

 USE [SANDBOX]
 GO
 
 ----------------------------------------------------------------------
 IF OBJECT_ID('[dbo].[tCliente]') IS NOT NULL
 DROP TABLE [dbo].[tCliente]
 GO
 IF OBJECT_ID('[dbo].[tEstado]') IS NOT NULL
 DROP TABLE [dbo].[tEstado]
 GO
 
 ----------------------------------------------------------
 CREATE TABLE  [dbo].[tEstado] 
 (
 [id_Estado] [int] NOT NULL,
 [Estado] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tEstado] PRIMARY KEY CLUSTERED 
 (
 [id_Estado] ASC
 ) ON [PRIMARY]
 ) ON [PRIMARY]
 
 GO
 
 CREATE TABLE  [dbo].[tCliente] 
 (
 [Id] [int] NOT NULL,
 [Nome] [varchar](50) NOT NULL,
 [Id_Estado] [int] NOT NULL,
 CONSTRAINT [PK_tCliente] PRIMARY KEY CLUSTERED 
 (
 [Id] ASC
 ) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 
 ----------------------------------------------------------
 INSERT INTO [dbo].[tEstado] 
 VALUES (1,'SP'), (2,'RJ'), (3,'MG')
 GO
 INSERT INTO [dbo].[tCliente] 
 VALUES (1,'SERGIO',1), (2,'FERNANDO',2), (3,'MARIA',3)
 GO

 

Após a criação vamos fazer um SELECT simples. (Obs: Só retornamos dados da tabela cliente "C.Id, C.Nome, C.Id_Estado" )

 

 SELECT C.Id, C.Nome, C.Id_Estado 
 FROM [dbo].[tCliente] C
 INNER JOIN [dbo].[tEstado] E
 ON C.Id_Estado = E.id_Estado

 E vemos o plano:

 

 

Apesar de NÃO TER UTILIZADO os campos da tabela Estado no SELECT, o SQL precisa consultar a tabela estado para garantir que não referenciamos na tabela Cliente nenhum dado fora do domínio.

Para que o SQL saiba que não precisa consultar a tabela, ele tem que CONFIAR em algo como uma Foreign Key

 

 ALTER TABLE [dbo].[tCliente] ADD CONSTRAINT
 FK_tCliente_tEstado FOREIGN KEY
 ( Id_Estado ) REFERENCES dbo.tEstado ( id_Estado ) 
 
 GO
 SELECT C.Id, C.Nome, C.Id_Estado 
 FROM [dbo].[tCliente] C
 INNER JOIN [dbo].[tEstado] E
 ON C.Id_Estado = E.id_Estado
 GO

 

Analisando novamente o plano, vemos que o SQL confia na FK e não precisa consultar uma tabela se os dados não são retornados.

 

 

Mas calma ! Não acabou.

O que acontece em sistemas de produção, principalmente em migrações? CARGA DE DADOS.

Qual técnica é usada para acelerar a carga?

  1. Desligar FK
  2. Carregar dados
  3. Religar FK

 

Algo como o comando abaixo:

 ALTER TABLE [dbo].[tCliente] 
 NOCHECK CONSTRAINT FK_tCliente_tEstado;
GO
INSERT INTO [dbo].[tCliente] 
 VALUES (4,'AAAA',1), (5,'BBBB',2), (6,'CCCC',3)
GO
ALTER TABLE [dbo].[tCliente] 
 CHECK CONSTRAINT FK_tCliente_tEstado;

 

Tudo OK? Não ! Na verdade, utilizando o comando acima, você ativa a constraint, mas apenas para o que for inserido a partir de agora.  

Logo, tudo o que foi inserido entre o momento que foi desligado e religado a FK, fica sem verificação. Ou seja, o SQL perde a confiança na FK, voltando ao estado inicial

 

 

Nos ambientes que tenho analisado, encontro muito este cenário, que pode levar a problemas de performance onde você não recebe um alerta, ou em caso de dados sem domínio valido, um UPDATE poderia gerar um erro.

Para verificar tudo o que está errado, use os comandos abaixo:

 SELECT * FROM sys.foreign_keys 
WHERE is_not_trusted = 1
 
SELECT * FROM sys.check_constraints 
WHERE is_not_trusted = 1

Pegue todos as FKs e Check Constraints não confiáveis e ligue novamente utilizando o comando abaixo

 

OBS: Planeje o melhor horário esta atividade, pois o SQL terá que varrer toda tabela para validar os dados.

 

 ALTER TABLE [dbo].[tCliente] 
 WITH CHECK CHECK CONSTRAINT FK_tCliente_tEstado;

REF: https://technet.microsoft.com/en-us/library/ms190273.aspx

"WITH CHECK | WITH NOCHECK               

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.   

The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. "

 

Anexo o script utilizado para este exemplo.

 

Espero que tenham gostado e que seja útil para mais alguém, até a próxima!

 

SCRIPT_FK_NOT_TRUSTED.sql

Comments

  • Anonymous
    June 19, 2015
    Muito boa dica!

  • Anonymous
    June 26, 2015
    Excelente!

  • Anonymous
    July 29, 2015
    Sergio, bom dia. Com certeza uma ótima dica e sugestão!!! Parabéns pelo post.

  • Anonymous
    April 26, 2016
    Muito bom a explicação! Demo bem didatico!Abraço.