The Blocked Process Report will watch the SQL Server when you can’t

You aren’t always at the keyboard when an alert fires. Learn to configure the built-in Blocked Process Report to record information about blocking as it happens, so you can review it later.

To run the demos in this lesson…

1. Get code to capture the Blocked Process Report

The Blocked Process Report is built-in to SQL Server. You don’t have to install anything, but it can be helpful to have sample code to set up the trace you need to record BPR reports as they are issued.

I’ve published sample code on GitHub to:

• Configure the Blocked Process Report (BPR)
• Configure an Extended Events Trace to Collect the BRP (SQL Server 2012 and higher)
• Configure a Server Side SQL Trace to Collect the BPR (in case you prefer SQL Trace - SQL Server 2005 and higher)
• List and manage existing SQL Traces and XEvents traces

View and download the TSQL to manage the Blocked Process Report in this Gist. (License.)

2. Get the Blocked Process Report Viewer (helps interpret the results)

Download Michael J. Swart’s Blocked Process Report Viewer from GitHub here. (This moved from Codeplex in Feb 2018.)

Some notes that I and others have picked up on using the Blocked Process Report Viewer…

Case sensitive fix

If you using a case sensitive instance, you will need to replace all occurrences of  @filenamePattern with  @FilenamePattern

Resolved: Filename length limitation when using XEvents

Before February 2018, the Blocked Process Report viewer assumed that the filename (including directories) for your Blocked Process Report is 128 characters or less. If you store your Extended Events trace files in a deep directory structure and/or use long file names, it might not return any results for you.

If you are hitting this issue, make sure you have the latest version of the BPR from the link above.

Time zone when using XEvents

If you’re using an Extended Event session, the ‘traceTime’ column in the BRP is reported in the UTC time zone. It’s picking this up from the Extended Events session itself. I just convert the time in my head, but you could do it programmatically as well.

• If you click on the XML for the Blocked Process Events themselves, those are in the SQL Server’s system time.
• This doesn’t happen if you’re using a server side SQL Trace file (classic SQL Trace, not Extended Events)

Reading XEvents blocked process report files on a different SQL Server Instance

If you want to move .xel files created by an Extended Events Session and read them on a non-production SQL Server instance with the Blocked Process Report Viewer installed there, the easiest way I’ve found to do this is to do the following on the non-production SQL Server instance:

• Create an Extended Events session for sqlserver.blocked_process_report with the filename pointed to the directory where you copied the scripts.
  • Name it whatever you want, say, [Read-BPR].
  • You do NOT have to start the session - the BPR viewer is just going to read the metadata from it
• Run the BPR viewer with the name of the session you created, like this: exec sp_blocked_process_report_viewer @Source = ‘Read-BPR’, @Type = ‘XESESSION’

Transcript

Let’s see if we can catch a sneaky blocker in action.

Now, I’m back in Management Studio, and I’ve run the setup script, and then I closed the setup script out. That’s totally fine.

If you’re doing this at a different time, and then you ran the first demo, you can just rerun the setup script. Go straight to the file for this demo, which is file number two, and you don’t have to rerun through the first demo. They aren’t dependent on one another.

Let’s configure our SQL Server to record information about blocking when it happened

This is not on by default. We can do this with the Blocked Process Report in SQL Server.

This is a really cool, classic tool that’s been in SQL Server for a long time. And it works in lots of different versions of SQL Server.

We’ve got a couple steps to configure the Blocked Process Report

We are gonna first change a setting using sp_configure.

Every time I change something in sp_configure, I wanna see, before I go and run reconfigure for something I do, are there any pending configurations already? So I ran this quick query to say is there anything where the value is not the same as the value in use? There aren’t any, so it’s not like when I run reconfigure to say make my change active, I’m not gonna throw a bunch of other changes in that I’m unaware of. Just a little safety.

To enable the Blocked Process Report, first I need to make sure that I can see advanced options

We will be doing what is called an advanced option in SQL Server. I’m going to enable advanced options first.

I’m going to change the option for the Blocked Process Report

The default value for the blocked process threshold in SQL Server, that’s what controls whether or not the Blocked Process Report is issued. The value that’s the default is zero, which means I’m not going to issue a Blocked Process Report, and that’s the current setting for this. I am going to configure this to five seconds.

That means if you see blocking of five seconds, I want you to issue a report on who was blocked and who was blocking them.

You don’t wanna set this to a value under five seconds. The way this works is that the deadlock monitor in SQL Server is a tool that wakes up periodically, around five seconds by default, and looks, and says is there any blocking going on in the SQL Server that isn’t able to be resolved? In other words, two or more things are blocking one another in a way that nobody is going to be able to make progress. They are just stuck. So I’ve got to kill one of them off so at least the others can go through.

What we’re saying is hey, deadlock manager, when you wake up, could you also issue this report?

We don’t want it to wake up constantly because that could hurt our performance in SQL Server.

So you wanna either have this turned off, in other words, set to zero, or set to a value of five seconds or higher. So we’re goin' for five.

It’s gonna issue that report, but the report doesn’t go anywhere by default

Doesn’t get written to the error log or anything like that. We have to configure something to pick up the Blocked Process Report.

Option 1: We can use a classic SQL Trace for this

If you’re doing ye olde SQL Trace with Profiler, you wanna tell Profiler to generate a server side trace so that you don’t have the profiler app up and running. That app has a lot of baggage, and it can get you into some nasty situations.

Option 2: We can use an Extended Events trace

But you can say hey, script out a server side trace from it, or you can use extended events, and I’m gonna go ahead and do that in SQL Server 2016. It is not hard at all. I just tell SQL Server to create an event session.

This code is nothing fancy. I scripted this out from the GUI, but even the code isn’t so bad, right?

I’m saying here’s the name I wanna use for my event session. I want to collect just a single event, the sqlserver.blocked_process_report event, and I want you to save the results of it to a file which I’m keeping on the S drive in my XEvents folder, and then these are really the default, a bunch of default options here.

I’m saying it’s okay if the server’s under pressure, if you miss an event or, you know, something happens, and you don’t get everything in there because if the SQL Server instance is under pressure, I’ll take whatever I can get. It’s also saying, you know, you don’t have to write things to the file immediately, things like that.

So go ahead and create my event session.

And then I am going to start it.

You can totally do this using the GUI if you wanted. I find the TSQL to be pretty darn easy.

Another tool that we are going to use that will make interpreting the results easier is something called the Blocked Process Report Viewer

It’s written by Michael J. Swart, and it’s going to help us interpret the output from the Blocked Process Report. It’s totally optional, you don’t have to use it, but it will make your life easier.

The cool thing is you don’t have to install the Blocked Process Report viewer on your production server at all if you don’t want to.

You do have to if you wanna use the Blocked Process Report. You’ve gotta make the sp_configure change and set up a trace to capture the report, but you could take the trace that’s created, copy it over to your own test instance, and view the results there, and put the Blocked Process Report Viewer on your test instance to help you review the stuff there, too.

So you don’t have to put every bit of this on the production server, which is very cool.

The Blocked Process Report Viewer is going to read the trace file.

Now, I have it pointed at my extended event session itself. If you’re moving things off to another place, you can point it at the files. But it will tell us, it’ll look at the trace and say when there was blocking, I’m gonna crunch through all the Blocked Process Reports and tell you what the blocking chains looked like.

And if you’ve got an instance where there’s kind of a complex blocking chain, it’s just not one thing that’s blocked, and this is common, it’s usually not just one thing that’s blocked, this helps sort through it so that you don’t spend as much time reading the Blocked Process Report. But look, we haven’t had any blocking so far. The Blocked Process Report viewer doesn’t see anything at all yet.

Let’s make this more interesting and create some blocking!