LLI Wiki — My Digital Knowledge Base

Trouver les sessions bloquantes sur Azure SQL

Trouver les sessions, les requêtes en cours et les blocages sur Azure SQL avec les DMV, et utiliser sp_WhoIsActive.

Sur Azure SQL Database, sp_who ne donne qu'une vue limitée, pour des raisons de sécurité. Les requêtes ci-dessous utilisent les vues de gestion dynamique (DMV) pour lister les sessions, les requêtes en cours et les sessions qui en bloquent d'autres.

Sessions et connexions

SELECT
    c.session_id,
    c.net_transport,
    c.encrypt_option,
    s.status,
    c.auth_scheme,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_name,
    s.nt_domain,
    s.nt_user_name,
    s.original_login_name,
    c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
    JOIN sys.dm_exec_sessions AS s
        ON c.session_id = s.session_id
ORDER BY c.connect_time ASC;

Requêtes en cours

SELECT
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time
FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s
        ON r.session_id = s.session_id
ORDER BY r.cpu_time DESC;

Requêtes avec l'instruction en cours

SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    r.start_time,
    DATEDIFF(SECOND, r.start_time, GETUTCDATE()) AS duration_seconds,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads,
    r.writes,
    r.logical_reads,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(t.text,
              r.statement_start_offset / 2 + 1,
              (CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(t.text)
                 ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
               END)
    ) AS running_statement
FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s
        ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Requêtes bloquées

SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Instructions bloquées et bloquantes

SELECT
    TL.resource_type,
    D.name AS database_name,
    TL.resource_associated_entity_id AS blk_object,
    TL.request_mode,
    TL.request_session_id,
    WT.blocking_session_id,
    WT.wait_duration_ms,
    SUBSTRING(
        SQL.text,
        ER.statement_start_offset / 2,
        (
            CASE
                WHEN ER.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), SQL.text)) * 2
                ELSE ER.statement_end_offset
            END - ER.statement_start_offset
        ) / 2 + 1
    ) AS query_text,
    SUBSTRING(
        BSQL.text,
        BER.statement_start_offset / 2,
        (
            CASE
                WHEN BER.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), BSQL.text)) * 2
                ELSE BER.statement_end_offset
            END - BER.statement_start_offset
        ) / 2 + 1
    ) AS blk_query_text,
    WT.resource_description
FROM sys.dm_tran_locks AS TL
    INNER JOIN sys.dm_os_waiting_tasks AS WT
        ON TL.lock_owner_address = WT.resource_address
    INNER JOIN sys.dm_exec_requests AS ER
        ON WT.session_id = ER.session_id
    INNER JOIN sys.dm_exec_requests AS BER
        ON WT.blocking_session_id = BER.session_id
    INNER JOIN sys.databases AS D
        ON TL.resource_database_id = D.database_id
    CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS SQL
    CROSS APPLY sys.dm_exec_sql_text(BER.sql_handle) AS BSQL;

sp_WhoIsActive

sp_WhoIsActive est une procédure communautaire qui affiche les sessions en cours avec leurs détails. Elle doit être installée dans la base avant usage. Paramètres utiles :

Paramètre Effet
@get_plans = 1 Ajoute les plans d'exécution des requêtes en cours.
@get_locks = 1 Ajoute une valeur XML qui montre les verrous de chaque requête (table, ligne, objet). Utile pour trouver la requête qui en bloque d'autres.
@get_task_info = 2 Montre sur quoi attend chaque tâche d'une requête parallèle (utile pour les attentes CXPACKET).
EXEC sp_WhoIsActive @get_locks = 1, @get_plans = 1, @get_task_info = 2;

Remarques

Mastodon LLIMastodon SocialFosstodon