SQL SERVER – Find Blocking Using Blocked Process Threshold  

In my previous blog I was talking about my friend Jim who was on a consulting assignment and was asking me for some help around blocking. Though the tip mentioned was of help, Jim did reach out to me the day after and had more queries. Some of these sound simple, but you will be surprised how some of the out-of-box features are so hidden within the product of SQL Server. Let me give you a glimpse of our chat and what we ended up as technology discussion.

Jim: Hi Dave.

Me: Hey Jim, how are you? Have you started your assignment?

Jim:  I am doing great and the tips you shared last time were quite handy. But there is a problem in the approach though.

Me: You must be kidding, Activity Monitor didn’t work?

Jim: No, you got me wrong Dave. It is totally a different problem.

Me: Well, I am all ears and listening – go on.

Jim: The solution of Activity Monitor was awesome, but this finance based system of SQL Server is running round the clock. And there are problems that surface once in a while during the night.

Me: Ok, so what is the problem?

Jim: I need a mechanism to monitor or identify problems proactively and not in real time as-in using an Activity Monitor window in SSMS.

Me: Hmmm interesting, but the solution is easy then.

Jim: Dave, you must be kidding. Just to make sure, I was genuinely searching your blog this time before pinging you.

Me: Haha – you really took it to the heart buddy. Give me a second.

Jim: Sure, take your time – “I am waiting”.

Me: In a minute – Thanks to you Jim. For a matter of fact, I haven’t written on this topic though I have a demo that I show during my sessions.

Jim: Is it?

Me: Now that you brought this thing up, let me show you the steps. It is called “Blocked Process Threshold”.

It takes less than 5 minutes I showed the demo and we were done while Jim went back happy for a good night’s sleep. Below are the steps I used in my demo to
Jim:

Configuration Steps

The concept of Blocked Process Threshold was introduced with SQL Server 2008. The real reason is to identify if a query is waiting for a resource too long and as an administrator we want a mechanism to capture such waiting queries. The first step to configuration is to enable the server settings.

What does the command say?

sp_configure'blocked process threshold',10;

The above command means when a query waits for more than 10 seconds, raise an event. As simple as that – Now this event can be captured using profile and we can take action accordingly.

Configure Profiler

The first step is to configure profiles events. Open Profiler, give the credentials to connect and go to the Events Selection Tab. Under “Errors and Warnings”, select “Blocked Process report”. This is shown in the diagram below.

Now select “Run” and we are all set. Now based on the configuration we made, if any query is waiting for a resource for more than 10 seconds then an event is raised and this is captured in our profiler trace. We can also run it like a server side trace, but that will be for a different blog post. Below we can see a “Block process report” being generated and it will generate an event every 10 seconds until the query goes succeeds.

Reset script

If you like to reset this value and not generate the Blocked Process report, then set the server setting to 0. The command for the same will be:

sp_configure'blocked process threshold',0;

Let me know if you have ever used this feature in your environment and how handy this has been to your developers and administrators.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s