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
- The blocking queries show only the sessions that are blocked at the moment of the query.
sp_locklists the locks of the instance, but it does not show which statement holds them. Use the queries above orsp_WhoIsActivefor that.