Blocked Process Threshold

The blocked process threshold is a server configuration option in SQL Server that defines the duration (in seconds) a task must be blocked before generating a blocked process report. These reports help identify and diagnose tasks that are blocked by other processes, allowing for better management of performance bottlenecks.

Why Blocked Process Threshold Is Important

  • Identifying Blocked Tasks: Setting the threshold allows SQL Server to generate reports when a task is blocked for a specified amount of time. These reports can help track down processes causing delays.
  • Performance Monitoring: Properly configured, this setting provides insight into potential blocking issues before they escalate into major performance problems.

Suggested Action

The recommended threshold is 20 seconds. This means that if a task is blocked for 20 seconds or longer, a blocked process report will be generated. Setting this value too low can cause excessive reporting and decreased performance. A reasonable range for most systems is between 5 and 20 seconds.

How To Change the Blocked Process Threshold

Use the following SQL command to configure the blocked process threshold:

EXEC sys.sp_configure @configname = N'show advanced options', @configvalue = N'1';
RECONFIGURE;
GO
EXEC sp_configure 'blocked process threshold', 20;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure @configname = N'show advanced options', @configvalue = N'0';
RECONFIGURE;
GO

How Aireforge Detects Blocked Process Threshold Settings

Aireforge checks the blocked process threshold setting and flags configurations where the threshold is either too low (less than 5 seconds) or disabled. It recommends setting the threshold to at least 5 seconds, with a recommendation of 20 seconds to effectively monitor blocked processes without overwhelming system resources.