Blocked Process Report in SQL Profiler

One of my servers been giving me grief as of late because the vendor has created process into their application that dumps the full content of their table into the temp database before processing it for each page load.

I been trying to find out what kind of blocking it is causing but when I turned the profiler on the “Blocked Process Report” event under “Errors and Warnings” did not return any information. So I thought okay maybe there isn’t blocked process …

I created a blocked event on purpose but it still did not report it. I have used this before on other servers without problem so I was a bit confused. I checked the Configuration settings on server and found the Blocked Process detection was disabled. By default apparently it is disabled (new to me); as all other SQL 2005 servers I have it is enabled. I guess someone must have enabled it at later point.

How to check if you have Blocked Process Report enabled:

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

If the value returned from the last statement is zero; then it is disabled. You can set it to your preferred value it is in seconds with following code:

sp_configure 'blocked process threshold', 10
GO
RECONFIGURE
GO

Remember to turn off the advanced options :).

References:
How to Identify Blocking Problems with the Profiler by Brad McGehee, Link.
MSDN Online, sp_configure options, Blocked Process Threshold, Link.
MSDN Online, Blocked Process Event Details, Link.