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.

Solarwinds

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.

SQL SERVER - Find Blocking Using Blocked Process Threshold    blocked-process-1

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.

SQL SERVER - Find Blocking Using Blocked Process Threshold    blocked-process-2

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.

SQL SERVER - Find Blocking Using Blocked Process Threshold    blocked-process-3

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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
Developer’s Life – Every Developer is a Iron Man
Next Post
SQL SERVER – Introduction to SQL Server Management Studio Reports

Related Posts

8 Comments. Leave new

  • Excellent explanation. Thank you!

    Reply
  • You are amazing! Tks for share!

    Reply
  • Daniel Nicoletti
    June 27, 2017 6:32 am

    RE your comment:
    “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.”

    Pretty sure this is incorrect (at least for SQL 2014 my test system), My understanding is its how often the server **checks for blocking,** EG if if its set for 60, it checks every 60 seconds, so if we have 50 second blocking when the 60 second check runs, the alert is raised – regardless of how often the blocking has been occurring for. (IE not longer than the 60 seconds configured)
    see:
    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option?view=sql-server-2017
    specifically:
    Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated.

    To test this, I had the threshold set for 60 seconds, which had an alert run an insert script… and found the script was inserting even when the blocking wasn’t running for longer than 60 seconds.

    EG in one window run the folllowing:
    begin transaction

    select * from [dbo].[TestTable]
    update testtable set duration =1
    waitfor delay ‘0:00:50’
    select * from [dbo].[TestTable]

    rollback

    in the second window run this:
    select * from [dbo].[TestTable]

    and just to confirm i was getting inserts after only 8 seconds of blocking

    Reply
    • Thiago Anitelle
      March 9, 2018 9:35 pm

      The threshold is exactly the minimum time in seconds that a process have to been blocked to raise a block report AND also the interval at which the checks occur.

      So consider that the blocked process report was set to 30 (seconds) at 14:00:10 time.
      Then a process got blocked at 14:00:20.
      The first check for blocking will occur at 14:00:40. On this time, our hipotetical process is being blocked during 20 seconds (less than necessary to generate a raise).
      The second check for blocking will occur at 14:01:10. This time, the blocking process will raise, saying that the process is blocked for 50 seconds.

      Long story short: the waiting time on the blocked process report will always be greater than or equal to the threshold.

      Reply
  • I have a server that does not seem to show some processes as blocked even though when I kill the blocker after waiting 120 mins then my process finishes in a few seconds. What else could be at play here.
    sp_who2 and sp_who3 dont show blocked.

    Does it show blocking for any object indexes, resources, what ?
    all servers are set to 0 here is that normal ?

    Reply
  • Mustafa ELmasry
    December 17, 2019 2:13 am

    Hi Pinal
    thanks for this helpful information now after enabling this option Blocked Process Threshold shall i keep the profiler run daily to be able to capture this events on time . i think this will impact the performance of the production
    i need solution to save this event in table for example to be able to capture it easily is there is any solution

    Reply

Leave a Reply

Menu