Blocages SQL Server : trouver qui bloque qui, et pourquoi.

Blocage, interblocage, lenteur : ne pas confondre

Un blocage se produit quand une session attend un verrou détenu par une autre. Il se résout tout seul dès que la session qui détient le verrou termine sa transaction. Un interblocage (deadlock) est différent : deux sessions s'attendent mutuellement, SQL Server en sacrifie une (voir l'article sur les deadlocks). Une simple lenteur n'implique pas de verrou.

Le signe d'un blocage : des sessions en attente avec le type d'attente LCK_M_... (par exemple LCK_M_S ou LCK_M_X), alors que le processeur et les disques sont calmes.

1. Voir les sessions bloquées en direct

-- Sessions actuellement bloquées, et par qui
SELECT
    r.session_id,
    r.blocking_session_id                          AS bloquee_par,
    r.wait_type,
    r.wait_time / 1000.0                           AS attente_s,
    r.wait_resource,
    DB_NAME(r.database_id)                         AS base,
    s.login_name, s.host_name, s.program_name,
    SUBSTRING(t.text, r.statement_start_offset / 2 + 1,
        (CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1) AS requete_bloquee
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Plusieurs sessions peuvent s'attendre en chaîne : A bloque B, qui bloque C. Ce qui compte est la session en tête de chaîne.

2. Afficher l'arborescence des verrous

Cette requête reconstruit la chaîne de blocage sous forme d'arbre : la session en tête de chaîne est au niveau 0, et chaque session qu'elle bloque (directement ou non) est indentée en dessous.

-- Arborescence des blocages : tête de chaîne, puis sessions bloquées en cascade
;WITH bloquees AS (
    SELECT session_id, blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
),
arbre AS (
    -- niveau 0 : sessions qui bloquent les autres sans être elles-mêmes bloquées
    SELECT s.session_id,
           CAST(0 AS int) AS niveau,
           CAST(RIGHT('00000' + CONVERT(varchar(5), s.session_id), 5) AS varchar(900)) AS chemin
    FROM sys.dm_exec_sessions AS s
    WHERE s.session_id IN (SELECT blocking_session_id FROM bloquees)
      AND s.session_id NOT IN (SELECT session_id FROM bloquees)
    UNION ALL
    -- niveaux suivants : sessions bloquées par la session du niveau précédent
    SELECT b.session_id,
           a.niveau + 1,
           CAST(a.chemin + '>' + RIGHT('00000' + CONVERT(varchar(5), b.session_id), 5) AS varchar(900))
    FROM bloquees AS b
    JOIN arbre AS a ON b.blocking_session_id = a.session_id
)
SELECT
    REPLICATE('     ', a.niveau)
        + CASE WHEN a.niveau = 0 THEN 'TETE > ' ELSE '+-- ' END
        + CONVERT(varchar(10), a.session_id)         AS arborescence,
    s.status,
    s.open_transaction_count                          AS transactions_ouvertes,
    r.wait_type,
    r.wait_time / 1000.0                              AS attente_s,
    s.login_name, s.host_name, s.program_name,
    t.text                                            AS derniere_requete
FROM arbre AS a
JOIN sys.dm_exec_sessions AS s ON s.session_id = a.session_id
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = a.session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = a.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
ORDER BY a.chemin;

• Niveau 0 (TETE) avec status = sleeping et transactions_ouvertes supérieur à 0 : le cas le plus fréquent. L'application a exécuté une instruction, puis n'a ni validé ni annulé la transaction. Elle attend peut-être une saisie utilisateur, ou a oublié le COMMIT.
• Niveau 0 avec status = running : la session travaille encore (grosse mise à jour, traitement par lots). Le blocage cessera à sa fin.
• Les sessions indentées sont les victimes : inutile de les arrêter, elles repartiront quand la tête de chaîne aura terminé.
• La colonne derniere_requete montre la dernière instruction exécutée, pas forcément celle qui a posé le verrou : cherchez aussi la plus ancienne transaction ouverte.

3. Les transactions ouvertes depuis longtemps

-- Transactions ouvertes, de la plus ancienne à la plus récente
SELECT
    st.session_id,
    at.transaction_begin_time,
    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS duree_s,
    s.login_name, s.host_name, s.program_name, s.status
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_tran_active_transactions  AS at ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions             AS s  ON s.session_id = st.session_id
WHERE s.is_user_process = 1
ORDER BY at.transaction_begin_time;

Une transaction ouverte depuis des minutes ou des heures dans une application métier est presque toujours un défaut de conception ou un oubli. Elle bloque aussi la purge du journal de transactions : voir l'article sur le journal qui grossit.

4. Voir les verrous détenus

-- Verrous d'une session (remplacer 57 par la session en tête de chaîne)
SELECT
    l.request_session_id        AS session_id,
    DB_NAME(l.resource_database_id) AS base,
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.resource_description
FROM sys.dm_tran_locks AS l
WHERE l.request_session_id = 57
ORDER BY l.resource_type, l.request_mode;

• request_status GRANT : verrou obtenu. WAIT : verrou attendu.
• Des verrous de type OBJECT en mode X ou IX sur une table entière peuvent signaler une escalade de verrous : SQL Server remplace de nombreux petits verrous par un verrou de table, vers 5 000 verrous pour une même instruction. Cela arrive souvent quand une requête sans index adapté parcourt toute la table.

Agir : du plus doux au plus brutal

• Attendre ou demander à l'utilisateur ou à l'application de terminer sa transaction, si elle est en cours de traitement légitime.
• Arrêter la session fautive en dernier recours : KILL 57;. La transaction est annulée, ce qui peut être long si elle a beaucoup écrit. Suivez l'avancement avec KILL 57 WITH STATUSONLY; : n'essayez pas de la tuer plusieurs fois.
• Ne tuez jamais une session système ni le processus qui réalise une restauration ou une reconstruction sans en comprendre les conséquences.
• Pour limiter l'attente côté application : SET LOCK_TIMEOUT 5000; fait échouer une requête après 5 secondes d'attente, au lieu de la laisser attendre indéfiniment.

Corriger la cause

• Transactions courtes : aucune attente d'utilisateur, aucun appel externe dans une transaction. Les verrous sont libérés au COMMIT.
• Index adaptés : une requête qui trouve ses lignes par un index verrouille peu de lignes. Les scripts sur les index manquants et sur les clés étrangères non indexées aident.
• Séparer lecteurs et écrivains : en isolation par défaut, une lecture est bloquée par une écriture. L'isolation par versions de lignes (RCSI) supprime ce cas : voir l'article sur RCSI et SNAPSHOT.
• Traitements par lots : modifier de grosses quantités de lignes par paquets de quelques milliers plutôt qu'en une seule instruction.

Surveiller dans la durée

Pour être averti sans devoir être connecté au bon moment, activez le seuil de rapport de processus bloqué (blocked process threshold) et enregistrez l'événement blocked_process_report dans une session d'événements étendus. Le montage est détaillé dans l'article sur les XEvents.