[SQL Server] DMV : identification de blocages et de deadlocks

Ce billet montre comment identifier les blocages et les deadlocks (ou verrous mortels) via les DMV.

Vous pouvez jeter un coup d’œil ici si vous souhaitez lire la présentation des verrous sous SQL Server, ainsi que , si vous voulez comprendre la différence entre les modèles de concurrence utilisés par SQL Server. Vous pouvez également jeter un coup d’œil ici pour connaître les niveaux d’isolation sous SQL Server et leurs spécificités, où les anomalies transactionnelles sont déjà présentées du point de vue théorique dont des rappels seront effectués dans ce billet.

Entrée dans le vif du sujet…

Voici quelques requêtes DMV utiles pour l’audit des blocages :

  • Affichage de tous les processus en cours d’exécution qui sont bloqués :
USE master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id<>0
GO
  • Affichage de toutes les tâches en attente de déverrouillage d’une ressource :
USE master
GO
SELECT session_id, wait_type, wait_duration_ms, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id<>0
GO

Exemple de résultat :


La tâche utilisant la session de spid 54 est bloquée par une autre de spid 53. Elle est en attente d’un verrou de mise-à-jour (LCK_M_U)
sur une ressource depuis 4324485 millisecondes.

  • Affichage d’informations détaillées relatives aux bloqueurs et aux bloqués actuels :
WITH blocker
AS
 (SELECT w.session_id,
   s.original_login_name,
   s.login_name,
   w.wait_duration_ms,
   w.wait_type,
   r.status,
   r.wait_resource,
   w.resource_description
   s.program_name,
   w.blocking_session_id,
   s.host_name,
   q.text,
   r.command,
   r.total_elapsed_time,
   p.query_plan,
FROM sys.dm_os_waiting_tasks w
        INNER JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id
          INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
             CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) q
                CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE w.session_id > 50)
SELECT b.session_id AS waiting_spid,
   b.blocking_session_id AS blocking_spid,
   b.login_name AS waiting_login_name,
   es.login_name AS blocking_login_name,
   b.host_name AS waiting_hostname,
   es.host_name AS blocking_hostname,
   b.wait_type AS wait_type,
   b.wait_duration_ms AS wait_duration,
   t.request_mode AS wait_request_mode,
   b.status AS waiter_status,
   es.status AS blocker_status,
   b.wait_resource AS locked_resource_id
   b.command AS waiting_command_type,
   b.text AS waiting_command_text,
   b.total_elapsed_time AS waiting_command_total_elapsed_time,
   b.query_plan AS waiting_command_query_plan,
   o.name AS locked_object_name,
   t.resource_type AS locked_resource_type,
   b.resource_description AS locked_resource_description,
   DB_NAME(t.resource_database_id) AS locked_resource_database_name,
   b.program_name AS waiting_program_name,
   es.program_name AS blocking_program_name,
FROM blocker b
        INNER JOIN sys.dm_exec_sessions es ON b.blocking_session_id = es.session_id
          INNER JOIN sys.dm_tran_locks t ON t.request_session_id = b.session_id
             INNER JOIN sys.partitions p ON p.hobt_id = t.resource_associated_entity_id
               INNER JOIN sys.objects o ON o.object_id=p.object_id
WHERE t.request_status='WAIT'
GO

Des informations très utiles sont fournies, à savoir :

  • … les processus concernés (le(s) bloqueur(s) et le(s) bloqué(s)) : spid, login, hostname, programme utilisé… Extrait :


  • … la (ou les) ressource(s) verrouillée(s) : type, nom (généralement le nom d’une table en cas de mise-à-jour), base de données,… Extrait :


  • … l’attente : type, durée,…


  • … la requête du bloqué : type, contenu, durée totale (jusqu’à présent), plan d’exécution… Extrait :


Dans nos différents extraits de résultat, on peut donc déduire qu’une transaction de mise-à-jour de la table Voiture exécutée via une session de spid 54 connectée, avec le login MAC-ROG\Mac, à SQL Server à partir de SSMS situé sur une machine appelée MAC-ROG est, depuis 260972 millisecondes, en attente de libération de la ressource de RID 1:121:0 (située sur la page 121 du fichier 1) stockée au sein de la base de données mydatabase. Cette ressource est bloquée par un verrou exclusif (mode X) appartenant à une session de spid 53.

Voici une petite description des DMV utilisées :

DMV Description
sys.dm_exec_sessions. Fournit des informations sur les sessions connectées à SQL Server.
sys.dm_exec_requests. Fournit des informations sur les requêtes en cours d’exécution.
sys.dm_tran_locks. Fournit des informations sur les verrous en cours d’utilisation et les blocages.
sys.dm_os_waiting_tasks. Fournit des informations sur les tâches en attente d’un verrou sur une ressource.
sys.dm_exec_query_plan. Fournit des informations sur le plan d’exécution d’une requête en format XML.
sys.dm_exec_sql_text. Fournit des informations sur le code source T-SQL d’une requête.

Ces différentes DMV permettent donc de récolter des informations relatives aux blocages en cours. Elles offrent quelques éléments de réponse sur la nature d’un deadlock potentiel.

kill 53
GO

Pour aller plus loin…
Vous pouvez aller ici pour les conseils relatifs à la résolution de longs blocages/deadlocks.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s