Monitoring Blockings

The trace event class BLOCKED_PROCESS_REPORT can be used to monitor blocked processes. However, by default, this event class is disabled. You need to run sp_configure to reconfigure the blocked process threshold option. This option specifies the threshold, in seconds, at which blocked process reports are generated. For example, if you want a blocked process report to be generated for each task that is blocked for 30 seconds, run this query on the SQL Server.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'blocked process threshold', 30
GO
RECONFIGURE
GO

Please note that each report only contains two connections of a blocking. Unlike the DEADLOCK_GRAPH event, which shows you a deadlock chain, this event class doesn't show you the complete chain. You have to work through all the reports you gather at the same time to figure out which process is the head of the chain.

The MonitorBlockings.ps1 script, shown here, collects blocked process reports.

$query = "SELECT * FROM BLOCKED_PROCESS_REPORT"
$sqlnamespace = "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"
$selections= "LoginSid","PostTime","SQLInstance","IsSystem","DatabaseID","ComputerName", `
"SessionLoginName","SPID","TransactionID","EventSequence","IndexID","ObjectID","TextData", `
"EndTime","Duration","Mode"

Get-WMIEvent $query $sqlnamespace $selections

Let's run a test. In a connection using spid 53, run this query:

CREATE TABLE Test (i int)

INSERT Test SELECT 1
GO
BEGIN TRAN
UPDATE Test SET i = 1
WAITFOR DELAY '00:01:00'
COMMIT

DROP TABLE Test

This spid holds exclusive locks on the Test table.

In another connection using spid 54, run this query:

UPDATE Test SET i = 1

Spid 54 is blocked by spid 53 for 1 minute. The script detects the blocking and picks up the process report.