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

Solution

Un blocage se produit lorsque deux sessions ou plus, chacune comprenant une ressource verrouillée, tentent d'accéder à la ressource verrouillée de l'autre session. Il en résulte une « chaîne circulaire ». Dans ce cas, aucune des sessions ne continue à s'exécuter tant que :

• l'une des sessions libère ses verrous,
• ce qui permet à l'autre session d'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é sur les ressources ou 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 appliquant 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 permettent de 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 le blocage.
• 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 ces options, consultez Comment recevoir une notification lorsqu'un événement de blocage se produit sur mon instance DB Amazon RDS SQL Server ? Lorsque les indicateurs de trace sont activés, vous pouvez consulter les journaux d'erreurs SQL Server pour obtenir plus d'informations sur l'événement de blocage.

Utiliser la session system_health

Les événements étendus sont un système léger de surveillance des performances qui vous permet de collecter des données dans le but de surveiller et de résoudre les problèmes dans SQL Server. La session d'événements étendus system_health est incluse dans SQL Server et est activée par défaut. La session démarre automatiquement lorsque votre moteur de base de données SQL Server démarre et collecte les informations de base sur l'état 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énements étendus 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 maximal de fichiers de substitution est de 4. Cela permet d'ajouter jusqu'à 20 Mo de données d'événements étendus system_health. Pour SQL Server 2016, 2017 et 2019, la taille des fichiers individuels est de 100 Mo et le nombre maximal de fichiers est de 10. Cela permet d'ajouter 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 principe 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 Object Explorer (Explorateur d'objets), sélectionnez Management (Gestion), puis Extended Events (Événements étendus).
3. Sélectionnez 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 fichier XML au format graphique.

4.    Accédez à l'emplacement du fichier, puis ouvrez le fichier .xdl dans SSMS pour afficher le graphe 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 étendus, vous pouvez utiliser SSMS ou T-SQL. Une fois la session créée, vous pouvez récupérer des informations liées à un blocage sur votre instance DB. Pour ce faire, vous pouvez utiliser SSMS ou T-SQL.

Pour créer une session d'événements étendus à 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 étendus à 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 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)

Informations connexes

Documentation Microsoft pour les outils d'informations de blocage