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

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.

blocked process 1 SQL SERVER   Find Blocking Using Blocked Process Threshold   

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.

blocked process 2 SQL SERVER   Find Blocking Using Blocked Process Threshold   

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.

blocked process 3 SQL SERVER   Find Blocking Using Blocked Process Threshold   

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)

SQL SERVER – Activity Monitor to Identify Blocking – Find Expensive Queries

Recently when I was busy working on my next course for Pluralsight, one of my friend pinged me for some help over messenger. And the conversation went on a different spin, here is a gist of what we talked and I felt this was worth a share

 Me: Hi buddy, what’s up?

Jim: Dave, I have an unique problem and thought you can help me.

Me: Sure, let me know how I can be of help.

Jim: I am going on a quick consulting assignment tomorrow and I am wanting some helping hand.

Me: Hmmm Sure, let me know what the problem is?

Jim: This customer has a lot of deadlock and blocking problem. And I wanted to know if you know any easy steps to find blocking queries in a real time system?

Me: Hold on let me check.

Jim: Sure.

Me: Ahha Did you check my script available over my blog?

Jim: Awesome, Pinal I think you saved me a day. And apologize for not doing a quick search over your blog. But Pinal, I have one doubt.

Me: On the script?

Jim: No Pinal, the script was written 4 years back and is still a gold mine to help me tomorrow. I am just curious, have you ever explored any alternatives? I am sure your exploration wouldn’t have stopped.

Me: Well Jim, there are a number of other scripts but the one of the hidden gem I don’t think I have written over this blog is around Activity Monitor.

Jim: Oh is it? Can you show me what you are saying?

We get over a screen sharing and I show couple of neat tricks available with Activity Monitor. Let me take a moment to pen down what I showed Jim yesterday.

The quickest way to initialize the Activity monitor is to use the shortcut (CTRL+ALT+A) inside Management studio or feel free to read this blog on where to find the Activity Monitor.

Activity monitor to identify Blocking

The activity monitor has a number of tabs and the tab under question is the “Processes” tab. If you have a blocking scenario then activity monitor can be really a quick solution.

In the image below, we can see SPID of 51 is head of the Blocker and SPID of 55 is waiting for locks to be released by 51. We can also find the wait time, resource wait type and more from this screen.

01 activity monitor SQL SERVER   Activity Monitor to Identify Blocking   Find Expensive Queries

As a DBA if you want to kill SPID 51, we can quickly do that from Activity monitor using the right click menu as:

02 activity monitor SQL SERVER   Activity Monitor to Identify Blocking   Find Expensive Queries

When I showed this to Jim, he was super excited to see and was all awe of what we can do just with SQL Server Management Studio. So Jim quizzed me to show him one more tip using Activity monitor as a challenge. Since he was going for a performance troubleshooting exercise, I thought of sharing one more cooler tip using the Activity monitor.

Activity monitor to find execution plans of expensive queries

I asked Jim how he would find out if there are performance problems for a given query. The obvious answer as an expert was to use an execution plan. The problem of such an approach is that when the query executed with parameters, then when we pull them out from Profiler or other mechanisms based on Reads, Write or CPU usage – the parameters need to be replaced, executed and then we need to view the execution plan.

Interestingly, Activity monitor has an interesting tab called as “Recent Expensive Queries”. These are queries based on Reads, CPU, writes etc. Once you get into this screen, right click the query under question and select “Show Execution Plan”.

03 activity monitor SQL SERVER   Activity Monitor to Identify Blocking   Find Expensive Queries

End of the screen sharing exercise and about 10 mins of quick talk with me, Jim was super excited and said he will ping me once the assignment is over and how he used some of the tips I shared with him.

Activity Monitor is an awesome hidden gem inside SQL Server Management studio that many fail to use for quick monitoring. Hope this blog post inspired some of you to use it in your environments in the future. Do let me know if you ever used Activity monitor in these ways.

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

SQL SERVER – 2008 – Location of Activity Monitor – Where is SQL Serve Activity Monitor Located

I received question from Aloke Sinha after reading my article SQL SERVER – 2008 – Activity Monitor is Empty – Fix Activity Monitor for All Users.

Hello Pinalbhai,

Thank you for your post about activity monitor, but I can not find activity monitor under Menu — Tools. How to activate it?

[Other unrelated information removed]

Take care,
Aloke Sinha

The reason I decided to write about this subject is because I totally understand why Aloke is confused here. Activity Monitor can not be activated from any menu from top menu bar. There are two different methods to activate Activity Monitors.

From Standard Toolbar (Standard Toolbar can be made visible from here : Menu >> View >> Toolbar >> Standard) and from “Right Click” of SQL Server Node.

avmonact SQL SERVER   2008   Location of Activity Monitor   Where is SQL Serve Activity Monitor Located

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

SQL SERVER – 2008 – Activity Monitor is Empty – Fix Activity Monitor for All Users

This article is outcome of the technical discussion of activity monitor and its behavior with my friend and SQL Expert Tejas Shah. Tejas told me that he does not like to re-write content from MSDN but rather prefer to write real life scenarios, as that prepares him to become better SQL Expert. While discussing about Activity Monitor he informed that activity monitor throws an error when there is permissions issue. He has even blogged about how to give permissions to user to launch activity monitor on his blog . Tejas asked me to write on the same subject for SQL Server 2008. Here is the article covering the discussion I had with Tejas.

I have user called ‘ActivityUser’ when turned on Activity Monitor (while logging in with user ActivityUser’) it does not have show anything in Activity Monitor.

av1 s SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

The issue here is permissions issue. If user ActivityUser is given all the necessary permission it will start showing up data in Activity Monitor. Activity Monitor is new tool in SQL Server which displays activity in five sections. 1) Overview, 2) Processes, 3) Resources Waits, 4) Data File I/O, 5) Recent Expensive Queries. It is one of the new and very useful tool introduced by SQL Server.

Activity Monitor captures all the information at server level. For the same reason we need to give “View Server State” permission to user name to view data of Activity Monitor.We can give permission either using T-SQL or using SSMS.

T-SQL to give permission to user to view Activity Monitor:

av2 SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

SSMS to give permission to user to view Activity Monitor:

av3 SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

Once permissions is given to user, it displays the data in Activity Monitor. Click on images to enlarge the images.

av4 s SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

Additionally, note that if you are user belonging to sysadmin role, you can always see all the data in Activity Monitor without additional permissions.

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