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):
Syntax
sp_blocked_process_report_viewer [@Trace = ] 'TraceFileOrTable' [ , [ @Type = ] 'TraceType' ] |
Arguments
[@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 https://github.com/mjswart/sqlblockedprocesses 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:
Nicely organized, at least better than usual
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 github 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 github site,
- mail me
- or simply leave a comment here
This just creates more work for me. Now instead of sending you my blocked process reports and magically getting an answer, I have no excuse not to do the grunt work myself!
I kid. Can’t wait to try this out 🙂
Comment by Aaron Cooper — April 20, 2011 @ 7:52 pm
Hahaha. I know Aaron, You’re pretty much exactly the audience I had in mind for this tool. You’ll get to practice reading blocked process reports. There’s a knack to it, but hopefully I can (eventually) make that easier too.
Comment by Michael J. Swart — April 20, 2011 @ 9:10 pm
Hi Michael,
Personally I use Alerts & WMI query mapped on a job which processes and sends a mail.
I must admit this code allows a more abstract view of what happened. Good complementary solution for cold investiguation !
Thx for sharing !
Regards,
Vincent.
Comment by Vincent Salard — April 22, 2011 @ 10:06 am
[…] A New Way to Examine Blocked Process Reports – Giving back to the community in style, this week Michael J. Swart (Blog|Twitter) shares with us an improved method for troubleshooting blocked processes. […]
Pingback by Something for the Weekend – SQL Server Links 22/04/11 | John Sansom - SQL Server DBA in the UK — April 22, 2011 @ 12:38 pm
@Vincent Thanks! I hope people find it useful.
I’m curious about your solution (and WMI events in general). WMI has always seemed more complicated than it probably is (Maybe it’s Microsoft’s SQL vs. WMI docs that do it: http://msdn.microsoft.com/en-us/library/ms180560.aspx)
Tell me how you got started with WMI (a course, or docs, or a blog somewhere?) and how useful do you find it?
Comment by Michael J. Swart — April 22, 2011 @ 7:38 pm
[…] concurrency problems are a large part of troubleshooting. Michael J. Swart presents a new way to examine blocked process […]
Pingback by Log Buffer #217, A Carnival of the Vanities for DBAs | The Pythian Blog — April 25, 2011 @ 12:52 am
Michael, looks like good stuff and I’m in need of it. I have some blocking/blocked processes already identified. My problem is, I don’t really know what to do about it. I have a traffic control problem. Can you point me in a direction which will guide me to what I should be doing in my code to allow the big trucks to go thru and make make the Volkswagens yield?
Comment by Wallace Houston — April 25, 2011 @ 9:26 am
Awesome question Wallace, Have you tried putting Cow Catchers on all the big trucks? That should do it.
(Sorry Wallace, I have a hard time telling if you’re kidding. The processes I talk about are SQL Server database processes.)
Comment by Michael J. Swart — April 25, 2011 @ 4:47 pm
You say @Trace default is FILE, the default in the current download is TABLE
You say try to capture a SQL trace that includes the “blocked process report” event.
Never tried that before, you might include “Located in the Error and Warnings event list”
Since I never tried that before, what triggers it? My favorite blocking technique is to create a ## table, start a transaction, insert a row and in a different thread update the row just inserted. This event isn’t triggered as long as the command is blocked.
I use sp_who2 to figure out what is blocking what. Here is an example script:
–create a table of the output to analyse it
declare @tbl table(spid int primary key, status varchar(500), login varchar(500), host varchar(500),
BlkBy varchar(500), DBName varchar(500), Cmd varchar(500), CPU int, diskIO int, lastBat varchar(500), ProgramNm varchar(500), spid2 int, requestID int)
insert into @tbl
exec sp_who2
–Here are multiple rows combined into one row with quick hardcoded portion that could be generated from YEAR function and an example where you can compare dates that are older than a certain period. (I usually use 5 minutes.)
select a.spid, b.spid,
cast (substring(a.lastBat,1,5) + ‘/2011’ + substring(a.lastBat,6,25) as datetime),GETDATE() from @tbl a
join @tbl b on a.BlkBy != ‘ .’ and b.spid=a.BlkBy
–This selects everything on separate lines for the blocked and blocking spids.
select * from @tbl a
where a.BlkBy != ‘ .’ or spid in (select BlkBy from @tbl where BlkBy != ‘ .’)
Comment by Ken — April 27, 2011 @ 12:38 am
Thanks for the great feedback Ken! It’s much appreciated.
You’re comments are on the money and I’ll incorporate them.
It also reminds me that I should expand the documentation a little bit to include how to capture blocked process reports because these reports are disabled by default.
http://msdn.microsoft.com/en-us/library/ms181150.aspx describes the config setting.
You’re method of watching blocked process reports are useful when blocking is happening right now (similar to Who Is Active which is like sp_who on steroids). I’ll have to try it out.
So if your method captures acute complaints my method monitors chronic complaints, stuff you can’t catch as it’s happening. “Chronic” and “acute” are the wrong words to use, but the best ones I could come up with to demonstrate the difference between the two.
But thanks for stopping by Ken, I hope you’ll visit often.
Comment by Michael J. Swart — April 27, 2011 @ 8:28 am
Hi Michael,
I use WMI on SQL Server for ages now so I could not really remember where I took the first inspiration (probably a book).
I think few people are using it and it’s sad because it’s a gem !
I’m using it for Blocked process, deadlocks, and many live events related to critical errors.
There is no hard thing in using it. Just need to implement an alert, map it to a job, build a stored procedure to parse the result of a WMI query and send the well construct HTML into a mail and we’re done.
Comment by Vincent Salard — April 28, 2011 @ 3:26 am
[…] original blog post that explains how to use […]
Pingback by Okay, You've Found Blocking, Now What? | Michael J. Swart — May 18, 2011 @ 12:05 pm
Great SP !! Thank you Michael!
Comment by roman — August 3, 2011 @ 9:31 am
Glad to hear it Roman, Thanks for the feedback.
Comment by Michael J. Swart — August 3, 2011 @ 10:29 am
Hi Mike,
Do we need to capture any specific events in trace? I’m continously getting File ‘blabla.trc’ either doesn’t exist or it is not a recognizable trace file.
FYI my trace file is 450 MB in size and I’m able to open from profiler without any issues. I don’t think you are limiting anything by size! Also, i deployed the Stored Proc in our DBA Maintenance Database, not in the Master database.
Could you pls. help me why i’m getting this error msg?
Comment by sreekanth — September 26, 2011 @ 12:23 pm
Hi sreekanth,
I’ll try to address some things that maybe I didn’t make clear enough.
Comment by Michael J. Swart — September 26, 2011 @ 1:34 pm
Thank you for sharing the procedure. It helped a lot when I was analyzing a blocking problem on one of my servers.
Adi
Comment by Adi — November 13, 2011 @ 2:58 am
That’s so good to hear Adi. I’ve come to realize that feedback like yours is why I enjoy doing this stuff.
Michael
Comment by Michael J. Swart — November 13, 2011 @ 4:26 pm
Great Article Michael.
I was redirected to here by Paul Randal when I was watching one of the chapter on waits in pluralsight.
Initially I thought its just copy paste, but then understand it how to make it working.
So for new comers who going to use this tool, few time saving steps :-
1) This SP use the trace which you will saving as file or table.
2) You need to run server side trace or profile to caputre only and only “Blocked Process Report” for your instance.
3) Before starting the trace or profiler, just ensure you mentioning where to save trace (file or table). And configure accordingly.
4) use sp_configure and change the default value of “blocked process threshold” to 10 (which is in second, by default its some 86400). With default configuration, you can understand how long you have to wait for first occurence of deadlock.
5) Now all the things in place and you can start your server side trace or profiler.
6) Create deadlock/locked scenario in your sql
7) after 10 secs, the deadlock/locked information will start popping in file/table. You can stop the trace now or whenever you have enough data for deadlock/locked occurence.
8) Assuming you already created the SP “sp_blocked_process_report_viewer”. If not, run the script and create SP in your preferred database. Now,
8.1) if you saving your trace in file(in step 3 above) then you need to run as :-
sp_blocked_process_report_viewer @Trace = ‘DRIVE\COMPLETE FOLDER LOCATION\FILENAME.TRC’, @Type = ‘File’
For ex :- sp_blocked_process_report_viewer @Trace = ‘c:\temp\Tracing.trc’, @Type = ‘File’
8.2) if you saving your trace in Table(in step 3 above) then you need to run as :-
sp_blocked_process_report_viewer @Trace = ‘tablename’ , @Type = ‘TABLE’
sp_blocked_process_report_viewer @Trace = ‘TRACING’ , @Type = ‘TABLE’
PS:-
These steps are written considering you are trying to find the quick way for initial configuration required to test this tool. And you using non-prod box for testing purpose.
Comment by ashish — February 23, 2013 @ 2:55 am
Thanks Michael. I am in the process of creating this SP.
Thanks to Ashish for giving details of steps to use this SP. It is really a time saver.
Comment by Raj — February 25, 2014 @ 5:44 pm
Hi Micheal,
This is great. I’m actually capturing the blocked process report using event notifications and writing the blocked process report to a table.
I’m currently attempting to adapt some of your code to just directly read the out put of my table and shred the xml.
I’d be happy to post the result here or send it to you when I am done.
Cheers
Martin.
Comment by Martin Catherall — March 6, 2014 @ 8:02 pm
Yeah send it along, it sounds useful. When I wrote this tool, extended events (for blocked processes) weren’t yet released in any version of SQL Server.
Comment by Michael J. Swart — March 6, 2014 @ 8:04 pm
Hey Martin, if you want you can check below url from Jonathan
http://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-21-of-31-the-future-tracking-blocking-in-denali/
Please post your result.
Comment by Ashish — March 7, 2014 @ 1:40 am
I was directed to this via http://www.brentozar.com/sql/locking-and-blocking-in-sql-server/…
I downloaded the code via http://sqlblockedprocesses.codeplex.com/ — and got version Last edited Apr 18, 2011 at 3:15 PM by mjswart, version 2
When i run the spoc against a table I loaded via profiler using a tarce file re blocked procceses, I get an error message re invalid column ‘endTime’…
So — any ideas? I ran the profiler using the file supplied by the initial page…I’ll be asking them as well…
Thx
Comment by 954Mikem — July 2, 2014 @ 3:53 pm
Hi 954Mikem,
As a guess, it looks like
needs to be included in the traced columns.
If I have time, I’ll document what columns are required for the blocked process report event. Or I’ll improve error messaging (or both).
Thanks so much for the feedback
Michael
Comment by Michael J. Swart — July 2, 2014 @ 4:06 pm
I receive this error
Violation of PRIMARY KEY constraint ‘PK__#Reports__5E3C2CB26D0EC9A2’. Cannot insert duplicate key in object ‘dbo.#ReportsXML’. The duplicate key value is (unknown, 53, 0).
Comment by Kez — August 3, 2014 @ 11:40 pm
Thanks for the great stored procedure. It has been helping our team to find out the blocking query.
I am getting below error, when I try to open the trace file. Can you please modify the stored procedure to handle this scenario.
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column ‘blocking_ecid’, table ‘tempdb.dbo.#ReportsXML_________________________________________________________________________________________________________0000000000FD’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Comment by Venkataraman — November 6, 2014 @ 6:47 am
Hi there, the fastest way to get a fix is to mail me a trace file (mjswart@gmail.com). Alternatively, you can wait for me to debug this issue based on the error code but I don’t have an ETA on when that will get done.
Comment by Michael J. Swart — November 9, 2014 @ 3:22 pm
H Michael,
I have mailed the trace file for your debugging to your gmail address.
Thanks for your quick response in this regard. God bless you for your services to the SQL Server community.
Comment by Venkataraman — November 10, 2014 @ 12:12 am
This is fantastic!
Thank You!!
Comment by Richard Gruber — May 21, 2015 @ 9:35 am
I’ve updated the BPR Viewer with a new version that addresses the bugs listed in the previous comments.
Go to the download page and download the new version.
Comment by Michael J. Swart — May 29, 2015 @ 11:40 am
Thank you so much – this saved me a few days worth of analysis!
Comment by Hien Dang — June 17, 2015 @ 12:11 am
Note: the parameters as stated above are incorrect, probably because they were copied over verbatim from the codeplex examples. The parameters are @Source (i.e. the name of the object, table, file etc) and @Type (i.e. table, xml file, etc):
exec sp_blocked_process_report_viewer @source = ‘TraceResultsDatabaseName.dbo.TraceTableName’ , @Type = ‘TABLE’
Comment by Glen Moffitt — June 23, 2016 @ 10:17 am
I was directed to this via http://www.brentozar.com/sql/locking-and-blocking-in-sql-server/…
So, what do you think to include Deadlock process inside the sp ?
Regards,
Eric
Comment by Eric — August 23, 2016 @ 8:22 am
Hi Eric, you’re wondering about deadlock graphs and whether this tool can report them as well.
I think that the blocked process report and the deadlock graph are very very similar, but there are some differences.
The blocked process report viewer collects blocked process reports and groups them into “episodes”. The deadlock graph is already a collection of episodes.
I’ve updated the stored procedure since. It now can consume reports from an extended events session: https://michaeljswart.com/2016/02/look-at-blocked-process-reports-collected-with-extended-events/
In that article, I mention how to collect blocked process reports and deadlock graphs. I follow Jeremiah Peschka’s script at https://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/
Comment by Michael J. Swart — August 23, 2016 @ 9:13 am
Hi Michael,
I was directed to your solution from a Brent Ozar blog post. (Locking and Blocking in SQL Server) This is great stuff, but I can’t seem to find a way to direct your stored procedure to an existing Extended Event file. It seems I can only open Profiler trace files. Am I doing something wrong, or is this a limitation of the procedure?
It does work for Extended Event sessions that are currently running, but I have some existing.xel files I’d like to analyze with your procedure.
Thanks for your great work and help!
Comment by Sky — January 28, 2019 @ 11:44 am
Thanks for your comment Sky,
The options for this procedure are:
FILE: a .trc file generated with profiler
TABLE: a table, again filled with profiler data
XMLFILE: Also profiler data, but in xml format
XESESSION: the name of an existing extended events session
I don’t think I ever implemented a path to an XEL file. That would be a great addition to the script. But unfortunately, there is no way using the script as I’ve written it.
Comment by Michael J. Swart — February 1, 2019 @ 2:49 pm
[…] Neste exemplo, vamos usar o visualizador de relatĂłrio de processo bloqueado gratuito de Michael J Swart . […]
Pingback by O que Ă© SQL Server Lock e Block? - Blog Tripletech — August 5, 2021 @ 8:01 am