Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 105

Figure 5-5
To configure the threshold and frequency at which reports are generated, use the sp_configure
command to configure the blocked process threshold option, which can be set in seconds. By default, no
blocked process reports are produced. In the following code example, the blocked process threshold is
configured to report the blocking event every 10 seconds. After the configuration, execute sp_configure
again to verify the setting of ''blocked process threshold'' (see Figure 5-6).
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- The blocked process report threshold is the number of seconds the block is
-- in effect after which you get an event firing.
-- For example, setting this to 10 tells SQL Server to fire an event whenever
-- a block is 10 seconds old or 20 seconds or 30 seconds etc...
-- If the block lasted 40 seconds then you'd get 4 events.
sp_configure 'blocked process threshold', 10 -- 10 seconds
GO
RECONFIGURE
GO
To see a blocking event from Profiler, you will use the SQL Server sample database Adventureworks and
open two query windows.