Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Quels sont les problèmes de performance les plus fréquent ? Partie 2/5 : Contention lié aux verrous / Interblocages (Deadlock)

Sans aucun doute le second problème le plus fréquent sur un serveur de base de données type SQL Server. Ce problème augmente avec la charge que subit le serveur et souvent la conséquence d'autres problèmes sous jacent.

Partie 2 : Contention lié aux verrous / Interblocages (Deadlock)

Les verrous ont pour rôle d'éviter les collisions de lecture / écriture et certains comportement non souhaités durant les lectures. Malheureusement quand beaucoup de lectures et d'écritures ont lieux simultanément cela à tendance à bloquer le serveur, ou tout du moins à mettre en file d'attente ces derniers jusqu'à obtention du dit verrou.

Dans des cas sévères et lorsque le code n'a pas été écrit en vue de les éviter les problèmes d'inter blocage (deadlock) surviennent et mettent fin à certaines connexions. A noter, les interblocages peuvent aussi se produire sans que des problèmes de verrous soient avérés et important, si les temps de lectures et d'écritures sont important cela provoquera aussi ce type de problème sans forcément voir apparaitre le premier. Le phénomène d'escalade de verrou peut aussi se produire (lock escalation) qui permet à SQL Server d'obtenir un verrou d'une granularité pus importante lorsqu'un grand nombre de verrou est posé sur une table.

Le premier problème se manifeste par des ralentissements des requêtes, soit de manière franche et provoque des Timeout des requêtes, soit de manière plus insidieuse et ajoute quelques milliseconde à quelques secondes à chaques requêtes. Ce dernier cas est très dur à détecter et on se rendra compte du problème que si ces temps deviennent tellement importants qu'elles provoqueront les Timeouts. Il y a bien entendu des compteurs de performance à suivre pour détecter tel ou tel problème.

Les solutions consistent à écriture soigneuse des transactions en limitant leur durée (on regroupera si possible les écritures ensemble en démarrant la transaction juste avant et en la terminant juste après) et ordonnant convenablement les opérations pour éviter les interblocages (choisissez un ordre arbitraire comme l'ordre alphabétique des tables ou un ordre plus logique). Pour l'escalade de verrou il y a des solutions à manier avec précaution tel que : http://blogs.codes-sources.com/christian/archive/2008/06/11/sql-server-sql-server-2008-promotion-de-verrou-lock-escalation-statistiques-sur-les-blocages.aspx. D'une manière générale il est conseiller d'avoir des transactions impactant un faible nombre d'enregistrements pour les éviter. Une indexation correcte est elle aussi indispensable pour limiter le plus possible les problèmes de blocage. Même si je n'aime pas cette solution l'utilisation d'un niveau d'isolation plus bas est envisageable (NOLOCK, http://blogs.developpeur.org/christian/archive/2007/03/08/sql-server-les-verrous-et-l-utilisation-de-nolock.aspx ), c'est une solution très fréuquente mise en œuvre sur les version d'avant SQL Server 2005 qui a vu arriver les mode de concurrence d'accès dit optimistes que sont les mode SNAPSHOT (voir les améliorations au cours des versions : http://blogs.developpeur.org/christian/archive/2008/02/29/sql-server-gestion-des-transactions-verrous-et-concurrence-d-acc-s-aux-donn-es.aspx).

Comme je le disais plus haut ce problème découle souvent d'un autre, un problème de disque engendre très souvent comme « symptôme » un problème de blocage.

Les autres parties

Partie 1
http://blogs.codes-sources.com/christian/archive/2009/04/07/sql-server-quels-sont-les-problemes-de-performance-les-plus-frequent-partie-1-sur-5-le-systeme-disque.aspx

PS : Je mettrais des requêtes et des compteurs de performance pour diagnostiquer ces problèmes dans des billets séparés après cette série d'article… Je manque un peu de temps pour le faire en ce moment :o(

Bon dépannage…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié lundi 27 avril 2009 21:47 par christian

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Créer un périphérique Windows To Go 10 ! par Blog de Jérémy Jeanson le 11-21-2014, 04:54

- RDV à Genève le 12 décembre pour l’évènement “SharePoint–Office 365 : des pratiques pour une meilleure productivité !” par Le blog de Patrick [MVP Office 365] le 11-19-2014, 10:40

- [IIS] Erreurs web personnalisées par Blog de Jérémy Jeanson le 11-19-2014, 00:00

- BDD/TDD + Javascript par Fathi Bellahcene le 11-16-2014, 16:57

- Sécuriser sans stocker de mots de passe par Blog de Jérémy Jeanson le 11-15-2014, 08:58

- Où télécharger la preview de Visual Studio 2015 ? par Blog de Jérémy Jeanson le 11-13-2014, 21:33

- Les cartes sont partout ! par Le blog de Patrick [MVP Office 365] le 11-13-2014, 17:26

- [ #Office365 ] Courrier basse priorité ! par Le blog de Patrick [MVP Office 365] le 11-12-2014, 08:56

- [Oracle] Fichier oranfsodm12.dll absent du package client par Blog de Jérémy Jeanson le 11-10-2014, 20:44

- [ #Office365 ] Le chapitre 1 des Groupes est écrit, et alors ? par Le blog de Patrick [MVP Office 365] le 11-10-2014, 20:23