1 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 42 43 44 45 46 47 48 49 50 51 52
| WITH blocker
AS
(SELECT w.session_id,
s.original_login_name,
s.login_name,
w.wait_duration_ms,
w.wait_type,
r.STATUS,
r.wait_resource,
w.resource_description
s.program_name,
w.blocking_session_id,
s.host_name,
q.text,
r.command,
r.total_elapsed_time,
p.query_plan,
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
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) q
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE w.session_id > 50)
SELECT b.session_id AS waiting_spid,
b.blocking_session_id AS blocking_spid,
b.login_name AS waiting_login_name,
es.login_name AS blocking_login_name,
b.host_name AS waiting_hostname,
es.host_name AS blocking_hostname,
b.wait_type AS wait_type,
b.wait_duration_ms AS wait_duration,
t.request_mode AS wait_request_mode,
b.STATUS AS waiter_status,
es.STATUS AS blocker_status,
b.wait_resource AS locked_resource_id
b.command AS waiting_command_type,
b.text AS waiting_command_text,
b.total_elapsed_time AS waiting_command_total_elapsed_time,
b.query_plan AS waiting_command_query_plan,
o.name AS locked_object_name,
t.resource_type AS locked_resource_type,
b.resource_description AS locked_resource_description,
DB_NAME(t.resource_database_id) AS locked_resource_database_name,
b.program_name AS waiting_program_name,
es.program_name AS blocking_program_name,
FROM blocker b
INNER JOIN sys.dm_exec_sessions es ON b.blocking_session_id = es.session_id
INNER JOIN sys.dm_tran_locks t ON t.request_session_id = b.session_id
INNER JOIN sys.partitions p ON p.hobt_id = t.resource_associated_entity_id
INNER JOIN sys.objects o ON o.object_id=p.object_id
WHERE t.request_status='WAIT'
GO |
Partager