Foreign Keys, confiança e planos de execução
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?
- Desligar FK
- Carregar dados
- 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!