LLI Wiki — My Digital Knowledge Base

Capturing activity sp_WhoIsActive-style on Azure SQL and finding the blocker

Azure SQL Database limits, a scheduled capture to a table using DMVs, retention and a query to find the blocker after the fact.

"Who was blocking whom yesterday at 2:12 pm?" On classic SQL Server you run sp_WhoIsActive from an Agent job every minute, store the output in a table, and the question is answered. On Azure SQL Database the method has to adapt. Here is what changes and how I do it.

What you can and cannot do

Need SQL Server / Managed Instance Azure SQL Database
Schedule the capture SQL Server Agent No Agent: external scheduler (scheduled pipeline, Batch task, Automation, Elastic Jobs)
Scope Whole instance One database at a time: no cross-database queries, master and msdb unusable
Session DMVs sys.dm_exec_*, sys.dm_os_waiting_tasks Available, limited to the current database
Extended Events Files, server sessions Database-scoped sessions, storage target
Install a procedure In master In the target database

Two consequences: the capture must connect to every relevant database, and the scheduler must live outside the database. The required permissions are of the VIEW DATABASE STATE family (or VIEW DATABASE PERFORMANCE STATE depending on the service tier); I check the service documentation before granting them.

Capture architecture

flowchart TD
    A["Scheduler (every 10 min)"] --> B["PowerShell script"]
    B --> C["List of busy databases (CPU metric via KQL)"]
    C --> D["Managed identity connection per database"]
    D --> E["Session capture"]
    E --> F[("Capture table in the database")]
    B --> G["Purge old rows"]
    G --> F

Steps:

  1. The script picks the databases that deserve a capture (average CPU within a range) so it does not write everywhere for nothing. If the selection query fails, it falls back to all primary databases.
  2. For each non-read-only database: create the capture table if missing, insert the output, purge.
  3. A failing database is logged; the others carry on.

A minimal capture with DMVs

If you do not want to deploy the whole community procedure, a DMV query already goes a long way:

IF OBJECT_ID('dbo.ActivitySnapshot') IS NULL
BEGIN
    CREATE TABLE dbo.ActivitySnapshot (
        collection_time datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
        session_id int, blocking_session_id int, status nvarchar(30),
        wait_type nvarchar(60), wait_time_ms int, cpu_time int,
        logical_reads bigint, login_name nvarchar(128),
        database_name nvarchar(128), command nvarchar(32), sql_text nvarchar(max)
    );
    CREATE CLUSTERED INDEX cx_collection_time ON dbo.ActivitySnapshot (collection_time);
END;

INSERT dbo.ActivitySnapshot (session_id, blocking_session_id, status, wait_type,
       wait_time_ms, cpu_time, logical_reads, login_name, database_name, command, sql_text)
SELECT r.session_id, r.blocking_session_id, r.status, r.wait_type, r.wait_time,
       r.cpu_time, r.logical_reads, s.login_name, DB_NAME(r.database_id), r.command, t.text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1 AND r.session_id <> @@SPID;

The community procedure does the same thing better (plans, locks, tempdb): installing it in the database is idempotent, and you ask it to write into your table through its destination parameter.

Retention

One row per active session every 10 minutes grows fast. My rules:

Finding the blocker after the fact

SELECT b.collection_time, b.session_id AS blocked, b.blocking_session_id AS blocker,
       b.wait_type, b.wait_time_ms, bl.login_name AS blocker_login, bl.sql_text AS blocker_sql
FROM dbo.ActivitySnapshot AS b
LEFT JOIN dbo.ActivitySnapshot AS bl
       ON bl.collection_time = b.collection_time AND bl.session_id = b.blocking_session_id
WHERE b.blocking_session_id > 0
  AND b.collection_time BETWEEN '2026-01-01T14:00' AND '2026-01-01T14:30'
ORDER BY b.collection_time, b.wait_time_ms DESC;

To find the head of the chain, look for blocking_session_id values that are not themselves blocked in the same snapshot. Beware: the blocker is often a sleeping session holding an open transaction, so absent from dm_exec_requests; start from sys.dm_exec_sessions (or the community procedure, which lists it) to see it.

Lessons learned

Mastodon LLIMastodon SocialFosstodon