Head Blocker

SQL Server Administration
1

SQL 2008 R2

Hello - In SQL 2005 I had a script where I could see "Blocking" and "Blocked By". It was Very Easy to find the head blocker by finding the SPID that was blocking but not blocked.

I have searched but have not found a script that will quickly provide the actual head blocker for SQL 2008.

Can somebody point me in the right direction please?

Thanks!
Terry

2

You can use sp_who2 or sp_WhoIsActive.

3

I haven't run this code so I may be wildly off base here but...[code]-----------------------------------------------------------------------------
--- Blocking Chains: (X -> Y -> Z) X is blocking Y; Y is blocking Z

set nocount on;
use master;

if Object_id(N'tempdb..#Who2') is NULL
begin
create table #Who2 (
SPID smallint,
Status nchar(30),
Login sysname,
HostName sysname,
BlkBy char(5),
DBName sysname null,
Command nchar(20),
CPUTime bigint,
DiskIO bigint,
LastBatch varchar(20), --smalldatetime,
ProgramName varchar(100),
SPID2 smallint,
REQUESTID int
)
end

truncate table #Who2;

insert into #Who2
exec sp_Who2

;with BlockingChain
as (
select
cast(w1.spid as varchar(10)) LastLink,
cast(w1.spid as varchar(200)) DependencyChain,
cast(0 as bit) Terminus
from
#Who2 w1
inner join
#Who2 w2
on cast(w1.spid as varchar(10)) = w2.BlkBy
and w1.BlkBy = '.'

union all

select
cast(w.spid as varchar(10)),
cast(bc.DependencyChain + ' -> ' + cast(w.spid as varchar(200)) as varchar(200)),
case
when exists (
select *
from #Who2 ww
where ww.BlkBy = bc.LastLink
)
then cast(0 as bit)
else cast(1 as bit)
end
from
BlockingChain bc
inner join
#Who2 w
on bc.LastLink = w.BlkBy
)
select DependencyChain
from BlockingChain
where terminus = 1
[/code]

4

Thanks Tara! It's been quite a while since I've posted a question. Nice talking again :smile: - You've always been a Great help!

I ran both of those but I didn't see how to find the head blocker from the output.

sp_WhoIsActive
I see the "blocking_session_id" column and in that column I see multiple id numbers, but also see 6 that are NULL which I'm guessing means there isn't anything blocking it.

However - The 6 NULL show different names for login_name, host_name, and database. So I'm unsure how to find the "head" blocker from this.

sp_who2
Here I see BlkBy which is fine. But the head blocker isn't blocked and therefore the head blocker isn't indicated.

I remember way back there was a script that showed both BlkBy and Blocking so when you located the SPID that was blocking but not blocked by ... well you had your offender.

Sorry if I'm missing something simple ... which is likely the case.

Regards,
Terry

5

I think I figured it out. I'll need to wait for the next time we have an issue.

This is what I found that helped

https://msdn.microsoft.com/en-us/library/ms179881.aspx
http://www.sqlservercentral.com/articles/Blocking/73148/

/*
The query results will return all active transactions and connections on the SQL Server involved in blocking, identified by SPIDs.
If no blocking exists, the query will return no results.
The blocked column will show which transaction is doing the blocking of the other transaction(s).
There is often a long running chain where SPID X is blocked by SPID Y which is blocked by SPID Z.
This is only able to trap real time blocking.
*/

select spid, blocked, waittype
, waittime, lastwaittype, dbid
, uid, cpu, physical_io, memusage
, login_time, last_batch, hostname
, program_name, nt_domain, nt_username, loginame
from master..sysprocesses
where blocked <> 0
or spid in (select blocked from master..sysprocesses)

Thanks ... and have a Great day!

6

FWIW, I re-visited the script posted earlier. I think that this modified version will produce the blocking chain and the lead SPID that is holding up the dependent cascade.[code]-----------------------------------------------------------------------------
--- Blocking Chains: (X -> Y -> Z) X is blocked by Y; Y is blocked by Z

set nocount on;
use master;

if Object_id(N'tempdb..#Who2') is NULL
begin
create table #Who2 (
SPID smallint,
Status nchar(30),
Login sysname null,
HostName sysname null,
BlkBy char(5),
DBName sysname null,
Command nchar(20),
CPUTime bigint,
DiskIO bigint,
LastBatch varchar(20), --smalldatetime,
ProgramName varchar(100),
SPID2 smallint,
REQUESTID int
)
end

truncate table #Who2;

insert into #Who2
exec sp_Who2

;with BlockingChain
as (
select
cast(w1.spid as char(5)) BaseLink,
cast(w2.spid as char(5)) LastLink,
cast(cast(w2.spid as varchar(200)) + ' -> ' + cast(w1.spid as varchar(200))as varchar(200)) DependencyChain
from
#Who2 w1
inner join
#Who2 w2
on cast(w1.spid as char(5)) = w2.BlkBy
and w1.BlkBy = ' . '

union all

select
bc.BaseLink,
cast(w.spid as char(5)),
cast(cast(w.spid as varchar(200)) + ' -> ' + bc.DependencyChain as varchar(200))
from
BlockingChain bc
inner join
#Who2 w
on cast(bc.LastLink as char(5)) = w.BlkBy
)
select bc.*,
case
when exists (
select *
from #Who2 ww
where ww.BlkBy = cast(bc.LastLink as char(5))
)
then cast(0 as bit)
else cast(1 as bit)
end Terminus
from BlockingChain bc
order by
bc.BaseLink,
Terminus DESC,
bc.LastLink[/code]