Il est utile de voir quelles sont les ressources mises en jeu dans le blocage de requêtes en cours d’exécution dans une instance SQL Server.
Voici donc une requête retournant quelques informations relatives aux requêtes bloquées par d’autres :
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
, 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
Bon débloquage 
ElSuket