Comprendre et résoudre les problèmes de blocage de SQL Server

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

Numéro de l’article d’origine dans la base de connaissances :224453

Objectif

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

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 d’un session_id dans de nombreuses 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. Au lieu de cela, chaque SPID se compose des ressources serveur et des structures de données nécessaires pour traiter les demandes d’une connexion unique à partir d’un client donné. Une application cliente unique peut avoir une ou plusieurs connexions. Du point de vue de SQL Server, il n’existe aucune différence entre plusieurs connexions à partir d’une seule application cliente sur un seul ordinateur client et plusieurs connexions à partir de plusieurs applications clientes ou de plusieurs ordinateurs clients : elles sont atomiques. Une connexion peut bloquer une autre connexion, quel que soit le client source.

Qu’est-ce que le blocage ?

Le blocage est une caractéristique inévitable et par défaut de tout système de gestion de base de données relationnelle (SGBDR) avec accès concurrentiel basé sur un verrou. 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, l’intervalle de temps pour lequel le premier SPID verrouille la ressource est petit. 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 continuer 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 d’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 conservés 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, la durée pendant laquelle les verrous sont détenus 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 verrouillage 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 des lignes
• Modes de verrouillage
• Compatibilité des verrous
• 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’à ce qu’il y ait un effet négatif sur les 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 au fil du temps, mais peut entraîner une dégradation 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 l’accès aux ressources affectées indéfiniment.

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, ce qui crée une cible mobile. Ces situations sont difficiles à résoudre à l’aide de SQL Server Management Studio pour limiter le problème à des requêtes individuelles. En revanche, la deuxième situation entraîne un état cohérent qui peut être plus facile à diagnostiquer.

Applications et blocage

Il peut y avoir une tendance à se concentrer sur les problèmes de plateforme et de réglage côté serveur en cas de problème de blocage. Toutefois, l’attention accordée uniquement à la base de données peut ne pas conduire à une résolution, et peut absorber le temps et l’énergie mieux dirigés vers l’examen de l’application cliente et les requêtes qu’elle soumet. Quel que soit le niveau de visibilité que l’application expose concernant les appels de base de données effectués, un problème de blocage nécessite néanmoins fréquemment l’inspection des instructions exactes SQL soumises par l’application et le comportement exact de l’application en ce qui concerne l’annulation des requêtes, la gestion des connexions, l’extraction de toutes les lignes de résultat, 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, l’extraction des résultats, etc., les problèmes de blocage peuvent ne pas être résolus. Ce potentiel doit être examiné 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.

Soyez attentif 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 d’accès de transaction doivent être évalués pour chaque requête. Chaque requête et transaction doivent être aussi légères que possible. Une bonne discipline de gestion des connexions doit être exercée. Sans elle, l’application peut sembler avoir des performances acceptables à un faible nombre d’utilisateurs, mais les performances peuvent se dégrader considérablement à 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 on se trouve, la méthodologie de résolution des problèmes de blocage est la même. Ces séparations logiques sont ce qui dictera le reste de la composition de cet article. Le concept consiste à rechercher le bloqueur d’en-tête et à identifier ce que cette requête fait et pourquoi elle bloque. Une fois que la requête problématique est identifiée (c’est-à-dire ce qui contient des verrous pendant une période prolongée), l’étape suivante consiste à analyser et à déterminer pourquoi le blocage se produit. Après avoir compris pourquoi, nous pouvons apporter des modifications en repensant la requête et la transaction.

Étapes de résolution des problèmes :

1. Identifier la session de blocage principale (bloqueur d’en-tête)

2. Rechercher la requête et la transaction à l’origine du blocage (ce qui contient des verrous pendant une période prolongée)

3. Analyser/comprendre pourquoi le blocage prolongé se produit

4. Résoudre le problème de blocage en repensant la requête et la transaction

Voyons maintenant comment identifier la session de blocage principale avec une capture de données appropriée.

Collecter des 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 consiste à interroger des objets de gestion dynamique (DMO) et à stocker les résultats à des fins de comparaison au fil du temps. Certains objets référencés dans cet article sont des vues de gestion dynamique (DMV) et d’autres des fonctions de gestion dynamique (DMF).

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

Collecter des informations à partir des DMV

Le référencement des vues de gestion dynamique (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 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). Le SPID propriétaire est-il également bloqué ? Vous pouvez parcourir la chaîne pour trouver le bloqueur d’en-tête, puis examiner pourquoi il maintient son verrou.

Pour ce faire, utilisez 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 Bloqué par. 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 des DMV :

• Les commandes sp_who et sp_who2 sont des commandes plus anciennes permettant d’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 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 trouver la dernière instruction 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 requête la plus récente 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 répertorie uniquement les requêtes en cours d’exécution, toute connexion (active ou non) est répertoriée dans sys.dm_exec_sessions. Appuyez-vous sur cette jointure commune entre sys.dm_exec_requests et sys.dm_exec_sessions dans la requête suivante. Gardez à l’esprit que pour être renvoyé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 par lots ou texte de mémoire tampon d’entrée SQL actuel, à l’aide des 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, y compris 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 élaboré, fourni par Support Microsoft, pour identifier le chef d’une chaîne de blocage de plusieurs sessions, y compris 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. Plusieurs DMV sont associées au suivi des transactions. Pour plus d’informations sur les DMV associées aux transactions, cliquez 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érencez sys.dm_os_waiting_tasks qui se trouve au niveau de la couche thread/tâche de SQL Server. Cette opération renvoie des informations sur ce que la requête SQL wait_type rencontre actuellement. Comme sys.dm_exec_requests, seules les requêtes actives sont renvoyées par sys.dm_os_waiting_tasks.

• Utilisez la DMV sys.dm_tran_locks pour obtenir des informations plus précises sur les verrous qui ont été 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 INNER JOIN sur sys.dm_os_waiting_tasks, la requête suivante limite la sortie de sys.dm_tran_locks uniquement 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>';

Avec les DMV, le stockage des résultats de la requête au fil du temps fournit des points de données qui vous permettent d’examiner le blocage sur un intervalle de temps spécifié pour 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 ci-dessus, il est souvent nécessaire de capturer une trace des activités sur le serveur pour examiner en profondeur un problème de blocage dans SQL Server. Par exemple, si une session exécute plusieurs instructions au sein d’une transaction, seule la dernière instruction qui a été soumise est 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 actuelle.

Il existe deux façons de capturer des traces dans SQL Server : Événements étendus (XEvents) et les traces du générateur de profils. Toutefois, les traces SQL utilisant le générateur de profils de SQL Server sont obsolètes. XEvents est la plateforme de traçage la plus récente et supérieure qui permet plus de 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éfines prêtes à démarrer dans SSMS, répertoriées dans 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, consultez l’Assistant Nouvelle session des événements étendus. Pour résoudre les problèmes de blocage, nous allons généralement capturer :

• Des 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 produit.

• Des avertissements de catégorie :

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Des exécutions de catégorie :

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Verrou de catégorie

  • Lock_deadlock

• Session de catégorie

  • Existing_connection
  • Connexion
  • Déconnexion

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 de blocage (référencés précédemment) pour capturer des informations sur les SPID bloquants et que vous avez 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 bloquantes, à l’aide de blocking_these et session_id. Cela permet d’identifier clairement les requêtes bloquées et celles qui bloquent. Examinez plus en détail les sessions bloquées et bloquantes. Existe-t-il une racine commune à la chaîne bloquante ? Elles partagent probablement un tableau commun, et une ou plusieurs des 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 affiche l’état d’une requête particulière. En règle générale, un état de veille indique que le SPID a terminé l’exécution et attend que l’application envoie une autre requête ou un autre lot. Un état exécutable ou en cours d’exécution indique que le SPID traite actuellement une requête. Le tableau suivant fournit de brèves explications sur les différentes valeurs d’état.

    Statut	Signification
    Contexte	Le SPID exécute une tâche en arrière-plan, telle que la détection des interblocages, l’enregistreur de journaux ou le point de contrôle.
    Mode 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.
    En cours d’exécution	Le SPID est en cours d’exécution sur un planificateur.
    Exécutable	Le SPID se trouve dans la file d’attente exécutable d’un planificateur et attend pour obtenir l’heure du planificateur.
    Suspendu	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 une instruction au sein de la transaction.

  • sys.dm_exec_requests.open_transaction_count
    De même, cette colonne vous indique le nombre de transactions ouvertes dans cette requête. Si cette valeur est supérieure à 0, le SPID se trouve dans une transaction ouverte et peut contenir des verrous acquis par une instruction au sein de la transaction.

  • sys.dm_exec_requests.wait_type, wait_time et last_wait_type
    Si la sys.dm_exec_requests.wait_type est NULL, la requête n’attend rien et la valeur last_wait_type indique la dernière wait_type que la requête a rencontrée. Pour plus d’informations sur sys.dm_os_wait_stats et pour une description des types d’attente les plus courants, consultez sys.dm_os_wait_stats. La valeur wait_time peut être utilisée pour déterminer si la requête progresse. Lorsqu’une requête sur le tableau sys.dm_exec_requests renvoie 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 qu’il attend maintenant un nouveau verrou (en supposant une wait_time non égale à zéro). Cela peut être vérifié en comparant la sortie wait_resource avec sys.dm_exec_requests, qui 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 requête bloquée attend. Le tableau suivant répertorie les formats de wait_resource courants et leur signification :

    Resource	Format	Exemple	Explication
    Tableau	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 principal et la page 104 est une page appartenant au tableau de titres. Pour identifier l’object_id auquel la page appartient, utilisez la fonction de gestion dynamique sys.dm_db_page_info, en transmettant databaseID, FileId, PageId à partir 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 (tableau de titres). 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étacher le hachage de la clé d’index sur 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 principal, la page 104 est une page appartenant au tableau de titres et l’emplacement 3 indique la position de la ligne sur la page.
    Compilation	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 principal, la page 104 est une page appartenant au tableau de titres 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. Prenez en compte l’état actuel d’une transaction, transaction_begin_time, et d’autres données situationnelles pour déterminer s’il peut s’agir d’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;
    

  • Autres colonnes

    Les colonnes restantes dans sys.dm_exec_sessions et sys.dm_exec_request peuvent également fournir des informations sur la racine d’un problème. Leur utilité varie en fonction des 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 mappe les symptômes courants à 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 sera résolu par lui-même ou si la session doit être tuée par le biais de la commande KILL. Pour plus d’informations, reportez-vous à 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 :

La solution à ce type de problème de blocage consiste à rechercher des moyens d’optimiser la requête. En fait, cette classe de problème de blocage peut être un problème de performances, et vous obliger à le poursuivre en tant que tel. Pour plus d’informations sur la résolution des problèmes d’une requête lente spécifique, consultez Comment résoudre les problèmes de requêtes lentes sur SQL Server. Pour plus d’informations, consultez la section Surveiller et régler les performances.

Les rapports intégrés à SSMS 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 une requête de longue durée bloque les autres utilisateurs et ne peut être optimisée, envisagez de la déplacer d’un environnement OLTP vers un système 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

Ce type de blocage peut souvent être identifié par un SPID en veille ou en attente d’une commande, mais dont le niveau d’imbrication des transactions (@@TRANCOUNT, open_transaction_count à partir de sys.dm_exec_requests) est supérieur à zé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’expiration de requête ou une annulation, il met fin à la requête et au lot actuels, mais n’annule pas ou ne valide pas automatiquement la transaction. L’application en est responsable, car SQL Server ne peut pas supposer qu’une transaction entière doit être restaurée en raison de l’annulation d’une requête unique. 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;

Exécutez ensuite 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 communication ou une autre erreur peut laisser la session et sa transaction dans un état ouvert.

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

Solutions :

• En outre, cette classe de problème de blocage peut également être un problème de performances et vous obliger à la traiter en tant que tel. Si le temps d’exécution de la requête peut être réduit, 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 les scénarios de délai d’expiration ou 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 risquent de provoquer un problème de blocage après l’annulation de la requête de cette manière. Vous devez tenir compte des éléments suivants :

  • Dans le gestionnaire d’erreurs de l’application cliente, exécutez IF @@TRANCOUNT > 0 ROLLBACK TRAN après une 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 l’exécution de la procédure au-delà de l’instruction actuelle. Par conséquent, même si la procédure a la logique de vérifier IF @@ERROR <> 0 et d’abandonner la transaction, ce code de restauration n’est pas exécuté dans de tels cas.

  • Si le regroupement de connexions est utilisé dans une application qui ouvre la connexion et exécute quelques requêtes avant de libérer la connexion vers le 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 provoque la restauration des transactions ouvertes par le serveur.

  • 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 renvoie le contrôle au client. Pour plus d’informations, consultez l’article 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ésultats jusqu’à la fin

Après l’envoi d’une requête au serveur, toutes les applications doivent extraire immédiatement toutes les lignes de résultats jusqu’à leur achèvement. Si une application n’extrait pas toutes les lignes de résultats, des verrous peuvent être laissés sur les tables, ce qui bloque d’autres utilisateurs. Si vous utilisez une application qui envoie de manière transparente des instructions SQL au serveur, l’application doit extraire toutes les lignes de résultats. Si ce n’est pas le cas (et si elle ne peut pas être configurée pour le faire), vous risquez de ne pas pouvoir résoudre le problème de blocage. Pour éviter ce problème, vous pouvez restreindre les applications à comportement médiocre à une base de données de rapports ou de prise de décision, distincte de la base de données OLTP principale.

Résolution :

L’application doit être réécrite pour extraire toutes les lignes du résultat jusqu’à 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 a plusieurs connexions ouvertes et 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 illustré ci-dessus, un thread d’application cliente 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. Quand les données commencent à revenir (quelle que soit le dbproc qui répond en premier, supposons qu’il s’agit de dbproc1), toutes les données renvoyées sont traitées jusqu’à la fin 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. 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.

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

Même s’il existe un thread distinct pour chaque connexion sur le client, une variante de cet interblocage 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, 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 :

Quand un délai d’expiration de requête est spécifié, si l’interblocage distribué se produit, il est rompu quand le délai d’expiration se produit. Pour plus d’informations sur l’utilisation d’un délai d’expiration de requête, consultez la documentation de votre fournisseur de connexion.

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

Une requête de modification de données qui est supprimé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 de la déconnexion de la session réseau cliente quand une demande est sélectionnée comme victime d’interblocage. Cela peut souvent être identifié en observant la sortie de sys.dm_exec_requests, qui peut indiquer command ROLLBACK, et la colonne percent_complete peut afficher la progression.

Une requête de modification de données qui est supprimé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 la déconnexion de sa session réseau. De même, une requête sélectionnée comme victime d’interblocage est annulée. Une requête de modification de données ne peut souvent pas être restaurée plus rapidement que les modifications initialement appliquées. Par exemple, si une instruction DELETE, INSERT ou UPDATE avait été exécutée pendant une heure, la restauration peut prendre au moins une heure. Ce comportement est normal, car les modifications apportées doivent être annulées, sinon l’intégrité transactionnelle et physique de la base de données serait compromise. Comme cette situation 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 supprimé 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 colonne status de sys.dm_exec_sessions indique un état ROLLBACK.

Résolution :

Vous devez attendre que la session ait terminé de restaurer les modifications apportées.

Si l’instance est arrêtée au cours de cette opération, la base de données passe en mode de récupération au redémarrage et est inaccessible jusqu’à ce que toutes les transactions ouvertes soient 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, le fait de forcer l’arrêt du serveur pour corriger un SPID dans un état de restauration est souvent contreproductif. Cette situation ne se produit pas si la fonctionnalité Récupération de base de données accélérée est activée dans SQL Server 2019.

Pour éviter cette situation, n’effectuez pas d’opérations d’écriture par lots volumineuses ou de création ou de maintenance d’index pendant les heures de pointe sur les 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 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 FINALLY ou CATCH 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)

Exécutez ensuite 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.

Solutions :

• La meilleure façon d’éviter cette situation consiste à améliorer la gestion des erreurs/exceptions d’application, en particulier pour les arrêts 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 restaure automatiquement toutes les transactions ouvertes et renvoie le contrôle au client. Pour plus d’informations, consultez l’article 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 SPID comme entrée. Par exemple, pour supprimer SPID 9, exécutez la commande suivante :

KILL 99

Voir aussi

• Surveillance des performances à l’aide du magasin de 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
• SET TRANSACTION ISOLATION LEVEL
• Démarrage rapide : Événements étendus dans SQL Server