Trouver les sessions bloquantes sur Azure SQL
Trouver les sessions, les requêtes en cours et les blocages sur Azure SQL avec les DMV, et utiliser sp_WhoIsActive.
Sur Azure SQL Database, sp_who ne donne qu'une vue limitée, pour des raisons de sécurité. Les requêtes ci-dessous utilisent les vues de gestion dynamique (DMV) pour lister les sessions, les requêtes en cours et les sessions qui en bloquent d'autres.
Sessions et connexions
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;
Requêtes en cours
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;
Requêtes avec l'instruction en cours
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;
Requêtes bloquées
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
Instructions bloquées et bloquantes
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 est une procédure communautaire qui affiche les sessions en cours avec leurs détails. Elle doit être installée dans la base avant usage. Paramètres utiles :
| Paramètre | Effet |
|---|---|
@get_plans = 1 |
Ajoute les plans d'exécution des requêtes en cours. |
@get_locks = 1 |
Ajoute une valeur XML qui montre les verrous de chaque requête (table, ligne, objet). Utile pour trouver la requête qui en bloque d'autres. |
@get_task_info = 2 |
Montre sur quoi attend chaque tâche d'une requête parallèle (utile pour les attentes CXPACKET). |
EXEC sp_WhoIsActive @get_locks = 1, @get_plans = 1, @get_task_info = 2;
Remarques
- Les requêtes de blocage ne montrent que les sessions bloquées au moment de l'exécution.
sp_lockliste les verrous de l'instance, mais n'indique pas quelle instruction les détient. Utilisez les requêtes ci-dessus ousp_WhoIsActivepour cela.