Discussion :

[olivtone]

Bonjour a Tous

En lancant la

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
TSQL SELECT *
from sys.foreign_keys
WHERE is_not_trusted = 1

Je me suis apercu que j'avais un grand nombre de qui ne sont pas confiance (une centaine)

Je voulais juste savoir quelle etait l'impact sur les performances et si vous avez des exemples concrets sur les performances que cela peut engendrer ?

merci a vous

[Bernardos]

Bonjour,
oulalalala la catastrophe!
tu parles de performance... là c'est déjà très très mauvais. mais le gros du problème n'est pas là.
elle est où la cohérence de tes données si tu check pas les contraintes?
le résultat de ta requete doit être 0.
c'est sur une base en production?

Cordialement,

[olivtone]

Merci de ton retour
oui c'est sur une BDD de production
Tu as un exemple concret a ce niveau la ?

[SQLpro]

Si vous utilisez des insertions massive de fichiers via SSIS, bcp.exe ou BULK INSERT, les FK sont désactivées par défaut. Il convient, soit de les réactiver après chaque lot d’insertion, soit de forcer la vérification des FK au cours de l'insertion.

A +

[Bernardos]

olivtone,
pourrais-tu passer cette requete?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
from sys.foreign_keys
WHERE is_disabled= 1

cordialement,

[olivtone]

desole du retard

cela me retourne une dizaine de lignes

[StringBuilder]

Essaie de lancer, pour chaque ligne retournée :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
-- Enable the constraint
ALTER TABLE MyTable 
WITH CHECK CHECK CONSTRAINT MyConstraint
GO

Attention cependant : ça risque de rammer un moment s'il y a du volume...

Sinon, le problème de désactiver les contraintes, c'est qu'un traitement qui déconne (fichier d'import pas à jour, transaction mal gérée dans le programme, etc.) et tu te retrouves avec des lignes orphelines.
Ceci a deux conséquences notables :
- Corruption des données (à quoi rattacher les lignes orphelines ?)
- Performances (à vérifier, mais pour moi, l'index lié aux FK sera inutilisable, puisque incomplet)

[olivtone]

ok donc si j'ai bien compris il n'y a aucun interet a laisser les FK en no_trusted

[Bernardos]

Bonjour,
Bon j'avais été trop vite pour répondre l'autre jour.
Tu m'as induit en erreur avec le titre

[2014] Grand nombre de FK désactivées

j'ai répondu en me disant que tu avais des centaines de FK Désactivées
c'est pour ca que quand j'ai compris que tu parlais de is_not_trusted et non pas is_disabled, je t'ai demandé de repasser la requête pour is_disabled

Si vous utilisez des insertions massive de fichiers via SSIS, bcp.exe ou BULK INSERT, les FK sont désactivées par défaut. Il convient, soit de les réactiver après chaque lot d’insertion, soit de forcer la vérification des FK au cours de l'insertion.

Ce n'est pas totalement vrai. c'est même plutôt faux
Alors je ne me souviens plus comment c'était avant et je n'ai pas d'autres versions à disposition aujourd'hui mais en 2014 les composants bulkinsert et oledb destination font la vérification des clés par défaut
Par contre, le composant destination ado.net lui ne fait pas la vérification par défaut. il faut décocher "insertion en bloc si possible"
je n'ai pas vérifié pour BCP.exe

Alors par contre, ce qui n'est pas vrai du tout, c'est que les clés restent désactivées après le traitement (toujours testé en 2014).
tu as 2 propriétés

1. Is_not_trusted
2. Is_disabled

Je ne sais pas techniquement comment ca se déroule. mais je présume que lorsqu'on est dans le cas où on ne vérifie pas les clés, le composant ou la commande qui lance l'insert désactive les clés avant traitement et les réactive après.
Toujours est il que la clé étrangère est bel bien active à la fin du traitement.
Par contre, du fait d'avoir insérer des lignes sans vérification, la propriété is_not_trusted passe à 1.
Pour la refaire passer à 0, il faut relancer un check comme stringbuilder l'a fait par exemple
en bonus

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT name, 
       type_desc, 
       is_disabled, 
	   is_not_trusted, 
	   ' ALTER TABLE ' + object_name(parent_object_id) + ' WITH CHECK CHECK CONSTRAINT ' + name as 'Requête pour vérifier la contrainte'
FROM            sys.foreign_keys
where is_not_trusted = 1

la dernière colonnes te donne les requêtes à exécuter. il n'y a qu'à copier/coller, vérifier, exécuter.

rassurez-vous, les clés primaires sont vérifiées

cela me retourne une dizaine de lignes

on parlait des FK disabled.
Bon la par contre c'est pas bien. Vérifie l'intégrité et essaye de les réactiver. mais je crains que tu aies des incohérences.
Cherche les raisons aussi pour lesquelles ces FK sont désactivées! en règle générale ce sont les développeurs qui désactivent ces clés parce que ca les fait chier de devoir respecter les contraintes pendant qu'il font des dévellopement et/ou des tests. les pires oublient de les réactiver(j'ai déjà vu une base en prod ou toutes les clés étaient désactivées...).
Si ce ne sont pas les développeurs faut voir si t'as pas un script qui tourne de manière régulière et qui désactive ces clés.

Cordialement,

[hmira]

- Performances (à vérifier, mais pour moi, l'index lié aux FK sera inutilisable, puisque incomplet)

Oui tu as tout à fait raison concernant les conséquences négatives sur les performances du fait que les contraintes FK (Foreign Key) ne soient pas dignes de confiance (ie untrusted), mais ceci n'est pas, comme tu le "supposes", lié à l'index incomplet !
La notion d'"index complet" ou d'"index incomplet" n'existe pas. Un index est soit activé, soit désactivé.
Le problème de performance inféré par les contraintes FK, qui ne sont pas (ou ne sont plus !) dignes de confiance (untrusted), provient souvent du plan d'exécution, non optimal, produit par l'optimiseur de requête comparé au plan optimal généré par l'optimiseur si les dites contraintes FK étaient dignes de confiance.
En effet, lorsque les contraintes sont dignes de confiance (trusted), l'optimiseur peut "réécrire" la requête SQL en supprimant certaines jointures internes (INNER JOIN) ou certaines restrictions telles que EXISTS, NOT EXISTS, etc. considérées inutiles (ou plutôt considérées déjà vérifiées puisque induites par la contrainte FK digne de confiance).
En conclusion, selon que les contraintes FK soient dignes de confiance ou non (trusted vs untrustred), le plan d'exécution généré par l'optimiseur ne sera pas du tout le même. Le plan d'exécution généré par l'optimiseur est bien souvent beaucoup plus performant, plus optimal (Time, CPU, I/O, etc.) lorsque les contraintes sont dignes de confiance (trustud).
Les contraintes qui ne sont pas dignes de confiance n'apportent aucune plus value et sont même ignorées par l'optimiseur.
A+