Blocked Process Threshold – sp_configure

This configuration option is an easy way to automatically find and alert on blocked processes, without actively monitoring in profiler. The threshold can be specified in seconds between 0 and 86,400. By default, no blocked process reports are generated so this is something that has to be ‘turned on’.

Waits are not the same as deadlocks – and this only generates reports for deadlocks and not for tasks that are waiting on resources!

The example shows the threshold being changed so that reports are generated after 20 seconds of deadlocking.

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

The setting takes effect immediately without restart.

Test Case…

The below script will create the trace:

/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 02/15/2011  04:48:09 PM                    */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50 
exec @rc = sp_trace_create @TraceID output, 2, N'c:\BlockedProcess', @maxfilesize, NULL 
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error: 
select ErrorCode=@rc
finish: 
go

Note: The above can be generated via SQL Profiler by setting the trace via the GUI and then extracting the T-SQL

(File –> Export –> Script Trace Definition)

The event ID for Blocked process report is 137 (In the GUI it can be found under the Errors and Warnings header) – Full List Here. A column list for this event is available here

First, create the table:

CREATE TABLE X(rowid int)
INSERT INTO X VALUES (1)

Then, start 2 sessions with the following syntax

SELECT * FROM X

BEGIN TRAN
update X set rowid = rowid + 1
waitfor delay '00:00:30'
ROLLBACK

Once both sessions have completed, cleanup the test:

DROP TABLE X

To Stop the trace…

Where 2 is the Trace ID

--stop the trace
exec sp_trace_setstatus 2, 0
--delete the trace but leaves the file on the drive
exec sp_trace_setstatus 2, 2

To Check the Trace output…

--extension of trc is appended automatically
select cast(TextData as xml), SPID, EndTime, Duration/1000/1000
from fn_trace_gettable(N'c:\BlockedProcess.trc', default)
where eventclass = 137

The output shows in xml, the blocked process and also the blocking process

<blocked-process-report monitorLoop="2173262">
  <blocked-process>
    <process id="processec5ac8" taskpriority="0" logused="0" waitresource="RID: 16:1:176:0" waittime="23437" ownerId="957361298" transactionname="SELECT" lasttranstarted="2011-02-15T17:05:56.693" XDES="0x80053ca8" lockMode="S" schedulerid="2" kpid="18964" status="suspended" spid="60" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2011-02-15T17:05:56.693" lastbatchcompleted="2011-02-15T17:04:21.787" lastattention="2011-02-15T17:04:20.460" clientapp="Microsoft SQL Server Management Studio - Query" hostname="TCY-SQL01" hostpid="12360" loginname="GROUP\sys.mpatel" isolationlevel="read committed (2)" xactid="957361298" currentdb="16" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame line="1" stmtend="36" sqlhandle="0x02000000aa03d61b27d1e0c406266b410d31c3e5c34ff161" />
      </executionStack>
      <inputbuf>
SELECT * FROM X

BEGIN TRAN
update X set rowid = rowid + 1
waitfor delay '00:00:30'
ROLLBACK   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="suspended" waittime="25421" spid="58" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2011-02-15T17:05:54.710" lastbatchcompleted="2011-02-15T17:02:09.270" clientapp="Microsoft SQL Server Management Studio - Query" hostname="TCY-SQL01" hostpid="12360" loginname="GROUP\sys.mpatel" isolationlevel="read committed (2)" xactid="957361297" currentdb="16" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame line="5" stmtstart="126" stmtend="176" sqlhandle="0x02000000aa03d61b27d1e0c406266b410d31c3e5c34ff161" />
      </executionStack>
      <inputbuf>
SELECT * FROM X

BEGIN TRAN
update X set rowid = rowid + 1
waitfor delay '00:00:30'
ROLLBACK   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

Advertisements

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: