Discussion :

[Sergejack]

Bonjour,

J'aimerais si vous me le permettez, faire un tour de table sur nos habitudes respectives pour prévenir ou limiter les scénarios de deadlock.

Avez-vous des stratégies particulière et à quel point les jugez-vous efficaces ?
Mettez-vous en place certains mécanismes et/ou au contraire évitez-vous certains mécanismes ?

Merci de nous faire part de vos expériences sur le sujet.

[darkelend]

Bonjour,
Voici ce que je fais ou j'ai vu faire. Pas forcement les bonnes solutions :
Je mets des with(NOLOCK) dans les select lorsque c'est faisable
J'ai vu une instance en read commited snapshot

Mais sinon le mieux est de limiter au maximum les IO pour limiter les locks.

[WOLO Laurent]

1. Une transaction ne doit pas etre longue
2. Bien considerer le niveau d'isolation necessaire
3. Utiliser un pool de connexion
4. Acceder aux tables dans le meme ordre dans toutes les procedures
5. Ne retourner que les donnees necessaires a ta couche busness
6. Considerer la pagination des donnees
7. Considerer l'utilisation d'un datareader versus un dataset
8. ......Et la liste est longue....

[Sergejack]

Acceder aux tables dans le meme ordre dans toutes les procedures

Ça me semble difficile, surtout si on utilise des relations (Foreign Key) qui implique des suppressions "en cascade" faites dans un ordre de table inverse à celui des insertions.

Mais peut-être devrions nous éviter un maximum les FK ?
Je pose sincèrement la question.

Considerer la pagination des donnees

Tu parles de ranking (row_number, ect.) ?

[SQLpro]

Mais peut-être devrions nous éviter un maximum les FK ?
Je pose sincèrement la question.

Ce serait bien pire...

• sans FK, votre base serait polluée par des données fausses et il faudrait faire des requêtes plus complexes donc plus couteuses pour éradiquer les données polluantes.
• De plus SQL Server sait faire de l'optimisation sémantique et utilise les contraintes CHECK et FK pour simplifier et optimiser les plans de requêtes, notamment lorsque l'on fait appel à des vues complexes.

A +

[Sergejack]

Ce serait bien pire...

• sans FK, votre base serait polluée par des données fausses et il faudrait faire des requêtes plus complexes donc plus couteuses pour éradiquer les données polluantes.
• De plus SQL Server sait faire de l'optimisation sémantique et utilise les contraintes CHECK et FK pour simplifier et optimiser les plans de requêtes, notamment lorsque l'on fait appel à des vues complexes.

A +

Vous êtes certains qu'il y a un gain de performance quand des FK sont employées ? Et de quel ordre ?

Ce qui me gène dans les FK est qu'il faut faire la plupart des opérations DML en cascade et cela alourdit les transactions et donc augmente les scénarios de deadlock (sans compter que TRIGGER et CASCADE n'aiment pas faire ménage).
Sans FK, on peut par exemple postposer la suppression d'un élément qui aurait perdu un de ses parents.

Et avec des indexes établis sur les champs qui auraient composé une FK, il n'y a pas de problème de performance (d'ailleurs dans la plupart des cas où des FK sont employés, de tels indexes sont déjà utiles).

[WOLO Laurent]

Vous êtes certains qu'il y a un gain de performance quand des FK sont employées ? Et de quel ordre ?

Les FK ne ralentissent pas les performances, elles permettent de renforcer les contraintes et donc la validite des tes donnees. Il faut donc connaitre les bonnes pratiques :

1. Desactiver les contraintes de clef etrangere et les index associes lors des bulk insert !
2. Garder les transactions le plus courts possibles.

[Sergejack]

Pour ma part voici ce que j'ai l'habitude de faire :

J'essaie de rendre mes queries les plus rapides possibles.
Je porte une attention particulière aux indexes.

Il est très rare que j'utilise des hints du genre NOLOCK ou des isolations READ UNCOMITED.

En gros, je table sur la faible probabilité que des deadlock arrivent puisque les transactions sont rapides (mais pas forcément courtes/simples) grâce à des structures pensées pour et aussi sur l’aptitude de SQL Server à prendre des locks pertinents à l'aide des indexes.

--

Je pense qu'à l'avenir je vais un peu plus faire de NOLOCK/READ UNCOMITED ainsi que du data-warehousing.
Je vais aussi arrêter de systématiquement définir des FK afin de ne pas contraindre SQL Server à devoir répercuter en temps réel et sur un nombre importants de tables certaines opérations.
Entre autres choses...

[SQLpro]

Vous êtes certains qu'il y a un gain de performance quand des FK sont employées ? Et de quel ordre ?

Il suffisait de venir aux SQL Days en décembre 2011 chez MS pour voir ma présentation concernant les contraintes et les performances... http://www.microsoft.com/fr-fr/showc...2-8fe0100a9068[/QUOTE]

Ce qui me gène dans les FK est qu'il faut faire la plupart des opérations DML en cascade et cela alourdit les transactions et donc augmente les scénarios de deadlock (sans compter que TRIGGER et CASCADE n'aiment pas faire ménage).[/QUOTE]
L'utilisation du mode cascade est à proscrire, sauf sur de très petites bases. Préférez les modes SET NULL et SET DEFAULT et réalisez des batch de nettoyage aux heures creuses... A lire : http://blog.developpez.com/sqlpro/p8...plexes-dans-l/

Sans FK, on peut par exemple postposer la suppression d'un élément qui aurait perdu un de ses parents.

C'est généralement comme cela que l'on arrive avec des bases corrompues ! Lorsque je fais des audit, chaque fois qu'il n'y a pas de FK je trouve des lignes orphelines. Depuis plus de 10 ans, aucune exception....

Et avec des indexes établis sur les champs qui auraient composé une FK, il n'y a pas de problème de performance (d'ailleurs dans la plupart des cas où des FK sont employés, de tels indexes sont déjà utiles).

A +

[WOLO Laurent]

Serje,

S'il te plait, les clef etrangeres sont la pour renforcer l'integrite referentielle qui est d'une utilite qui ne va pas faire l'objet d'une discussion :

Retiens que lors que tu as une base de donnees OLTP, tu as besoin de poser des contraintes d'integrites fortes. En outre pour une base de donnees OLAP, vu que les modifications sont presque rare, vous avez besoins d'etudier les indexes afin d'accelerer les temps de reponse de vos select. Pendant la population de votre base, je recommanderais de desactiver les contraintes et de supprimer les index !

Maintenant tout ceci doit etre etudie au cas par cas, c'est juste un guideliness.

[mikedavem]

Les contraintes de clé étrangères sont en effet utile et je pense que l'on a pas forcément besoin de revenir dessus. Je prendrai pour exemple les tables d'ERP avec lesquels j'ai pu travailler (notamment sur l'AS400) et qui ne possèdaient aucune contrainte d'intégrité ... Résultat de l'histoire, qualité de données médiocre ... nettoyage de données à faire dans 99% des cas ...

De plus je rejoins SQL Pro quant à l'utilisation des contraintes d'intégrité pour les performances. L'optimiseur de requête sait qu'il peut avoir confiance sur la qualité des données assurées par celles-ci et peut en tirer parti lors de la création de plan d'exécution.

++

[elsuket]

Bonjour,

Effectivement, l'utilité des contraintes de clé étrangère n'est pas à discuter.
En revanche il est nécessaire pour toutes les contraintes qu'elles aient été vérifiées.

En effet, dans le cas où l'on créée une contrainte avec la clause NO CHECK, l'optimiseur n'en tient pas compte.
C'est l'objet du billet que j'ai publié à ce sujet.

On notera au passage que les contraintes CHECK sont compilées : c'est ce que montre, en outre, la colonne objtype de la DMV sys.dm_exec_cached_plans.

@++

[Sergejack]

À partir du moment où le comportement que vous donnez à la/les colonne(s) qui font référence à une ligne d'une autre table est ON DELETE SET NULL/DEFAULT, vous vous retrouvez déjà dans une situation dans laquelle vous avez des lignes sans parent réel et dans laquelle vous devrez penser à des suppressions ultérieures.
Dans cette configuration (je ne vous apprendrai rien), une suppression dans la tablé référée cause immédiatement une MAJ dans la table.
Par ailleurs dans cette situation, vous ne pouvez pas définir une contrainte d'unicité sur ces colonnes (ce qui n'est pas fréquemment utile mais peut l'être).
Néanmoins dans cette situation, vous pouvez faire vos suppressions sans devoir vérifier l'existence de la ligné référée (ce qui resterait rapide mais pê plus bloquant).

Pour résumé (et au risque d'électrifier certaines des hypothèses dans lesquelles vous avez foi) :
Si vous ne supprimer pas en cascade (et par corolaire si vos suppressions sont différés), il y a de nombreux avantages à ne pas définir les contraintes FK.
L'avantage principale à retenir reste évidement la réduction des impacts immédiat des suppressions.
Et le désavantage principale est évidemment qu'il faille connaître la relation (qui n'est plus explicite) et à cet effet peut-être trouver une nomenclature pour la reconnaître (créer des index préfixeé par FKIDX_ par exemple ?).

[Sergejack]

Ce que j'ai dit ne suscite pas bcp de réactions ^^

Soit vous vous dites "MER IL EST FOU", soit "Si c'était vrai, ça se saurait".

Pourtant je suis convaincu de l'intérêt de mon idée et (puisque, je ne vous demande pas d'être d'accord avec) j'aimerais que vous m'indiquiez quelles problèmes, selon vous, je négligerais/sous-estimerais.
Si je fais fausse route, j'ai besoin d'avis pertinents pour le réaliser.

[Rei Ichido]

À partir du moment où le comportement que vous donnez à la/les colonne(s) qui font référence à une ligne d'une autre table est ON DELETE SET NULL/DEFAULT, vous vous retrouvez déjà dans une situation dans laquelle vous avez des lignes sans parent réel et dans laquelle vous devrez penser à des suppressions ultérieures.

Sauf que ces lignes sont immédiatement identifiables, à NULL. Pas du tout la même chose que pointer vers une valeur qui n'existe plus.

[Ptit_Dje]

À partir du moment où le comportement que vous donnez à la/les colonne(s) qui font référence à une ligne d'une autre table est ON DELETE SET NULL/DEFAULT, vous vous retrouvez déjà dans une situation dans laquelle vous avez des lignes sans parent réel et dans laquelle vous devrez penser à des suppressions ultérieures.

Comme le dit Rei Ichido, cela concerne un nombre de lignes restreint et ciblé, les lignes à supprimer par la suite.
Vous pourriez aussi définir un DEFAULT comme étant une valeur préchoisie et existant dans la table parent référant une ligne "A Supprimer" afin de ne pas avoir de lignes orphelines qui se baladent.

Par ailleurs dans cette situation, vous ne pouvez pas définir une contrainte d'unicité sur ces colonnes (ce qui n'est pas fréquemment utile mais peut l'être).

Si, avec un index filtré.

Et le désavantage principale est évidemment qu'il faille connaître la relation (qui n'est plus explicite) et à cet effet peut-être trouver une nomenclature pour la reconnaître (créer des index préfixeé par FKIDX_ par exemple ?).

Et surtout que vous mettez en jeu l'intégrité de vos données.