Analyse des blocages dans SQL Server

Surveillez SQL Server et identifiez les causes premières des blocages

Démo interactive En savoir plus

Surveillance des blocages dans SQL Server

Résolution des goulots d’étranglement

Exécution de rapports sur les blocages

Optimisation de la visibilité

Identification des principaux bloqueurs

Affichage des bloqueurs dans SQL Server

SQL Server Blocking 0 Features Array Item - features item image

Dans SQL Server, un blocage est un comportement normal qui permet de contrôler les accès simultanés aux données. Parfois, les blocages peuvent nuire aux performances et il n’est pas toujours facile de les localiser et de les neutraliser. SQL Sentry^® garantit une grande visibilité sur les blocages dans SQL Server. Il capture et affiche les détails des blocages, y compris les types d’attente et les ressources, les hôtes, les bases de données et les instructions exécutées, selon des seuils que vous spécifiez. Vous pouvez afficher les chaînes de blocage en temps réel ou après coup.

Télécharger pour essai Lien par e-mail pour essai

Version complète, 14 jours

Suivi et résolution des requêtes qui provoquent des blocages

SQL Server Blocking 1 Features Array Item - features item image

SQL Sentry facilite et accélère la localisation des bloqueurs dans SQL Server, ainsi que le processus de dépannage. Un tableau de bord SQL Sentry identifie les chaînes de blocage dans SQL Server via une vue hiérarchique à codes de couleurs qui affiche les instructions en cours d’exécution, exécutées ou bloquées. Vous pouvez même arrêter les processus à l’origine des blocages directement dans SQL Sentry. En accédant à des données sur les performances historiques, vous pouvez afficher les changements clés effectués dans SQL Server au fil du temps, et ainsi procéder à un dépannage plus efficace.

Télécharger pour essai Lien par e-mail pour essai

Version complète, 14 jours

Restez informé grâce aux rapports de SQL Sentry

SQL Server Blocking 2 Features Array Item - features item image

Plutôt que de vérifier manuellement les blocages dans SQL Server, vous pouvez utiliser SQL Sentry pour exécuter facilement les rapports dont vous avez besoin sur les blocages dans SQL. SQL Sentry intègre plus de 25 rapports de performance préformatés qui vous permettent de comprendre en quelques minutes les performances de SQL Server. Grâce à ces rapports, vous pouvez établir des références, identifier avec précision les goulots d’étranglement et mieux comprendre globalement les performances de SQL Sentry, en temps réel et au fil du temps.

Télécharger pour essai Lien par e-mail pour essai

Version complète, 14 jours

Recevez des alertes en temps réel sur les blocages dans SQL Server

SQL Server Blocking 3 Features Array Item - features item image

Le processus de surveillance des performances de SQL Server peut être chronophage, mais, de par sa conception, SQL Sentry peut le faciliter grâce à des alertes intégrées. SQL Sentry propose des alertes lorsque des blocages et interblocages dont détectés dans SQL. Il peut également vous avertir en présence de conditions préconfigurées pour vous permettre de résoudre immédiatement les problèmes.

Télécharger pour essai Lien par e-mail pour essai

Version complète, 14 jours

Identifiez les principaux bloqueurs en analysant le temps d’attente total

Analyze MySQL - Analytics Tools for MySQL Database Use case type 1 2 Features Array Item - features item image

SolarWinds^® SQL Sentry est conçu pour collecter des données sur les blocages. Il offre une grande visibilité sur le principal bloqueur et le temps d’attente total qui en résulte. Cela permet aux administrateurs de bases de données d’explorer les principales données à l’origine des blocages et de constater leur impact sur les temps de réponse des bases de données. Le logiciel peut également identifier les processus qui attendent le plus, ce qui permet aux administrateurs de bases de données d’identifier rapidement et avec précision les principaux bloqueurs, et d’utiliser ces données pour optimiser au mieux les requêtes qui réduisent le plus les blocages.

Télécharger pour essai Lien par e-mail pour essai

Version complète, 14 jours

En savoir plus sur les blocages dans SQL Server

Vous demandez-vous…

Qu’est-ce qu’un blocage dans SQL Server ?
Quelle est la différence entre un interblocage et un blocage dans SQL Server ?
Comment puis-je détecter un blocage problématique ?
Comment puis-je dépanner un blocage dans SQL Server ?
Pourquoi est-il important de détecter les 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.

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 :

Exécution d’instructions SQL inefficaces lors du verrouillage de la session de base de données
Perte de sessions en veille au niveau de l’imbrication des transactions
Incapacité des applications à récupérer toutes les lignes de résultats
Restauration de données par des sessions
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.

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.
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 :
Exécution d’instructions SQL inefficaces lors du verrouillage de la session de base de données
Perte de sessions en veille au niveau de l’imbrication des transactions
Incapacité des applications à récupérer toutes les lignes de résultats
Restauration de données par des sessions
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.