SQL Server

Diagnostiquer les blocages dans SQL Server

Repérez les requêtes bloquées, remontez les chaînes jusqu’au bloqueur principal et examinez prudemment la session et la transaction responsables.

Par JaviPublié Mis à jour 15 min de lectureIntermédiaire

Trois sessions SQL Server reliées dans une chaîne de blocage, avec des verrous sur les requêtes en attente

Réponse courte : interrogez sys.dm_exec_requests pour trouver les requêtes dont blocking_session_id n’est pas nul, remontez chaque bloqueur jusqu’à une session qui en bloque d’autres sans être bloquée elle-même (le bloqueur principal), puis examinez l’âge de sa transaction, son propriétaire et son dernier SQL. Rassemblez ces éléments avant que quiconque n’agisse.

Le blocage est-il l’incident en cours ? Une fois le blocage confirmé et les utilisateurs touchés, passez au runbook de blocage SQL Server (en anglais) pour la procédure opérationnelle pas à pas. Cette page explique comment trouver et lire les éléments.

Vous n’êtes pas sûr qu’il s’agisse d’un blocage ? Pour voir d’abord toute l’activité en cours, utilisez Voir les requêtes en cours.

Un blocage se produit lorsqu’une session doit attendre qu’une autre libère un verrou incompatible. Cette coordination est fondamentale pour la justesse transactionnelle. Le problème en production est un blocage prolongé ou inattendu qui dépasse l’objectif de latence de la charge, crée une file d’attente ou empêche la réutilisation du journal.

Une requête bloquée est généralement victime de la dépendance, pas sa cause racine. Interrompre les sessions bloquées réduit la file visible, mais n’explique pas pourquoi la transaction bloquante reste ouverte.

Problème

Les utilisateurs signalent des expirations de délai pour les requêtes de SalesDb. Plusieurs sessions affichent des attentes LCK_M_*. Vous devez cartographier les relations entre bloqueurs et sessions bloquées, trouver la session à la tête de la chaîne et examiner son lot actuel ou le plus récent ainsi que l’ancienneté de sa transaction.

Diagnostic rapide

Cette requête répertorie les requêtes bloquées actives. Exécutez-la plusieurs fois : un blocage bref qui disparaît peut être normal, tandis qu’une chaîne stable dont le temps d’attente augmente est plus préoccupante.

Requête SQL: Diagnostic rapideT-SQL
SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    r.session_id AS blocked_session_id,
    r.request_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    s.login_name,
    s.host_name,
    s.program_name,
    r.start_time AS request_start_time,
    r.open_transaction_count,
    SUBSTRING(
        sql_text.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(sql_text.text)
            ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1
    ) AS blocked_statement
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;
T-SQL29 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Bloqueur direct, chaîne de blocage et bloqueur principal

  1. BloquéeSession 92LCK_M_S, blocking_session_id = 81
  2. attend
    Bloquée et bloquanteSession 81LCK_M_X, blocking_session_id = 65
  3. attend
    Bloqueur principalSession 65Inactive, transaction ouverte, aucune requête active
Bloqueur direct, chaîne et bloqueur principal. La session 92 attend la 81 et la 81 attend la 65. Seule la 65 n’attend rien ; c’est le bloqueur principal, souvent une session inactive qui garde une transaction ouverte.
  • Bloqueur direct : la session indiquée dans le blocking_session_id d’une requête. Pour la session 92, c’est la 81.
  • Chaîne de blocage : la suite d’attentes qui mène d’une requête bloquée à la session qui détient ce que tout le monde attend : 92 → 81 → 65. Plusieurs chaînes peuvent converger vers le même bloqueur.
  • Bloqueur principal : une session qui en bloque d’autres sans être bloquée elle-même. N’examiner que la 81 fait manquer le début de la chaîne ; c’est la transaction de la 65 qui compte.
  • Bloqueur principal inactif : une session au statut sleeping, sans ligne dans sys.dm_exec_requests et avec open_transaction_count > 0. L’application a ouvert une transaction puis a cessé d’envoyer du travail sans valider ni annuler : un chemin d’erreur qui a sauté le rollback, une connexion de pool rendue en pleine transaction, ou une saisie utilisateur à l’intérieur d’une transaction. Les requêtes sur l’activité en cours ne peuvent pas la montrer ; c’est pourquoi la requête sur le bloqueur principal ci-dessous part de sys.dm_exec_sessions.

Requête : afficher la chaîne de blocage

Cette requête récursive part des requêtes bloquées et remonte les bloqueurs actifs. Elle évite les cycles et limite la récursion. Elle décrit le graphe des requêtes en cours ; la session principale en veille sera enrichie ensuite, car elle n’a aucune requête active.

Requête SQL: Afficher la chaîne de blocageT-SQL
;WITH blocking_chain AS
(
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.database_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        0 AS chain_level,
        CAST(CONCAT('/', r.session_id, '/') AS varchar(max)) AS visited
    FROM sys.dm_exec_requests AS r
    WHERE r.blocking_session_id > 0

    UNION ALL

    SELECT
        blocker.session_id,
        blocker.blocking_session_id,
        blocker.database_id,
        blocker.wait_type,
        blocker.wait_time,
        blocker.wait_resource,
        chain.chain_level + 1,
        CAST(CONCAT(chain.visited, blocker.session_id, '/') AS varchar(max))
    FROM blocking_chain AS chain
    INNER JOIN sys.dm_exec_requests AS blocker
        ON blocker.session_id = chain.blocking_session_id
    WHERE chain.visited NOT LIKE CONCAT('%/', blocker.session_id, '/%')
)
SELECT DISTINCT
    chain.chain_level,
    chain.session_id,
    NULLIF(chain.blocking_session_id, 0) AS blocking_session_id,
    DB_NAME(chain.database_id) AS database_name,
    session_info.login_name,
    session_info.host_name,
    session_info.program_name,
    chain.wait_type,
    chain.wait_time AS wait_time_ms,
    chain.wait_resource
FROM blocking_chain AS chain
INNER JOIN sys.dm_exec_sessions AS session_info
    ON session_info.session_id = chain.session_id
ORDER BY chain.chain_level, chain.session_id
OPTION (MAXRECURSION 32);
T-SQL46 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Plusieurs feuilles bloquées peuvent converger vers le même bloqueur ; DISTINCT élimine donc les nœuds répétés. Il n’agrège pas les compteurs. Le niveau maximal atteint depuis une feuille n’est pas, à lui seul, une mesure de gravité. Concentrez-vous sur les sessions qui en bloquent d’autres sans être elles-mêmes bloquées.

Requête : examiner le bloqueur principal

Définissez une seule fois l’identifiant de la session principale. Cette requête fonctionne pour une session active ou en veille et expose l’ancienneté de la transaction ainsi que le texte actuel ou le plus récent.

Requête SQL: Examiner le bloqueur principalT-SQL
DECLARE @HeadBlockerSessionId int = 65;

SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    s.session_id,
    s.login_time,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(COALESCE(r.database_id, s.database_id)) AS database_name,
    r.status AS request_status,
    r.command,
    r.start_time AS request_start_time,
    r.wait_type,
    r.cpu_time AS cpu_time_ms,
    r.logical_reads,
    r.writes,
    s.open_transaction_count,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds,
    log_use.log_bytes_used,
    sql_text.text AS current_or_most_recent_batch
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
OUTER APPLY (
    SELECT SUM(dt.database_transaction_log_bytes_used) AS log_bytes_used
    FROM sys.dm_tran_database_transactions AS dt
    WHERE dt.transaction_id = st.transaction_id
) AS log_use
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS sql_text
WHERE s.session_id = @HeadBlockerSessionId;
T-SQL39 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Interpréter les résultats

Les attentes de verrou commencent par LCK_M_ ; le suffixe décrit le mode de verrou demandé. La ressource d’attente peut identifier une base, un objet, une page, une clé ou une ressource de métadonnées, mais la décoder permet seulement de remonter à l’opération verrouillée ; ce n’est pas une correction automatique. Déterminez les instructions, les limites de transaction, le niveau d’isolation, les lignes concernées et les chemins d’accès des index.

Un bloqueur principal actif peut effectuer un travail légitime. Un bloqueur principal en veille avec une ancienne transaction ouverte mérite une attention particulière : le client a peut-être cessé d’envoyer du travail sans valider ni annuler la transaction. Le lot le plus récent donne une piste, mais il ne s’agit pas nécessairement de l’instruction qui a acquis tous les verrous encore détenus.

Ce que les résultats peuvent indiquer

Des transactions longues, des mises à jour volumineuses, des prédicats peu sélectifs, des scans touchant davantage de clés, une interaction utilisateur dans une transaction et l’absence de chemins de validation ou d’annulation peuvent prolonger les blocages. Un mauvais index peut augmenter le nombre et la durée des ressources verrouillées, mais en ajouter un n’est pas automatiquement la bonne réponse. Les changements de niveau d’isolation modifient la sémantique de cohérence et doivent relever de décisions d’architecture, pas de raccourcis en pleine intervention.

Les deadlocks sont différents. Dans un deadlock, les sessions forment un cycle et SQL Server choisit une victime pour permettre aux autres transactions de continuer. Un blocage ordinaire forme une ligne ou un arbre d’attentes qui peut persister. Examinez les deadlocks avec leurs graphes ; ne vous attendez pas à ce que cet instantané reconstitue un deadlock déjà terminé.

Que vérifier ensuite ?

Examinez le plan du bloqueur principal, son code transactionnel et la responsabilité de l’application. Vérifiez le nombre de lignes modifiées, la sélectivité des prédicats et la possibilité que des erreurs contournent le rollback. Pour les incidents récurrents, capturez des rapports de processus bloqués ou une session Extended Events adaptée afin de conserver les éléments après la fin de l’incident.

Éléments à collecter avant d’agir

Élément D’où il vient Pourquoi il compte
session_id et login_time du bloqueur principal sys.dm_exec_sessions Les ID de session sont réutilisés ; login_time confirme qu’il s’agit toujours de la même connexion
Nombre de requêtes bloquées et attente la plus longue La première requête, exécutée plusieurs fois Indique si la chaîne s’allonge, se stabilise ou se résorbe
Heure de début et âge de la transaction sys.dm_tran_active_transactions Une transaction ancienne garde ses verrous plus longtemps et peut empêcher la réutilisation du journal
Octets de journal utilisés par la transaction sys.dm_tran_database_transactions Une indication approximative du travail qu’un rollback devrait défaire
Login, hôte et application sys.dm_exec_sessions À qui appartient la session, et qui contacter
SQL en cours ou le plus récent sys.dm_exec_sql_text ou sys.dm_exec_input_buffer Ce que faisait la transaction ; pas la preuve de l’instruction qui a pris chaque verrou
Type et ressource d’attente des requêtes bloquées sys.dm_exec_requests Le mode de verrou demandé et l’objet, la page ou la clé concernés
Base de données et heure de début de la requête sys.dm_exec_requests L’étendue de l’impact et sa durée

Horodatez chaque capture et stockez-la hors de la base affectée. Pendant un incident, le runbook propose un modèle de collecte (en anglais) à remplir avant toute mesure corrective.

Erreurs fréquentes

  • Tuer la session bloquée tout en laissant ouverte la transaction à la tête de la chaîne.
  • Supposer que tout blocage est anormal et affaiblir l’isolation sans analyser les exigences.
  • Se fier à un seul instantané sans mesurer la durée du problème.
  • Considérer most_recent_sql_handle comme une preuve définitive de l’instruction qui détient chaque verrou.
  • Utiliser NOLOCK comme solution universelle malgré les résultats incohérents ou manquants.
  • Confondre une chaîne de blocage avec un cycle de deadlock.

Notes de production et risques de KILL

Si vous gérez un incident de production en cours, utilisez le runbook de blocage SQL Server (en anglais).

KILL n’est pas une requête de diagnostic. L’arrêt du bloqueur principal annule sa transaction ouverte ; le rollback peut prendre autant de temps, voire plus, que le travail initial, tout en continuant à conserver les verrous. Il peut également interrompre une opération métier critique. Si l’arrêt est nécessaire pour protéger la disponibilité, vérifiez de nouveau l’identité de la session, coordonnez-vous avec son responsable, capturez les éléments utiles et prévoyez de suivre le rollback.

Les résultats des DMV sont transitoires et les autorisations varient selon la version de SQL Server. Conservez les échantillons horodatés de manière sécurisée. Une solution durable réduit généralement la portée ou la durée des transactions, corrige la gestion transactionnelle de l’application, améliore le plan d’accès ou modifie la conception de la concurrence tout en préservant la cohérence requise.

Continuer le parcours de performance

Utilisez les éléments de blocage dans la démarche d’optimisation. Si le signalement est un ralentissement général plutôt qu’un blocage connu, commencez par SQL Server est lent : les 15 premières minutes. Revenez aux requêtes en cours, examinez le bloqueur avec le guide de l’ID de session et utilisez les régressions Query Store lorsque le ralentissement est passé. Aide-mémoire : Trouver les blocages et Trouver les transactions ouvertes.

Sources

Vérifié en novembre 2026. Pour voir les autres sessions, SQL Server 2019 et versions antérieures exigent VIEW SERVER STATE ; à partir de SQL Server 2022, Microsoft documente VIEW SERVER PERFORMANCE STATE pour les DMV de session et de transaction.

Étiquettes

  • SQL Server
  • Performance
  • Dépannage
  • DMV
  • Blocages
  • Concurrence