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

S’applique à : SQL Server Azure SQL Managed Instance

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

Résumé

Le blocage est normal dans n’importe quelle base de données relationnelle basée sur les verrous, mais le blocage persistant ou de longue durée dégrade le débit et provoque des expirations d’application. Cet article décrit le blocage dans SQL Server, comment capturer des données bloquantes à partir de vues de gestion dynamique (DMV) et d’événements étendus, et comment identifier le bloqueur principal. Il décrit ensuite les scénarios de blocage les plus courants et leurs résolutions.

Dans cet article, le terme connexion fait référence à une session connectée unique de la base de données. Chaque connexion apparaît sous la forme d’un ID de session (SPID) ou session_id dans de nombreux DMV. Chaque SPID est souvent appelé « processus », mais ce n’est pas un processus de système d’exploitation distinct. Il s’agit des ressources côté serveur et des structures de données qui servicent une connexion cliente. Une application cliente unique peut avoir une ou plusieurs connexions. Du point de vue de SQL Server, les connexions de nombreux clients et de nombreuses connexions d'un client sont traitées de la même façon. 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, le blocage se produit lorsqu’une session détient un verrou sur une ressource spécifique et qu’un deuxième SPID tente d’acquérir un type de verrou en conflit sur la même ressource. 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 tout au long 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 des 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 une transaction, le type de requête, le niveau d’isolation des transactions et si les indicateurs de verrou sont utilisés dans la requête déterminent la durée pendant laquelle les verrous sont conservés. 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 jusqu’au point où ils provoquent un effet néfaste sur les performances du système, ils résultent de 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

Lorsque vous rencontrez un problème de blocage, vous pouvez vous concentrer sur les problèmes de paramétrage côté serveur et de plateforme. Toutefois, une attention particulière à la base de données peut ne pas entraîner de résolution. Il peut absorber le temps et l’énergie que vous pourriez mieux diriger lors de l’examen de l’application cliente et des requêtes qu’il envoie. Quel que soit le niveau de visibilité que l’application expose concernant les appels de base de données qu’elle effectue, un problème bloquant nécessite fréquemment l’inspection des instructions SQL exactes envoyées par l’application et le comportement exact de l’application concernant l’annulation de requête, la gestion des connexions, l’extraction de toutes les lignes de résultat, et ainsi de suite. 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, vous ne pourrez peut-être pas résoudre les problèmes de blocage. Examinez ce potentiel de près 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, évaluez la consommation des ressources, le niveau d’isolation et la longueur du chemin de transaction pour chaque requête. Chaque requête et chaque transaction doivent être aussi simples que possible. Exercer une bonne discipline de gestion des connexions. Sans cela, l’application peut sembler avoir des performances acceptables à un faible nombre d’utilisateurs, mais les performances peuvent se dégrader de manière significative à mesure que le nombre d’utilisateurs augmente vers le haut.

Avec une conception d’application et de requête appropriée, SQL Server peut 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 vous vous trouvez, la méthodologie de dépannage du verrouillage est la même. Ces séparations logiques déterminent 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 que vous avez identifié la requête problématique (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, vous pouvez apporter des modifications en remaniant la requête et la transaction.

Étapes de résolution du problème :

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

2. Recherchez la requête et la transaction qui provoquent le blocage (ce qui contient les verrous pendant une période prolongée).

3. Analysez et comprenez pourquoi le blocage prolongé se produit.

4. Résolvez le problème de blocage en remaniant 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 du verrouillage et du blocage sur SQL Server. Pour collecter ces données, il existe deux méthodes complémentaires.

La première méthode consiste à interroger des objets de gestion dynamique (DMV) et à stocker les résultats pour la comparaison au fil du 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 deuxième méthode consiste à utiliser des événements étendus (XEvents) ou des traces sql Profiler 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 dépannage se concentre sur XEvents.

Collecter des informations à partir de DMV

Pour résoudre les problèmes de blocage, utilisez des DMV pour identifier le SPID (ID de session) à la tête de la chaîne de blocage et de l’instruction SQL. Recherchez les SPID victimes qui sont bloquées. Si un SPID est bloqué par un autre SPID, examinez le SPID propriétaire de la ressource (SPID bloquant). Est-ce que le propriétaire SPID est également bloqué ? Vous pouvez suivre la chaîne pour trouver le bloqueur de tête, puis examiner pourquoi il maintient son verrou.

Pour cela, appliquez l’une des méthodes suivantes :

• Dans l’Explorateur d’objets de SQL Server Management Studio (SSMS), cliquez avec le bouton droit sur l’objet serveur de niveau supérieur, développez Rapports et Rapports standard, puis sélectionnez Activité - Toutes les transactions bloquantes. Ce rapport montre les transactions actuelles en 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 bloquant SQL et l’instruction SQL bloquée.

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

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

• Les sp_who commandes et sp_who2 les commandes sont des commandes plus anciennes qui affichent toutes les sessions actuelles. La vue de sys.dm_exec_sessions gestion dynamique retourne 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, utilisez DBCC INPUTBUFFER(<session_id>) cette option pour rechercher la dernière instruction envoyée par une session. Les résultats similaires sont retournés par la sys.dm_exec_input_buffer fonction de gestion dynamique (DMF), dans un jeu de résultats plus facile à interroger et filtrer, en fournissant les session_id et les 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 et référencez la colonne blocking_session_id. Quand blocking_session_id = 0, une session n’est pas bloquée. Bien que sys.dm_exec_requests les listes ne s’exécutent actuellement que, sys.dm_exec_sessions répertorie n’importe quelle connexion (active ou non). Tirez parti de cette jointure commune entre sys.dm_exec_requests et sys.dm_exec_sessions dans la requête suivante. N’oubliez pas que pour être retournée par sys.dm_exec_requests, la requête doit être exécutée activement 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 renvoyées par la colonne text de sys.dm_exec_sql_text sont NULL, la requête n’est pas en cours d’exécution. Dans ce cas, la colonne event_info de sys.dm_exec_input_buffer contient la dernière chaîne de 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 les transactions de longue durée 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. Pour plus de DMV associées au suivi des transactions, consultez DMV sur les transactions.

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. Cette vue DMV 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 DMV peut renvoyer de grandes quantités de données sur une instance de production de SQL Server 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 indispensable pour que CSS résolve ces problèmes est le collecteur de données PSSDiag. Cet outil utilise « les statistiques de perf SQL Server » pour collecter des jeux de résultats à partir de DMV référencées ci-dessus, au fil du temps. Comme cet outil est en constante évolution, passez en revue la dernière version publique de DiagManager sur GitHub.

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

En plus des informations précédentes, vous devez souvent capturer une trace des activités sur le serveur pour examiner soigneusement 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 envoyée par la session est représentée. Toutefois, l’une des instructions précédentes peut être la raison pour laquelle les verrous sont toujours conservés. Une trace vous permet de voir toutes les commandes qu’une session exécute dans la transaction actuelle.

Vous pouvez capturer des traces dans SQL Server à l’aide de deux méthodes : Événements étendus (XEvents) et Traces profileur. Toutefois, les traces SQL qui utilisent le profileur SQL Server sont déconseillées. XEvents est la plateforme de suivi plus récente et supérieure qui offre plus de polyvalence et moins d’impact sur le système observé. Son interface est intégrée à SSMS.

Dans Explorateur d'objets, sous le menu de XEvent Profiler, vous trouverez des sessions d’événements étendus prédéfinies prêtes à démarrer dans SSMS. Pour plus d’informations, consultez XEvent Profiler. Vous pouvez également créer vos propres sessions d’événements étendus personnalisées dans SSMS. Pour plus d’informations, consultez l’Assistant Nouvelle session d’événements étendus. Pour résoudre les problèmes de blocage, vous capturez généralement :

• 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 de génération des rapports de processus bloqué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 précédentes, vous pouvez déterminer la cause de la plupart des problèmes bloquants. 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 utilisez les scripts bloquants (référencés précédemment) pour capturer des informations sur les SPID bloquants et capturer l’activité de l’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. Cette sortie identifie clairement les requêtes qui sont bloquées et qui sont bloquées. 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 de SPID a terminé et 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 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 n’importe quelle instruction de la transaction. La transaction ouverte peut être créée par une instruction active ou par une requête d’instruction exécutée dans le passé et qui n’est plus active.

  • sys.dm_exec_requests.open_transaction_count

    De même, cette colonne affiche 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 une 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 valeur est NULL, la requête n’attend actuellement rien. La last_wait_type valeur indique la dernière wait_type que la demande 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. Lorsqu’une requête sur la sys.dm_exec_requests table retourne une valeur dans la wait_time colonne inférieure à la wait_time valeur d’une requête sys.dm_exec_requestsprécédente, cette condition indique que le verrou précédent a été acquis et libéré et attend maintenant un nouveau verrou (en supposant qu’il n’est pas égal à zéro wait_time). Vous pouvez vérifier cette condition en comparant la wait_resource sortie qui sys.dm_exec_requests affiche la ressource pour laquelle la requête 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)	CLÉ : 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 la vue catalogue de sys.partitions pour associer hobt_id à un index_id particulier 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.

  • 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), quand le dernier lot a été envoyé par un SPID (last_request_start_time dans sys.dm_exec_sessions), combien de temps une requête était en cours d’exécution (start_time dans sys.dm_exec_requests), etc.

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 renvoyées par sys.dm_exec_request, d’autres colonnes peuvent être renvoyé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 une longue durée d’exécution

Dans ce scénario, une requête en cours d’exécution a acquis des verrous, qui ne sont pas libérés (le niveau d’isolation de la transaction joue un rôle). Par conséquent, d’autres sessions attendront les verrous jusqu’à ce qu’ils soient libérés.

Résolution :

Pour résoudre ce problème de blocage, optimisez la requête. Ce type de problème de blocage peut être un problème de performances. Traitez-le 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 très 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 que vous ne pouvez pas l’optimiser, 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é AlwaysOn 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

Vous pouvez souvent identifier ce type de blocage 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. Cela peut se produire si l’application rencontre un délai d’expiration de requête ou émet une annulation sans également é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 traitement actuel, mais ne restaure pas automatiquement la transaction. L'application est responsable de cette action, 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 de 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 soit 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. Si vous pouvez réduire le temps d’exécution de la requête, le délai d’expiration ou l’annulation de la requête risque de 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, ou elles peuvent entraîner un problème de blocage après l’annulation de la requête de cette façon. 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 nécessaire, car une procédure stockée appelée pendant le lot peut avoir démarré une transaction à l’insu de 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 vous utilisez le regroupement de connexions dans une application qui ouvre la connexion et exécute quelques requêtes avant de libérer 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 provoque une déconnexion physique de la connexion SQL Server, ce qui entraîne la restauration du serveur sur toutes les transactions ouvertes.

  • Utilisez SET XACT_ABORT ON pour la connexion ou dans toute procédure stockée qui commence les transactions et qui n’est pas nettoyée à la suite d’une erreur. En cas d’erreur d’exécution, ce paramètre abandonne toutes les transactions ouvertes et retourne 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 récupéré 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 ne récupère pas toutes les lignes de résultat, elle peut laisser des verrous sur les tables qui bloquent 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 elle ne le fait pas (et si elle ne peut pas être configurée pour le faire), vous ne pourrez peut-être pas résoudre le problème de blocage. Pour éviter le problème, limitez les applications mal comportées à une base de données de création de rapports ou de prise de décision, distincte de la base de données OLTP principale.

Résolution :

Réécrire l’application pour extraire toutes les lignes du résultat à la fin. 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 interblocage client/serveur distribué

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

Exemple A : Interblocage client/serveur distribué 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. À noter que le terme dbproc utilisé ici désigne la structure de la connexion client.

 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 précédent, un thread d’application client unique a deux connexions ouvertes. Il envoie de façon asynchrone une opération SQL sur dbproc1. Il n’attend dès lors pas le retour de l’appel avant de continuer. L’application envoie ensuite une autre opération SQL sur dbproc2 et attend les résultats pour démarrer le traitement des données renvoyées. Lorsque les données commencent à revenir (selon la première réponse de dbproc - supposons qu’il s’agit de dbproc1), il traite l’achèvement de toutes les données retournées sur ce dbproc. Les résultats sont extraits de dbproc1 jusqu’à ce que SPID1 soit bloqué sur un verrou détenu par SPID2 (car les deux requêtes s’exécutent de façon 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 d’application, car le thread d’exécution unique de l’application est utilisé par dbproc1. Cette situation 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 illustré dans l’exemple suivant.

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, sauf que dbproc2 et SPID2 exécutent une instruction SELECT avec l’intention d’effectuer un traitement d’une ligne à la fois et de remettre chaque ligne via une mémoire tampon à dbproc1 pour une instruction INSERT, UPDATE ou DELETE sur la même table. SPID1 (qui exécute INSERT, UPDATE ou DELETE) finit par se bloquer sur un verrou maintenu par SPID2 (qui exécute SELECT). SPID2 écrit une ligne de résultat dans le client dbproc2. Dbproc2 tente ensuite de transmettre la ligne dans une mémoire tampon à dbproc1, mais constate que dbproc1 est occupé (bloqué en attendant que SPID1 termine l’instruction INSERT, qui est bloquée sur SPID2). À ce stade, dbproc2 est bloqué au niveau de la couche d’application par dbproc1, dont SPID (SPID1) est bloqué au niveau de la base de données par SPID2. Ici aussi, cela entraîne un interblocage 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 dont les développeurs d’applications doivent avoir connaissance. Ils doivent coder les applications pour gérer ces cas de manière appropriée.

Résolution :

Lorsque vous fournissez un délai d’expiration de requête, si le blocage distribué se produit, le délai d’attente interrompt le blocage. Pour plus d’informations sur l’utilisation d’un délai d’expiration de requête, consultez la documentation de votre fournisseur de connexions.

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

Lorsque vous supprimez ou annulez une requête de modification de données en dehors d’une transaction définie par l’utilisateur, la requête est annulée. Cette restauration peut également se produire en tant qu’effet secondaire de la déconnexion de la session réseau cliente, du redémarrage de l’ordinateur client ou de la demande sélectionnée en tant que victime d’interblocage. Vous ne pouvez souvent pas restaurer une requête de modification de données plus rapidement que les modifications initialement appliquées. Par exemple, si une instruction ou DELETEINSERT une UPDATEinstruction s’est exécutée pendant une heure, la restauration peut prendre au moins une heure. Ce comportement est attendu, car si les modifications ne sont pas restaurées, l’intégrité transactionnelle et physique de la base de données serait compromise. Étant donné que cette restauration doit se terminer, SQL Server marque la session dans un état KILLED/ROLLBACK et vous ne pouvez pas tuer la session à nouveau ou la sélectionner comme victime d'interblocage. Vous pouvez souvent identifier cet état en observant la sortie de sys.dm_exec_requests, où la command colonne signale KILLED/ROLLBACK et la percent_complete colonne affiche la progression.

Résolution :

Attendez que la session termine la restauration des modifications.

Si vous arrêtez l’instance au milieu de cette opération, la base de données est en mode de récupération lors du redémarrage, et elle n’est pas accessible tant que toutes les transactions ouvertes ne sont pas traitées. La récupération au 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 est souvent contre-productif. Dans SQL Server 2019 avec accélération de la récupération de base de données activée, cette situation 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 connexion orpheline

Il s’agit d’un scénario courant, similaire en partie avec le Scénario 2. Une transaction peut rester ouverte dans les cas suivants : arrêt de l’application cliente, redémarrage du poste de travail client ou survenue d’une erreur entraînant l’annulation du lot. Cette situation peut se produire si l’application ne restaure pas la transaction dans les blocs CATCH ou FINALLY de l’application ou si elle ne gère pas autrement cette situation.

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

Pour observer une transaction orpheline, exécutez la requête suivante, qui reproduit 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. Comme le lot est déjà annulé par la requête, l’application qui l’exécute peut continuer à exécuter d’autres requêtes sur la même session, sans effacer la transaction qui est toujours ouverte. Le verrou est maintenu jusqu’à ce que la session soit terminé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 d’application et des exceptions, en particulier pour les terminaisons inattendus. Veillez à utiliser un bloc Try-Catch-Finally 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 toute procédure stockée qui commence les transactions et qui n’est pas nettoyée à la suite d’une erreur. En cas d’erreur d’exécution qui abandonne le lot, ce paramètre annule 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 commande KILL. Pour référence, consultez l’article KILL (Transact-SQL).

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

KILL 99

Contenu connexe

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