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
(
SELECTCAST('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
SELECTCAST(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.