I introduced the SQL Server Blocked Process Report Viewer a couple weeks ago and I realize that I took something for granted. Not everyone has practice watching for Blocked Process Reports and not everyone understands how or when they can use my utility to help troubleshoot concurrency issues.
Steps For Finding Concurrency Problems
Remember: When it comes to concurrency problems, you don’t have to guess what’s wrong!!!
And I explain it flowchart style!
- Checking for LCK_M_XX waits Paul Randal has a great script that interprets the data in the dmv sys.dm_os_wait_stats. I like this script because when all else fails. This script is a great starting point for understanding where the system’s bottlenecks are.
- Using sp_WhoIsActive Adam Machanic wrote Who Is Active as an tricked out version of sp_who and sp_who2. I recommend it because it is a great view into what’s active on your server right now. And that includes blocked processes and other concurrency issues. (i.e. For any acute problem go there. For chronic concurrency problems, come back here).
- Using SQL Trace You might know this as Profiler. Capture a trace with the “Blocked Process Report” event which is located in the Error and Warnings event list. But don’t forget! You first have to decide on what it means for your system to have excessive blocking and configure the blocked process threshold accordingly. I’ve learned very recently that peoples’ ideas of excessive blocking vary a lot. In my own environment, I often look for blocking longer than 10 seconds. Other people use a threshold of 10 minutes!
- Analyzing Traces With Blocked Process Report Viewer This is the tool I wrote that I hope you find useful. Right now it tells you who the lead blocker is. And I hope to expand the features into analysis soon.
- Configuring Server for Event Notifications I’m really not too familiar with this option and don’t use it much. As an alternative you can also use WMI queries and events mapped to a sql agent job (Thanks Vincent Salard, for that tip).
- Using Extended Events Once Denali Arrives Jonathan Kehayias knows extended events backwards and forwards. In his blog post here, he describes how in the next version of SQL Server, the blocked process report will be traceable using extended events.
An Ounce of Prevention
In an extremely timely post Kendra Little writes about understanding locks in It’s a Lock. Following much of the same steps, you can understand what your app is doing beforehand and avoid any blocking problems from the start (e.g. understanding locks held during schema changes).
Next Week
- I’ll be releasing SQL Server Blocked Process Report Viewer. (Right now we’re only in beta and I’m also open to suggestions about a new name for the tool)
- Understanding blocking is the first step. Next week I’ll talk about what happens after analysis. I’ll write about the steps I’ve taken and had success with. after analysis.
Nicely written michael. Excellent collection!
Comment by Muthukkumarn Kaliyamoorthy — May 5, 2011 @ 3:24 am
Thanks Muthukkumaran! Glad you liked it.
Comment by Michael J. Swart — May 5, 2011 @ 8:32 am
When I grow up, I want to blog like you. Great resource!
Comment by Jorge Segarra — May 6, 2011 @ 8:25 am
Thanks SQL Chicken – sorry, Mr. SQL Chicken. Glad you liked it. I’ve been using this technique subconsciously for some years. It was good to get it down on paper, I mean html.
BTW, what do you think of my HTML 1.0 image maps (images with different clickable areas)? I remember learning about this feature in 1995. It’s the first time I ever used it though.
Comment by Michael J. Swart — May 6, 2011 @ 8:47 am
Haha thank you sir. I loved the image maps. I had flashbacks of Netscape and Angelfire pages. In that respect, you should add more animated gifs and invite everyone to join in your WebRing!
Comment by Jorge Segarra — May 6, 2011 @ 9:04 am
This looks great! I can’t wait to try this out.
Comment by Mark Freeman — May 6, 2011 @ 12:28 pm
Thanks Mark, Be sure to tell me how it goes!
Comment by Michael J. Swart — May 6, 2011 @ 3:05 pm
Hi
This looks very good just tried out the beta version and i look forward to the full release
Comment by Simon Day — May 9, 2011 @ 5:45 am
[…] When To Use Blocked Processes Reports – Michael J. Swart (Blog|Twitter) tells us exactly when to make use of his freely available utility. If you’ve not yet taken a look at Michael’s Blocked Process Reports you’re missing out. […]
Pingback by Something for the Weekend – SQL Server Links 15/05/11 | John Sansom - SQL Server DBA in the UK — May 15, 2011 @ 2:28 pm
Handy tool..thx for publishing.
In looking at the source it looks like you are joining on ‘monitorLoop’ in the recursive CTE. Have you successfully executed this against traces captured from SQL 2008? (My examination of those trace files seems to indicate that monitorLoop no longer exists in the 2008 blocked process report schema which should cause the join to fail)
example schemas:
–from SQL 2005
<process id="process2181cef8" taskpriority="0" logused="0……
–from SQL 2008
<process id="process42645048" taskpriority="0" logused="100"……
Comment by jharris93 — August 15, 2011 @ 1:59 pm
Yes!
Good catch jharris, very keen.
But I managed to catch that too before releasing.
Monitorloop was perfect for what I needed. It helps group blocked process reports well. And like you I discovered that it wasn’t used in all traces. So I use this expression to populate the “monitorloop” column
That way, if it’s there it uses it, otherwise it uses the “endtime” of the trace row. If you browse the test cases (at the codeplex site), you can see a couple of traces there that I use for testing. Some with monitorloop, one without. 🙂
Comment by Michael J. Swart — August 15, 2011 @ 2:20 pm
I missed the COALESCE in the source. That’s basically the same approach I’d taken in a homebrew script and was hoping you’d know of a better way…can’t imagine why MS would pull it.
In going back through some of my old trace files, I have found some instances where EndTime rolls over to the next second, which could break some of the block chains. This appeared to be fairly rare and generally only shows up when the server is under heavy blocking stress.
I just found the tool today, will give it a whirl.
Thanks.
Comment by jharris93 — August 15, 2011 @ 3:16 pm
[…] J. Swart brings you a simple, easy-to-use flow chart showing how to figure out if you’ve got locking – and how to react. Don’t just […]
Pingback by 2011 Yearly Link Roundup | Brent Ozar PLF | Brent Ozar PLF — December 26, 2011 @ 11:01 am