Lead blocker or head blocker is one of old stories for sql server dba and there are lot of scripts also available to find same. Some people use sys.sysprocesses and some go with dmv's but i personally recommend DMv's as they are the future of sql server.
So first we need to understand what is lead blocker, Those who are not much interested in scenario and need just script can Download or copy from below
Download Script
/***********Script To Find Lead \ Head Blocker*******************/
/
Thanks to Derek Dieter for sharing this Script
*/
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
/*****************************************************/
So here's the scenario:
When SPID 1 is blocking SPID 2 and SPID 2 is Blocking SPID 3 , This is called Blocking chain and the SPID which blocks other spid causing chain b of blocks but is not blocked buy any other SPID called Lead Blocker
Here I have create scenerio to create blocking chain and find head blocker
I have create 4 tables and trying to update 1st table from 2nd and 2nd from third
/******create 4 tables**********/
CREATE TABLE t1 (i1 int);
CREATE TABLE t2 (i2 int);
CREATE TABLE t3 (i3 int);
CREATE TABLE t4 (i4 int);
/*************************/
/******Insert Record in 4 Tables***********/
INSERT t1 SELECT 1;
INSERT t2 SELECT 2;
INSERT t3 SELECT 3;
INSERT t4 SELECT 4;
/*************************/
Now we will Run 3 transactions in 3 different query analyzer
/*&&&&&&&&&&&&& Query Window 1 &&&&&&&&&&&&&&&&&*/
BEGIN TRAN
UPDATE t2 SET t2.i2 = t3.i3
FROM t2 , t3
/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
Here after running transaction we will get (1 row(s) affected), But still transaction is open means untill we will pass commit it will not be completed. Causing Below locks
/*******LOCKS ON Table T2:****************
Intent-exclusive lock on table
Intent-exclusive lock on Page
exclusive lock on RID
********LOCKS ON TABLE T3:***************/
None
/***********************/
Then open 2nd query analyzer and run below code
/*&&&&&&&&&&&&& Query Window 2 &&&&&&&&&&&&&&&&*/
BEGIN TRAN
UPDATE t1 SET t1.i1 = t2.i2
FROM t1 , t2
Select * from t1
/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
Here after running transaction we will find transaction is continuously running and blocked by above spid
, This will create some more below blocks as well
/*******LOCKS on Table T1****************/
Intent-exclusive lock on table
Update lock on RID
Intent-update lock on Page
/***********************/
Then open 3rd query analyzer and run below code
/*&&&&&&&&&&&&& Query Window 3 &&&&&&&&&&&&&&&&&&&*/
BEGIN TRAN
UPDATE t1 SET t1.i1 = t3.i3
FROM t1 , t3
/*&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&*/
Here after running transaction we will find transaction is continiously running and blocked by above spid only
, This will create some more below blocks as well
/********LOCK ON TABLE T3***************/
Intent-share lock on table
/********LOCK ON TABLE T1***************/
Update lock on RID is waiting
Intent-Update lock on Page
Intent-exclusive lock on table
/***********************/
If any one is confused about locks ,You can directly run sp_locks and get lock details as below and extract same
Now you can try below mention Query for Head blocker , I have also check output of sys.processes to confirm there is a blocking chain
No comments:
Post a Comment