Capturer l'activité façon sp_WhoIsActive sur Azure SQL et retrouver le bloqueur
Limites d'Azure SQL Database, capture planifiée dans une table via les DMVs, rétention et requête pour retrouver le bloqueur après coup.
« Qui bloquait qui hier à 14 h 12 ? » Sur SQL Server classique, on lance sp_WhoIsActive dans un job de l'Agent toutes les minutes, on stocke le résultat dans une table, et la question est réglée. Sur Azure SQL Database, il faut adapter la méthode. Voici ce qui change et comment je procède.
Ce qu'on peut et ne peut pas faire
| Besoin | SQL Server / Managed Instance | Azure SQL Database |
|---|---|---|
| Planifier la capture | Agent SQL Server | Pas d'Agent : ordonnanceur externe (pipeline planifié, tâche Batch, Automation, Elastic Jobs) |
| Portée | Instance entière | Une base à la fois : pas de requête inter-bases, master et msdb non utilisables |
| DMVs de session | sys.dm_exec_*, sys.dm_os_waiting_tasks |
Disponibles, limitées à la base courante |
| Extended Events | Fichiers, sessions serveur | Sessions limitées à la base, cible en stockage |
| Installer une procédure | Dans master |
Dans la base ciblée |
Deux conséquences : la capture doit se connecter à chaque base concernée, et le planificateur doit vivre en dehors de la base. Les permissions nécessaires sont de type VIEW DATABASE STATE (ou VIEW DATABASE PERFORMANCE STATE selon le niveau de service) ; je vérifie dans la documentation du service avant de les accorder.
Architecture de la capture
flowchart TD
A["Ordonnanceur (toutes les 10 min)"] --> B["Script PowerShell"]
B --> C["Liste des bases occupées (métrique CPU via KQL)"]
C --> D["Connexion identité managée par base"]
D --> E["Capture des sessions"]
E --> F[("Table de capture dans la base")]
B --> G["Purge des lignes anciennes"]
G --> F
Étapes :
- Le script identifie les bases qui méritent une capture (CPU moyen dans une fourchette) pour ne pas écrire partout pour rien. Si la requête de sélection échoue, il se rabat sur toutes les bases primaires.
- Pour chaque base non en lecture seule : créer la table de capture si absente, insérer le résultat, purger.
- Une base en échec est journalisée, les autres continuent.
Une capture minimale avec les DMVs
Si on ne veut pas déployer toute la procédure communautaire, une requête sur les DMVs suffit déjà :
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;
La procédure communautaire fait la même chose en mieux (plans, verrous, tempdb) : l'installer dans la base est idempotent, et on lui demande d'écrire dans la table via son paramètre de destination.
Rétention
Une ligne par session active toutes les 10 minutes grossit vite. Règles que j'applique :
- Index clusterisé sur l'heure de collecte : la purge
DELETE ... WHERE collection_time < DATEADD(DAY, -30, SYSUTCDATETIME())reste un balayage de plage. - 30 jours suffisent pour comparer « ce lundi » à « le lundi précédent ».
- Purger par lots si la table est grosse, pour éviter les longs blocages et la croissance du journal.
Retrouver le bloqueur après coup
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;
Pour trouver la tête de chaîne, on cherche les blocking_session_id qui ne sont pas eux-mêmes bloqués dans le même instantané. Attention : le bloqueur est souvent une session en veille avec une transaction ouverte, donc absente de dm_exec_requests ; il faut alors partir de sys.dm_exec_sessions (ou de la procédure communautaire, qui la liste) pour la voir.
Retour d'expérience
- La capture n'est qu'un échantillon : un blocage de 20 secondes peut passer entre deux passages. Ne resserrer l'intervalle que sur les bases chaudes.
- Ne pas capturer les bases saturées à 100 % de CPU : la connexion elle-même risque d'échouer ou d'aggraver la situation.
- Ignorer les secondaires géo (lecture seule) : la table ne peut pas y être créée.
- Utiliser un timeout de commande court, sinon la capture devient elle-même un problème.
- Documenter le chemin « alerte, table de capture, bloqueur » : c'est ce qui fait gagner du temps à 3 h du matin.