In my previous blog, I discussed how to use the Activity Monitor in SQL Server to identify and resolve blocking issues. However, my friend Jim, who is currently on a consulting assignment, reached out to me with a different problem. He needed a way to proactively monitor and identify problems in a finance-based SQL Server system that runs round the clock.

Jim wanted a mechanism that would allow him to identify issues before they become critical, rather than relying on real-time monitoring using the Activity Monitor window in SQL Server Management Studio (SSMS). After some discussion, I introduced Jim to the concept of “Blocked Process Threshold,” a feature that is often overlooked but can be incredibly useful in such scenarios.

Configuration Steps

The Blocked Process Threshold feature was introduced with SQL Server 2008. Its purpose is to identify queries that are waiting for a resource for too long and provide a mechanism for capturing these waiting queries. Here are the steps to configure it:

1. Enable the server setting by running the following command:

sp_configure 'blocked process threshold', 10;

This command sets the threshold to 10 seconds, meaning that if a query waits for more than 10 seconds, an event will be raised.

2. Configure Profiler to capture the Blocked Process report:

Open SQL Server Profiler, provide the necessary credentials to connect to the server, and navigate to the Events Selection tab. Under “Errors and Warnings,” select “Blocked Process report.” This will ensure that the profiler captures the events triggered by the Blocked Process Threshold.

3. Run the profiler trace:

Once the profiler is configured, click “Run” to start capturing the events. Based on the threshold configuration, if any query waits for a resource for more than 10 seconds, an event will be raised and captured in the profiler trace.

Note: It is also possible to run the profiler trace as a server-side trace, but that topic will be covered in a separate blog post.

Resetting the Configuration

If you want to reset the Blocked Process Threshold configuration and stop generating the Blocked Process report, you can set the server setting to 0 by running the following command:

sp_configure 'blocked process threshold', 0;

This will disable the feature and prevent any further events from being raised.

I hope this feature proves to be as useful for you as it has been for me and many other developers and administrators. Proactively monitoring SQL Server can help identify and resolve issues before they impact the system’s performance and stability.

Let me know if you have ever used this feature in your environment and how handy it has been for your team.