In a previous post I discussed how to Create A SQL Server Agent Job To Send An Alert When Database Blocking Occurs. The steps employed in that post utilize a combination of xp_cmdshell scripts and database mail to check for blocking and send out a blocking report. I find this strategy very useful but it may not be the best option for all environments due to the need to enable xp_cmdshell .

Fortunately there is another low impact option available. If you’re running SQL Server 2008 or later you have the ability to utilize Extended Events. Though there are a plethora of options available within Extended Events, I’m only going to focus on what you will need to do in order to create a blocked process report. If you’ve never worked with Extended Events then this can also serve as a basic tutorial on how to create your first EE.

The first step in creating an Extended Event to monitor for database blocking is to set the blocked process threshold on whichever server you are wanting to monitor. In order to do this you’ll need to connect to the server in SQL Management Studio, open a new query window and use the following script.

/* Set blocked process threshold*/ EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'blocked process threshold', 10; GO RECONFIGURE; GO 1 2 3 4 5 6 7 8 9 /* Set blocked process threshold*/ EXEC sp_configure 'show advanced options' , 1 ; GO RECONFIGURE ; GO EXEC sp_configure 'blocked process threshold' , 10 ; GO RECONFIGURE ; GO

The above script will set the blocked process threshold to 10 seconds. This value is simply for testing. Depending on your environment you may want to set this value much lower, or even a bit higher. It will be up to you to determine what this setting should be in your environment. Microsoft suggests that the best practice is to set this at 5 seconds. Setting it to 0 will effectively disable the blocked process threshold.

Once you execute the script you should see the following output. It may be a little different depending on your previous blocked process threshold.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'blocked process threshold (s)' changed from 5 to 10. Run the RECONFIGURE statement to install.

Now that the threshold has been set it is time to create the Extended Event Session.

To do this, expand the server drop-down and then locate and expend the Management node. Then expand the Extended Events node. Right-click on Sessions and select New Session Wizard.

Once the New Session Wizard – Set Session Properties window appears you’ll need to give it a name. In the example below I am calling it Blocked Processes. For now, I will leave the option to Start the event session at server startup unchecked as I want to manually start this once the session has been fully configured.

Once you have provided a name click Next > to move to the Choose Template window.

As shown below, make sure the option for Do not use a template is flagged.

Click Next > to move to the Select Events To Capture window.

Here is where you will configure the Session to reference a particular Event. There are numerous event options located in the library and I would recommend revisiting this to further familiarize yourself with them as these are very powerful monitoring tools and the more you know about it the more useful they become.

The screenshot below illustrates the Select Events To Capture window prior to locating the desired blocked process report event.

In the Search Events text box, enter “blocked” to begin narrowing the search. You’ll notice three events are now listed. Select the event titled blocked process report and then click the arrow [>] to move the event over to the Selected events: box as shown below.

Once the blocked process report event has been selected click Finish. Though there are additional options that can be configured, and I would recommend reviewing these, they are not required for creating the blocked process report.

Once you have clicked Finish you should see the following Success page.

Leave both check boxes unchecked and click Close.

You should now see the newly created Blocked Processes Extended Event Session listed.

Now that the session has been created we want to configure the report session data to write to a file. In order to do this, right-click on the newly created Blocked Processes session and select Properties.

Once the Session Properties window is displayed, click on the Data Storage page on the left-hand side of the window.

Under Type, select the option for event_file. Then, under File name on server, provide a path and file name for the .xel file.

I would highly recommend putting this file on a network drive and avoid putting it on the C:\ drive. Though file growth should be minimal, the potential risk of putting it on the C:\ drive is never worth it.

Once you have selected the Type and provided a file name, click OK to save.

Now it is time to Start the Blocked Processes session. To do this, simply right-click the session and select Start Session as shown below.

If you expand the Blocked Process session you will now see the Event File package. It should be called something like package0.event_file . In order to view the records being captured by the Blocked Processes session, right-click the event file package and select View Target Data as shown below.

Chances are you won’t see any records listed quite yet. Since no blocking within the threshold that was previously set has been recorded, there is no target data to review.

Fortunately, we can create some momentary blocking to better illustrate the type of information that will be recorded by the blocked process report.

It should go without saying, but it would be ill advised to perform the following steps in a production SQL Server environment. The following steps will create a blocking event. Even though the blocking will be isolated to a specific table that will be created, it’s never a good idea to do this on a production server.

In order to do this you’ll want to make sure you are still connected to the server on which you created the Blocked Processes Extended Event Session and open two query windows.

In the first query window copy and execute the following script. This will create a table called TestBlock on the tempdb database and attempt to INSERT a record into the table within a transaction block without committing or rolling back the transaction.

USE tempdb GO CREATE TABLE TestBlock (var1 VARCHAR(15)) GO BEGIN TRANSACTION INSERT INTO TestBlock VALUES('Machu Picchu') GO --ROLLBACK TRANSACTION 1 2 3 4 5 6 7 8 9 USE tempdb GO CREATE TABLE TestBlock ( var1 VARCHAR ( 15 ) ) GO BEGIN TRANSACTION INSERT INTO TestBlock VALUES ( 'Machu Picchu' ) GO --ROLLBACK TRANSACTION

Now, in the second query window copy execute the following SELECT script.

USE tempdb GO SELECT * FROM TestBlock 1 2 3 USE tempdb GO SELECT * FROM TestBlock

Once executed you will notice no records are being returned and the query is continuing to run. Blocking is occurring due to the open transaction in the first query window.

Let this run for about a minute or two so that the Blocked Processes report will begin to capture the events.

Once you are ready to terminate the blocking, go back to the first query window and remove the comment tags from ROLLBACK TRANSACTION , highlight that line of code and execute the rollback.

Now go back to the package0.event_file package under the Blocked Processes Extended Event Session, right-click it and select View Target Data again.

You should now see Event records listed.

You’ll notice multiple records listed. Even though I only had a single blocked process, I let it run for a couple minutes which resulted in multiple events being recorded.

The best way to go about digging into each event is to select an event at the top and then double-click the XML path (highlighted above).

From here you can easily review the details of both the blocked process and the blocking process. The file should appear similar to the following screenshot. Most of the relevant information is located in each of the two <process> tags though you’ll also see the actual query (or queries) being executed in the <inputbuf> tag.

One last feature that I find useful (and a little fun) is the ability to monitor, in real time, the events being captured by the session. This can be very useful, for example, when comparing a newly created Extended Event to monitor database blocking with an older monitoring process. As blocking occurs you can compare the results of the extended event with the results of the previous method as they occur. In order to do this, simply right-click the event Session and select Watch Live Data.

There is so much more to Extended Events than just creating a blocked process report. Though I hope the information provided above helps you better understand Extended Events as well as provide a useful tool for monitoring database blocking, I hope you will also dig more into the steps outlined above to further familiarize yourself with all the options that are available when building and configuring an Extended Event in SQL Server.