Michael J. Swart

May 18, 2011

Okay, You’ve Found Blocking, Now What?

Takeaway: You’ve detected, analyzed and understood blocked processes that have happened on your machine. What can you do to avoid this blocking in the future?

Those who know me well, know I’ve started a project that helps database professionals analyze the blocked process reports that SQL Server produces. I’ve talked about it before:

Barry White endorses my software ... probably

What Next?

But I got an excellent comment from Wallace Houston. He writes:

“What I’m looking for is “what to do” when blocks are already identified.  I want to try to prevent them.  In other words, how to modify my code in such a way as to avoid as many blocks as possible.”

That’s an excellent question. Wallace went on to talk about a “chain reaction” of blocking where everything locked up. Man, I’ve been there and it can be disastrous. It sometimes seems like the only thing to do is to kill the lead blocker’s process. And that feels like giving up.

So after giving it some thought. I came up with this (slightly reworded from my reply to Wallace). These are actions you can take to avoid blocking in the future.

This is Next:

There’s a lot you can do to avoid excessive blocking. I’ve learned very recently that peoples’ ideas of excessive blocking vary a lot. In my environment, I look for blocking longer than 10 seconds. Other people watch for a threshold of 10 minutes!
Either way,
  • If at all possible, tune the lead blocker. If a lead blocker can get take its lock and let it go immediately then there’s no more blocking.
  • Avoid transactions if you don’t need them (but don’t be afraid of them if you do).
  • Pick the nicest isolation level for your transaction. Read committed is nicer than repeatable read is nicer than serializable.
  • If you can get away with it, maybe put the query that’s the lead blocker in a transaction that uses “snapshot isolation”. It uses a bit of tempdb resources, but it’s awesome for concurrency problems.
  • There’s table lock hints (readpast, nolock, holdlock) for more targeted locking, but really you want to understand who’s doing what before you go down that road.
  • I’m not ashamed to say that I’ve used isolation level “read uncommitted” which is equivalent to the NOLOCK table hint. Especially when the caller can tolerate the very very rare cases of inconsistency that might result. NOLOCK is easy and you can’t argue with results – it works – but snapshot isolation is preferred because it is guaranteed to be consistent (if not current).
I remember once I had a table, let’s call it  T whose columns a,b,c,d, and e got queried a lot. But column x got updated a lot and we had blocking issues. The solution was to split the table T into T1 (a,b,c,d,e) and T2(x) with a foreign key from T2 to T1. We then updated queries that used it and got seriously reduced contention.

Progress on my Blocked Process Report Viewer

And for those that are curious, my Blocked Process Report (BPR) Viewer is coming along nicely.

Lately I’ve

  • fixed a few bugs
  • added some documentation (which you’ve already seen if you read this blog).
  • added some SQL Profiler template files and SQL Trace scripts to help collect BPR
  • added a quick and dirty test suite.

It’s pretty much good to go and release as version 1.0. But I still want to add features that makes it easier to analyze.

Stay tuned and happy block busting!

9 Comments »

  1. this is not about your post but about your drawings: please, please tell me that you have some for sale. I need to decorate my office with them.

    Comment by joe positive — May 18, 2011 @ 1:00 pm

  2. Thanks! I don’t have any for sale, but if you’d like, I can send you some for free as high-res (i.e. pdf) version if you want to take on the printing yourself as a one time thing. Just email me which one(s) you’d like.

    Comment by Michael J. Swart — May 18, 2011 @ 1:08 pm

  3. I think the actual quote from Barry White was:

    “AAAWWW Yeaaah! Michael’s BPR viewer is the SEXIEST way to find lead blockers!”

    Oh yeah. Great series on blocking, I have enjoyed it.

    Cheers,
    David

    Comment by David Nelles — May 19, 2011 @ 8:35 am

  4. Agree with Joe–your graphics are amazing! They actually (along with kendra’s) inspired me to pressure our resident manga fan into doing a cartoon series for the blog

    Comment by Claire — May 19, 2011 @ 10:10 am

  5. I admire the spurning of Comic Sans despite a legitimate use case.

    Comment by David Swart — May 19, 2011 @ 4:15 pm

  6. Thanks Dave, 100% deliberate.

    Comment by Michael J. Swart — May 19, 2011 @ 4:26 pm

  7. [...] Okay, You’ve Found Blocking, Now What? – This week Michael J. Swart (Blog|Twitter) looks at what you can do to minimise, if not eliminate excessive blocking. [...]

    Pingback by Something for the Weekend – SQL Server Links 20/05/11 | John Sansom - SQL Server DBA in the UK — May 20, 2011 @ 5:41 am

  8. Dave, The font is by a guy (or company) called Jaws Lafayette called “Jaws Comic Pro”. I found it on http://Dafont.com which is a great place to get free fonts. (It doesn’t seem to be there any more but a google search gives a lot more places for it)

    Claire, Promise you’ll let me know when that cartoon blog series come out?

    Comment by Michael J. Swart — May 20, 2011 @ 12:57 pm

  9. [...] processes that have happened on your machine. What can you do to avoid this blocking in the future? Michael Swart shares the [...]

    Pingback by Log Buffer #221, A Carnival of the Vanities for DBAs | The Pythian Blog — May 21, 2011 @ 11:05 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress