Il existe plusieurs façons de diagnostiquer les problèmes de blocking/locks dans une base de données SQL. Vous allez trouver sur le web une multitude d'outils afin de trouver les blocks. Toutefois, ceux-ci ne prennent généralement pas en considération le champ program_name qui inclut le session ID de l'utilisateur AX.
Donc, voici deux requêtes que j'utilise pour trouver les blocks en incluant la session ID de l'utilisateur AX. Je suggère de les exécuter en même temps.
Afin d'obtenir l'information dans le champ AX SPID, vous devez avoir activé connexion contexte: Troubleshooting database performance [AX 2012]
USE [master]
GO
SELECT cast(s.context_info as varchar(128)) AS ax_spid,
s.host_name,
w.session_id AS sql_spid,
w.wait_duration_ms,
r.total_elapsed_time,
t.text,
w.wait_type,
w.blocking_session_id AS sql_head_blocker,
s.cpu_time,
s.reads,
s.writes,
s.logical_reads,
r.open_transaction_count,
DB_NAME(s.database_id) AS db_name
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1 AND s.program_name like '%Dynamics%'
ORDER BY sql_spid
GO
SELECT cast(s.context_info as varchar(128)) AS ax_spid,
p1.spid AS sql_spid,
p1.hostname,
p1.cmd,
text,
p1.cpu AS cpu_time,
p1.physical_io AS physical_io,
p1.memusage,
p1.open_tran
FROM sysprocesses p1
INNER JOIN sys.dm_exec_connections c
ON p1.spid = c.session_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE EXISTS (SELECT p2.spid FROM sysprocesses p2 WHERE p2.blocked > 0 AND p2.blocked = p1.spid)
AND NOT EXISTS (SELECT p3.spid FROM sysprocesses p3 WHERE p3.blocked > 0 AND p3.spid = p1.spid)
AND s.program_name like '%Dynamics%'
GO
USE [master]
GO
SELECT cast(s.context_info as varchar(128)) AS ax_spid,
s.host_name,
w.session_id AS sql_spid,
w.wait_duration_ms,
r.total_elapsed_time,
t.text,
w.wait_type,
w.blocking_session_id AS sql_head_blocker,
s.cpu_time,
s.reads,
s.writes,
s.logical_reads,
r.open_transaction_count,
DB_NAME(s.database_id) AS db_name
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1 AND s.program_name like '%Dynamics%'
ORDER BY sql_spid
GO
SELECT cast(s.context_info as varchar(128)) AS ax_spid,
p1.spid AS sql_spid,
p1.hostname,
p1.cmd,
text,
p1.cpu AS cpu_time,
p1.physical_io AS physical_io,
p1.memusage,
p1.open_tran
FROM sysprocesses p1
INNER JOIN sys.dm_exec_connections c
ON p1.spid = c.session_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE EXISTS (SELECT p2.spid FROM sysprocesses p2 WHERE p2.blocked > 0 AND p2.blocked = p1.spid)
AND NOT EXISTS (SELECT p3.spid FROM sysprocesses p3 WHERE p3.blocked > 0 AND p3.spid = p1.spid)
AND s.program_name like '%Dynamics%'
GO
S'il y a beaucoup de blocks dans votre base de données, j'aime bien utiliser la requête de Pinal Dave pour y voir l'arbre: SQL SERVER – Identifying Blocking Chain Using SQL Scripts