LLI Wiki — My Digital Knowledge Base

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 :

  1. 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.
  2. Pour chaque base non en lecture seule : créer la table de capture si absente, insérer le résultat, purger.
  3. 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 :

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

Mastodon LLIMastodon SocialFosstodon