Troubleshoot and Capture Blocking on a SQL database

Prerequisites to capture blocking chains is to install Brent Ozar’s First Responder Kit. The tool kit is an excellent resource for SQL troubleshooting.

The below mentioned process uses these scripts when checking out SQL Servers. Sometimes we can run them as needed and you can even have them running from an Agent job and logging to tables on a regular basis (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 over time if you don’t have any other monitoring tools, 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 is blocking. Unless you’ve got some monitoring in place it’s near impossible to identify the cause of any historical blocking incidents. If you have scheduled SQL agent job and running the First responder kit regularly into tables, you will have a history of running statement 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, we can use BlitzBlocking Chain (courtesy of David Fowler) 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.

Parameters – The proc takes two parameteres, @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 OR ALTER 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 we’ll just use the default parameters and return any blocks for the past hour.

EXEC BlitzBlockingChain

and the output: