Comment puis-je collecter des informations concernant un blocage sur mon instance de base de données Amazon RDS qui s'exécute sur SQL Server ?

Brève description

Votre instance de base de données rencontre un blocage lorsqu’au moins deux sessions, chacune avec une ressource verrouillée, tentent d'accéder à la ressource verrouillée de l'autre session. Lorsqu'un blocage se produit, aucune session ne peut continuer à fonctionner tant que l'une des sessions n'a pas libéré ses verrous.

Pour résoudre un blocage, le détecteur de blocages SQL Server utilise un mécanisme basé sur les ressources ou sur les coûts pour mettre fin à l'une des sessions. Ensuite, le détecteur annule toutes les modifications liées à cette session. Par défaut, le moteur de base de données SQL Server choisit la session qui exécute la transaction la moins coûteuse à annuler en tant que victime du blocage. À la fin de l'une des sessions, les verrous détenus par la session sont libérés et l'autre session est autorisée à continuer. Pour en savoir plus, consultez la page Blocages sur le site Web de Microsoft.

Résolution

Pour capturer des informations concernant un événement de blocage sur votre instance de base de données, vous pouvez utiliser des indicateurs de suivi, une session system_health ou la session d'événements étendus xml_deadlock_report.

Activer les indicateurs de suivi

Vous pouvez activer les indicateurs de suivi de blocage (1204,1222). Vous utilisez des indicateurs de suivi pour personnaliser le comportement de SQL Server afin de capturer les informations relatives aux blocages dans les journaux d'erreurs de SQL Server.

Remarque : Il est recommandé d'éviter d'utiliser ces indicateurs de suivi avec des instances de charge de travail hautes performances présentant des blocages, car cela peut entraîner des problèmes de performances. Créez plutôt une session d’événements étendus pour capturer les informations sur les événements de blocage.

• L'indicateur de suivi 1204 fournit des informations de blocage sur chacun des nœuds concernés par le blocage.
• L'indicateur de suivi 1222 fournit des informations de blocage plus détaillées que l'indicateur de suivi 1204 au format XML.

Pour plus d'informations, consultez la page Indicateur de suivi 1204 et Indicateur de suivi 1222 sur le site Web de Microsoft.

Activez les deux indicateurs de suivi pour obtenir deux représentations différentes pour chaque événement de blocage via un groupe de paramètres personnalisé dans RDS for SQL Server. Pour configurer cela, consultez la section Comment puis-je recevoir une notification lorsqu'un événement de blocage se produit sur mon instance de base de données Amazon RDS for SQL Server ? Après avoir activé les indicateurs de suivi, vous pouvez consulter les journaux d'erreurs de SQL Server pour plus d'informations sur l'événement de blocage.

Utiliser la session system_health

Vous pouvez utiliser des événements étendus avec Amazon RDS for SQL Server pour collecter des données afin de surveiller et de résoudre les problèmes liés à SQL Server. La session d'événements étendus system_health est incluse dans SQL Server et activée par défaut. La session démarre automatiquement au démarrage de votre moteur de base de données SQL Server et collecte des informations de base sur l'état du serveur. Vous pouvez utiliser ces informations pour résoudre les problèmes de performances et surveiller les blocages au sein de votre moteur de base de données. Pour plus d'informations, consultez la page Utiliser la session system_health sur le site Web de Microsoft.

La session d'événements étendus system_health utilise deux cibles, un fichier d'événements et un tampon annulaire, pour stocker les données. Le tampon annulaire stocke les données selon le principe du premier entré, premier sorti (FIFO). La mémoire cible du tampon annulaire 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 alterner les événements.

Par défaut, la taille de chaque fichier individuel est de 5 Mo et le nombre maximum de fichiers de survol est de 4. Cela représente 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 maximum de fichiers est augmenté à 10. Cela représente 1 Go de données.

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

Pour utiliser SSMS afin de récupérer les informations relatives aux blocages, procédez comme suit :

1. Ouvrez SSMS.
2. Dans Object Explorer, sélectionnez Gestion, puis Événements étendus.
3. Sélectionnez Sessions.
4. Recherchez la session system_health, puis choisissez (double-cliquez sur) package0.event_file pour ouvrir le fichier d'événements étendus.
5. Une fois le contenu du fichier chargé, dans le menu SSMS, sélectionnez Événements étendus.
6. Sélectionnez ** Filtres**.
7. Dans la fenêtre Filtre, procédez comme suit :
   Pour Champ, sélectionnez Nom.
   Pour Opérateur, sélectionnez Contient.
   Pour Valeur, sélectionnez Blocage.
8. Cliquez sur OK. Vous pouvez consulter 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.

Pour utiliser T-SQL afin de récupérer les informations relatives aux blocages, 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 DeadlockGraphFROM (
       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 la sortie XML de blocage pour ouvrir le fichier XML dans une nouvelle fenêtre.

3. Utilisez l'extension de fichier .xdl pour enregistrer le fichier XML. Cette action convertit le XML en format graphique.

4. Accédez à l'emplacement du fichier, puis ouvrez le fichier .xdl dans SSMS pour afficher le graphique des blocages.

Pour récupérer les informations relatives aux blocages à partir de ring_buffer, exécutez la requête T-SQL. La cible ring_buffer contient les données d'événement en mémoire. Ces informations ne sont disponibles que tant que l'instance n'est pas redémarrée. Lorsque vous redémarrez, ces informations sont supprimées.

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

Pour créer une session d'événements étendus, sélectionnez l’événement xml_deadlock_report pour capturer les blocages. Pour enregistrer les événements dans le fichier à des fins d'analyse, sélectionnez un fichier d'événements comme cible. Pour créer une session d’événements étendus, vous pouvez utiliser SSMS ou T-SQL.

Pour utiliser SSMS afin de créer une session d'événements étendus, procédez comme suit :

1. Ouvrez SSMS.
2. Dans Object Explorer, sélectionnez Gestion, puis Événements étendus.
3. Sélectionnez (cliquez avec le bouton droit sur) Sessions, puis sélectionnez Assistant nouvelle session.
4. Pour Nom de la session, saisissez le nom de votre session, puis sélectionnez 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 sélectionnez Suivant.
8. Sur la page Capturer les champs globaux, sélectionnez les valeurs communes à tous les événements.
   Remarque : Sélectionnez le champ sql_text pour voir la requête à l'origine du blocage.
9. Sélectionnez Suivant.
10. Sur la page Définir les filtres d'événement de session, créez des filtres d'événement pour limiter les données que vous souhaitez capturer.
11. Sélectionnez Suivant.
12. Sur la page Spécifier le stockage des données de session, sélectionnez Enregistrer les données dans un fichier pour une analyse ultérieure et Utiliser uniquement les données les plus récentes.
13. Sélectionnez Terminer.

Vous pouvez maintenant voir votre nouvelle session dans le dossier Sessions de SSMS. Choisissez la session (cliquez avec le bouton droit de la souris), puis sélectionnez Démarrer la session.

Pour utiliser T-SQL afin de créer une session d’événements étendus, exécutez une requête similaire à la suivante :

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 utiliser SSMS afin de récupérer les informations de blocage, procédez comme suit :

1. Ouvrez SSMS.
2. Dans Object Explorer, sélectionnez Gestion, puis Événements étendus.
3. Sélectionnez Sessions.
4. Recherchez la session d'événements étendus que vous avez créée précédemment, puis sélectionnez (double-cliquez) package0.event_file pour ouvrir le fichier d'événements étendus.
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.

Pour utiliser T-SQL afin d'afficher la liste des blocages, exécutez une requête similaire à la suivante :

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

Informations connexes

Guide des blocages (sur le site Web de Microsoft)

KB4541132 - Amélioration : La taille et la stratégie de rétention sont augmentées dans le système trace system_health XEvent par défaut dans SQL Server 2016, 2017 et 2019 (sur le site Web de Microsoft)

Surveiller les blocages dans Amazon RDS for SQL Server et définir des notifications à l'aide d'Amazon CloudWatch