Lead Blocker identification SQL Server
​
The term BLOCKS AND LOCKS is quite common in SQL Server terminologies and is one of the usual stuff every DBAs has to come across during his daily life. DBAs are smarter enough to find the lock details and kill the culprits also. But in some cases, where there are hundreds of lock chains in a database , it would be difficult to find out which lock is actually causing the root issue holding up all the rest of the locks. The below script will help you to identify that and kill it from the root after making the analysis.
Hope this helps.
SELECT
spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0