That link again…

You can download Michael J. Swart’s Blocked Process Report Viewer from Codeplex here. (This is the same URL from earlier, just giving you a clickable version since it was in the video again)

Transcript

Let’s sum up The Sneaky Blocker situation and look at some suggestions to fix this.

What we found was that…

Frank was running some code that was taking out locks on the Sales.OrderLines table.

This was impacting inserts to the table from customers.

Big picture: we have a change management problem

Now if you were alarmed by this and saying, “Why the heck do we have a table like order data being modified by someone in the middle of the day? Data in that table shouldn’t be incorrect! The nature of this table doesn’t seem like that should be a major problem. And if it was incorrect, it seems like fixing it would be a carefully vetted and tested one time production change, not a surprise to the DBA, right?”

So if your response to this was around: Can we introduce change management? Can we do this for changes just in general? We have a big process problem!

I completely get where you’re coming from.

In terms of the code itself…

I would ask if this wasn’t work that was valid to be done. If it was legit and followed the correct business processes and it was safe, it would probably not be against table like Sales.OrderLines where actual customer orders are coming in in most cases.

Is it something that can be run in non-peak times and let the team, the DBA team know before you run it? Totally valid question.

Or does it need to be run at all before the code is reviewed?

With code review I would look at really that transaction versed the writes that are being done. Do all the writes need to be done in a large transaction with nested cursors in it? Is it valid to do this in smaller units?

You always wanna ask that question and kind of test it carefully to make sure that it is a valid change.

Is it also possible if it can be indexed? That’s a really good question, are there ways to speed up the updates? Or to not to do them at all.

Why does the data need to be modified by what looks like some sort of correction script?

So there’s lots of different ways that you could suggest fixing it.

Summarizing a blocking problem is usually most of the work of getting it fixed

The more practice that you get at summarizing up, here’s the blocking situation and the impact it’s having on a customer, and brainstorming ideas to fix it, the better you’re gonna get at tackling these problems and addressing them. Because often we do have to work with people to make this problems go away.

The Blocked Process Report has helped me a ton over the years

It’s helped me answer big questions about is the blocking that I have usually caused by the same thing or not. It’s given me clues to the queries involved.

It doesn’t always give us the whole query, it sometimes just gives us a chunk, but a chunk is a lot better than nothing and that’s a starter point to find the code that we need to test.

Oftentimes when we have a blocking query and this isn’t really Frank’s Code, I mean Frank’s Code which I wrote is such a mess that really that needs like a serious, serious set of revisions, and probably needs to be lit on fire.

But with other queries, sometimes the blocking query– if we can speed up that query, we can make it take much less time and then it blocks much less.

For example: let’s say that a query that is modifying some data is scanning a large table to find the rows it wants to update. Possibly by tuning some indexes we can make it really quick and seek to the data that it needs to update, thereby taking out fewer locks… thereby causing less blocking.

Sometimes you don’t have to change the TSQL in order to reduce the blocking.

The Blocked Process Report has helped me with vendor code

Sometimes we have vendor applications where we can’t change the code, we can’t change the indexes. Beauty of the Blocked Process Report is it’s a built in tool, it’s well documented by Microsoft, it’s not some crazy script that can’t be understood.

Here is a known documented tool in SQL Server, here’s the output from it. It shows the queries that are being blocked in the application. We need your help making the app faster.

Where do folks normally goof with the Blocked Process Report?

Configuring the Blocked Process Report isn’t hard, but it is that two step process and the issue that I find the most is that folks will run the sp_configure and forget to either set up the trace or turn the trace on until they go wanna go look at the results.

Then they figure out they didn’t actually capture the data.

So remember to do both steps.

I personally don’t like to leave the Blocked Process Report on forever, though, because I don’t want it to fill up a drive and I wanna make sure that any data I’m tracing I’m actively using. So I tend to only configure and turn on the Blocked Process Report when I’m actually going to be reviewing the output on that system. Say in response to a super simple alert I get.

I really enjoy that Blocked Process Report Viewer to simplify looking at the output, looking at every single Blocked Process Report can be a bit tough.

Next up, we’re gonna tackle something that the Blocked Process Report won’t solve for us, we’re gonna need more information in order to solve another tough problem in SQL Server. We’ll get into Deadlocks next.