BlitzBlockingChain, Getting Blocking Chains from sp_BlitzWho

Photo by Joey Kyber on Pexels.com

I’m going to assume that most people will have at least heard of Brent Ozar’s brilliant First Responder Kit. If you haven’t then you’ve been missing out on some serious (and FREE) SQL troubleshooting goodness and I’d suggest you check it out.

Now I use these scripts all the time when checking out SQL Servers. Sometimes I run them as and when I need them but one of the most handy uses for them is to have them running from an Agent job and logging to tables on a regular basis (I usually run them every 15 minutes but might run them more or less regularly as needed). It’s a great way to catch performance data overtime if you don’t have any other monitoring tools (which more often than not, if I’m looking at an external customer’s SQL server that’s the case), or even if you do, the data these procs catch can be a great supplement to those tools.

By far the most common cause for poor performance that I see is blocking. Unless you’ve got some monitoring in place it’s near impossible to identify the cause of any historical blocking incidents. Luckily, if you’re doing as I do and running the First Responder Kit regularly into tables, you will have a history of running statements thanks to sp_BlitzWho. That data will contain the SPIDs of any blocking processes.

Now, if you’ve ever had to look at that data and tried to figure out what the blocking chain is then you’ll know that it can be a total headache.

For that reason, I wrote BlitzBlockingChain to clearly show the lead blocker and how the chain is hanging together. By default, the proc expects the data to be in a table named BlitzWho. If you’re using a different database name then you will need to alter the proc accordingly (I know, I could alter the proc so it takes a parameter for the table name but I’m being lazy 😉 ).

I’ll say that again incase anyone wasn’t listening and to save myself from a barrage of emails telling me that my proc doesn’t work…

By default, the proc expects the data to be in a table named BlitzWho. If you’re using a different database name then you will need to alter the proc accordingly

Parameters- The proc takes two parameters, @StartDate and @EndDate to specify the date range that you want to return. By default the proc will return any blocks recorded for the past hour.

CREATE PROC BlitzBlockingChain
@StartDate AS DATETIME = NULL,
@EndDate AS DATETIME = NULL

AS

BEGIN

	IF @StartDate IS NULL
	SELECT @StartDate = DATEADD(MINUTE, -60, GETDATE());

	IF @EndDate IS NULL
	SELECT @EndDate = DATEADD(MINUTE, 60, @StartDate);

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 BlitzWho
WHERE blocking_session_id IS NULL
AND EXISTS (SELECT 1 FROM BlitzWho BW WHERE BlitzWho.CheckDate = BW.CheckDate AND BlitzWho.session_id = BW.blocking_session_id)
AND CheckDate BETWEEN @StartDate AND @EndDate

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 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

END

So what’s it look like if we run it, for the sake of this post I’ll just use the default parameters and return any blocks for the past hour.

EXEC BlitzBlockingChain

and the output…..

Dead easy to spot your lead blocker and deal with it as you feel fit.

Thanks for reading and I hope you find this useful.

5 thoughts on “BlitzBlockingChain, Getting Blocking Chains from sp_BlitzWho

Add yours

    1. Yeah sp_whoisactive is amazing and is great when identifying lead blockers that are still active. This script was something I put together to easily see historical blocking chains when investigating a past report of poor performance.

      Like

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: