Exigences : Il existe de nombreuses raisons pour lesquelles la base de données SQL Server provoque des blocages ; en production, tout le monde peut rencontrer des problèmes de blocage, et la cause spécifique peut ne pas être localisée uniquement à partir des informations anormales des blocages applicatifs, il est donc nécessaire d’utiliser des événements d’extension dans SQL Server pour suivre les problèmes d’impasse et enregistrer les détails précis des moments où ces blocages surviennent.
Aperçu étendu de l’événement
Les événements étendus sont une fonctionnalité légère de surveillance des performances qui permet aux utilisateurs de recueillir les données nécessaires pour surveiller et résoudre les problèmes. Cette fonctionnalité vous permet de consulter les détails sur les opérations internes du moteur de données pour la surveillance des performances et la résolution de problèmes.
Avec l’architecture Extended Events (XEvents), les utilisateurs peuvent utiliser autant ou aussi peu de données qu’ils ont besoin pour surveiller, identifier ou dépanner les performances de SQL Server, Azure SQL Database et Azure SQL Managed Instance. Les événements étendus sont très configurables, légers et très évolutifs.
Référence:
La connexion hyperlientérée est visible.
La connexion hyperlientérée est visible.
Causes des blocages
Les blocages sont causés par des verrous concurrents dans une base de données, généralement dans des transactions en plusieurs étapes. Chaque session utilisateur peut avoir une ou plusieurs tâches exécutées en son nom, où chaque tâche peut récupérer ou attendre pour récupérer différentes ressources. Les types de ressources suivants peuvent provoquer un blocage et finalement provoquer un blocage.
- Serrures : Les verrous en attente de récupérer des ressources telles que des objets, des pages, des lignes, des métadonnées et des applications peuvent provoquer des blocages. Par exemple, la transaction T1 possède un verrou partagé (verrou S) sur la ligne r1 et attend d’obtenir un verrou exclusif (verrou X) pour la ligne r2. La transaction T2 a un verrou partagé (verrou S) sur la ligne r2 et attend d’obtenir un verrou exclusif (verrou X) pour la ligne r1. Cela crée une boucle de verrouillage où T1 et T2 attendent l’un pour que l’autre libère la ressource verrouillée.
- Fils de travail : Les tâches qui font la file d’attente pour les fils de travail disponibles peuvent provoquer des blocages. Si une tâche en file d’attente possède une ressource qui bloque tous les threads de travaux, cela entraînera un blocage. Par exemple, après que la session S1 commence une transaction et acquiert un verrou partagé (S-lock) pour la ligne r1, elle passe en veille. Une session active fonctionnant sur tous les threads ouvriers disponibles tente d’obtenir un verrou exclusif (X-lock) pour la ligne r1. Comme la session S1 ne peut pas obtenir un thread de travail, elle ne peut pas valider la transaction ni libérer le verrouillage sur la ligne r1. Cela entraînera une impasse.
- Mémoire : Un blocage peut survenir lorsqu’une requête concurrente attend de récupérer de la mémoire et que la mémoire disponible n’est pas suffisante pour ses besoins. Par exemple, deux requêtes concurrentes (Q1 et Q2) sont exécutées en tant que fonctions définies par l’utilisateur, acquérant respectivement 10 Mo et 20 Mo de mémoire. Si chaque requête nécessite 30 Mo et que la mémoire totale disponible est de 20 Mo, Q1 et Q2 doivent attendre que l’autre libère la mémoire, ce qui entraînera un blocage.
- Requêtes parallèles et ressources liées à l’exécution : Les coordinateurs de traitement, générateurs ou threads consommateurs généralement associés aux ports commutés peuvent se bloquer mutuellement lorsqu’ils contiennent au moins un processus qui ne fait pas partie d’une requête parallèle, ce qui entraîne des blocages. De plus, lorsqu’une requête parallèle démarre son exécution, SQL Server détermine le degré de parallélisme ou le nombre de threads de travail en fonction de la charge de travail actuelle. Un blocage peut survenir s’il y a un changement inattendu dans la charge de travail du système, par exemple lorsqu’une nouvelle requête commence à s’exécuter sur le serveur ou lorsque le système manque de threads de travaux.
- Ressources MARS (Multiple Activity Outcome Set) : Ces ressources sont utilisées pour contrôler l’exécution croisée de plusieurs requêtes d’activité sous MARS.
Référence:
La connexion hyperlientérée est visible.
Journal des événements étendus par blocage
Créez un événement d’extension pour enregistrer les informations de blocage avec la commande suivante :
Lancer une session d’événement de blocage
Arrêter une session d’événement
Supprimer une session d’événement
Requête des données de session d’événement
Bloquages de test
Créer une nouvelle table Tab1 pour les tests, créer deux nouvelles fenêtres d’exécution, et exécuter respectivement les commandes suivantes :
Un blocage se produit comme suit :
Une transaction (ID de processus 68) avec un autre processus est bloquée sur une ressource verrouillée et a été sélectionnée comme victime d’impasse. Veuillez relancer la transaction.
La transaction (ID de processus 68) était bloquée sur les ressources de verrouillage avec un autre processus et a été choisie comme victime de blocage. Relance la transaction. 
Consultez les données de la session événementielle telles que montrées dans la figure suivante :
Les données XML détaillées sont les suivantes :
(Fin)
|
Publié sur 02/03/2024 19:13:04
|
|
|
|
Propriétaire|
Publié sur 02/03/2024 19:14:35
|
