Advertisements
Home > Performance Tuning, SQL 2005, Tools > Blocked Process Report in SQL Profiler

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.

Advertisements
  1. August 25, 2009 at 8:42 pm

    I tried doing this, and set an alert for a 5 second threshold for Lock Wait Time, and simulated a blocking condition- I let the blocking condition run for 2 minutes, but still the alert didn't fire. Then I committed the transaction, causing the lock to release, then the alert fired. Is this the expected behavior? Shouldn't the alert fire WHILE the blocking is occurring?

  2. August 26, 2009 at 3:25 pm

    Hi Kory,Hmm that is strange; you should see a report in the SQL Profiler every 5 second a new line pop up. Not just when the block is completed, I did following to produce it:Started SQL Server ProfilerConnected to server, told it to trace All Columns for Event Errors and Warnings/Blocked process report.Started SSMS and openned to connections.In Connection #1 I entered following command:BEGIN TRANSACTION A Update Table1 Set Col1 = 'A'In Connection #2 I entered following command:SELECT * FROM Table1I went back to SQL Server Profiler and started to see Blocked process report event every 5 seconds (blocked process threshold on this server is 5).If you are doing something different please let me know we can try to figure this out :).

  1. December 21, 2011 at 1:31 am
  2. April 15, 2013 at 11:31 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: