Comment puis-je capturer des informations à propos d'un blocage sur mon instance DB Amazon RDS exécutant SQL Server ?

Date de la dernière mise à jour : 06/07/2021

Je dispose d'une instance Amazon Relational Database Service (Amazon RDS) qui exécute SQL Server. Je souhaite obtenir plus de détails à propos d'un blocage sur mon instance DB RDS.

Solution

Un blocage se produit lorsque deux sessions ou plus, chacune avec une ressource verrouillée, tentent d'accéder à la ressource verrouillée de l'autre session. Le résultat est une « chaîne circulaire » dans laquelle aucune des sessions ne peut continuer à s'exécuter jusqu'à ce que l'une des sessions libère ses verrous et autorise l'autre session à accéder à la ressource verrouillée. Le détecteur de blocage de SQL Server résout cette situation en mettant fin à l'une des sessions à l'aide d'un mécanisme basé soit sur les ressources soit sur les coûts. Ensuite, le détecteur annule toutes les modifications liées à cette session. Une fois l'une des sessions fermée, les verrous détenus par celle-ci sont relâchés et l'autre session est autorisée à continuer. Pour plus d'informations, consultez la documentation Microsoft concernant les Blocages.

Vous pouvez capturer des informations relatives à un événement de blocage sur votre instance DB en utilisant l'une des méthodes suivantes.

Activer les indicateurs de trace

Vous pouvez activer les indicateurs de trace de blocage (1204,1222). Les indicateurs de trace sont utilisés pour personnaliser les comportements SQL Server, tels qu'une surveillance supplémentaire en capturant des informations de blocage dans les journaux d'erreurs SQL Server.

  • L'indicateur de trace 1204 fournit des informations de blocage sur chacun des nœuds impliqués dans l'interblocage.
  • L'indicateur de trace 1222 fournit des informations de blocage plus détaillées que l'indicateur de trace 1204, au format XML.

Vous pouvez activer les deux indicateurs de trace pour obtenir deux représentations différentes pour chaque événement de blocage. Pour configurer cela, consultez Comment recevoir une notification lorsqu'un événement de blocage se produit sur mon instance de base de données Amazon RDS for SQL Server ? Une fois les indicateurs de trace activés, vous pouvez consulter les journaux d'erreurs SQL Server pour plus d'informations sur l'événement de blocage.

Utiliser la session system_health

Les événements étendus sont un système léger d'analyse des performances qui vous aide à collecter des données dans le but d'analyser et résoudre les problèmes liés à SQL Server. La session d'événements étendus system_health est incluse dans SQL Server et est activée par défaut. La session s'initie automatiquement lorsque votre moteur de base de données SQL Server démarre et collecte les informations de base sur l'intégrité du serveur. Vous pouvez utiliser ces informations pour résoudre les problèmes de performances et analyser les blocages au sein de votre moteur de base de données. La session d'événement étendue system_health utilise deux cibles, le fichier d'événement et la mémoire tampon en anneau, pour stocker les données. Par défaut, la taille de chaque fichier est de 5 Mo et le nombre maximum de fichiers de substitution est de quatre, ce qui ajoute jusqu'à 20 Mo de données d'événements étendues system_health. Cependant, pour les versions SQL 2016, 2017 et 2019, la taille des fichiers individuels est portée à 100 Mo et le nombre maximum de fichiers est porté à 10, ce qui représente jusqu'à 1 Go de données. La mémoire tampon en anneau est une structure de données spéciale dans la mémoire, qui stocke les données sur la base du premier entré, premier sorti (FIFO). La mémoire cible du tampon en anneau ne peut pas dépasser 4 Mo dans Amazon RDS for SQL Server. Par conséquent, sur une instance occupée, la session system_health peut faire pivoter des événements.

Utilisez la session system_health pour récupérer des informations de blocage sur votre instance DB avec Microsoft SQL Server Management Studio (SSMS) ou Transact-SQL (T-SQL).

Pour récupérer les informations de blocage à l'aide de SSMS, procédez comme suit :

  1. Ouvrez SSMS.
  2. Dans l'Explorateur d'objets, choisissez Gestion, puis Evénements étendus.
  3. Choisissez Sessions.
  4. Recherchez la session system_health, puis double-cliquez sur package0.event_file pour ouvrir le fichier d'événement étendu.
  5. Une fois le contenu du fichier chargé, dans le menu SSMS, choisissez Evénements étendus.
  6. Choisissez Filtres.
  7. Dans la fenêtre Filtre, procédez comme suit :
    Pour Champ, sélectionnez le nom.
    Pour Opérateur, sélectionnez contient.
    Pour Valeur, sélectionnez blocage (deadlock).
  8. Choisissez OK. Vous pouvez voir les événements avec des blocages.
  9. Choisissez l'événement que vous souhaitez afficher, puis cliquez sur l'onglet Blocage pour afficher le graphique.

-ou-

Pour récupérer les informations de blocage à l'aide de T-SQL, procédez comme suit :

1.    Exécutez une requête similaire à la suivante pour afficher la liste des blocages :

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
    SELECT XEvent.query('.') AS XEvent
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        INNER JOIN sys.dm_xe_sessions s 
        ON s.address = st.event_session_address
        WHERE s.NAME = 'system_health'
        AND st.target_name = 'ring_buffer'
        ) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS source;

2.    Choisissez le format de sortie de blocage XML pour ouvrir le fichier XML dans une nouvelle fenêtre.

3.    Enregistrez le fichier XML à l'aide de l'extension de fichier .XDL. Cela convertit le XML en format graphique.

4.    Accédez à l'emplacement du fichier, puis ouvrez le fichier .XDL dans SSMS pour afficher le graphique de blocage.

Vous pouvez exécuter la requête T-SQL pour récupérer des informations de blocage à partir de ring_buffer. La cible ring_buffer conserve les données d'événements en mémoire. Ces informations sont disponibles uniquement tant que l'instance n'a pas été redémarrée. Lorsque vous redémarrez, ces informations sont purgées.

Utiliser une session d'événements étendus xml_deadlock_report

Vous pouvez créer une session d'événements étendue en sélectionnant l'événement xml_deadlock_report pour capturer des blocages. La sélection d'un fichier d'événements comme cible enregistre les événements dans le fichier. Ce fichier peut être analysé plus tard. Pour créer une session d'événements étendue, vous pouvez utiliser SSMS ou T-SQL. Une fois la session créée, utilisez la pour récupérer des informations sur un blocage sur votre instance DB à l'aide de Microsoft SQL Server Management Studio (SSMS) ou Transact-SQL (T-SQL).

Pour créer une session d'événements étendue à l'aide de SSMS, procédez comme suit :

  1. Ouvrez SSMS.
  2. Dans l'Explorateur d'objets, choisissez Gestion, puis Evénements étendus.
  3. Choisissez (cliquez avec le bouton droit) Sessions, puis Assistant Nouvelle Session.
  4. Pour Nom de session, entrez le nom de votre session, puis cliquez sur Suivant.
  5. Sur la page Choisir un modèle, sélectionnez Ne pas utiliser de modèle.
  6. Cliquez sur Suivant pour ouvrir la page Assistant Nouvelle Session.
  7. Dans la Bibliothèque d'événements, sélectionnez xml_deadlock_report, puis Suivant.
  8. Dans la page Capture des champs globaux, sélectionnez des valeurs communes à tous les événements.
    Remarque : Sélectionnez le champ sql_text pour voir la requête qui a provoqué le blocage.
  9. Cliquez sur Suivant.
  10. Dans la page Définir les filtres d'événements de session, créez des filtres d'événements pour limiter les données que vous souhaitez capturer.
  11. Cliquez sur Suivant.
  12. Dans la page Spécifier le stockage des données de session, sélectionnez Enregistrer les données dans un fichier pour analyse ultérieure et Travailler uniquement avec les données les plus récentes.
  13. Cliquez sur Terminer.

Vous pouvez désormais voir votre nouvelle session dans le dossier Sessions dans SSMS. Cliquez avec le bouton droit de la souris sur la session, puis cliquez sur Démarrer la session.

-ou-

Exécutez une requête similaire à la suivante pour créer une session d'événements étendue à l'aide de T-SQL :

CREATE EVENT SESSION [Deadlock_detection] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'D:\rdsdbdata\Log\Deadlock',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO     
-- Start the event session  
ALTER EVENT SESSION Deadlock_detection ON SERVER  
STATE = start;  
GO

Pour récupérer les informations de blocage à l'aide de Microsoft SSMS, procédez comme suit :

  1. Ouvrez SSMS.
  2. Dans l'Explorateur d'objets, choisissez Gestion, puis Evénements étendus.
  3. Choisissez Sessions.
  4. Recherchez la session d'événements étendue que vous avez créée précédemment, puis double-cliquez sur package0.event_file pour ouvrir le fichier d'événement étendu.
  5. Une fois le contenu du fichier chargé, choisissez l'événement que vous souhaitez afficher, puis cliquez sur l'onglet Blocage pour afficher le graphique.

-ou-

Exécutez une requête similaire à la suivante dans T-SQL pour afficher la liste des blocages :

SELECT * FROM sys.fn_xe_file_target_read_file('d:\rdsdbdata\log\deadlock*.xel', null, null, null)

Documentation Microsoft pour les Outils d'information de blocage

Cet article vous a-t-il été utile ?


Besoin d'aide pour une question technique ou de facturation ?