We’ve all been there, trying to figure out who’s blocking who, who is the most avid blocker. Personally, I was and still am a huge fan of Adam Machanic’s (b) sp_WhoIsActive for accomplishing that. But I stumbled upon a nice script from @SQL_Undercover (b|t) which is using sp_BlitzWho stored procedure.
For the ones not familiar with the Blitz family, you don’t know what you’re missing. I strongly encourage you to get familiar with them, they’re simply awesome.
Back to the blocking chain topic… The original script looked thru data that was already collected. This is awesome when you want to look at what happened in the past. But when your server is on fire, you want to see the blocking chain, like right now.
So, I took the original script, added a few more parameters, converted it to a dynamic TSQL, and voila…
create or alter proc BlitzBlockingChain
( @now bit = 0, /* show what's the blocking chain now rather then in the past */
@startdate as datetime = null,
@enddate as datetime = null,
@blitzwho_table as nvarchar(128) = N'dbatools.dbo.BlitzWho',
@execute bit = 0, /* execute the statement or just show it */
@minutes_back int = 60 /* how far back to go when @now = 0 */
)
as
begin
set nocount on;
declare @tsql nvarchar(4000);
declare @tbl nvarchar(128) = N'blocking_chain_now_' + cast(@@spid as nvarchar(5))
/* check if the table exists */
if @now = 0 and object_id(@blitzwho_table) is null
begin
raiserror('Specified BlitzWho table (%s) does not exists.', 16, 1, @blitzwho_table) with nowait;
return;
end;
/* check what happened in the past 2 hrs */
if @now = 0
begin
if @startdate is null
select @startdate = dateadd(minute, 0-@minutes_back, getdate());
if @enddate is null
select @enddate = dateadd(minute, @minutes_back, @startdate);
end;
if @now = 1
begin
/* make sure we do get the correct snapshot, not mixed with some old data */
set @tsql = N'drop table if exists tempdb.dbo.' + @tbl;
exec(@tsql);
/* get the data */
exec sp_BlitzWho @OutputDatabaseName = 'tempdb', @outputschemaname = 'dbo', @outputtableName = @tbl;
end
/* tsql template to use */
set @tsql = N'
;WITH BlockChain
( Chain,
ID,
CheckDate,
session_id,
blocking_session_id,
login_name,
host_name,
program_name,
database_name,
elapsed_time,
query_text,
status,
wait_info)
as
( select cast(''Lead Blocker: '' + cast(BlitzWho.[session_id] as varchar) as varchar(50)) as [chain],
BlitzWho.ID,
BlitzWho.CheckDate,
BlitzWho.session_id,
BlitzWho.blocking_session_id,
BlitzWho.login_name,
BlitzWho.host_name,
BlitzWho.program_name,
BlitzWho.database_name,
BlitzWho.elapsed_time,
BlitzWho.query_text,
BlitzWho.status,
BlitzWho.wait_info
from ##TABLE## BlitzWho
where [blocking_session_id] is null
and exists (select 1 from ##TABLE## BW where BlitzWho.[CheckDate] = BW.[CheckDate] and BlitzWho.[session_id] = BW.[blocking_session_id])
##WHERE_DATES##
union all
select cast(BlockChain.[Chain] + '' -> '' + cast(BlitzWho.[session_id] as varchar) as varchar(50)) as [chain],
BlitzWho.ID,
BlitzWho.CheckDate,
BlitzWho.session_id,
BlitzWho.blocking_session_id,
BlitzWho.login_name,
BlitzWho.host_name,
BlitzWho.program_name,
BlitzWho.database_name,
BlitzWho.elapsed_time,
BlitzWho.query_text,
BlitzWho.status,
BlitzWho.wait_info
from ##TABLE## BlitzWho
inner join BlockChain on BlitzWho.[CheckDate] = BlockChain.[CheckDate] AND BlitzWho.[blocking_session_id] = BlockChain.[session_id]
)
select * from blockchain
order by [checkdate], [database_name], [chain]';
/* replace all tokens */
set @tsql = replace(@tsql, '##WHERE_DATES##', iif(@now=1, N'', N'AND BlitzWho.[CheckDate] BETWEEN @StartDate AND @EndDate'));
set @tsql = replace(@tsql, '##TABLE##', iif(@now = 1, N'tempdb.dbo.' + @tbl, @blitzwho_table));
/* execute the statement */
if @execute = 1
begin
if @now = 0
exec sp_executesql @stmt = @tsql, @params = N'@StartDate datetime, @EndDate datetime', @StartDate = @startDate, @endDate = @endDate;
if @now = 1
exec sp_executesql @stmt = @tsql;
end
/* do not execute - show the generated tsql */
if @execute = 0
select cast('<?q-- ' + @tsql + ' --?>' as xml) as [tsql];
/* Cleanup */
if @now = 1
begin
set @tsql = N'drop table if exists tempdb.dbo.' + @tbl;
exec(@tsql);
end
end
go
I hope I do not missed anything in the code, I tested it to the best of my abilities but I am still human.
Of course, please test it on a non-production instance first 🙂
SQL-PLAY 