Database Reference
In-Depth Information
There is a configuration setting called
blocked process threshold
, which specifies how often SQL Server checks for
blocking in the system and generates a report. Listing 18-3 shows the code that sets the threshold to 20 seconds.
Listing 18-3.
Specifying Blocking Process Threshold
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'blocked process threshold', 20;
go
reconfigure;
go
■
You need to fine-tune the value of the
blocked process threshold
in production. it is important to avoid false
positives and, at the same time, capture the problems. Microsoft suggests not going below the 5 seconds as the
minimum value, and you obviously need to set the value to less than query timeout.
Note
There are a few ways to capture that report in the system. The simplest is to use SQL Trace - there is "Blocked
Process Report" event in the "Errors and "Warnings" section, as shown in Figure
18-5
.
Figure 18-5.
Blocked Process Report event in SQL Trace
