-2

I am getting alerts stating:

The SQL Server Performance counter 'Process Blocked' (Instance N/A) of object 'General Statistics' is now above the above threshold of 40 (the current Value is 41)"

What do I need to do to address this issue?

closed as too broad by Randi Vertongen, Josh Darnell, Erik Darling, John Eisbrener, Max Vernon Mar 20 at 14:55

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • How to solve this issue or Is there is any Tool to Monitor – sai baba Mar 20 at 12:46
  • I know this is "too broad," but it is a common question and we need an answer that provides general guidance due to the frequency. I don't care of its my answer or not--we need something to help people that have this question. If we do have one, I couldn't find it. – Tony Hinkle Mar 20 at 15:55
  • Thank You For Your Valuable Response – sai baba Mar 21 at 5:07
3

First you need to determine whether the blocking is a problem or not. Some applications are going to have numerous connections running INSERTS, UPDATES, or DELETES as fast as they can. This is by design (perhaps not good design), and as long as it's something that users are not impacted by, then you may see periods where there are a lot of blocked processes, but there is nothing you can do about it unless you can get the vendor to rewrite the application.

If the blocking is causing a problem, you first need to find out more about the blocking. If you know when it is occurring and can run a query at the time, the easiest way (in my opinion) to learn more about what's happening is to run sp_whoisactive (download from http://whoisactive.com). This will show you what processes are blocking, blocked, the queries involved, and lots more. You can also set sp_whoisactive up to log to a table, so you can have a SQL Agent job run it every few minutes and capture the needed information. In general, just Google "sql server how to find blocking process" and you'll find tons of help.

What you do next depends on what you learn about the blocking. It's impossible to provide specific instructions for every situation, but these are some of the most common solutions:

  • As mentioned above, if it's not causing a problem for any users and only occurs a few minutes a day, you may not need to do anything because there's nothing broken. If the application has 10 connections that are deleting items from a table as fast as they can, then you'll have some blocking while this is occurring because a delete locks the table and the other 9 connections are blocked, just waiting in line.

  • If there are some poorly optimized queries that are blocking, the solution may be as simple as adding an index or rewriting a bit of a stored procedure so that it will finish much faster. You'll still have a bit of blocking, but far less if the unoptimized query or code can run a lot faster.

  • Enabling read committed snapshot isolation (RCSI) on the database can solve a lot of blocking issues. You'll first need to do some research/testing to make sure that the application supports it as it can cause problems in some situations. RCSI uses row versioning so that readers don't block writers, and vice versa. So this may be a solution if you have SELECTs blocking other operations and vice versa. However, it won't be a solution if you have DELETEs blocking INSERTs, for example.

  • If you're seeing a lot of physical reads for SELECT queries that are blocking, you will likely benefit from adding memory to the server to reduce physical reads. Again, you'll still have blocking, but it will be less intense if the queries can finish faster.

  • For the sake of completeness, I'll add that you may need to get faster storage and/or CPUs, depending on which one is a bottleneck. It is unlikely that you'll need to resort to getting better or more hardware, but at extreme loads this becomes reality.

There are numerous open source and third-party SQL Server monitoring utilities that will provide more information about what queries are blocking. I think it's safe to say that the majority of the monitoring tools will have this functionality. We typically don't make a list of such things on StackExchange because the list is quickly outdated.

See also How to identify blocking in SQL Server by Nitansh Agarwal.

  • Thank You For Your Valuable Response. For an Process Block Issue to Fine Tune the Stored Procedure , How to Identify,Were its Happening and all or How to Add Procedure Name in DB Mail to Sort it out – sai baba Mar 21 at 5:12
  • Getting it in an email is not really a great idea as you may get thousands of emails, and you can't easily sort them to find which ones are duplicates. I would recommend having the job that sends the email to run sp_whoisactive and log it to a table. – Tony Hinkle Mar 21 at 14:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.