Collecting the Blocked Process Report (XEvents and Server Side Trace)

unblocked-clean-upI’m a big fan of the built-in Blocked Process Report in SQL Server. It’s come in handy for troubleshooting blocking situations for me many times.

I wanted a friendly way to share code to configure and manage the Blocked Process Report, so I’ve created a gist on GitHub sharing TSQL that:

  • Enables the Blocked Process Report (BPR)
  • Collects the BPR with an Extended Events trace
  • Collects the BPR using a Server Side SQL Trace (in case you don’t care XEvents or are running an older version of SQL Server)
  • Lists out the Extended Events and SQL Traces you have running, and gives you code to stop and delete traces if you wish

View or download the code from GitHub, or get it below.

Tools to help decode the Blocked Process Report after you collect it

/***********************************************************************
Copyright 2016, Kendra Little - littlekendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/
/***********************************************************************
FIRST, TELL SQL SERVER TO ISSUE THE BLOCKED PROCESS REPORT
***********************************************************************/
/* Check if there are any pending configuration items before you start */
/* Take care of those before proceeding if they exist */
SELECT *
FROM sys.configurations
where value <> value_in_use;
GO
/* Show 'advanced options' -- the BPR setting is advanced! */
/* Warning: RECONFIGURE pushes through ALL pending changes! */
IF (SELECT value_in_use FROM sys.configurations
where name=N'show advanced options') <> 1
BEGIN
EXEC ('EXEC sp_configure ''show advanced options'', 1;');
EXEC ('RECONFIGURE');
END
/* Set the blocked process threshold (seconds) to a value of 5 */
/* or higher to tell SQL Server to issue blocked process reports. */
/* Set this back to 0 at any time to stop blocked process reports. */
EXEC sp_configure 'blocked process threshold (s)', 5;
GO
RECONFIGURE;
GO
/* You're not done-- you must configure a trace to pick up the
Blocked Process Report.
You may use either:
* SQL Trace (server side trace recommended)
* Extended Events
*/
/***********************************************************************
NEXT, TRACE THE BLOCKED PROCESS REPORT
THIS OPTION USES XEVENTS.
YOU CAN USE A SERVER SIDE SQL TRACE INSTEAD THOUGH (SCROLL DOWN)
***********************************************************************/
/* Pre-requisites and notes:
Configure 'blocked process threshold (s)' to 5 or higher in sp_configure
This works with SQL Server 2014 and higher
Change the filename to a relevant location on the server itself
Tweak options in the WITH clause to your preference
Note that there is no automatic stop for this! If you want that, use a
Server Side SQL Trace instead.
THIS CREATES AND STARTS AN EXTENDED EVENTS TRACE
*/
/* Create the Extended Events trace */
CREATE EVENT SESSION [Blocked Process Report] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file
(SET filename=
N'S:\XEvents\Blocked-Process-Report.xel', max_file_size=(1024),max_rollover_files=(4))
/* File size is in MB */
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=120 SECONDS /* 0 = unlimited */,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON)
GO
/* Start the Extended Events trace */
ALTER EVENT SESSION [Blocked Process Report]
ON SERVER
STATE = START;
GO
/* Drop the trace when you're done with a command like this:
DROP EVENT SESSION [Blocked Process Report] ON SERVER
GO
*/
/***********************************************************************
THIS OPTION USES A SERVER SIDE SQL TRACE TO PICK UP THE BPR.
YOU ONLY NEED THIS *OR* THE XEVENTS TRACE -- NOT BOTH
***********************************************************************/
/* Modified from a script generated from SQL Server Profiler */
/* Pre-requisites and notes:
Configure 'blocked process threshold (s)' to 5 or higher in sp_configure
This works with SQL Server 2005 and higher
Change the filename to a relevant location on the server itself
Tweak options to your preference (including the end date)
THIS CREATES AND STARTS A SERVER SIDE SQL TRACE
*/
declare @rc int;
declare @TraceID int;
declare @maxfilesizeMB bigint;
declare @TraceEndDateTime datetime;
declare @TraceFilename nvarchar(500);
declare @rolloverfilecount int;
set @TraceEndDateTime = '2020-12-12 00:00:00.000';
set @maxfilesizeMB = 1024;
set @TraceFilename = N'S:\XEvents\Blocked-Process-Report';
set @rolloverfilecount = 4;
/* Create the basic server side trace */
exec @rc = sp_trace_create
@TraceID output,
@options = 2 /* trace will use rollover files */,
@tracefile = @TraceFilename,
@maxfilesize = @maxfilesizeMB,
@stoptime = @TraceEndDateTime,
@filecount = @rolloverfilecount;
if (@rc != 0) goto error;
/* Add the blocked process report event and collect some columns */
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 26, @on
/* Start the trace */
exec sp_trace_setstatus @TraceID, 1
/* Return the trace id to the caller */
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
/***********************************************************************
NOT SURE HOW MANY XEVENTS TRACES YOU'RE RUNNING?
USE THIS TO CHECK, AND POSSIBLY STOP AND DELETE.
***********************************************************************/
/* List Extended Events Traces which are currently started.
Built-in sessions include:
system_health
sp_server_diagnostics session
hkenginexesession
telemetry_xevents
*/
SELECT
name,
pending_buffers,
create_time,
session_source
FROM sys.dm_xe_sessions;
GO
/* Plug the trace name you want to stop and drop into
the commands below */
ALTER EVENT SESSION [Blocked Process Report]
ON SERVER
STATE = STOP;
GO
DROP EVENT SESSION [Blocked Process Report] ON SERVER
GO
/***********************************************************************
NOT SURE HOW MANY SERVER SIDE TRACES OR PROFILER TRACES YOU'RE RUNNING?
USE THIS TO CHECK, AND POSSIBLY STOP AND DELETE.
***********************************************************************/
/* Want to clean up a server side trace for the Blocked Process Report, or anything else? */
/* This will list all Server Side Traces (whether or not they have started) */
/* The default trace is usually trace id=1,
it will show as having no stop time and have a path like
D:\MSSQL\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log\log_123.trc
*/
SELECT *
FROM sys.traces;
GO
/* To stop a trace, get the id from the query above */
/* Stop the trace by setting it to status = 0 */
EXEC sp_trace_setstatus @traceid = ? , @status = 0;
GO
/* Delete the trace by setting the status to 2 */
EXEC sp_trace_setstatus @traceid = ? , @status = 2;
GO

 

 

, , , , , , , ,

10 Responses to Collecting the Blocked Process Report (XEvents and Server Side Trace)

  1. Garry Bargsley January 16, 2017 at 11:15 am #

    This is great information. I read your article and enabled this in a server side trace for a continued problem. I captured the problem and am not struggling to identify the root cause. Do you know how I can interpret the results to identify the type of lock causing the blocking and object?

  2. Garry Bargsley January 16, 2017 at 12:40 pm #

    Thank you, I will check both resources out.

  3. Garry Bargsley January 18, 2017 at 6:49 am #

    I have a question maybe you can help with. We have a SSIS package that has a long select query with multiple joins and temp variables. This process is doing a Sch-S lock. Then we have another job that runs at a similar time that is taking a Sch-M lock on the same schema that the Sch-S lock is on. Then anything that runs behind the process with the Sch-M lock is blocked and starts to back up. Is there a way to make the first process not take the Sch-S lock so it does not cause the Sch-M to cause ripple blocking?

    • Kendra Little January 18, 2017 at 10:45 am #

      Interesting!

      You need to have a shared schema lock to query a table. Even with NOLOCK/read uncommitted. Even with optimistic locking (snapshot / RCSI). Basically, SQL Server needs an assurance that the table you’re reading isn’t going to be dropped out from under it while you’re using it.

      What kind of command requires the SCH-M lock? That’s more unusual. If the command is something like an index rebuild or partition switch, you may be able to get around the blocking chain by using the WAIT_AT_LOW_PRIORITY option on the command. (SQL Server 2014 and higher.)

      • Garry Bargsley January 18, 2017 at 10:51 am #

        Our ETL guys have tried to be fancy with their data loads and are doing a sp_rename with an alter schema mixed in.

        • Kendra Little January 18, 2017 at 11:02 am #

          Ah. Yeah, that’s a hard one.

          I just had an idea for one thing that might help. But it may be utterly terrible. I’ll play with it and report back.

        • Kendra Little January 18, 2017 at 12:13 pm #

          Alright, I did some quick testing, and there is an option that sucks less. It’s still not perfect, and it only works on SQL Server 2014 and higher, but it’s something.

          To change in the new data, you could ask your ETL team to test out a partition switching pattern instead of sp_rename. This will work even in old versions on Standard Edition as long as the tables only have one partition (every table has at least one).

          Here’s the basic pattern, although you might not want to use this duration or abort the blockers, it’s up to you:
          BEGIN TRAN

          ALTER TABLE dbo.ProductionTable SWITCH PARTITION 1 TO dbo.ProductionTableOld PARTITION 1
          WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ));

          –Anyone who tries to query the table after the switch has happened and before
          –the transaction commits will be blocked: we’ve got a schema mod lock on the table

          ALTER TABLE dbo.StagingTable SWITCH PARTITION 1 TO dbo.ProductionTable PARTITION 1

          COMMIT

          The benefit here is that WAIT_AT_LOW_PRIORITY will not cause that big blocking chain you’re seeing now. It will simply sit to the side and wait, and the command controls how long, and whether it kills others, kills itself, or simply waits. It’s all up to you.

          You do have to create a table to switch out to, if you want to avoid deletes (TRUNCATE TABLE WITH PARTITION doesn’t have the WAIT_AT_LOW_PRIORITY even in 2016), but you wouldn’t be using any more space than you’re using now– you could just truncate it immediately after switch out.

          I’ll write up a blog post today with more code examples. I’m kind of excited that this works! Not sure why I didn’t put this together six months ago when I was thinking about how alter table switch works even in Standard Edition.

  4. Garry Bargsley January 18, 2017 at 12:37 pm #

    That is so helpful. Thank you so much for your input and research. Locking and blocking is not my specialty.

    • Kendra Little January 18, 2017 at 12:47 pm #

      Don’t worry, I teach classes on it, and it’s so complicated that I often feel the same way 😉

Leave a Reply