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.
Dans cet article
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.
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;Bloqueur direct, chaîne de blocage et bloqueur principal
- BloquéeSession 92LCK_M_S, blocking_session_id = 81
- attendBloquée et bloquanteSession 81LCK_M_X, blocking_session_id = 65
- attendBloqueur principalSession 65Inactive, transaction ouverte, aucune requête active
- Bloqueur direct : la session indiquée dans le
blocking_session_idd’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 danssys.dm_exec_requestset avecopen_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 desys.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.
;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);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.
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;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_handlecomme une preuve définitive de l’instruction qui détient chaque verrou. - Utiliser
NOLOCKcomme 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
- Microsoft Learn : comprendre et résoudre les problèmes de blocage SQL Server
- Microsoft Learn : sys.dm_exec_requests, sys.dm_tran_active_transactions, sys.dm_tran_database_transactions
- Microsoft Learn : blocked process threshold (désactivé par défaut), la session system_health (enregistre les attentes de verrou de plus de 30 secondes et les interblocages), KILL
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.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Continuer à apprendre
- Aide-mémoireTrouver les blocages dans SQL ServerIdentifiez les sessions bloquées, les bloqueurs, les attentes de verrou et le bloqueur principal pour diagnostiquer les blocages SQL Server sans tuer la mauvaise session.Ouvrir l’aide-mémoire
- Feuille de routeSQL Server Troubleshooting & DiagnosticsRepérez les requêtes bloquées, remontez les chaînes jusqu’au bloqueur principal et examinez prudemment la session et la transaction responsables.Voir la feuille de route
Que devrais-je créer ensuite ?
Je développe Javi Builds autour des problèmes concrets rencontrés par les data engineers. Dites-moi quel sujet vous aimeriez voir ensuite.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.