Analyse des blocages dans SQL Server

Des blocages se produisent lorsque deux processus (ou plus) doivent accéder simultanément à la même ressource. Lorsqu’un processus demande à accéder à une ressource, il se voit attribuer un verrou qui force les autres processus nécessitant cette ressource à attendre l’exécution du premier processus. Dans la plupart des cas, le deuxième processus attend que le premier libère le verrou. Les opérations peuvent alors se poursuivre normalement. Cela prend habituellement quelques secondes.

Toutefois, il arrive que l’exécution du premier processus se prolonge, auquel cas le deuxième processus (et éventuellement d’autres) doit attendre avant de pouvoir utiliser cette ressource, ce qui risque d’affecter les performances de l’application et l’expérience de l’utilisateur final. La durée d’une requête et le contexte d’une transaction déterminent la durée de maintien des verrous correspondants.

Sachez toutefois que, dans SQL Server, les blocages ne sont pas tous indésirables. En fait, le phénomène de blocage est normal. Après tout, SQL Server est un système de concurrence basé sur des verrous dans lequel seul un processus à la fois peut accéder aux ressources. Certains chevauchements pourront évidemment se produire à un moment ou à un autre. Les blocages qui posent problème sont ceux qui prennent plus de temps à se résoudre et qui créent des chaînes de blocage.

En outre, les types de verrous ne bloquent pas tous automatiquement tous les autres verrous. Certains types de verrous sont plus restrictifs et ainsi plus susceptibles de provoquer des blocages. Parmi les types de verrous, on compte les suivants :

• Verrous exclusifs : ces verrous sont attribués lorsqu’un processus doit modifier des données. Cela permet d’empêcher que deux processus modifient simultanément des données.
• Verrous de schéma : les verrous de schéma sont attribués pour les processus qui dépendent d’un schéma cohérent pendant les opérations.
• Verrous partagés : ces verrous sont attribués pour les opérations de lecture. Ils sont moins restrictifs que d’autres types de verrous et entraînent rarement des blocages.
• Verrous de mise à jour : les verrous de mise à jour sont attribués pour les mises à jour et habituellement utilisés pour des mises à jour qui se produisent en plusieurs étapes.

Certains types de verrous sont plus compatibles avec d’autres. Par exemple, alors que les verrous exclusifs ne sont compatibles avec aucun autre type de verrou et provoquent un blocage si un autre processus nécessite une ressource, des verrous partagés et de mise à jour peuvent être appliqués simultanément à la même ressource.

On peut facilement confondre les interblocages et les blocages, car les deux se produisent lorsque deux processus ont besoin de ressources spécifiques, sans pouvoir y accéder, pour exécuter une requête. Toutefois, il existe une différence entre les blocages et les interblocages.

Dans SQL Server, on parle de blocage lorsque deux (ou plus) processus ont besoin de la même ressource, car seul un processus à la fois peut accéder à une ressource. Cela signifie que si un processus utilise une ressource, le processus suivant n’y aura pas accès tant que le premier processus n’aura pas fini de l’utiliser.

En revanche, des interblocages se produisent lorsque des processus tentent d’accéder à des ressources différentes. Par exemple, le premier processus peut verrouiller la ressource A pendant que le deuxième processus verrouille la ressource B. Si le premier processus doit verrouiller la ressource B pour s’exécuter et que le deuxième doit verrouiller la ressource A, les deux opérations seront interrompues, car elles s’attendent mutuellement. On constate donc une impasse.

Heureusement, dans bien des cas, SQL Server peut résoudre automatiquement des interblocages en restaurant un processus pour permettre à l’autre de se poursuivre sans obstacle. SQL Server redémarre alors le deuxième processus lorsque le premier a fini d’utiliser la ressource.

Deux processus vont nécessairement avoir besoin simultanément de la même ressource à un moment ou à un autre, et un blocage est souvent résolu dès que le premier processus est terminé. Toutefois, un blocage peut poser problème dans certains cas.

Pour vérifier les blocages dans SQL Server, vous pouvez utiliser une requête qui identifie le SPID à l’origine du blocage. Il vous suffit d’utiliser les vues de gestion dynamique de SQL Server et d’exécuter une requête via la vue de gestion dynamique sys.dm_exec_requests. Cette opération renvoie une liste des SPID à l’origine d’un blocage et identifie les SPID bloqués, ainsi que le type d’attente et sa durée.

Vous pouvez également utiliser SQL Server Management Studio pour vérifier les blocages dans SQL Server et identifier le début de la chaîne de blocage. Vous devez sélectionner Server Object, Reports, Standard Reports (Objet Server, rapports, rapports standard), puis Activity — All Blocking Transactions (Activité — Toutes les transactions de blocage). SQL Server Management Studio renvoie alors un rapport identifiant la transaction au début de la chaîne de blocage et toutes les transactions actuellement bloquées par cette première transaction.

SQL Sentry facilite encore plus l’identification des blocages problématiques en surveillant continuellement les blocages dans SQL Server. SQL Sentry Event Calendar vous permet d’afficher les blocages à côté d’autres événements. En outre, SQL Sentry propose des analyses fiables des blocages et peut en capturer les détails selon les seuils de durée spécifiques que vous avez appliqués. Par exemple, pour obtenir des informations sur chaque blocage qui dure plus de dix secondes, SQL Sentry enregistre automatiquement des données (telles que l’instruction exécutée, l’hôte, la connexion, la base de données, ainsi que les types d’attente et les ressources) concernant le blocage en question et vous permet d’afficher ces informations détaillées en temps réel ou ultérieurement.

Grâce à sa vue hiérarchique et aux nœuds à codes de couleurs, SQL Sentry permet de comprendre immédiatement une chaîne de blocage. Vous saurez tout de suite si une instruction est en cours d’exécution, exécutée ou bloquée. Vous pouvez également voir la relation entre le SPID à l’origine du blocage et le SPID bloqué dans la chaîne, ce qui facilite grandement l’analyse.

Une fois un blocage problématique identifié, il suffit de le résoudre. Parmi les scénarios de blocage et solutions courants que vous pourrez rencontrer, on compte les suivants :

• Une requête dont la durée d’exécution se prolonge : si une requête met trop de temps à s’exécuter et pose un verrou agressif pour empêcher d’autres processus de lire ou de modifier les mêmes données, il peut en résulter un blocage problématique dans SQL Server. Pour résoudre le problème, localisez tout d’abord la requête qui bloque SQL Server en analysant les vues de gestion dynamique ou en consultant votre plateforme de surveillance. Optimisez ensuite votre requête et vos plans d’exécution.
• Une application qui utilise une transaction complexe exécutant des instructions ou des lots avant la validation : si une application exécute plusieurs instructions ou lots avant la validation (enregistrement des données), d’autres processus ne pourront pas accéder à la ressource au même moment. Dans ce cas, vous devrez également identifier la requête au début de la chaîne de blocage, puis en ajuster les performances.
• Une transaction est abandonnée et ne libère pas les verrous : si la requête d’une application expire ou est annulée, le SPID met fin à la requête et au lot. Toutefois, il ne va pas automatiquement restaurer ou valider la transaction et cela peut provoquer un blocage. Pour résoudre ce problème, vous devez identifier le SPID qui garde la transaction ouverte et procéder à une restauration ou utiliser la commande T-SQL kill.
• Un SPID dont l’application cliente n’a pas récupéré toutes les lignes de résultats jusqu’au bout : lorsqu’une application ne récupère pas immédiatement toutes les lignes de résultats jusqu’au bout, les verrous sont conservés sur la table, ce qui entraîne un blocage. Pour résoudre ce problème, vous devez réécrire le code de l’application pour qu’elle puisse extraire jusqu’au bout toutes les lignes de résultats. Avant tout, pour éviter ce problème, limitez les applications au comportement instable à un rapport distinct ou à une base de données d’aide à la décision.

La bonne nouvelle, c’est que SQL Sentry peut vous aider, quel que soit le type de scénario de blocage qui se présente. SQL Sentry collecte et conserve les données sur les blocages et les interblocages. Ainsi, non seulement, vous bénéficiez d’une vue complète des performances actuelles, mais vous pouvez consulter des données historiques. En outre, vous pouvez voir les problèmes de blocage en temps réel et arrêter les processus qui en sont à l’origine, dans SQL Sentry et en un seul clic. Grâce à SQL Sentry, vous pouvez comprendre pourquoi des blocages se produisent et modifier vos requêtes et transactions pour empêcher qu’ils ne se reproduisent.

SQL Sentry peut même contrecarrer automatiquement des conditions de blocage dans SQL quand vous êtes absent. Il vous suffit de préconfigurer SQL Sentry pour qu’il exécute des actions spécifiques lorsque certaines conditions sont vérifiées.

Les administrateurs de bases de données doivent impérativement savoir comment détecter les blocages dans SQL Server, car ces derniers risquent de les empêcher d’accéder à des ressources critiques pour l’entreprise et de gérer efficacement leurs bases de données. Toutefois, le processus de dépannage des blocages peut être complexe et chronophage. Voici les cinq causes les plus courantes des blocages :

1. Exécution d’instructions SQL inefficaces lors du verrouillage de la session de base de données
2. Perte de sessions en veille au niveau de l’imbrication des transactions
3. Incapacité des applications à récupérer toutes les lignes de résultats
4. Restauration de données par des sessions
5. Abandon de sessions en raison d’une panne ou d’une déconnexion de l’application cliente

Toutefois, dans une grande entreprise dont les bases de données sont encore plus volumineuses, le blocage d’une session peut avoir bien d’autres origines. Un administrateur de bases de données aurait de grandes difficultés à accéder au début de la chaîne de blocage et à identifier ces raisons manuellement.

Les outils standard de surveillance de SQL Server afficheront les blocages des bases de données et les attentes dues aux blocages, mais ils n’identifieront pas les principaux bloqueurs à l’origine de la chaîne de blocage. Plus les blocages sont nombreux, plus les applications sont lentes, mais comment un administrateur de bases de données peut-il déterminer si l’augmentation des blocages révèle l’inefficacité des bases de données ou justifie les temps prolongés de réponse des applications ?

Des outils comme SQL Sentry peuvent aider les administrateurs de bases de données à comprendre l’origine d’une chaîne de blocage dans SQL Server, à supprimer rapidement les blocages et à reprendre le travail.

Il est important de détecter des blocages lorsque des bloqueurs affectent activement les performances des bases de données, mais également pour d’autres raisons. Puisque les verrous et les blocages sont des sous-produits nécessaires liés à l’utilisation de SQL Server, les administrateurs de bases de données doivent s’efforcer proactivement de surveiller les problèmes de blocage ou de réparer le code à l’origine des bloqueurs inactifs. Un des aspects clés des pratiques conseillées en termes de gestion des bases de données consiste à utiliser un logiciel de détection des bloqueurs, et ce, même à un stade précoce.