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:
- 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.
- For each non-read-only database: create the capture table if missing, insert the output, purge.
- 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:
- Clustered index on the collection time, so the purge
DELETE ... WHERE collection_time < DATEADD(DAY, -30, SYSUTCDATETIME())stays a range scan. - 30 days is enough to compare "this Monday" with "last Monday".
- Purge in batches on big tables to avoid long blocking and log growth.
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
- The capture is only a sample: a 20-second block can slip between two runs. Tighten the interval only for hot databases.
- Do not capture databases pinned at 100% CPU: the connection itself may fail or make things worse.
- Skip geo-secondaries (read-only): the table cannot be created there.
- Use a short command timeout, otherwise the capture becomes a problem itself.
- Document the path "alert, capture table, blocker": it saves time at 3 am.