Comprendre et résoudre les problèmes de blocage d’Azure SQL Database

S’applique à : Azure SQL Database

L’article décrit les blocages qui peuvent se produire dans les bases de données Azure SQL et montre comment les détecter et les résoudre.

Objectif

Dans cet article, le terme « connexion » fait référence à une session ouverte unique de la base de données. Chaque connexion s’affiche sous la forme d’un ID de session (SPID) ou session_id dans de nombreuses vues de gestion dynamique (DMV). Chacun de ces SPID est souvent appelé processus, bien qu’il ne s’agisse pas d’un contexte de processus distinct au sens habituel. Chaque SPID est plutôt constitué des ressources du serveur et des structures de données nécessaires pour traiter les requêtes d’une seule connexion d’un client donné. Une même application cliente peut avoir une ou plusieurs connexions. Du point de vue d’Azure SQL Database, il n’existe aucune différence entre plusieurs connexions à partir d’une application cliente unique 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.

Pour plus d’informations sur la résolution des interblocages, consultez Analyser et empêcher les interblocages dans Azure SQL Database.

Comprendre les blocages

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. Le blocage d’une base de données dans Azure SQL Database se produit quand une session maintient un verrou sur une ressource spécifique et qu’un deuxième SPID tente d’acquérir sur la même ressource un type de verrou en conflit. En règle générale, le délai d’exécution pendant lequel le premier SPID verrouille la ressource est court. Lorsque la session propriétaire libère le verrou, la deuxième connexion est alors libre d’acquérir son propre verrou sur la ressource et de poursuivre le traitement. Ce comportement est normal et peut se produire plusieurs fois au cours d’une journée, sans effet notable sur les performances du système.

Chaque nouvelle base de données dans Azure SQL Database a le paramètre de base de données RCSI (read committed snapshot) activé par défaut. Le blocage entre les sessions qui lisent les données et les sessions écrivant des données est réduit avec RCSI, qui utilise le contrôle de version de ligne pour augmenter la concurrence. Toutefois, des blocages et interblocages peuvent toujours se produire dans les bases de données dans Azure SQL Database, car :

• Les requêtes qui modifient les données peuvent se bloquer les unes les autres.
• Les requêtes peuvent s’exécuter sous des niveaux d’isolement qui augmentent le blocage. Les niveaux d’isolement peuvent être spécifiés dans les chaînes de connexion d’application, les indicateurs de requête ou les instructions SET dans Transact-SQL.
• RCSI peut être désactivé, ce qui entraîne l’utilisation de verrous partagés (S) pour protéger l’exécution des instructions SELECT sous le niveau d’isolement read committed. Cela peut augmenter les blocages et interblocages.

Le Niveau d’isolement de capture instantanée est également activé par défaut pour les nouvelles bases de données dans Azure SQL Database. L’isolement d’instantané est un niveau d’isolement supplémentaire basé sur des lignes qui fournit une cohérence au niveau des transactions pour les données, et qui utilise des versions de ligne pour sélectionner des lignes à mettre à jour. Pour utiliser l’isolement d’instantané, les requêtes ou les connexions doivent définir explicitement leur niveau d’isolement des transactions sur SNAPSHOT. Cette opération peut être effectuée uniquement lorsque l’isolement d’instantané est activé pour la base de données.

Vous pouvez identifier si l’isolement RCSI et/ou d’instantané est activée avec Transact-SQL. Connectez-vous à votre base de données dans Azure SQL Database et exécutez la requête suivante :

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Si RCSI est activé, la colonne is_read_committed_snapshot_on retourne la valeur 1. Si l’isolement d’instantané est activé, la colonne snapshot_isolation_state_desc retourne la valeur ON.

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. Les instructions SELECT exécutées sous RCSI n’acquièrent pas de verrous partagés (S) sur les données lues, et ne bloquent donc pas les transactions qui modifient les données. Pour les instructions INSERT, UPDATE et DELETE, les verrous sont maintenus pendant la requête, à la fois pour la cohérence des données et pour permettre la restauration de la requête si nécessaire.

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

• Verrouillage du moteur de base de données
• Personnalisation du verrouillage et du contrôle de version de ligne
• Modes de verrouillage
• Compatibilité de verrouillage
• Transactions

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

• Un SPID maintient des verrous sur un ensemble de ressources pendant une période prolongée avant de libérer ces ressources. 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 maintient 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.

Verrouillage optimisé

Le verrouillage optimisé est une nouvelle fonctionnalité du moteur de base de données qui réduit considérablement la mémoire de verrouillage et le nombre de verrous requis simultanément pour les écritures. Le verrouillage optimisé utilise deux composants principaux : le verrouillage de l’ID de transaction (TID) (également utilisé dans d’autres fonctionnalités de contrôle de version de ligne) et le verrou après qualification (LAQ). Il ne nécessite aucune configuration supplémentaire.

Cet article s’applique actuellement au comportement du moteur de base de données sans verrouillage optimisé.

Pour plus d’informations et pour savoir où le verrouillage optimisé est disponible, consultez Verrouillage optimisé.

Applications et blocage

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

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

Si la conception d’application et de requête est appropriée, Azure SQL Database 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 nous nous trouvons, la méthodologie de résolution des problèmes de verrouillage est la même. Ces séparations logiques définissent le reste de la composition de cet article. Le concept consiste à trouver le bloqueur principal et à identifier ce que fait cette requête et la raison pour laquelle elle crée un blocage. Une fois que la requête problématique est identifiée (c’est-à-dire, ce qui maintient les verrous pendant une période prolongée), l’étape suivante consiste à analyser et à déterminer la cause du blocage. Une fois que nous avons compris la raison, nous pouvons apporter des modifications en reconcevant la requête et la transaction.

Étapes de résolution du problème :

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

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

3. Analyser/comprendre les raisons du blocage prolongé

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

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

Collecter les informations de blocage

Pour pallier la difficulté de résolution des problèmes de blocage, un Administrateur de base de données peut utiliser des scripts SQL qui surveillent en permanence l’état du verrouillage et du blocage dans la base de données Azure SQL Database. Pour recueillir ces données, il existe essentiellement deux méthodes.

La première consiste à interroger les objets de gestion dynamique (DMO) et à stocker les résultats à des fins de comparaison dans le temps. Certains objets mentionnés dans cet article sont des vues de gestion dynamique (DMV) et d’autres des fonctions de gestion dynamique (DMF). La seconde méthode consiste à utiliser des XEvents pour capturer ce qui est en cours d’exécution.

Collecter des informations à partir de DMV

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

N’oubliez pas d’exécuter chacun de ces scripts dans la base de données Azure SQL Database cible.

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

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

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

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

• Exécutez cet exemple de requête pour rechercher les requêtes en cours d’exécution et leur texte actuel de lot SQL ou de mémoire tampon d’entrée en utilisant les DMV sys.dm_exec_sql_text ou sys.dm_exec_input_buffer. Si les données retournées par le champ text de sys.dm_exec_sql_text ont la valeur NULL, cela signifie que la requête n’est pas en cours d’exécution. Dans ce cas, le champ event_info de sys.dm_exec_input_buffer contient la dernière chaîne de commande transmise 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 repérer les transactions durables ou non validées, utilisez un autre ensemble de DMV pour visualiser les transactions ouvertes actuelles, notamment sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections et sys.dm_exec_sql_text. Il existe plusieurs DMV associées au suivi des transactions, consultez DMV sur les transactions pour plus d’informations.

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 de thread/tâche de SQL. Cela renvoie des informations sur le type d’attente SQL que 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 base de données de production et est utile pour diagnostiquer quels verrous sont actuellement maintenus.

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.

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

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

Il existe deux façons de capturer des traces dans SQL Server : les événements étendus (XEvents) et les traces du profileur. Toutefois, la technologie de trace SQL Server Profiler est déconseillée et n’est pas prise en charge pour Azure SQL Database. Les événements étendus constituent la nouvelle technologie de suivi qui permet une plus grande polyvalence et un impact moindre sur le système observé, et son interface est intégrée à SQL Server Management Studio (SSMS).

Reportez-vous au document qui explique comment utiliser l’Assistant Nouvelle session d’événements étendus dans SSMS. Toutefois, pour les bases de données Azure SQL, SSMS fournit un sous-dossier Événements étendus sous chaque base de données dans Explorateur d’objets. Utilisez l’Assistant Session XEvent pour capturer ces événements utiles :

• Catégorie Erreurs :

  • Attention
  • Error_reported
  • Execution_warning

• Catégorie Avertissements :

  • Missing_join_predicate

• Catégorie Exécution :

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Catégorie deadlock_monitor

  • database_xml_deadlock_report

• Session de catégorie

  • Existing_connection
  • Connexion
  • Logout

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

En examinant les informations ci-dessus, vous pouvez déterminer la cause de la plupart des problèmes de blocage. Le reste de cet article explique comment utiliser ces informations pour identifier et résoudre certains scénarios de blocage courants. Cette discussion part du principe 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é de l’application en utilisant une session XEvent.

Analyser les données de blocage

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

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

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

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

  • sys.dm_exec_sessions.open_transaction_count
    Ce champ indique le nombre de transactions ouvertes dans cette session. Si cette valeur est supérieure à 0, le SPID se trouve dans une transaction ouverte et peut contenir des verrous acquis par toute instruction dans la transaction.

  • sys.dm_exec_requests.open_transaction_count
    De même, ce champ 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 toute instruction dans la transaction.

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

  • sys.dm_exec_requests.wait_resource Ce champ indique la ressource qu’une requête bloquée attend. 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 », l’ID d’objet 261575970 est la table « titles » et 1 est l’index cluster.
  Page	DatabaseID:FileID:PageID	PAGE: 5:1:104	Dans ce cas, l’ID de base de données 5 est « pubs », l’ID de fichier 1 est le fichier de données primaire et la page 104 est une page appartenant à la table « titles ». Pour identifier l’object_id auquel la page appartient, utilisez la fonction de gestion dynamique sys.dm_db_page_info, en transmettant les DatabaseID, FileId et PageId de wait_resource.
  Clé	DatabaseID:Hobt_id (valeur de hachage pour la clé d’index)	KEY: 5:72057594044284928 (3300a4f361aa)	Dans ce cas, l’ID de base de données 5 est « pubs », Hobt_ID 72057594044284928 correspond à index_id 2 pour object_id 261575970 (table « titles »). Utilisez l’affichage catalogue sys.partitions pour associer le hobt_id à un index_id et un object_id particuliers. Il n’existe aucun moyen de décomposer le hachage de la clé d’index en une valeur de clé spécifique.
  Ligne	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	Dans ce cas, l’ID de base de données 5 est « pubs », l’ID de fichier 1 est le fichier de données primaire, la page 104 est une page appartenant à la table « titles » et l’emplacement 3 indique la position de la ligne sur la page.
  Compiler	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	Dans ce cas, l’ID de base de données 5 est « pubs », l’ID de fichier 1 est le fichier de données primaire, la page 104 est une page appartenant à la table « titles » et l’emplacement 3 indique la position de la ligne sur la page.

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

  SELECT tst.session_id, [database_name] = db_name(s.database_id)
  , tat.transaction_begin_time
  , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
  , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                  WHEN 2 THEN 'Read-only transaction'
                                                  WHEN 3 THEN 'System transaction'
                                                  WHEN 4 THEN 'Distributed transaction' END
  , input_buffer = ib.event_info, tat.transaction_uow     
  , transaction_state  = CASE tat.transaction_state    
              WHEN 0 THEN 'The transaction has not been completely initialized yet.'
              WHEN 1 THEN 'The transaction has been initialized but has not started.'
              WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
              WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
              WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
              WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
              WHEN 6 THEN 'The transaction has been committed.'
              WHEN 7 THEN 'The transaction is being rolled back.'
              WHEN 8 THEN 'The transaction has been rolled back.' END 
  , transaction_name = tat.name, request_status = r.status
  , azure_dtc_state = CASE tat.dtc_state 
                      WHEN 1 THEN 'ACTIVE'
                      WHEN 2 THEN 'PREPARED'
                      WHEN 3 THEN 'COMMITTED'
                      WHEN 4 THEN 'ABORTED'
                      WHEN 5 THEN 'RECOVERED' END
  , 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 (net_library), quand le dernier lot soumis par un SPID a été last_request_start_time dans sys.dm_exec_sessions, la durée d’exécution d’une requête à l’aide de start_time dans sys.dm_exec_requests, et ainsi de suite.

Scénarios de blocage courants

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

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

Scénarios de blocage détaillés

1. Blocage causé par une requête normalement en cours d’exécution avec une durée d’exécution longue

   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 n’être qu’un problème de performances et vous obliger à la poursuivre en tant que 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 de Magasin des requêtes dans SSMS sont également un outil précieux et hautement recommandé pour identifier les requêtes les plus coûteuses, à savoir les plans d’exécution non optimaux. Consultez également la section Performances intelligentes du portail Azure pour la base de données Azure SQL, y compris Query Performance Insight.

   Si la requête n’effectue que des opérations SELECT, envisagez d’exécuter l’instruction sous isolement d’instantané si celui-ci est activé dans votre base de données, en particulier si RCSI a été désactivé. En effet, lorsque RCSI est activé, les requêtes qui lisent les données ne nécessitent pas de verrous partagés (S) sous le niveau d’isolement d’instantané. En outre, l’isolement d’instantané fournit une cohérence au niveau des transactions pour toutes les instructions d’une transaction multi-instruction explicite. L’isolement d’instantané peut déjà être activé dans votre base de données. L’isolement d’instantané peut également être utilisé avec des requêtes effectuant des modifications, mais vous devez gérer les conflits de mise à jour.

   Si vous disposez d’une requête durable qui bloque d’autres utilisateurs et ne peut pas être optimisée, envisagez de la déplacer d’un environnement OLTP à un système dédié de création de rapports, un réplica synchrone en lecture seule de la base de données.

2. Blocage causé par un SPID en veille dont une transaction n’est pas 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’attente de requête ou émet une annulation sans émettre également 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 termine la requête et le traitement en cours, mais ne restaure pas ou ne valide pas automatiquement la transaction. L’application est responsable de cela, car Azure SQL Database ne peut pas supposer qu’une transaction entière doit être annulée en raison du fait qu’une seule requête est annulée. Le délai d’attente ou l’annulation de la requête s’affiche sous la forme d’un événement de signal ATTENTION pour le SPID dans la session XEvent.

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

   CREATE TABLE #test (col1 INT);
   INSERT INTO #test SELECT 1;
   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 niveau d’imbrication de la transaction est 1. 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 :

   • En outre, cette classe de problème de blocage peut également être un problème de performances et vous obliger à la poursuivre en tant que tel. Si la durée d’exécution de la requête peut être réduite, le délai d’attente ou l’annulation de la requête ne se produit pas. Il est important que l’application soit capable de gérer les scénarios de délai d’attente ou d’annulation s’ils se produisent, mais vous pouvez également tirer parti de l’examen des performances de la requête.

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

     • Dans le gestionnaire d’erreurs de l’application cliente, exécutez IF @@TRANCOUNT > 0 ROLLBACK TRAN après toute erreur, même si l’application cliente ne pense pas qu’une transaction est ouverte. La vérification des transactions ouvertes est obligatoire, car une procédure stockée appelée pendant le traitement 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 le regroupement de connexions est utilisé dans une application qui ouvre la connexion et exécute un petit nombre de requêtes avant de libérer la connexion et de la rendre 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. Si vous désactivez le regroupement de connexions, le fait de libérer la connexion entraîne la déconnexion physique de la connexion Azure SQL Database, amenant le serveur à restaurer toute transaction ouverte.
     • Utilisez SET XACT_ABORT ON pour la connexion ou dans toutes les procédures stockées qui commencent des transactions et qui ne sont pas nettoyées après une erreur. En cas d’erreur d’exécution, ce paramètre abandonne toutes les transactions ouvertes et rend le contrôle au client. Pour plus d’informations, consultez SET XACT_ABORT (Transact-SQL).

   Notes

   La connexion n’est pas réinitialisée tant qu’elle n’est pas réutilisée à partir du pool de connexions. Il est donc possible qu’un utilisateur ouvre une transaction puis libère la connexion pour le pool de connexion. Cependant, celle-ci pourrait ne pas être réutilisée pendant plusieurs secondes, période pendant laquelle la transaction resterait ouverte. Si la connexion n’est pas réutilisée, la transaction est abandonnée lorsque le délai d’attente de la connexion expire et est supprimée du pool de connexions. Il est donc optimal pour l’application cliente d’abandonner les transactions dans son gestionnaire d’erreurs ou d’utiliser SET XACT_ABORT ON pour éviter ce retard potentiel.

   Attention

   Après SET XACT_ABORT ON, les instructions T-SQL qui suivent une instruction causant une erreur ne seront pas exécutées. Cela peut nuire au flux prévu du code existant.

3. Blocage causé par un SPID dont l’application cliente correspondante n’a pas extrait toutes les lignes du résultat 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ésultat jusqu’au bout. Si une application n’extrait pas toutes les lignes du résultat, des verrous peuvent être maintenus sur les tables et bloquer d’autres utilisateurs. Si vous utilisez une application qui envoie en toute transparence des instructions SQL au serveur, l’application doit extraire toutes les lignes de résultat. Si 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 ce problème, vous pouvez limiter les applications au comportement médiocre à un signalement ou à une base de données d’aide à la décision, séparée de la base de données OLTP principale.

   L’impact de ce scénario est réduit lorsque l’instantané read committed est activé sur la base de données, ce qui est la configuration par défaut dans Azure SQL Database. Pour plus d’informations, consultez la section Comprendre le blocage de cet article.

   Notes

   Consultez l’aide relative à la logique de nouvelle tentative pour les applications se connectant à Azure SQL Database.

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

4. Blocage causé par une session en état de restauration

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

   Grâce à la fonctionnalité de récupération de base de données accélérée introduite en 2019, les restaurations longues devraient être rares.

   Résolution : Attendez que le SPID termine de restaurer les modifications apportées.

   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é.

5. Blocage causé par une connexion orpheline

   Si l’application cliente intercepte des erreurs ou que la station de travail cliente est redémarrée, la session réseau sur le serveur peut ne pas être annulée immédiatement dans certaines conditions. Du point de vue d’Azure SQL Database, le client semble toujours être présent et tous les verrous acquis peuvent toujours être conservés. Pour plus d’informations, consultez Comment résoudre des problèmes de connexion orpheline dans SQL Server.

   Résolution : Si l’application cliente s’est déconnectée sans nettoyer correctement ses ressources, vous pouvez terminer le SPID à l’aide de la commande KILL. La commande KILL prend la valeur du SPID comme entrée. Par exemple, pour tuer SPID 99, émettez la commande suivante :

   KILL 99
   

Contenu connexe

• Analyser et empêcher les blocages dans Azure SQL Database
• Supervision et réglage des performances dans Azure SQL Database et Azure SQL Managed Instance
• Surveillance des performances à l’aide du magasin des requêtes
• 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
• Azure SQL Database : amélioration du réglage des performances grâce au réglage automatique
• Offrez des performances cohérentes avec Azure SQL
• Résolution des problèmes de connectivité et autres erreurs avec Azure SQL Database et Azure SQL Managed Instance
• Transient Fault Handling
• Configurer le degré maximal de parallélisme (MAXDOP) dans Azure SQL Database
• Diagnostiquer et résoudre les problèmes d’utilisation élevée du processeur dans Azure SQL Database