Outil de surveillance des interblocages dans SQL Server

Les interblocages dans SQL Server se produisent lorsque deux transactions ou plus, ou des collections de requêtes SQL, se bloquent mutuellement par une dépendance cyclique. Cela se produit lorsque les transactions maintiennent des verrous sur des ressources dont d’autres transactions ont également besoin, ce qui se traduit par l’application d’un verrou SQL Server sur une table. Une requête SQL sujette à un interblocage ne peut pas aboutir tant que le verrou n’est pas libéré, ce qui est également le cas pour les autres requêtes SQL dans la même situation.

Lorsque vous recevrez une notification d’interblocage dans SQL Server, vous remarquerez qu’une transaction est appelée « victime » et l’autre « survivante ». La victime est la session restaurée et automatiquement sélectionnée par SQL Server en fonction de la priorité de l’interblocage et du coût de la restauration.

Parmi les raisons courantes à l’origine d’un interblocage, on compte une conception imparfaite de la base de données, un manque d’indexation, des niveaux d’isolement inappropriés et des requêtes mal rédigées. Lorsqu’un verrou SQL Server bloque une table, les opérations sont retardées ou même entièrement arrêtées. Finalement, les interblocages peuvent entraîner un arrêt complet du traitement dans votre moteur de base de données.

Le processus de résolution des interblocages dans SQL Server est parfois frustrant et chronophage, surtout en présence de plusieurs interblocages complexes de serveurs. Un interblocage se produit habituellement comme suit : une procédure stockée a verrouillé une ressource A (comme un objet, une page ou une ligne) et attend l’exécution d’une autre procédure pour pouvoir accéder à la ressource B. Mais la deuxième procédure a verrouillé la ressource B et attend également que la première procédure déverrouille la ressource A.

Pour arrêter ce cycle interminable, vous devez rapidement identifier et éliminer la source de l’interblocage.

Selon la situation, plusieurs méthodes permettent de résoudre des interblocages dans SQL Server. SQL Server peut effectuer une détection automatique via un outil de surveillance des verrous. Comme indiqué ci-dessus, dès qu’un interblocage est détecté, SQL Server choisit automatiquement la « victime » et restaure cette transaction. C’est ensuite à vous et à votre équipe d’administration informatique de résoudre les interblocages dans SQL Server.

Voici quelques méthodes courantes pour résoudre les interblocages dans SQL Server :

• Index couvrant : en supprimant entièrement les recherches de signets, un index couvrant peut réduire le risque d’interblocages provoqués par les recherches de signets.
• Index sur des clés étrangères : réduisez les interblocages dans SQL Server liés à l’intégrité référentielle en cascade en créant des index qui correspondent à vos colonnes de clés étrangères. Sans index sur des clés étrangères, les actions en cascade prendront plus de temps et augmenteront le risque d’interblocage.
• Ordre d’accès aux objets : pour créer des interblocages à des fins de test, accédez aux objets de bases de données dans un ordre différent. Pour éviter ces interblocages, faites en sorte que les transactions soient aussi courtes que possible et continuez d’accéder aux objets selon le même ordre logique.
• Niveaux d’isolement des transactions : le niveau d’isolement par défaut des transactions dans SQL Server, READ COMMITTED, peut entraîner des interblocages entre les opérations d’écriture et de lecture de données. Pour éviter cela, utilisez le niveau COMMITTED SNAPSHOT ou SNAPSHOT, dans lequel une opération de lecture ne verrouille pas des objets, mais isole plutôt des versions de lignes.
• Priorité des interblocages : en définissant la priorité d’une transaction comme élevée, normale (par défaut), ou faible, vous pouvez permettre aux sessions en cours de se poursuivre en cas d’interblocage dans SQL Server.
• Traitement des erreurs d’application : l’utilisation de la logique TRY...CATCH vous permet d’identifier le numéro de l’interblocage. Vous pouvez également définir le nombre de nouvelles tentatives autorisées afin de réduire les risques de boucle infinie.

La meilleure façon d’éviter les interblocages dans SQL Server est de recourir à un outil de surveillance des interblocages SQL. Un outil de surveillance des interblocages SQL permet de détecter et de résoudre les interblocages dans SQL Server en fournissant des informations et des fonctionnalités qui améliorent la surveillance des interblocages.

Il existe plusieurs façons de détecter un verrou dans SQL Server sans utiliser un outil de surveillance des interblocages dans SQL. Ces méthodes sont exécutées via SQL Server :

• Indicateurs de trace : grâce aux indicateurs de trace 1204 ou 1222, SQL Server peut surveiller les interblocages. Les indicateurs de trace doivent être actifs à tout moment, ce qui complique la détection proactive des interblocages.
• Compteur de performances : conçu pour indiquer le nombre d’interblocages présents dans SQL Server, il ne fournit toutefois aucun détail sur les transactions de sessions impliquées.
• SQL Server Profiler : également connu sous le nom de trace côté serveur, il peut capturer les détails des interblocages dans SQL Server et les afficher dans un graphique XML. Cette méthode doit également être appliquée en permanence, ce qui vous empêche de collecter des indicateurs de manière proactive.
• Session d’événements étendus : SQL Server exécute par défaut une trace d’événements étendus, mais cette méthode prend du temps, est sujette à des erreurs humaines et nécessite un effort manuel.

Bien que ces méthodes puissent présenter une certaine utilité, elles ne sont pas aussi rapides, faciles et efficaces que l’utilisation d’un outil de surveillance des interblocages pour éviter les interblocages dans SQL Server. Un outil de détection des interblocages SQL tel que SolarWinds SQL Sentry vous permet d’identifier rapidement un interblocage, d’en comprendre les causes premières et de déterminer les mesures à prendre pour éviter qu’il ne se reproduise.

Les fonctionnalités SQL Sentry d’analyse des interblocages (Deadlock Analysis) communiquent des indicateurs détaillés pour vous permettre de résoudre les interblocages des serveurs et de limiter le temps consacré à rétablir les performances optimales de votre base de données. Parmi ces indicateurs, on compte les suivants :

• Point d’interblocage dans SQL Server
• Heure de l’interblocage de la base de données
• SPID victime : ID de session de la victime de l’interblocage
• Hôte victime : station de travail appartenant au thread victime
• Application victime : nom de l’application appartenant au thread victime
• Base de données victime
• Données de texte victime
• Version XML de l’interblocage : version XML de l’interblocage capturé

La zone de détails sur les verrous décompose l’interblocage de la base de données par types de verrous spécifiques, notamment les propriétaires et les processus en attente pour chaque verrou. Parmi les détails, on compte les suivants :

• SPID : ID de session du propriétaire/processus en attente associé
• Plan : ouvre une session Plan Explorer permettant d’afficher le plan de requête associé
• Hôte
• Application
• Base de données
• Utilisation du journal : volume d’espace dans le journal utilisé par le processus
• Priorité de l’interblocage : zéro (0) ou normale est la priorité par défaut
• Temps d’attente : temps d’attente de la disponibilité de la ressource, en millisecondes
• Heure de début de la transaction
• Heure de début du dernier lot
• Heure d’achèvement du dernier lot
• Mode/type de verrou de la ressource
• État de la tâche
• Niveaux d’isolement de la transaction en cours
• Nom de connexion de la session

SolarWinds^® SQL Sentry est conçu pour capturer et examiner les interblocages dans SQL Server, et ce, rapidement et avec précision. Ses fonctionnalités de surveillance des interblocages SQL peuvent vous aider à détecter et à résoudre les interblocages avant qu’ils ne compromettent les opérations de votre base de données, puis à obtenir des informations exploitables sur la manière de prévenir la réapparition d’interblocages similaires à l’avenir.

SQL Sentry utilise une session d’événements étendus modifiée pour capturer les détails de la surveillance. SQL Sentry affiche les interblocages détectés en bas de l’onglet Trends (Tendance), où vous pouvez trouver d’autres informations essentielles sur l’interblocage dans SQL Server, telles que les suivantes :

• L’heure de l’apparition de l’interblocage
• Le nombre de sessions impliquées dans l’interblocage
• Les objets impliqués
• Le nom d’utilisateur et le nom de la machine

Il existe dans SQL Sentry un autre indicateur appelé Victim Impact (Impact sur la victime) qui affiche la durée d’exécution et de consommation des ressources d’une transaction sujette à un interblocage. La durée d’une transaction étant synonyme de ressources gaspillées, la durée de la transaction de la victime d’une requête sujette à un interblocage SQL est un bon indicateur de l’impact de l’interblocage sur SQL Server et les utilisateurs finaux.

Outre l’onglet Trends (Tendances), vous pouvez accéder sur d’autres pages à plus d’informations et d’actions sur les interblocages dans SQL Server. Par exemple, SQL Sentry vous permet de configurer des sessions autres que celles par défaut via la page Advanced Properties (Propriétés avancées). Dans SQL Sentry, la page Deadlocks Details (Détails des interblocages) affiche le récapitulatif de l’interblocage qui indique l’horodatage, les objets, les programmes et le nombre de sessions pour le survivant et la victime. Vous pouvez également y trouver des données sur les ressources sujettes à un interblocage, notamment le nom de l’index, l’identifiant de la base de données et celui du verrou.

SQL Sentry vous permet d’exporter des indicateurs clés et des détails que vous pouvez utiliser avec SQL Server Management Studio (SSMS). SSMS est un logiciel fourni par Microsoft qui peut migrer avec SQL Sentry et d’autres bases de données. En prenant en charge l’intégration de SSMS, SQL Sentry peut améliorer la communication et optimiser la résolution des interblocages. Vous pouvez également envoyer les détails de l’interblocage par e-mail via SQL Sentry et inclure en pièce jointe le fichier exporté.

Exploitez au mieux SQL Sentry pour surveiller les divers aspects des opérations de SQL Server et obtenir une vue d’ensemble des éléments affectant les performances des bases de données.