Solving concurrency problems are a large part of troubleshooting. Often solutions include tuning the blockers to minimize the blocked time or tweaking locks and isolation levels to make processes play nicely with each other. But to dig into the problem, you have to understand the blocking chain.
If you’re troubleshooting a concurrency problem that’s happening on your server right now then you can get information from the DMVs or even better, by using Adam Machanic’s Who Is Active stored procedure.
But what if the excessive blocking behavior is intermittent? Then the best strategy is to monitor the server and try to capture a SQL trace that includes the “blocked process report” event. I’ve had a lot of luck with that event, it can really tell you a story about excessive blocking. But I find that interpreting the trace can be tricky, especially when there’s a large blocking chain. Sorting through hundreds of events to find the lead blocker is not fun.
New and Free: sp_blocked_process_report_viewer
So I wrote a script! And I stuffed it in a stored procedure! Here’s the syntax (BOL-Style):
sp_blocked_process_report_viewer [@Trace = ] 'TraceFileOrTable' [ , [ @Type = ] 'TraceType' ]
[@Trace = ] ‘TraceFileOrTable’
- Is the name of the trace table or trace file that holds the blocked process reports
[@Trace = ] ‘TraceType’
- Is the type of file referenced by TraceFileOrTable. Values can be TABLE, FILE or XMLFILE. The default is FILE
Download it Now!
Go to the http://sqlblockedprocesses.codeplex.com site and download it. Once you’re there, click on the big green download button (as shown to the right) and you’ll have the stored procedure!
Here’s a sample output. It shows clearly who the lead blocker is:
I’m Promoting This Script to a Project
Although, you still have to know how to read a blocked process report, this utility makes the structure of the blocking chain clear. I find this script useful for my own purposes. In fact I like it enough that I’m going to maintain it on codeplex as: SQL Server Blocked Process Report Viewer
Let Me Know How It Goes
Run the script! Use it! Tell your friends. Tell me what you think of it (for once in my life, I’m seeking out criticism).
Going forward, I do have some plans for the script. There’s a number of things I eventually want to do with it:
- Add error handling
- Really make the sproc perform well (it’s already decent).
- Develop a test suite (sample traces that exercise the procedure)
- There’s an opportunity to look up object names, index names and sql text based on object ids if the traces belong to the local server.
- A SQL Server Management Studio plugin. A treeview control would really be useful here. (This might be easier after Denali comes out)
I plan to do the work, but if you’re really keen and you want to pitch in, you’re welcome to. If you see any errors you can
- let me know at the SQL Server Blocked Process Report Viewer codeplex site,
- mail me
- or simply leave a comment here