LLI Wiki — My Digital Knowledge Base

Find blocking sessions on Azure SQL

Find sessions, running requests and blocking on Azure SQL with DMVs, and use sp_WhoIsActive.

On Azure SQL Database, sp_who gives a limited view for security reasons. The queries below use dynamic management views (DMVs) to list the sessions, the running requests and the sessions that block others.

Sessions and connections

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;

Running requests

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;

Requests with the statement they run

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;

Requests that are blocked

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

Blocked and blocking statements

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 is a community procedure that shows the running sessions with their details. It must be installed in the database before use. Useful parameters:

Parameter Effect
@get_plans = 1 Adds the execution plans of the running queries.
@get_locks = 1 Adds an XML value that shows the locks each query owns (table, row, object). Useful to find which query blocks the others.
@get_task_info = 2 Shows what each task of a parallel query waits on (useful for CXPACKET waits).
EXEC sp_WhoIsActive @get_locks = 1, @get_plans = 1, @get_task_info = 2;

Notes

Mastodon LLIMastodon SocialFosstodon