Michael J. Swart

Takeaway: Skip to the end for a script that reports the lead blocker for a set of Blocked Process Reports. And tune in later this week for a nifty graphing trick for blocked process chains.

On Blocking
Sometimes SQL Server is like a highway, and is prone to traffic jams. Blocking processes (not to be confused with deadlocked processes) are processes that are waiting for resources that are being used by other processes.

Every now and then people talk about blocking chains. These are blocked processes that are waiting in a line. Process A is waiting for Process B. Process B is waiting for Process C. etc… In all cases, you want to find the lead blocker (the head of the chain) and address any issues that that process has. Think of the traffic jam analogy, it’s no good trying to focus on the Toyota that’s waiting patiently; you have to focus on the stalled Honda in the front.

I can think of two good ways to analyze blocking and blocking chains. The first method analyzes the current state of the database. The other focuses on collected trace data:

Method 1: Look at the current state of the database:
Look at the DMVs that are available, or at least to the sys.processes table. These tables provide data about the current state of the database. These tables have been discussed by Tim Chapman here and by Kalen Delaney here (Kalen’s script was written before 2005 but is still awesome). Or in short, try a variety of other solutions found here: http://www.lmgtfy.com/?q=sql+blocking.chain. I’m not going to add another solution with this post.

Method 2: Look at Blocked process report events in a trace:
Another method of analyzing blocking chains looks at data that is retrieved in one or more Blocked processes report. These Blocked process reports are generated and captured using a server-side trace or by using SQL Profiler. The blocked process report holds a ton of useful information. You can find a good example here (courtesy Johnathan Kehayias): http://jmkehayias.blogspot.com/2008/09/monitoring-for-blocked-processes-on-sql.html

Notice that there are two xml nodes under the blocked-process-report tag. These two refer to two processes, a blocked process and a blocking process (always two there are, no more, no less). So what happens if you have a blocking chain involving 12 processes? Then you’ll have 11 events in your trace. 11 blocked process reports with the same timestamp. This can make it tricky to find the lead blocker (the stalled Honda).

One way is to scan through the trace to find the process that’s been blocked the longest (the blocked process report with the largest duration). The blocking process is likely to be the lead blocker.

Or you can use this script:

WITH rankedEvents AS
(
   SELECT ROW_NUMBER() OVER (PARTITION BY EndTime ORDER BY Duration DESC) AS myRank ,
       CAST (TEXTData AS XML) AS TEXTdata
   FROM blocked
   WHERE EventClass = 137
)
SELECT TEXTdata
FROM rankedevents
WHERE myRank = 1

where blocked is the name of the table where SQL trace data is stored.

Later this week I plan to describe a method where I use SQL Server’s Deadlock Graph control to display blocking chains.