Troubleshoot blocking chain in SQL server
Write an email to [email protected] for SQL server training
SQL Server
Blocking
Blocking Chain
Performance
--Lead Blocker
USE [master]
select spid,kpid,lastwaittype,dbid,login_time,last_batch
from sysprocesses s
where exists ( select 1 from sysprocesses sp where sp.blocked != 0 and sp.blocked = s.spid )
and not exists ( select 1 from sysprocesses sp where sp.blocked != 0 and sp.spid = s.spid )
--Blocking objects
SELECT blocking.session_id,
OBJECT_NAME(p.[object_id]) BlockedObject,l.request_mode
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked
ON blocking.session_id = blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id
INNER JOIN sys.partitions p ON SUBSTRING(resource_description,
PATINDEX('%associatedObjectId%', resource_description) + 19,
LEN(resource_description)) = p.partition_id
LEFT JOIN sys.dm_tran_locks AS l
ON waitstats.resource_address = l.lock_owner_address