FrankMcB’s Blocking Script

February 10, 2015February 11, 2015

This is my attempt at a “best” blocking script. I’ve hacked and chewed several other people’s together, tried to document where they came from within the code, and then put what I wanted on top of it all.

My Goal:

Find the Lead Blocker
The Type Of Lock It’s Holding
Show What They Are Blocking
Determine What Resource It’s Blocking On
Create A Script To Show The Row/Page It’s Blocking On
Make It Dynamic So Nothing Is Hard Coded To A Database
Resolve IDs to Real Names
Convert Obscure Codes to Human Readable Information
If any of the SQL is a Server Side API Cursor Fetch, Resolve it to the Original SQL Statement.



set nocount on


declare @i bigint
declare @dbname sysname
declare @dbid bigint
declare @sqlstmnt nvarchar(max)

IF OBJECT_ID('tempdb..AllBlockersLocks') IS NOT NULL DROP TABLE tempdb..AllBlockersLocks;


IF OBJECT_ID('tempdb..##AllBlocking') IS NOT NULL DROP TABLE ##AllBlocking;

WITH LeadBlockers (spid, milliseconds, waittype, blocker, blockee, level, dbname,dbid, sqlstmnt)
AS
(
 SELECT spid,waittime 'milliseconds', s.waittype, s.blocked as blocker, s.spid as blockee, 0 as level, dbname = SUBSTRING(DB_NAME(dbid),1,20), dbid, (SELECT text FROM ::fn_get_sql(s.sql_handle)) sql
 FROM master..sysprocesses s
 WHERE blocked = 0 AND EXISTS (SELECT * FROM master..sysprocesses inside WHERE inside.blocked = s.spid)
 UNION ALL
 SELECT s.spid, waittime 'milliseconds',s.waittype, s.blocked as blocker, s.spid as blockee, level + 1, SUBSTRING(DB_NAME(s.dbid),1,20), s.dbid, (SELECT text FROM ::fn_get_sql(s.sql_handle)) sql
 FROM master..sysprocesses s
 INNER JOIN LeadBlockers
 ON s.blocked = LeadBlockers.blockee
)
SELECT *
into ##AllBlocking
FROM LeadBlockers
ORDER BY level




print ''
print '****************************************'
print '*** LEAD BLOCKER(S) LOCKS HERE ***'
print '****************************************'
print ''

print ''
select count(distinct(spid)) 'Number of Lead Blocking Processes' from ##AllBlocking where level = 0
print ''
select distinct(spid) 'Unique Lead Blocking Process SPIDs' from ##AllBlocking where level = 0
print ''

select @i = spid from ##AllBlocking where blocker=0
select @dbid = dbid from ##AllBlocking where blocker = 0

select @i = spid from ##AllBlocking where blocker <> 0
select @dbname = dbname from ##AllBlocking where blocker <> 0

-- select @i = spid from ##AllBlocking where level = 0;
IF OBJECT_ID('tempdb..#TopLevelBlockersSpids') IS NOT NULL DROP TABLE #TopLevelBlockersSpids;
select spid into #TopLevelBlockersSpids from ##AllBlocking where level = 0;


print ''
print 'These Lead SPIDs Hold These Locks'
print '---------------------------------'
print ''


select @sqlstmnt = 
'IF OBJECT_ID(''tempdb..#LeadBlockersListTopLevel'') IS NOT NULL DROP TABLE #LeadBlockersListTopLevel;
select convert (smallint, req_spid) As spid,
 substring(db_name(rsc_dbid), 1, 15) As DatabaseName,
 substring(so.name, 1,15) as TableName,
 rsc_objid as objId,
 rsc_indid As IndId,
 ''ObjType'' =case when rsc_indid = 1 then ''ClustTbl'' when rsc_indid > 1 then ''Index'' else ''HeapTbl'' end,
 ''LockType'' =case 
 when substring (v.name, 1, 4) = ''RID'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''KEY'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''PAG'' then ''PageLock'' 
 when substring (v.name, 1, 4) = ''TAB'' then ''TableLock'' 
 else ''UNKNOWN'' end,
 substring (v.name, 1, 4) As Type,
 substring (rsc_text, 1, 32) as Resource,
 substring (u.name, 1, 8) As Mode,

 ''LockMode'' = case
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''X'' then ''Exclusive''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''S'' then ''Share''
 else ''UNKNOWN''
 end,

 substring (x.name, 1, 5) As Status
into #LeadBlockersListTopLevel
 from master.dbo.syslockinfo (nolock),
 master.dbo.spt_values v (nolock),
 master.dbo.spt_values x (nolock),
 master.dbo.spt_values u (nolock),
--> this statement is dynamic for each database involved. drive off the lead blockers top level.
 '+dbname+'..sysobjects so (nolock)

 where master.dbo.syslockinfo.rsc_type = v.number
 and v.type = ''LR''
 and master.dbo.syslockinfo.req_status = x.number
 and x.type = ''LS''
 and master.dbo.syslockinfo.req_mode + 1 = u.number
 and u.type = ''L''
-- and req_spid in (cast(@i as nvarchar(4)))
--> i get the top level locks held by the blockers here:
 and req_spid in (select spid from #TopLevelBlockersSpids)
 and so.id = rsc_objid
 and substring (u.name, 1, 8) = ''X''
 order by spid;' from ##AllBlocking

--print @sqlstmnt

 exec sp_executesql @sqlstmnt


 
IF OBJECT_ID('tempdb..AllBlockersLocks') IS NOT NULL DROP TABLE tempdb..AllBlockersLocks;
CREATE TABLE tempdb..AllBlockersLocks(
 [spid] [smallint] NULL,
 [DatabaseName] [nvarchar](15) NULL,
 [TableName] [nvarchar](15) NULL,
 [objId] [int] NOT NULL,
 [IndId] [smallint] NOT NULL,
 [ObjType] [varchar](8) NOT NULL,
 [LockType] [varchar](12) NOT NULL,
 [Type] [nvarchar](4) NULL,
 [Resource] [nvarchar](32) NULL,
 [ToView] [nvarchar](222) NULL,
 [Mode] [nvarchar](8) NULL,
 [LockMode] [varchar](15) NOT NULL,
 [Status] [nvarchar](5) NULL,
 [Level] [int] NULL,
 [Duration] [bigint] NULL,
 [Blockee] [smallint] NULL,
 [Blocker] [smallint] NULL,
 [BlockSpid] [smallint] NULL,
 [SQLStmnt] [nvarchar](4000) NULL,
 [rsc_text] [nchar](32) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_bin] [binary](16) NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_valblk] [binary](16) NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_dbid] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_indid] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_objid] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_type] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [rsc_flag] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_mode] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_status] [tinyint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_refcnt] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_cryrefcnt] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_lifetime] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_spid] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_ecid] [int] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_ownertype] [smallint] NOT NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_transactionID] [bigint] NULL
ALTER TABLE tempdb.[dbo].AllBlockersLocks ADD [req_transactionUOW] [uniqueidentifier] NULL




-- set @sqlstmnt = ''

declare @sqlstmnt1 nvarchar(max)
declare @sqlstmnt2 nvarchar(max)

truncate table tempdb..AllBlockersLocks

-- select * from tempdb..AllBlockersLocks where mode = 'X'

select @sqlstmnt1 = 
'insert into tempdb..AllBlockersLocks
select convert (smallint, req_spid) As spid,
 substring(db_name(rsc_dbid), 1, 15) As DatabaseName,
 substring(so.name, 1,15) as TableName,
 rsc_objid as objId,
 rsc_indid As IndId,
 ''ObjType'' =case when rsc_indid = 1 then ''ClustTbl'' when rsc_indid > 1 then ''Index'' else ''HeapTbl'' end,

-- https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx
 ''LockType'' =case 
 when substring (v.name, 1, 4) = ''RID'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''KEY'' then ''RowLock'' 
 when substring (v.name, 1, 4) = ''PAG'' then ''PageLock'' 
 when substring (v.name, 1, 4) = ''TAB'' then ''TableLock'' 
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''DB'' then ''DBLock''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''FIL'' then ''DBFileLock''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''EXT'' then ''ExtentLock''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''APP'' then ''AppResLck''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''MD'' then ''MetaDataLck''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''HBT'' then ''HeapBTreeLck''
 when rtrim(ltrim(substring (v.name, 1, 4))) = ''AU'' then ''AllocUnitLck''
 else ''UNKNOWN'' end,
 substring (v.name, 1, 4) As Type,


-- http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

 substring (rsc_text, 1, 32) as Resource, -- PAG 1:288 
 ''ToView'' = case 
 when (substring (v.name, 1, 4) = ''PAG'') and (rsc_indid = 0 or rsc_indid=1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''DBCC TRACEON(3604) DBCC PAGE( ''+char(39)+substring(db_name(rsc_dbid), 1, 15)+char(39)+ 
 '',''+
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),1,CHARINDEX('':'', rtrim(ltrim(rsc_text)))-1)))+
 '',''+
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),CHARINDEX('':'', rtrim(ltrim(rsc_text)))+1,10))) +'',3) WITH TABLERESULTS --dbcc ind(tempdb, x, -1)''

 -- RID 1:288:0 in x:y:z format
 when (substring (v.name, 1, 4) = ''RID'') and (rsc_indid = 0 or rsc_indid=1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''DBCC TRACEON(3604) DBCC PAGE( ''+char(39)+substring(db_name(rsc_dbid), 1, 15)+char(39)+ 
 '',''+
 -- x is the file number
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),1,CHARINDEX('':'', rtrim(ltrim(rsc_text)))-1)))+
 '',''+
 substring(
 -- y:z
 rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),CHARINDEX('':'', rtrim(ltrim(rsc_text)))+1,10))),
 1,
 charindex('':'',rtrim(ltrim(substring(rtrim(ltrim(rsc_text)),CHARINDEX('':'', rtrim(ltrim(rsc_text)))+1,10))))-1)
 +
 '',3) WITH TABLERESULTS --dbcc ind(tempdb, x, -1)''+
 '' -- select %%lockres%%,* from '' +substring(so.name, 1,15)+''(nolock) where %%lockres%% = ''+char(39)+
 rtrim(ltrim((cast(rsc_text as nvarchar(20))))
 )+char(39)


 when (substring (v.name, 1, 4) = ''TAB'') and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then ''EXCLUSIVE TABLE LOCK ON ''+substring(so.name, 1,15)

 when (rsc_indid = 1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''EXCLUSIVE LOCK ON CLUSTIDX: ''+cast(rsc_indid as nvarchar(3))+'' ON OBJID ''+cast(rsc_objid as nvarchar(10))+'' TABLE NAME: ''+substring(so.name, 1,15)+
 '' -- select %%lockres%%, * from '' +substring(so.name, 1,15)+''(nolock) where %%lockres%% = ''+char(39)+
 substring(cast(rsc_text as nvarchar(20)),1,14 
 )+char(39)

 when (rsc_indid > 1) and (rtrim(ltrim(substring (u.name, 1, 5))) = ''X'') 
 then 
 ''EXCLUSIVE LOCK ON NONCLUSTIDX: ''+cast(rsc_indid as nvarchar(3))+'' ON OBJID ''+cast(rsc_objid as nvarchar(10))+'' TABLE NAME: ''+substring(so.name, 1,15)

 else '''' 
 end,

 substring (u.name, 1, 8) As Mode,'


--declare @sqlstmnt2 nvarchar(max)
select @sqlstmnt2 = 
'
-- https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx
 ''LockMode'' = case
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''X'' then ''Exclusive''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''S'' then ''Share''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''IX'' then ''IntentExclusive''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''IS'' then ''IntentShare''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''U'' then ''Update''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''BU'' then ''BulkUpdate''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''SIX'' then ''SharedIntEx''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''IU'' then ''IntentUpd''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''SIU'' then ''ShIntentUpd''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''UIX'' then ''UpdIntentEx''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''Sch-M'' then ''SchemaMod''
 when rtrim(ltrim(substring (u.name, 1, 5))) = ''Sch-S'' then ''SchemaStability''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeS-S'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeS-U'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-Null'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-S'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-U'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeIn-X'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeX-S'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeX-U'' then ''Range''
 --when rtrim(ltrim(substring (u.name, 1, 5))) = ''RangeX-X'' then ''Range''
 else ''UNKNOWN''
 end,

 substring (x.name, 1, 5) As Status,
 ab.level as Level,
 ab.milliseconds as Duration,
 ab.blockee as Blockee,
 ab.blocker as Blocker,
 ab.spid as BlockSpid, 
 cast(ab.sqlstmnt as nvarchar(4000)) as SQLStmnt,
 master.dbo.syslockinfo.*
-- into tempdb..AllBlockersLocks
 from master.dbo.syslockinfo (nolock),
 master.dbo.spt_values v (nolock),
 master.dbo.spt_values x (nolock),
 master.dbo.spt_values u (nolock), ##AllBlocking ab (nolock),
--> this statement is dynamic for each database involved. drive off the lead blockers top level.
 '+dbname+'..sysobjects so (nolock)


 where master.dbo.syslockinfo.rsc_type = v.number
 and v.type = ''LR''
 and master.dbo.syslockinfo.req_status = x.number
 and x.type = ''LS''
 and master.dbo.syslockinfo.req_mode + 1 = u.number
 and u.type = ''L''
 and so.id = rsc_objid
 and ab.spid = req_spid' from ##AllBlocking


-- have to concat like this or else the string is bigger than 4000 bytes and you get a syntax error on the dynamic sql
declare @bigsql nvarchar(max)
set @bigsql = cast(@sqlstmnt1 as nvarchar(max))+cast(@sqlstmnt2 as nvarchar(max))

exec sp_executesql @bigsql


 select
 spid, 
 DatabaseName, 
 TableName, 
 objId, 
 IndId, 
 ObjType, 
 LockType, 
 Type, 
 Resource, 
 ToView, 
 LockMode, 
 Status
 from tempdb..AllBlockersLocks 
 where level = 0 and mode = 'X'
 order by level, spid





DECLARE @bspid AS INT;
DECLARE @bblocker AS INT;
DECLARE @bblockee AS INT;
DECLARE @blevel AS INT;
DECLARE @rowcounts AS INT;
DECLARE @nomore AS INT;
DECLARE @blockingchain AS INT;
DECLARE @currentlevel AS INT;
DECLARE @spacing AS NVARCHAR (20);


SET @blockingchain = 1;

print ''
print ''
print 'Here Is The Chain Of Events Being Blocked By The Above SPIDs'
print '------------------------------------------------------------'


-- first, get the top level blockers
DECLARE blocked_cursor CURSOR
 FOR SELECT spid,
 blocker,
 blockee,
 level
 FROM ##AllBlocking
 WHERE level = 0
 ORDER BY spid;

OPEN blocked_cursor;

FETCH NEXT FROM blocked_cursor INTO @bspid, @bblocker, @bblockee, @blevel;

WHILE @@FETCH_STATUS = 0
 BEGIN
 -- do a depth first tree search for each top level blocker spid
 SET @currentlevel = 1;
 SET @nomore = 0;
 SET @rowcounts = 0;
 WHILE (@nomore = 0)
 BEGIN
 -- check to see if there are any rows for this blocking chain
 SELECT @rowcounts = count(*)
 FROM ##AllBlocking
 WHERE blocker = @bspid
 AND level = @currentlevel;
 -- if there are, do a depth search of the tree to show the chain
 IF (@rowcounts > 0)
 BEGIN
 IF (@currentlevel = 1)
 BEGIN
 PRINT '';
 PRINT '';
 PRINT '***************************************************************************************************'
 PRINT '******************* MOST IMPORTANT HERE: LEVEL = 1 IS LEAD BLOCKER ********************************';
 PRINT '***************************************************************************************************'
 print ''
 END
 -- indent based on the level
 SET @spacing = replicate(' ', 5 * (@currentlevel - 1));

 IF (@currentlevel > 1)
 BEGIN
 PRINT '+++++++++++++++++++++++++++++++';
 END

 PRINT @spacing + 'blocking chain: ' + CAST (@blockingchain AS NVARCHAR (4)) + ' level: ' + CAST (@currentlevel AS NVARCHAR (4));
 

 -- show me what is blocking here
 SELECT @spacing + 'Spid: ' + CAST (blocker AS NVARCHAR (4)) + ' is blocking Spid ' + CAST (spid AS NVARCHAR (4))+char(10),
-- @spacing + 'Spid: ' + CAST (spid AS NVARCHAR (4)) + ' wants a '+LockMode+' '+LockType+' lock on object '+ObjType+' '+TableName +' and is being blocked by Spid '+ (select top 1 CAST (spid AS NVARCHAR (4))+' which has a '+ LockMode+' '+LockType+' lock on the same object.' from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X')
 @spacing + 'Spid: ' + CAST (isnull(spid,' ') AS NVARCHAR (4)) + ' wants a '+isnull(LockMode, ' ') +' '+isnull(LockType, ' ')+' lock on object '+isnull(ObjType, ' ')+' '+
 isnull(TableName, ' ') +' and is being blocked by Spid '--
 + case 
 when @currentlevel > 1 then (select top 1 CAST (isnull(spid,' ') AS NVARCHAR (4)) +' which has a ' + isnull(LockMode, ' ') +isnull(LockType, ' ') +' lock on the same object.' from tempdb..AllBlockersLocks where spid = @bspid order by Mode)
 when @currentlevel = 1 then (select top 1 CAST (isnull(spid,' ') AS NVARCHAR (4)) +' which has a ' + isnull(LockMode, ' ') +isnull(LockType, ' ') +' lock on the same object.' from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X' order by Mode)
 end
 FROM tempdb..AllBlockersLocks
 WHERE blocker = @bspid
 AND level = @currentlevel;

 if (@currentlevel = 1)
 begin
 IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
 select @spacing+'Spid '+CAST (spid AS NVARCHAR (4))+
 ' has a '+LockMode+' '+LockType+' on ['+ObjType+'] ['+ TableName + '] on Resource ['+rtrim(ltrim(Resource))+'] : '+ ToView 'Statement To See Row(s) *CAUSING* The Blocking' into #t from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X';
 select * from #t
 select top 1 @spacing + cast(SQLStmnt as nvarchar(max)) 'SQL Statement Thats Blocking' from tempdb..AllBlockersLocks where spid = @bspid and Mode = 'X';
 end


 -- select cast(SQLStmnt as nvarchar(max)) 'SQL Statement Thats Blocking' from tempdb..AllBlockersLocks


-- now go another level deeper in the tree for this chain
 SELECT @bspid = blockee
 FROM ##AllBlocking
 WHERE blocker = @bspid
 AND level = @currentlevel;

 SET @currentlevel = @currentlevel + 1;
 END
 IF (@rowcounts = 0)
 BEGIN
 -- if there are no more levels for the chain, the bail out of the loop and get the next lead blocker
 SET @nomore = 1;
 END
 END
 SET @blockingchain = @blockingchain + 1;
 -- get next lead blocker here
 FETCH NEXT FROM blocked_cursor INTO @bspid, @bblocker, @bblockee, @blevel;
 END

CLOSE blocked_cursor;

DEALLOCATE blocked_cursor;




DECLARE @fetchcount AS BIGINT;

SELECT @fetchcount = COUNT(*)
FROM tempdb..AllBlockersLocks
WHERE upper(CAST (SQLStmnt AS NVARCHAR (MAX))) LIKE '%FETCH%';

IF @fetchcount > 0
 BEGIN
 PRINT '';
 PRINT '*******************************';
 PRINT '*** FETCH TEXT HERE ***';
 PRINT '*******************************';
 PRINT '';
 DECLARE fetch_cursor CURSOR
 FOR SELECT spid
 FROM tempdb..AllBlockersLocks
 WHERE upper(CAST (SQLStmnt AS NVARCHAR (MAX))) LIKE '%FETCH%';
 DECLARE @spid AS BIGINT;
 OPEN fetch_cursor;
 FETCH NEXT FROM fetch_cursor INTO @spid;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 -- now loop through and look for FETCH and show that if it's there
 -- http://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/'
 SELECT @spid 'spid', 
 c.session_id,
 c.properties,
 c.creation_time,
 c.is_open,
 t.text
 FROM sys.dm_exec_cursors (@spid) AS c CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) AS t;
 FETCH NEXT FROM fetch_cursor INTO @spid;
 END
 CLOSE fetch_cursor;
 DEALLOCATE fetch_cursor;
 END





/*

Sample Output:
 
****************************************
*** LEAD BLOCKER(S) LOCKS HERE ***
****************************************
 
 
Number of Lead Blocking Processes
---------------------------------
1

 
Unique Lead Blocking Process SPIDs
----------------------------------
57

 
 
These Lead SPIDs Hold These Locks
---------------------------------
 
spid DatabaseName TableName objId IndId ObjType LockType Type Resource ToView LockMode Status
------ --------------- --------------- ----------- ------ -------- ------------ ---- -------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------- ------
57 SilverSystem x 1571536682 1 ClustTbl RowLock KEY (052c8c7d9727) EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(052c8c7d9727)' Exclusive GRANT
57 SilverSystem x 1571536682 1 ClustTbl RowLock KEY (9d6bf8154a2a) EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(9d6bf8154a2a)' Exclusive GRANT

 
 
Here Is The Chain Of Events Being Blocked By The Above SPIDs
------------------------------------------------------------
 
 
***************************************************************************************************
******************* MOST IMPORTANT HERE: LEVEL = 1 IS LEAD BLOCKER ********************************
***************************************************************************************************
 
blocking chain: 1 level: 1
 
----------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Spid: 57 is blocking Spid 62
 Spid: 62 wants a IntentShare PageLock lock on object ClustTbl x and is being blocked by Spid 57 which has a ExclusiveRowLock lock on the same object.
Spid: 57 is blocking Spid 62
 Spid: 62 wants a IntentShare TableLock lock on object HeapTbl x and is being blocked by Spid 57 which has a ExclusiveRowLock lock on the same object.
Spid: 57 is blocking Spid 62
 Spid: 62 wants a Share RowLock lock on object ClustTbl x and is being blocked by Spid 57 which has a ExclusiveRowLock lock on the same object.

Statement To See Row(s) *CAUSING* The Blocking
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Spid 57 has a Exclusive RowLock on [ClustTbl] [x] on Resource [(052c8c7d9727)] : EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(052c8c7d9727)'
Spid 57 has a Exclusive RowLock on [ClustTbl] [x] on Resource [(9d6bf8154a2a)] : EXCLUSIVE LOCK ON CLUSTIDX: 1 ON OBJID 1571536682 TABLE NAME: x -- select %%lockres%%, * from x(nolock) where %%lockres%% = '(9d6bf8154a2a)'

SQL Statement Thats Blocking
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin tran 
update x set cola = 3





*/

xxx

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Connecting to %s

%d bloggers like this: