MSSQL DMVs — Blockings

This is a very good DMV script that gives you blocking/​locking even on page level and the SQL state­ment each spid is run­ning at the time.

 

SELECT  tl.request_session_id AS WaitingSessionID
,wt.blocking_session_id AS BlockingSessionID
,wt.resource_description
,wt.wait_type
,wt.wait_duration_ms
,DB_NAME(tl.resource_database_id) AS DatabaseName
,tl.resource_associated_entity_id AS WaitingAssociatedEntity
,tl.resource_type AS WaitingResourceType
,tl.request_type AS WaitingRequestType
,wrt.[text] AS WaitingTSql
,btl.request_type BlockingRequestType
,brt.[text] AS BlockingTsql
FROM    sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks wt
ON tl.lock_owner_address = wt.resource_address
JOIN sys.dm_exec_requests wr
ON wr.session_id = tl.request_session_id
CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt
LEFT JOIN sys.dm_exec_requests br
ON br.session_id = wt.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) AS brt
LEFT JOIN sys.dm_tran_locks AS btl
ON br.session_id = btl.request_session_id;

No Comments

Post a Comment

Your email is never shared. Required fields are marked *

*