Compréhension et résolution des problèmes bloquants dans SQL Server

S’applique à : SQL Server (toutes les versions prises en charge), Azure SQL Managed Instance

Numéro de base de connaissances d’origine : 224453

Objectif

L’article décrit le blocage dans SQL Server et montre comment résoudre les problèmes et résoudre le blocage.

Dans cet article, le terme « connexion » fait référence à une session ouverte unique de la base de données. Chaque connexion s’affiche sous la forme d’un ID de session (SPID) ou session_id dans de nombreuses vues de gestion dynamique (DMV). Chacun de ces SPID est souvent appelé processus, bien qu’il ne s’agit pas d’un contexte de processus distinct dans le sens habituel. Chaque SPID est plutôt constitué des ressources du serveur et des structures de données nécessaires pour traiter les requêtes d’une seule connexion d’un client donné. Une même application cliente peut avoir une ou plusieurs connexions. Du point de vue de SQL Server, il n’existe aucune différence entre plusieurs connexions d’une seule application cliente sur un seul ordinateur client et plusieurs connexions de plusieurs applications clientes ou plusieurs ordinateurs clients ; ils sont atomiques. Une connexion peut bloquer une autre connexion, quel que soit le client source.

Ce qui bloque

Le blocage est une caractéristique inévitable et inhérente à tout système de gestion de base de données relationnelle (SGBDR) avec concurrence basée sur les verrous. Comme mentionné précédemment, dans SQL Server, un blocage se produit lorsqu’une session maintient un verrou sur une ressource spécifique et qu’un deuxième SPID tente d’acquérir sur la même ressource un type de verrou en conflit. En règle générale, le délai d’exécution pendant lequel le premier SPID verrouille la ressource est court. Lorsque la session propriétaire libère le verrou, la deuxième connexion est alors libre d’acquérir son propre verrou sur la ressource et de poursuivre le traitement. Le blocage comme décrit ici est un comportement normal et peut se produire plusieurs fois au cours d’une journée sans effet notable sur les performances du système.

La durée et le contexte de transaction d’une requête déterminent la durée de conservation de ses verrous et, par conséquent, leur effet sur les autres requêtes. Si la requête n’est pas exécutée dans une transaction (et qu’aucun indicateur de verrou n’est utilisé), les verrous pour les instructions SELECT sont conservés uniquement sur une ressource au moment où elle est en cours de lecture, et non pendant la requête. Pour les instructions INSERT, UPDATE et DELETE, les verrous sont maintenus pendant la requête, à la fois pour la cohérence des données et pour permettre la restauration de la requête si nécessaire.

Pour les requêtes exécutées dans le cadre d’une transaction, la durée pendant laquelle les verrous sont maintenus est déterminée par le type de requête, le niveau d’isolation de la transaction et si les indicateurs de verrou sont utilisés dans la requête. Pour obtenir une description du verrouillage, des indicateurs de verrou et des niveaux d’isolation des transactions, consultez les articles suivants :

• Verrouillage du moteur de base de données
• Personnalisation du verrouillage et du contrôle de version de ligne
• Modes de verrouillage
• Compatibilité de verrouillage
• Niveaux d'isolation basés sur le contrôle de version de ligne dans le moteur de base de données
• Transactions

Lorsque le verrouillage et le blocage persistent au point de nuire aux performances du système, cela est dû à l’une des raisons suivantes :

• Un SPID contient des verrous sur un ensemble de ressources pendant une période prolongée avant de les libérer. Ce type de blocage se résout de lui-même avec le temps, mais peut entraîner une détérioration des performances.

• Un SPID contient des verrous sur un ensemble de ressources et ne les libère jamais. Ce type de blocage ne se résout pas de lui-même et empêche indéfiniment l’accès aux ressources concernées.

Dans le premier scénario, la situation peut être très fluide, car différents SPID provoquent un blocage sur différentes ressources au fil du temps, créant ainsi une cible mobile. Ces situations sont difficiles à résoudre en utilisant SQL Server Management Studio pour limiter le problème aux requêtes individuelles. En revanche, la deuxième situation aboutit à un état cohérent qui peut être plus facile à diagnostiquer.

Applications et blocage

Les utilisateurs ont tendance à se concentrer sur les problèmes de plateforme et de réglage côté serveur lorsqu’ils sont confrontés à un problème de blocage. Toutefois, l’attention portée uniquement à la base de données peut ne pas aboutir à une résolution et peut absorber du temps et de l’énergie mieux consacrés à l’examen de l’application cliente et des requêtes qu’elle envoie. Quel que soit le niveau de visibilité que l’application expose en ce qui concerne les appels à la base de données, un problème de blocage requiert souvent l’inspection des instructions SQL exactes soumises par l’application et du comportement exact de l’application concernant l’annulation des requêtes, la gestion des connexions, l’extraction de toutes les lignes de résultats, etc. Si l’outil de développement n’autorise pas le contrôle explicite sur la gestion des connexions, l’annulation des requêtes, le délai d’expiration des requêtes, la récupération des résultats, et ainsi de suite, les problèmes de blocage peuvent ne pas être résolus. Ce potentiel doit être examiné attentivement avant de sélectionner un outil de développement d’applications pour SQL Server, en particulier pour les environnements OLTP sensibles aux performances.

Faites attention aux performances de la base de données pendant la phase de conception et de construction de la base de données et de l’application. En particulier, la consommation des ressources, le niveau d’isolation et la longueur du chemin de la transaction doivent être évalués pour chaque requête. Chaque requête et chaque transaction doivent être aussi simples que possible. Une bonne discipline en matière de gestion des connexions doit être exercée, sans quoi l’application peut sembler avoir des performances acceptables lorsque le nombre d’utilisateurs est faible, mais les performances peuvent se dégrader de manière significative à mesure que le nombre d’utilisateurs augmente.

Avec une conception d’application et de requête appropriée, SQL Server est capable de prendre en charge plusieurs milliers d’utilisateurs simultanés sur un seul serveur, avec peu de blocage.

Résoudre les problèmes de blocage

Quelle que soit la situation de blocage dans laquelle nous nous trouvons, la méthodologie de résolution des problèmes de verrouillage est la même. Ces séparations logiques définissent le reste de la composition de cet article. Le concept consiste à trouver le bloqueur principal et à identifier ce que fait cette requête et la raison pour laquelle elle crée un blocage. Une fois la requête problématique identifiée (autrement dit, ce qui contient des verrous pendant la période prolongée), l’étape suivante consiste à analyser et à déterminer pourquoi le blocage se produit. Après avoir compris pourquoi, nous pouvons ensuite apporter des modifications en remaniant la requête et la transaction.

Étapes de résolution du problème :

1. Identifier la session de blocage principale (bloqueur principal)

2. Rechercher la requête et la transaction à l’origine du blocage (qu’est-ce qui maintient les verrous pendant une période prolongée)

3. Analyser/comprendre les raisons du blocage prolongé

4. Résoudre le problème bloquant en reconcevant la requête et la transaction

Voyons maintenant comment identifier la session de blocage principale grâce à une capture de données appropriée.

Collecter les informations de blocage

Pour contrer la difficulté de résoudre les problèmes de blocage, un administrateur de base de données peut utiliser des scripts SQL qui surveillent constamment l’état de verrouillage et de blocage sur SQL Server. Pour collecter ces données, il existe deux méthodes complémentaires.

La première consiste à interroger les objets de gestion dynamique (DMO) et à stocker les résultats à des fins de comparaison dans le temps. Certains objets mentionnés dans cet article sont des vues de gestion dynamique (DMV) et d’autres des fonctions de gestion dynamique (DMF).

La seconde consiste à utiliser des événements étendus (XEvents) ou des traces de profileur SQL pour capturer ce qui est en cours d’exécution. Étant donné que SQL Trace et SQL Server Profiler sont déconseillés, ce guide de résolution des problèmes se concentre sur XEvents.

Collecter des informations à partir de DMV

Le référencement des DMV pour résoudre les problèmes de blocage a pour objectif d’identifier le SPID (ID de session) en tête de la chaîne de blocage et de l’instruction SQL. Recherchez les SPID victimes qui sont bloqués. Si un SPID est bloqué par un autre SPID, examinez le SPID propriétaire de la ressource (le SPID bloquant). Ce SPID propriétaire est-il également bloqué ? Vous pouvez parcourir la chaîne pour rechercher le bloqueur principal, puis rechercher la raison pour laquelle il conserve son verrou.

Pour ce faire, vous pouvez utiliser l’une des méthodes suivantes :

• Dans l’Explorateur d’objets SQL Server Management Studio (SSMS), cliquez avec le bouton droit sur l’objet serveur de niveau supérieur, développez Rapports, développez Rapports standard, puis sélectionnez Activité - Toutes les transactions bloquantes. Ce rapport montre les transactions actuelles à la tête d’une chaîne de blocage. Si vous développez la transaction, le rapport affiche les transactions bloquées par la transaction de tête. Ce rapport affiche également l’instruction SQL bloquante et l’instruction SQL bloquée.

• Ouvrez Le moniteur d’activité dans SSMS et reportez-vous à la colonne Blocked By. Pour plus d’informations sur le Moniteur d’activité, cliquez ici.

Des méthodes plus détaillées basées sur des requêtes sont également disponibles à l’aide de DMV :

• Les sp_who commandes et sp_who2 les commandes sont des commandes plus anciennes pour afficher toutes les sessions actuelles. La DMV sys.dm_exec_sessions renvoie plus de données dans un jeu de résultats plus facile à interroger et à filtrer. Vous trouverez la DMV sys.dm_exec_sessions au cœur d’autres requêtes.

• Si vous avez déjà identifié une session particulière, vous pouvez utiliser DBCC INPUTBUFFER(<session_id>) pour rechercher la dernière instruction qui a été soumise par une session. Des résultats similaires peuvent être retournés avec la fonction de gestion dynamique (DMF) sys.dm_exec_input_buffer, dans un jeu de résultats plus facile à interroger et à filtrer, en fournissant le session_id et le request_id. Par exemple, pour retourner la dernière requête soumise par session_id 66 et request_id 0 :

SELECT * FROM sys.dm_exec_input_buffer (66,0);

• Reportez-vous à la sys.dm_exec_requests colonne et référencez-la blocking_session_id . Lorsque blocking_session_id = 0, aucune session n’est bloquée. Si sys.dm_exec_requests répertorie uniquement les requêtes en cours d’exécution, toutes les connexions (actives ou non) sont répertoriées dans sys.dm_exec_sessions. Tirez parti de cette jointure commune entre sys.dm_exec_requests et sys.dm_exec_sessions dans la requête suivante. N’oubliez pas d’être retournée par sys.dm_exec_requests, la requête doit être en cours d’exécution active avec SQL Server.

• Exécutez cet exemple de requête pour rechercher les requêtes en cours d’exécution et leur texte actuel de lot SQL ou de mémoire tampon d’entrée en utilisant les DMV sys.dm_exec_sql_text ou sys.dm_exec_input_buffer. Si les données retournées par la text colonne de sys.dm_exec_sql_text valeur NULL, la requête n’est pas en cours d’exécution. Dans ce cas, la event_info colonne de cette colonne contient la dernière chaîne de sys.dm_exec_input_buffer commande passée au moteur SQL. Cette requête peut également être utilisée pour identifier les sessions bloquant d’autres sessions, par exemple avec une liste de session_ids bloqués par session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

• Exécutez cet exemple de requête plus complexe, fourni par le Support Microsoft, pour identifier le début d’une chaîne de blocage de plusieurs sessions, notamment le texte de requête des sessions impliquées dans une chaîne de blocage.

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

• Pour intercepter des transactions longues ou non validées, utilisez un autre ensemble de DMV pour afficher les transactions ouvertes actuelles, notamment sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections et sys.dm_exec_sql_text. Il existe plusieurs DMV associées au suivi des transactions : voir plus de DMV sur les transactions ici.

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

• Référence sys.dm_os_waiting_tasks qui se trouve au niveau de la couche thread/tâche de SQL Server. Cela retourne des informations sur ce que SQL wait_type la requête rencontre actuellement. Comme pour sys.dm_exec_requests, sys.dm_os_waiting_tasks ne retourne que les requêtes actives.

• Utilisez la DMV sys.dm_tran_locks pour obtenir des informations plus granulaires sur les verrous placés par les requêtes. Cette vue de gestion dynamique peut retourner de grandes quantités de données sur une instance SQL Server de production et est utile pour diagnostiquer les verrous actuellement conservés.

En raison de la JOINTURE INTERNE sur sys.dm_os_waiting_tasks, la requête suivante limite la sortie de sys.dm_tran_locks aux requêtes actuellement bloquées, à leur état d’attente et à leurs verrous :

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

Grâce aux DMV, le stockage des résultats de requête dans le temps fournira des points de données qui vous permettront d’examiner le blocage sur un intervalle de temps donné afin d’identifier les blocages persistants ou les tendances. L’outil d’accès à CSS pour résoudre ces problèmes consiste à utiliser le collecteur de données PSSDiag. Cet outil utilise les statistiques « SQL Server Perf » pour collecter des jeux de résultats à partir de DMV référencés ci-dessus, au fil du temps. À mesure que cet outil évolue constamment, passez en revue la dernière version publique de DiagManager sur GitHub.

Collecter des informations à partir d’événements étendus

En plus des informations ci-dessus, il est souvent nécessaire de capturer une trace des activités sur le serveur pour examiner minutieusement un problème de blocage dans SQL Server. Par exemple, si une session exécute plusieurs instructions dans une transaction, seule la dernière instruction soumise sera représentée. Toutefois, l’une des instructions précédentes peut être la raison pour laquelle les verrous sont toujours maintenus. Une trace vous permet de voir toutes les commandes exécutées par une session dans la transaction en cours.

Il existe deux façons de capturer des traces dans SQL Server ; Événements étendus (XEvents) et traces de profileur. Toutefois, les traces SQL utilisant SQL Server Profiler sont déconseillées. Les XEvents sont la plateforme de suivi plus récente et supérieure qui permet une plus grande polyvalence et moins d’impact sur le système observé, et son interface est intégrée à SSMS.

Il existe des sessions d’événements étendus prédéfinis prêtes à démarrer dans SSMS, répertoriées dans l’Explorateur d’objets sous le menu de XEvent Profiler. Pour plus d’informations, consultez XEvent Profiler. Vous pouvez également créer vos propres sessions d’événements étendus personnalisées dans SSMS, voir l’Assistant Nouvelle session d’événements étendus. Pour résoudre les problèmes de blocage, nous allons généralement capturer :

• Erreurs de catégorie :
  • Attention
  • Blocked_process_report**
  • Error_reported (administrateur de canal)
  • Exchange_spill
  • Execution_warning

**Pour configurer le seuil et la fréquence à laquelle les rapports de processus bloqués sont générés, utilisez la commande sp_configure pour configurer l’option de seuil de processus bloqué, qui peut être définie en secondes. Par défaut, aucun rapport de processus bloqué n'est généré.

• Catégorie Avertissements :

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Catégorie Exécution :

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Verrou de catégorie

  • Lock_deadlock

• Session de catégorie

  • Existing_connection
  • Connexion
  • Logout

Identifier et résoudre les scénarios de blocage courants

En examinant les informations ci-dessus, vous pouvez déterminer la cause de la plupart des problèmes de blocage. Le reste de cet article explique comment utiliser ces informations pour identifier et résoudre certains scénarios de blocage courants. Cette discussion suppose que vous avez utilisé les scripts bloquants (référencés précédemment) pour capturer des informations sur les SPID bloquants et avoir capturé l’activité d’application à l’aide d’une session XEvent.

Analyser les données de blocage

• Examinez la sortie des DMV sys.dm_exec_requests et sys.dm_exec_sessions pour déterminer les têtes des chaînes de blocage, en utilisant blocking_these et session_id. Cela permet d’identifier plus clairement les requêtes qui sont bloquées et celles qui bloquent. Examinez plus en détail les sessions bloquées et bloquantes. Existe-t-il un point commun ou une racine à la chaîne de blocage ? Elles partagent probablement une table commune, et une ou plusieurs sessions impliquées dans une chaîne de blocage effectuent une opération d’écriture.

• Examinez la sortie des DMV sys.dm_exec_requests et sys.dm_exec_sessions pour obtenir des informations sur les SPID en tête de la chaîne de blocage. Recherchez les colonnes suivantes :

  • sys.dm_exec_requests.status

    Cette colonne indique l’état d’une requête particulière. En règle générale, un état de veille indique que l’exécution du SPID est terminée et qu’il attend que l’application envoie une autre requête ou un autre lot. Un état Exécutable ou Exécution en cours indique que le SPID est en train de traiter une requête. Le tableau suivant fournit une brève explication des différentes valeurs d’état.

    Statut	Signification
    Arrière-plan	Le SPID exécute une tâche en arrière-plan, telle qu’une détection de blocage, l’écriture dans un journal ou un point de contrôle.
    En état de veille	Le SPID n’est pas en cours d’exécution. Cela indique généralement que le SPID attend une commande de l’application.
    Exécution en cours	Le SPID est actuellement en cours d’exécution sur un planificateur.
    Exécutable	Le SPID est dans la file d’attente exécutable d’un planificateur et attend d’obtenir l’heure du planificateur.
    Interrompu	Le SPID attend une ressource, telle qu’un verrou.

  • sys.dm_exec_sessions.open_transaction_count

    Cette colonne vous indique le nombre de transactions ouvertes dans cette session. Si cette valeur est supérieure à 0, le SPID se trouve dans une transaction ouverte et peut contenir des verrous acquis par toute instruction dans la transaction. La transaction ouverte peut avoir été créée par une instruction active ou par une demande d’instruction qui s’est exécutée dans le passé et qui n’est plus active.

  • sys.dm_exec_requests.open_transaction_count

    De même, cette colonne vous indique le nombre de transactions ouvertes dans cette demande. Si cette valeur est supérieure à 0, le SPID se trouve dans une transaction ouverte et peut contenir des verrous acquis par n’importe quelle instruction active au sein de la transaction. Contrairement sys.dm_exec_sessions.open_transaction_countà , s’il n’existe pas de requête active, cette colonne affiche 0.

  • sys.dm_exec_requests.wait_type, wait_time et last_wait_type

    Si la sys.dm_exec_requests.wait_type a la valeur NULL, la requête n’attend rien et la valeur last_wait_type indique le dernier wait_type que la requête a rencontrée. Pour plus d’informations sur sys.dm_os_wait_stats et pour obtenir une description des types d’attente les plus courants, consultez sys.dm_os_wait_stats. La valeur de wait_time peut être utilisée pour déterminer si la requête progresse. Quand une requête sur la table sys.dm_exec_requests retourne une valeur dans la colonne wait_time inférieure à la valeur wait_time d’une requête précédente de sys.dm_exec_requests, cela indique que le verrou précédent a été acquis et libéré, et que la requête continue d’attendre un nouveau verrou (en supposant une valeur wait_time différente de zéro). Cela peut être vérifié en comparant la valeur wait_resource de la sortie de sys.dm_exec_requests, qui affiche la ressource pour laquelle la demande est en attente.

  • sys.dm_exec_requests.wait_resource

    Cette colonne indique la ressource sur laquelle une demande bloquée est en attente. Le tableau suivant énumère les formats courants de wait_resource et leur signification :

    Resource	Format	Exemple	Explication
    Table de charge de travail	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	Dans ce cas, l’ID de base de données 5 est l’exemple de base de données pubs et object_id 261575970 est la table des titres et 1 est l’index cluster.
    Page	DatabaseID:FileID:PageID	PAGE: 5:1:104	Dans ce cas, l’ID de base de données 5 est « pubs », l’ID de fichier 1 est le fichier de données primaire et la page 104 est une page appartenant à la table « titles ». Pour identifier l’object_id auquel la page appartient, utilisez la fonction de gestion dynamique sys.dm_db_page_info, en transmettant les DatabaseID, FileId et PageId de wait_resource.
    Clé	DatabaseID:Hobt_id (valeur de hachage pour la clé d’index)	KEY: 5:72057594044284928 (3300a4f361aa)	Dans ce cas, l’ID de base de données 5 est « pubs », Hobt_ID 72057594044284928 correspond à index_id 2 pour object_id 261575970 (table « titles »). Utilisez l’affichage sys.partitions catalogue pour associer l’affichage hobt_id à un élément particulier index_id et object_id. Il n’existe aucun moyen de décomposer le hachage de la clé d’index en une valeur de clé spécifique.
    Ligne	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	Dans ce cas, l’ID de base de données 5 est « pubs », l’ID de fichier 1 est le fichier de données primaire, la page 104 est une page appartenant à la table « titles » et l’emplacement 3 indique la position de la ligne sur la page.
    Compiler	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	Dans ce cas, l’ID de base de données 5 est « pubs », l’ID de fichier 1 est le fichier de données primaire, la page 104 est une page appartenant à la table « titles » et l’emplacement 3 indique la position de la ligne sur la page.

  • sys.dm_tran_active_transactions La DMV sys.dm_tran_active_transactions contient des données sur les transactions ouvertes qui peuvent être jointes à d’autres DMV pour obtenir une image complète des transactions en attente de validation ou de restauration. Utilisez la requête suivante pour renvoyer des informations sur les transactions ouvertes, jointes à d’autres DMV, y compris sys.dm_tran_session_transactions. Tenez compte de l’état actuel d’une transaction, de transaction_begin_time, et d’autres données de situation pour évaluer si elle peut être une source de blocage.

    SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                    WHEN 2 THEN 'Read-only transaction'
                                                    WHEN 3 THEN 'System transaction'
                                                    WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
                WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                WHEN 1 THEN 'The transaction has been initialized but has not started.'
                WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
                WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                WHEN 6 THEN 'The transaction has been committed.'
                WHEN 7 THEN 'The transaction is being rolled back.'
                WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
    FROM sys.dm_tran_active_transactions tat 
    INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
    INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
    

  • D’autres colonnes

    Les colonnes restantes dans sys.dm_exec_sessions et sys.dm_exec_request peuvent également fournir des indications sur la racine d’un problème. Leur utilité varie selon les circonstances du problème. Par exemple, vous pouvez déterminer si le problème se produit uniquement à partir de certains clients (hostname), sur certaines bibliothèques réseau (client_interface_name), lorsque le dernier lot envoyé par un SPID était last_request_start_time dans sys.dm_exec_sessions, pendant combien de temps une demande était en cours d’exécution à sys.dm_exec_requestsl’aidestart_time, et ainsi de suite.

Scénarios de blocage courants

Le tableau ci-dessous établit une correspondance entre les symptômes courants et leurs causes probables.

Les colonnes wait_type, open_transaction_count et status font référence aux informations retournées par sys.dm_exec_request, et les autres colonnes peuvent être retournées par sys.dm_exec_sessions. La colonne « Resolves? » indique si le blocage se résoudra de lui-même ou si la session doit être supprimée à l’aide de la commande KILL. Pour plus d’informations, consultez KILL (Transact-SQL).

Scénarios de blocage détaillés

Scénario 1 : Blocage provoqué par une requête en cours d’exécution normale avec un temps d’exécution long

Dans ce scénario, une requête en cours d’exécution a acquis des verrous et les verrous ne sont pas libérés (ils sont affectés par le niveau d’isolation des transactions). Les autres sessions attendront donc les verrous jusqu’à ce qu’ils soient libérés.

Résolution :

La solution à ce problème de blocage consiste à rechercher des moyens d’optimiser la requête. Cette classe de problème de blocage peut être un problème de performances et vous oblige à le traiter comme tel. Pour plus d’informations sur le dépannage d’une requête spécifique à exécution lente, consultez Comment résoudre les problèmes de requête à exécution lente sur SQL Server. Pour plus d’informations, consultez Surveiller et régler les performances.

Les rapports intégrés à SSMS à partir du Magasin des requêtes (introduits dans SQL Server 2016) sont également un outil hautement recommandé et précieux pour identifier les requêtes les plus coûteuses et les plans d’exécution non optimaux.

Si vous avez une requête longue qui bloque d’autres utilisateurs et qu’elle ne peut pas être optimisée, envisagez de la déplacer d’un environnement OLTP vers un système de création de rapports dédié. Vous pouvez également utiliser des groupes de disponibilité Always On pour synchroniser un réplica en lecture seule de la base de données.

Scénario 2 : Blocage provoqué par un SPID en veille qui a une transaction non validée

Ce type de blocage peut souvent être identifié par un SPID en veille ou en attente d’une commande avec un niveau d’imbrication des transactions (@@TRANCOUNTopen_transaction_countà partir de ) supérieur à sys.dm_exec_requestszéro. Cette situation peut se produire si l’application rencontre un délai d’expiration de requête ou émet une annulation sans émettre le nombre requis d’instructions ROLLBACK et/ou COMMIT. Lorsqu’un SPID reçoit un délai d’attente de requête ou une annulation, il met fin à la requête et au lot en cours, mais ne restaure pas automatiquement la transaction. L’application est responsable de cela, car SQL Server ne peut pas supposer qu’une transaction entière doit être restaurée en raison d’une seule requête annulée. Le délai d’expiration ou l’annulation de la requête s’affiche en tant qu’événement signal ATTENTION pour le SPID dans la session d’événements étendus.

Pour illustrer une transaction explicite non validée, émettez la requête suivante :

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

Ensuite, exécutez cette requête dans la même fenêtre :

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

La sortie de la deuxième requête indique que le nombre de transactions est un. Tous les verrous acquis dans la transaction sont conservés jusqu’à ce que la transaction ait été validée ou restaurée. Si les applications ouvrent et valident explicitement des transactions, une erreur de communication ou autre peut laisser la session et sa transaction dans un état ouvert.

Utilisez le script plus haut dans cet article basé sur sys.dm_tran_active_transactions pour identifier les transactions actuellement non validées dans l’instance.

Résolutions :

• Cette classe de problème de blocage peut également être un problème de performances et vous oblige à le traiter comme tel. Si le temps d’exécution de la requête peut être diminué, le délai d’expiration ou l’annulation de la requête peut ne pas se produire. Il est important que l’application puisse gérer le délai d’expiration ou les scénarios d’annulation s’ils surviennent, mais vous pouvez également tirer parti de l’examen des performances de la requête.

• Les applications doivent gérer correctement les niveaux d’imbrication des transactions, sinon elles peuvent entraîner un problème bloquant suite à l’annulation de la requête de cette manière. Tenez compte des éléments suivants :

  • Dans le gestionnaire d’erreurs de l’application cliente, exécutez IF @@TRANCOUNT > 0 ROLLBACK TRAN après toute erreur, même si l’application cliente ne pense pas qu’une transaction est ouverte. La vérification des transactions ouvertes est requise, car une procédure stockée appelée pendant le lot peut avoir démarré une transaction sans connaître l’application cliente. Certaines conditions, telles que l’annulation de la requête, empêchent la procédure de s’exécuter au-delà de l’instruction actuelle. Par conséquent, même si la procédure a une logique pour vérifier IF @@ERROR <> 0 et abandonner la transaction, ce code de restauration ne sera pas exécuté dans ces cas.

  • Si le regroupement de connexions est utilisé dans une application qui ouvre la connexion et exécute quelques requêtes avant de renvoyer la connexion au pool, par exemple une application web, la désactivation temporaire du regroupement de connexions peut aider à atténuer le problème jusqu’à ce que l’application cliente soit modifiée pour gérer les erreurs de manière appropriée. En désactivant le regroupement de connexions, la libération de la connexion entraîne une déconnexion physique de la connexion SQL Server, ce qui entraîne la restauration de toutes les transactions ouvertes par le serveur.

  • Utilisez SET XACT_ABORT ON pour la connexion ou dans les procédures stockées qui commencent les transactions et ne nettoient pas après une erreur. En cas d’erreur d’exécution, ce paramètre abandonne toutes les transactions ouvertes et rend le contrôle au client. Pour plus d’informations, consultez SET XACT_ABORT (Transact-SQL).

Scénario 3 : Blocage provoqué par un SPID dont l’application cliente correspondante n’a pas extrait toutes les lignes de résultat à la fin

Après l’envoi d’une requête au serveur, toutes les applications doivent extraire immédiatement toutes les lignes de résultat jusqu’au bout. Si une application n’extrait pas toutes les lignes du résultat, des verrous peuvent être maintenus sur les tables et bloquer d’autres utilisateurs. Si vous utilisez une application qui envoie en toute transparence des instructions SQL au serveur, l’application doit extraire toutes les lignes de résultat. Si ce n’est pas le cas (et s’il ne peut pas être configuré pour le faire), vous ne pourrez peut-être pas résoudre le problème de blocage. Pour éviter ce problème, vous pouvez limiter les applications au comportement médiocre à un signalement ou à une base de données d’aide à la décision, séparée de la base de données OLTP principale.

Résolution :

L’application doit être réécrite afin d’extraire toutes les lignes du résultat. Cela n’exclut pas l’utilisation de OFFSET et FETCH dans la clause ORDER BY d’une requête pour effectuer la pagination côté serveur.

Scénario 4 : Blocage provoqué par un blocage de client/serveur distribué

Contrairement à un interblocage conventionnel, un interblocage distribué n’est pas détectable à l’aide du gestionnaire de verrous SGBDR. Cela est dû au fait qu’une seule des ressources impliquées dans l’interblocage est un verrou SQL Server. L’autre côté du blocage est au niveau de l’application cliente, sur lequel SQL Server n’a aucun contrôle. Les deux sections suivantes montrent comment cela peut se produire et comment l’application peut l’éviter.

Exemple A : interblocage distribué client/serveur avec un thread client unique

Si le client dispose de plusieurs connexions ouvertes et d’un seul thread d’exécution, l’interblocage distribué suivant peut se produire. Notez que le terme dbproc utilisé ici fait référence à la structure de connexion cliente.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

Dans le cas indiqué ci-dessus, un thread d’application cliente unique a deux connexions ouvertes. Il envoie de façon asynchrone une opération SQL sur dbproc1. Cela signifie qu’il n’attend pas l’appel pour retourner avant de continuer. L’application envoie ensuite une autre opération SQL sur dbproc2 et attend les résultats pour commencer à traiter les données retournées. Lorsque les données commencent à revenir (selon la première réponse de dbproc- supposons qu’il s’agit dbproc1), il traite l’achèvement de toutes les données retournées sur ce dbproc. Il extrait les résultats de dbproc1 jusqu’à ce que SPID1 soit bloqué sur un verrou détenu par SPID2 (car les deux requêtes s’exécutent de manière asynchrone sur le serveur). À ce stade, dbproc1 attend indéfiniment plus de données. SPID2 n’est pas bloqué sur un verrou, mais tente d’envoyer des données à son client, dbproc2. Toutefois, dbproc2 est effectivement bloqué sur dbproc1 au niveau de la couche application, car le thread unique d’exécution de l’application est utilisé par dbproc1. Cela entraîne un blocage que SQL Server ne peut pas détecter ou résoudre, car une seule des ressources impliquées est une ressource SQL Server.

Exemple B : interblocage distribué client/serveur avec un thread par connexion

Même si un thread distinct existe pour chaque connexion sur le client, une variante de ce blocage distribué peut toujours se produire comme indiqué ci-dessous.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Ce cas est similaire à l’exemple A, à l’exception de dbproc2 et SPID2, exécutent une SELECT instruction avec l’intention d’effectuer un traitement de ligne à temps et de remettre chaque ligne via une mémoire tampon à dbproc1 pour un INSERT, UPDATEou DELETE une instruction sur la même table. Finalement, SPID1 (exécutant le INSERT, UPDATEou DELETE) devient bloqué sur un verrou détenu par SPID2 (exécutant le SELECT). SPID2 écrit une ligne de résultat dans le client dbproc2. Dbproc2 tente ensuite de passer la ligne dans une mémoire tampon à dbproc1, mais trouve dbproc1 est occupé (il est bloqué en attente sur SPID1 pour terminer le courant INSERT, qui est bloqué sur SPID2). À ce stade, dbproc2 est bloqué au niveau de la couche application par dbproc1 dont SPID (SPID1) est bloqué au niveau de la base de données par SPID2. Là encore, cela entraîne un blocage que SQL Server ne peut pas détecter ou résoudre, car une seule des ressources impliquées est une ressource SQL Server.

Les deux exemples A et B sont des problèmes fondamentaux auxquels les développeurs d’applications doivent être conscients. Ils doivent coder des applications pour gérer ces cas de manière appropriée.

Résolution :

Lorsqu’un délai d’expiration de requête a été fourni, si le blocage distribué se produit, il est rompu lorsque le délai d’expiration se produit. Référencez la documentation de votre fournisseur de connexions pour plus d’informations sur l’utilisation d’un délai d’attente de requête.

Scénario 5 : Blocage provoqué par une session dans un état de restauration

Une requête de modification de données qui est tuée ou annulée en dehors d’une transaction définie par l’utilisateur est restaurée. Cela peut également être un effet secondaire de la déconnexion de la session réseau du client ou se produire lorsqu’une requête est sélectionnée comme victime d’un blocage. Cela peut souvent être identifié en observant la sortie de sys.dm_exec_requests, qui peut indiquer le ROLLBACK command, et la percent_complete colonne peut afficher la progression.

Une requête de modification de données qui est tuée ou annulée en dehors d’une transaction définie par l’utilisateur est restaurée. Cela peut également se produire en tant qu’effet secondaire du redémarrage de l’ordinateur client et de sa déconnexion de session réseau. De même, une requête sélectionnée comme victime d’interblocage sera restaurée. Une requête de modification des données ne peut souvent pas être restaurée plus rapidement que les modifications initialement appliquées. Par exemple, si une instruction ou INSERTUPDATE une DELETEinstruction était en cours d’exécution pendant une heure, la restauration peut prendre au moins une heure. Ce comportement est attendu, car les modifications apportées doivent être restaurées, ou l’intégrité transactionnelle et physique dans la base de données serait compromise. Comme cela doit se produire, SQL Server marque le SPID dans un état d’or ou de restauration (ce qui signifie qu’il ne peut pas être tué ou sélectionné comme victime d’interblocage). Cela peut souvent être identifié en observant la sortie de sp_who, qui peut indiquer la commande ROLLBACK. La status colonne de sys.dm_exec_sessions cette propriété indique un état ROLLBACK.

Résolution :

Vous devez attendre que la session termine la restauration des modifications qui ont été apportées.

Si l’instance est arrêtée au milieu de cette opération, la base de données est en mode de récupération lors du redémarrage, et elle est inaccessible tant que toutes les transactions ouvertes ne sont pas traitées. La récupération de démarrage prend essentiellement le même temps par transaction que la récupération au moment de l’exécution, et la base de données est inaccessible pendant cette période. Par conséquent, forcer le serveur à corriger un SPID dans un état de restauration sera souvent contre-productif. Dans SQL Server 2019 avec la récupération de base de données accélérée activée, cela ne doit pas se produire.

Pour éviter cette situation, n’effectuez pas d’opérations d’écriture par lots volumineux, de création d’index ou d’opérations de maintenance pendant les heures de forte utilisation des systèmes OLTP. Si possible, effectuez ces opérations pendant les périodes de faible activité.

Scénario 6 : Blocage provoqué par une transaction orpheline

Il s’agit d’un scénario de problème courant et se chevauche en partie avec le scénario 2. Si l’application cliente s’arrête, la station de travail cliente est redémarrée ou une erreur d’abandon par lot peut laisser une transaction ouverte. Cette situation peut se produire si l’application ne restaure pas la transaction dans les blocs de l’application CATCH ou FINALLY si elle ne gère pas cette situation.

Dans ce scénario, alors que l’exécution d’un lot SQL a été annulée, l’application laisse la transaction SQL ouverte. Du point de vue de l’instance SQL Server, le client semble toujours présent et tous les verrous acquis sont conservés.

Pour illustrer une transaction orpheline, exécutez la requête suivante, qui simule une erreur d’abandon de lot en insérant des données dans une table inexistante :

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

Ensuite, exécutez cette requête dans la même fenêtre :

SELECT @@TRANCOUNT;

La sortie de la deuxième requête indique que le nombre de transactions est un. Tous les verrous acquis dans la transaction sont conservés jusqu’à ce que la transaction soit validée ou restaurée. Étant donné que le lot est déjà abandonné par la requête, l’application qui l’exécute peut continuer à exécuter d’autres requêtes sur la même session sans nettoyer la transaction qui est toujours ouverte. Le verrou se tiendra jusqu’à ce que la session soit tuée ou que l’instance SQL Server soit redémarrée.

Résolutions :

• La meilleure façon d’éviter cette condition consiste à améliorer la gestion des erreurs/exceptions de l’application, en particulier pour les terminaisons inattendus. Veillez à utiliser un Try-Catch-Finally bloc dans le code de l’application et à restaurer la transaction en cas d’exception.
• Envisagez d’utiliser SET XACT_ABORT ON pour la session ou dans toutes les procédures stockées qui commencent les transactions et ne nettoient pas après une erreur. En cas d’erreur d’exécution qui abandonne le lot, ce paramètre restaure automatiquement toutes les transactions ouvertes et retourne le contrôle au client. Pour plus d’informations, consultez SET XACT_ABORT (Transact-SQL).
• Pour résoudre une connexion orpheline d’une application cliente qui s’est déconnectée sans nettoyer correctement ses ressources, vous pouvez arrêter le SPID à l’aide de la KILL commande. Pour plus d’informations, consultez KILL (Transact-SQL).

La commande KILL prend la valeur du SPID comme entrée. Par exemple, pour tuer SPID 9, exécutez la commande suivante :

KILL 99

Voir aussi

• Surveillance des performances à l’aide du magasin des requêtes
• Résoudre les problèmes de blocage causés par l’escalade de verrous dans SQL Server
• Guide du verrouillage des transactions et du contrôle de version de ligne
• SET TRANSACTION ISOLATION LEVEL
• Démarrage rapide : Événements étendus dans SQL Server