Building a Custom Blocked Process Report

The blocked process report was introduced with SQL Server 2005 and it hasn’t changed much since then. From one SQL Server version/edition to another, it helps us troubleshoot blocking by generating an XML document which describes who the blocker is and who is being blocked. If you have ever seen this report, you will probably agree with me that it’s not easy to read. It’s not too complicated when two sessions are involved in the blocking process, however, when more than two sessions are involved, a blocked process report is generated for each block. In that situation, the blocked process report could be a little annoying and confusing at the same time, because we can’t determine who the root blocker is.

This article will describe a practical solution which will tell you about blocking almost immediately by sending an e-mail when blocking occurs. (There are some requirements to use this solution, which I explain in the Prerequisites section). To learn more about the basics of locks and blocking, I highly recommend reading SQL Server Concurrency Locking, Blocking and Row Versioning by Kalen Delaney to understand this article correctly.

Over the years, I would receive complaints with just a few words saying that everything is frozen. This usually means that the main thread of the application is blocked, and the user cannot do anything with the mouse and keyboard. There are many causes of blocking, but those caused by locking are the most difficult to troubleshoot. Often, during the test phase, everything seems to be functional. Blocking isn’t discovered until a new version is deployed to the production system.

The blocked process report provided by SQL Server contains the information you need to understand blocking, but it is not easy to understand as shown in Figure 1.

Figure 1: The blocked process report from SQL Server

Eventually, I tried to make my life easier by developing a custom solution which will describe everything about blocking. In my opinion, receiving an e-mail like the one in Figure 2 could help DBAs as well as software developers understand blocking much better than the built-in report.

Figure 2: A more readable look of custom blocked process report

In this solution, when the blocking episode occurs for the first time, a detailed report is sent. If the situation continues, you don’t need to see the same information repeatedly. A reminder that the blocking still exists like shown in Figure 3 is all you need.

Figure 3: After first e-mail, the custom blocked process report changes its appearance.

In the detailed report shown in Figure 2, you can see:

Outlined in blue: the server name, the customer name, the ID of the blocked process report, how long it took to generate the blocked process report, and how many events are generated
Outlined in red: the resource name, which usually means the table and the index name, as well as the length of blocking
Outlined in yellow: the root blocker information (Level zero means root blocker if two SPID are involved and you can see N-rows if many SPID’s are involved in the blocked process report).
Outlined in pink: basic blocked information
And finally, outlined in brown: basic blocking information (both contain information about the application name, the host name, the user first, last and login name as well as isolation level, SPID + ECID and last T-SQL statement).

The blocked process report is generated by the same process as the deadlock monitor. The deadlock monitor runs frequently looking for deadlocks (which are just blocking chains in a circle). It runs every couple of seconds when there are no deadlocks, and if it detects any, it runs a bit more frequently. Each time it is called a monitor loop. See References.

Information about blocking in the report could be extended in many ways. For example, you might include detailed locking information as shown in Figure 4.

Figure 4: The table that shows information about locks acquired by a session

You might also include resource content as showed in Figure 5.

Figure 5: Show the blocked process report resource content

Prerequisites

This solution uses some features developed earlier. These include using the scalar-valued function EMAIL.QueryToHtml and the stored procedure EMAIL.CLRSendMail. Both are implemented in a SQLCLR project called SimpleTalk.SQLCLR.SendMail. You can find the source code on GitHub.

Because, this is a SQLCLR solution, you must enable CLR if not already enabled. To do this, execute the T-SQL script displayed in Listing 1.

Listing 1: Enabling CLR

-- Enable & Check CLR

sp_configure N'clr enabled', 1

RECONFIGURE

SELECT

sc.*

FROM sys.configurations AS sc

WHERE sc.[name] = N'clr enabled'

After that, you must deploy the assembly from the project. This article is going to use AdventureWorks2014 for all examples, so deploy the solution to that database. After deploying the assembly, please be sure that everything installed correctly. In the context of your database, expand the Programmability->Assemblies node and check that SimpleTalk.SQLCLR.SendMail is there as shown in Figure 6.

Figure 6: Check installation

Do the same with node Programmability->Functions->Scalar-valued Functions and check that function EMAIL.QueryToHtml is there, as shown in Figure 7.

Figure 7: Check installation

T-SQL is often not suitable when you are manipulating large strings. If such strings include data, HTML definitions and CSS styles, there is a good chance that you will produce code which is difficult to understand and maintain. That was the reason why I developed the SQLCLR solution, which I explain in detail in my article Practical usage of SQLCLR: Building QueryToHtml function .

Finally expand the node labeled Programmability->Stored Procedures and make sure that the EMAIL.CLRSendEmail stored procedure is there, as displayed in Figure 8.

Figure 8: Check installation

A detailed explanation about using this stored procedure can be found in SQLCLR in Practice: Creating a Better Way of Sending Email from SQL Server. You must also complete the information in the EMAIL.Profile table. See the article for more information.

Of course, you can customize a solution of your own by replacing the stored procedure with Microsoft SQL Server sp_send_dbmail and applying your query to the HTML transformation. If you decided to use sp_send_dbmail, please read this article Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail before you begin.

Source Code and Installation of the Custom Blocking Report

The solution source code for the Custom Blocking Report can be found on Github. This is a SQL Server Database Project which produces an assembly. The assembly name is SimpleTalk.SQLCLR.CustomBlockedReport as shown in Figure 9.

Figure 9: the project settings

To get the solution to work without issues, you will need to follow the instructions in this section. The first step is to make sure that the database is owned by the sa account. If you have renamed the account, adjust the code as necessary. Run Listing 2.

Listing 2: Change the database owner

USE AdventureWorks2014;

EXEC sp_changedbowner 'sa'

The solution uses Event Notifications which depends on Service Broker. To enable Service Broker, make sure that there are no connections to the database and run the code in Listing 3:

Listing 3: Enable Service Broker

USE Master;

ALTER DATABASE AdventureWorks2014 SET NEW_BROKER;

Now you can deploy the objects by running the CustomBlockedProcess project. The project will create several tables, stored procedures, and functions.

All code to define schema and tables can be found in Pre-Deploy script called DataBaseDefinition.sql. There is Post-Deploy script too. In this script, SQLCLR objects are transferred from the default database schema DBO to the BPR schema. The BPR schema holds all database objects created in the solution.

Everything connected with SQLCLR objects can be found in the SQLCLR folder. You may want to completely ignore the SQLCLR part and apply Pre-Deploy and Post-Deploy scripts separately. In that case, you should comment any references to SQLCLR. During the development phase, I put native T-SQL functions and procedures in a separate folder named Developing. This was easier for me. Therefore, before publishing, I had to copy all content from the Developing folder into DataBaseDefinition.sql. If you like to clean up your environment after playing with this solution, there is a script for this purpose located in the CleanUp directory.

After deploying the assembly, please expand the node labeled Programmability->Stored Procedures and make sure that the outlined procedures are there, as shown in Figure 10.

Figure 10: check installation

Do the same with Programmability->Functions as shown in Figure 11.

Figure 11: check installation

Once you have verified that the objects are in place, you must create a certificate and sign three of the objects. You will find the code in the project in Instance level operation Certifikates Signing.sql. Run the code in a Query Window in SSMS. Before running the code, create the C:\tmp folder.

The code to create the Service Broker queue can be found in the project under SSBObjects SSBObjects.sql. Run this code in a Query Window in SSMS. To verify that the objects are in place, review the Service Broker folder in SSMS as shown in Figure 12.

Figure 12: The Service Broker objects

The solution contains a table for configuration, BPR.Bpr_Configuration. Run the statement found in Listing 4 to update the Dedicated e-mail address, filling in the address you wish to use.

Listing 4: Update the email addres

USE AdventureWorks2014;

UPDATE BPR.bpr_Configuration

SET [value] = '<valid email address>'

WHERE [ID] = 8;

To test the solution, two small programs will be used to purposely create blocking. The source code for the .NET testing applications can be found here.

Blocking

Blocking is an unavoidable characteristic of any relational database management system with lock-based concurrency. In SQL Server, blocking occurs when one session holds a lock on a specific resource, and a second session attempts to acquire a conflicting lock type on the same resource.

We are interested especially in all blocks which take a long time to resolve. How long is that time? In my opinion, waiting for more than 10 secs is long enough, but it will depend on your application. After that, you should be notified in some way e.g. by sending an e-mail that potential problems exist.

To be notified about blocking, we must change an instance level setting called the blocked process threshold. Run the code shown in Listing 5 to enable this setting.

Listing 5: Changing the Blocked Process Threshold setting

sp_configure 'show advanced options'

, 1 ;

RECONFIGURE;

sp_configure 'blocked process threshold'

, 10;

RECONFIGURE;

--Check if everything is OK. Since 2005+ configuration_id is 1569

SELECT

FROM sys.configurations

WHERE configuration_id = 1569;

When setting this to an appropriate number of seconds, every block that takes longer will generate an event. Part of that event is information about blocking, called the blocked process report.

You can see the blocked process report by using SQL Server Profiler or Extended Events. Only by using Event Notification can you react to that event by causing a custom action. This action includes saving information about blocking in the newly created tables.

Event Notification

Microsoft documentation describes Event Notification as the following: Event notifications execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to SSB (SQL Server Service Broker).

We are interested in specially one event called BLOCKED_PROCESS_REPORT. To see the complete list of events that can be captured, execute the statement in Listing 6:

Listing 6: Querying event notification system view

--Blocked process is recognised by id 1137

SELECT

FROM sys.event_notification_event_types;

Notification Services performs an action in response to an event in an asynchronous mode instead of a synchronous manner. This means that the action will not affect the performance of the transaction that caused it. This action, as you see later, will detect the blocker and who is blocked, save the information in readable form in a database, and send an e-mail.

To use Event Notifications, you must enable SSB in the database. Listing 7 displays the command used to enable SSB. If you followed the instruction in the last section, it should already be set.

Listing 7: Enabling SSB

1	ALTER DATABASE AdventureWorks2014 SET NEW BROKER;

Listing 8 contains the code to set up the objects for Event Notification. Again, these are already in place if you have successfully followed the instructions in the last section. SSB includes a message type and contract specifically for event notifications. Therefore, a SSB initiating service does not have to be created because one already exists that specifies the following contract name: http://schemas.microsoft.com/SQL/Notifications/PostEventNotification. The target service that receives event notifications must honor this preexisting contract.

Listing 8: Creating Service Broker objects

USE AdventureWorks2014

--1. Drop event notification if exists

IF EXISTS (SELECt *

FROM sys.server_event_notifications

WHERE name = 'BPRNotification')

BEGIN

DROP EVENT NOTIFICATION BPRNotification ON SERVER;

END;

--2. Drop route if exists

IF EXISTS (SELECT *

FROM sys.routes

WHERE name = 'BPRRoute')

BEGIN

DROP ROUTE BPRRoute;

END;

--3. Drop service if exists

IF EXISTS (SELECT *

FROM sys.services

WHERE name = 'BPRService')

BEGIN

DROP SERVICE BPRService;

END;

--4. Drop queue

IF EXISTS (SELECT *

FROM sys.service_queues

WHERE name = 'BPRQueue')

BEGIN

DROP QUEUE BPRQueue;

END;

--Create queue

--Which is a storage area for the data that gets received from the event notification servise.

--Queues are implemented as internal tables inside of SQL Server.

--We can view user defined queues by quering system view, as showed bellow.

--SELECT

-- *

--FROM sys.service_queues

--WHERE is_ms_shipped = 0;

CREATE QUEUE BPRQueue;

--Create servis

--which are used to receive messages from the event notification servise

--by using contract [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]

--We can view contracts by quering system view

--SELECT *

--FROM sys.service_contracts

CREATE SERVICE BPRService ON QUEUE BPRQueue

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]

);

--Create route

--When a route specifies 'LOCAL' for the next_hop_address,

--the message is delivered to a service within the current instance of SQL Server

CREATE ROUTE BPRRoute

WITH SERVICE_NAME = 'BPRService',

ADDRESS = 'LOCAL';

--Create event notification

CREATE EVENT NOTIFICATION BPRNotification ON SERVER WITH FAN_IN FOR BLOCKED_PROCESS_REPORT TO SERVICE 'BPRService', 'current database';

--Setup custom action

--In order to automatically process the queue, we are using activation.

--Activation requires an activation stored procedure that is executed when new messages are added to the queue.

--The activation procedure is a standard stored procedure that works off the queue instead

--of tables in the database.

ALTER QUEUE BPRQueue WITH ACTIVATION (STATUS=ON, PROCEDURE_NAME = [BPR].[HandleBPR], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER);

The SSB queue is a storage area for the data that gets received from the event notification service. It is implemented as internal tables inside of SQL Server. In order to automatically process the queue, we are using activation. Activation requires an activation stored procedure that is executed when new messages are added to the queue. The activation procedure is a standard stored procedure that works off the queue instead of tables in the database. Our custom action, when blocking occurs, will be incorporated in the stored procedure BPR.HandleBPR. Next, we’ll take a look at how the stored procedure works.

Building Stored Procedure BPR.HandleBPR for SSB Queue Processing

The bpr.HandleBPR stored procedure that you installed in the Source Code and Installation of the Custom Blocking Report section is the heart of the whole solution. The entire stored procedure is quite lengthy, so only portions of it will be shown in this section.

It begins by querying the configuration table, BPR.Bpr_Configuration, to initialize configuration variables. There are 13 configuration variables and they are prefixed with config. Listing 9 shows a sample of the configuration statements.

Listing 9: Sample of configuration variables

DECLARE @configCustomerName NVARCHAR(35)= CAST(

(

SELECT TOP 1 value

FROM [Bpr].[Bpr_Configuration]

WHERE ID = 1

) AS NVARCHAR(35));

DECLARE @configNumberOfEvents INT= CAST(

(

SELECT TOP 1 value

FROM [Bpr].[Bpr_Configuration]

WHERE ID = 10

) AS INT);

Then, as shown in Listing 10, the RECEIVE statement reads messages from the queue, dbo.BPRQueue, and returns a result set which is stored in several variables.

Listing 10: Processing the SSB queue

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION;

WAITFOR(

RECEIVE TOP (1) -- only one message

@message_type = message_type_id, --message type ( pass by framework )

@messageTypeName=message_type_name,

@message_body = CAST(message_body AS XML), -- pass by framework

@dialog = conversation_handle -- pass by framework

FROM dbo.BPRQueue), TIMEOUT 1000;

-- wait one second

IF (@@ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION;

BREAK;

END;

The result set consists of zero or one row which contains one message. The message body is a well-formed XML document which must be processed to be more readable. By processing the XML document, we find out blocking and blocked SPIDs, as well as blocking and blocked ECID. Listing 11 shows a sample of the code used to extract the information from the message body.

Listing 11: Processing the message body

SET @innerBody =

@message_body.query('(/EVENT_INSTANCE/TextData/blocked-process-report/.)[1]');

SET @blocked =

@innerBody.query('(/blocked-process-report/blocked-process/process/.)[1]');

SET @blocking =

@innerBody.query('(/blocked-process-report/blocking-process/process/.)[1]');

After querying the header table shown in Listing 12, we know if this block occurs for the first time or not.

Listing 12: Query the header table

--Check if exists

SET @counter =

(

SELECT TOP 1 ID

FROM [Bpr].[Bpr_Header]

WHERE [BlockingKey] = @sessionsKey

AND [StartTime] = @lastBachStarted

);

If the block occurs for the first time, information that is more readable is inserted into appropriate tables. Then an e-mail is send to dedicated e-mail address. If the block occurs second, third and so on time, then solution will update only the header table, with appropriate wait time and number of events that are generated. Listing 13 shows the insert and update statements.

Listing 13: Determine if event occurs for the first time or it is second or n-th occurrence

--parse xml document and insert information in appropriate tables

IF @counter IS NULL

BEGIN

INSERT INTO [Bpr].[Bpr_Header]

([DataBaseName],

[WaitTimeInSec],

[BlockingKey],

[StartTime]

)

--OUTPUT INSERTED.ID

--, INSERTED.Counter

--INTO @newid

SELECT DB_NAME(@dbId),

@waitSec,

@sessionsKey,

@lastBachStarted;

SET @addedId =

(

SELECT IDENT_CURRENT('[Bpr].[Bpr_Header]')

);

SET @addedCounter = 0;

END;

ELSE

BEGIN

UPDATE [Bpr].[Bpr_Header]

SET

COUNTER = COUNTER + 1,

[WaitTimeInSec] = @waitSec

--OUTPUT INSERTED.Id

--, INSERTED.Counter

--INTO @newid

WHERE ID = @counter;

SET @addedId = @counter;

SET @addedCounter =

(

SELECT TOP 1 COUNTER

FROM [Bpr].[Bpr_Header]

WHERE ID = @counter

);

END;

If some exception occurs in the stored procedure, the exception message as well as the inner body of the XML document are saved in the table that holds information about bad messages. In that case, an e-mail, which displays information about errors, is generated and send to a dedicated e-mail address. The configuration table holds the email address; see section Understanding the Custom Blocked Process Report to learn more about the configuration. The e-mail should look similar to the one shown in figure 13.

Figure 13: Processing internal errors by sending e-mail

The code that handles errors is displayed in Listing 14.

Listing 14: Handles error in activation procedure

BEGIN CATCH

BEGIN

DECLARE @errorMess AS NVARCHAR(MAX) = (SELECT

ERROR_MESSAGE());

INSERT INTO [Bpr].[Bpr_BadMessage] ([BPR_INNER_BODY], [ERROR_MESSAGE])

SELECT

@innerBody

,@errorMess;

SET @subject = @@SERVERNAME + '- Error in BlockProcess Notification. Customer name ' + @configCustomerName

;

SET @body = 'Error message : ' + CHAR(13) + CHAR(10) + '<b>' + @errorMess + '</b>'

EXEC [EMAIL].[CLRSendMail] @profileName = @configProfileName

,@mailTo = @configEmailAddress

,@mailSubject = @subject

,@mailBody = @body

,@displayName = @configDisplayName

END

END CATCH

If we do not provide code to handle exceptions, the SSB queue will be automatically disabled in case of an error. This is the standard behavior of SSB. In that case, we have to remove the so called ‘poison messages’ from the queue and re-enable the SSB queue.

Not every TRY-CATCH block will catch an exception. There is the possibility to produce a ‘poison message’. In that case the SSB queue receives a message, which it could not handle, and the SSB queue will be disabled automatically. This is by design.

First step to follow in such a situation is to disable activation by issuing following command as shown in Listing 15.

Listing 15: Disabling activation

1	ALTER QUEUE [dbo].[BPRQueue] WITH STATUS = OFF , RETENTION = OFF , ACTIVATION ( STATUS = OFF , PROCEDURE_NAME = [BPR].[HandleBPR] , MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER ), POISON_MESSAGE_HANDLING (STATUS = ON)

Then it is safe to enable the queue again. This could be accomplished by using SSMS, right clicking on the queue and from the context menu choose ‘enable’. Once the queue has enabled and the activation procedure does not work automatically, it is safe to start the procedure in SSMS as shown in Listing 16.

Listing 16: Run the activation stored procedure

USE [AdventureWorks2014]

DECLARE @return_value int

EXEC @return_value = [BPR].[HandleBPR]

SELECT 'Return Value' = @return_value

Once you determine the real cause of the problem and fix the code, it is safe to enable activation by executing Listing 17.

Listing 17: Enabling activation

1	ALTER QUEUE [dbo].[BPRQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [BPR].[HandleBPR] , MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER ), POISON_MESSAGE_HANDLING (STATUS = ON)

In this section, I explained how the activation-stored procedure is organized and configured. A very important part is how to handle exceptions. If we do not handle the exceptions, SSB the queue will be automatically disabled. In my opinion this requires much more work to be done, because you must remove ‘poison’ messages and re-enable the queue.

Understanding the Custom Blocked Process Report

This custom solution includes a few new tables, a couple of new functions, and some stored procedures. For the sake of the readability of these objects, we will set them in a special schema called BPR.

Here is a list of the objects in Table 1.

Table 1: New objects created by the solution

Type	Name	Description
Table	BPR.Bpr_Header	Holds information about when blocking starts, how many events are generated, cumulative wait time etc.
Table	BPR.Bpr_Detail	Holds information about blocker and blocking. Host name, application name, SPID + ECID, isolation level etc.
Table	BPR.Bpr_Message	Holds the original blocked process report as XML document
Table	BPR.Bpr_Configuration	Holds the information about customer name, dedicated e-mail address, as well as possibilities to include/exclude part of blocked process report.
Table	BPR.Bpr_Resources	Holds information about the content of the blocked resource in form of XML document.
Table	BPR.Bpr_Plans	Holds information about query plan of the blocked session.
Table	BPR.Bpr_LockInfoBlocking	Holds information about locks acquired by the blocking session
Table	BPR.Bpr_LockInfoBlocked	Holds information about locks acquired by the blocked session
Table	BPR.Bpr_BadMessage	Holds information about exception which occurs in activation stored procedure.
Function	BPR.GetResourceName	Returns what is subject of locking. Usually this means table or index name.
Function	BPR.GetResourceNameCLR	SQLCLR equivalent to T-SQL function BPR.GetResourceName
Function	BPR.GetResourceContent	Return the content of the blocked resource in form of XML document.
Function	BPR.GetResourceContentCLR	SQLCLR equivalent to T-SQL function BPR.GetResourceContent.
Function	BPR.GetWaitInfo	Returns the blocked process chain as a table. On the top there is a root blocker.
Function	BPR.GetLockInfo	Returns table that displays locks acquired by a session.
Stored procedure	BPR. GetResourceNameFromPageClr	Getting resource name from the blocked process report when page lock is acquired
Stored procedure	BPR.ShowBlocking	Displays for each block blocking tree
Stored Procedure	BPR.HandleBpr	Stored procedure that responds to blocked process events

These new tables can be viewed better with a database diagram, as shown on Diagram 1.

Diagram 1: Database diagram

The configuration can be changed on fly by updating the configuration table BPR. Bpr_Configuration. The configuration parameters are listed in table 2.

Table 2: Configuration properties

ID	Name	Default Value	Description
1	Customer name	Simple Talk	Specify customer name
2	Show blocking info	1	Do we include information about locks acquired by the blocking session in the blocked process report?
3	Show blocked info	1	Do we include information about locks acquired by the blocked session in the blocked process report?
4	Show resource content	1	Do we include information about resource content in the form of a XML document in the blocked process report?
5	Show query plan	0	Do we include information about query plans?
6	How many maximum records will be displayed in the blocked session logs list	30	How many maximum records will be displayed in the blocked session logs list? These locks could be thousands, so in that case saving and processing them has no sense.
7	How many maximum records will be displayed in the blocking session logs list	30	How many maximum records will be displayed in the blocking session logs list? These locks could be thousands, so in that case saving and processing them has no sense.
8	Dedicated e-mail address	<yourEmail@email.com>	Dedicated e-mail address, which will receive information about the blocked process report.
9	Profile Name	SimpleTalk	Profile name used to send e-mail (see Appendix A).
10	Number of events	20	After sending first e-mail, how many events should be raised to send additional e-mails
11	Show original blocked process report as XML document	0	Show blocked process report xml message.
12	Display name	BPR	Display name on e-mail message
13	Use SQLCLR	0	Solution uses SQLCLR function and procedure equivalents.

Testing with the Sample Application

Now it’s time to simulate blocking. I will be playing with the AdventureWorks2014 database and in that database, run Listing 18 to create two users and grant the appropriate rights to the schemas Person and HumanResources.

Listing 18: Creating two logins and two users

USE AdventureWorks2014;

--login/user userBlocking

CREATE LOGIN [userBlocking]

WITH PASSWORD=N'myTestPass',

DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF;

CREATE USER [userBlocking]

FOR LOGIN [userBlocking]

WITH DEFAULT_SCHEMA=[dbo];

GRANT

SELECT, INSERT, UPDATE,DELETE, EXECUTE

ON SCHEMA::Person

TO userBlocking;

--login/user userBlocked

CREATE LOGIN [userBlocked]

WITH PASSWORD=N'myTestPass',

DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF;

CREATE USER [userBlocked]

FOR LOGIN [userBlocked]

WITH DEFAULT_SCHEMA=[dbo]

GRANT

SELECT, INSERT, UPDATE,DELETE, EXECUTE

ON SCHEMA::Person

TO userBlocked

GRANT

SELECT

ON SCHEMA::HumanResources

TO userBlocked

Now, let us build two console apps. The source code can be found here. The purpose of building these small applications is to simulate real blocking. By ‘real blocking’ I mean, information about the users as well as the applications.

Open the App.config file inside the Blocking application and modify the connection string to match your server as shown in Listing 19. Notice that the connection string has been extended in both applications. The parameter and value Application Name=HR has been added. (HR stands for Human Resources).

The app.config’s connection string looks similar to this for the blocking application.

Listing 19: Application configuration file

<add name="ConnStr"

connectionString="Data Source=DARKO-W10I7;Initial Catalog=AdventureWorks2014;Integrated Security=False;User ID=userBlocking;Password=myTestPass;Application Name=HR;"

providerName="System.Data.SqlClient" />

</connectionStrings>

The connection string for the blocked application will be the same, except that instead of user userBlocking, we will use userBlocked. Be sure to update the connection string in each application.

In both applications, I implemented a static class which simulates the application authentication by appending a random user last and first name to the connection string. Listing 20 shows the class.

Listing 20: Simulate user info

using System;

public class UserInfo

{

public static string InjectInformationAboutUser(string inputString)

{

string[] connBuilder = inputString.Split(';');

string replacement = string.Empty;

foreach (string s1 in connBuilder)

{

string s = s1;

if (s.StartsWith("Application Name"))

{

//At this point user is authenticated, so you know his/hers first and last name

Random rnd = new Random();

int id = rnd.Next(1, 6);

if (id == 1)

{

s += "\\Greg Robinson(GRobinson)";

}

else if (id == 2)

{

s += "\\John Smith(JSmith)";

}

else if (id == 3)

{

s += "\\Mila Jovovic(MJovovic)";

}

else if (id == 4)

{

s += "\\Richard Brown(RBrown)";

}

else if (id == 5)

{

s += "\\Tom Eliot(TEliot)";

}

else if (id == 6)

{

s += "\\Ana Richard(ARichard)";

}

replacement += s + ";";

}

if (replacement.Length > 1)

{

replacement = replacement.Substring(0, replacement.Length - 1);

}

return replacement;

}

So, when we issue command SELECT AppName(), the result will be, for example, HR\Ana Richard(ARichard), depending on what .NET Random will return.

In my opinion knowing the user application name is one of the most important things in solving blocking problems. As I saw in many environements, multiple users could use the same SQL Server login name which makes solving blocking problems much harder.

Our blocking application looks similar to Listing 21.

Listing 21: The Blocking application

using BlockingSession.ConfigHelper;

using System;

using System.Data.SqlClient;

using System.Diagnostics;

namespace BlockingSession

{

class Program

{

static void Main(string[] args)

{

string connectionString = UserInfo.InjectInformationAboutUser(DVConfiguration.myConnString);

// Provide the query string with a parameter placeholder.

string queryString =

+ "UPDATE PERSON.PERSON "

+ "SET LastName = LastName "

+ "WHERE BusinessEntityID = @parametar";

// Specify the parameter value.

int paramValue = 1;

// Create and open the connection in a using block. This

// ensures that all resources will be closed and disposed

// when the code exits.

SqlTransaction trn = null;

using (SqlConnection connection =

new SqlConnection(connectionString))

{

// Create the Command and Parameter objects.

SqlCommand command = new SqlCommand(queryString, connection);

command.Parameters.AddWithValue("@parametar", paramValue);

// Open the connection in a try/catch block.

try

{

connection.Open();

//It could be helpfull too, to name your transaction

trn = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"BlockingSessionTran");

command.Transaction = trn;

command.CommandTimeout = 0; //no limits

command.ExecuteNonQuery();

if ( Debugger.IsAttached)

Debugger.Break();

//running transaction

trn.Commit();

}

catch (Exception ex)

{

trn.Rollback();

Console.WriteLine(ex.Message);

}

Console.ReadLine();

}

After opening the transaction and executing update command, we will force the application to stop by issuing Debugger.Break(). In reallity Debugger.Break() could be a modal window which forces the user to answer something, e.g. ‘yes’ or ‘no’. Before answering this modal dialog, the user received a phone call and must go to a meeting. In this situation, we have open and running transaction.

Similar to the blocking application, the blocked application looks like Listing 22.

Listing 22: The blocked application

using BlockedSession.ConfigHelper;

using System;

using System.Data.SqlClient;

namespace BlockedSession

{

class Program

{

static void Main(string[] args)

{

string connectionString = UserInfo.InjectInformationAboutUser(DVConfiguration.myConnString);

// Provide the query string with a parameter placeholder.

string queryString =

+ "SELECT * FROM Person.Person "

+ "WHERE BusinessEntityID = @parametar";

// Specify the parameter value.

int paramValue = 1;

// Create and open the connection in a using block. This

// ensures that all resources will be closed and disposed

// when the code exits.

using (SqlConnection connection =

new SqlConnection(connectionString))

{

// Create the Command and Parameter objects.

SqlCommand command = new SqlCommand(queryString, connection);

command.Parameters.AddWithValue("@parametar", paramValue);

command.CommandTimeout = 0; //no limits

// Open the connection in a try/catch block.

try

{

connection.Open();

command.ExecuteNonQuery();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

Console.ReadLine();

}

In this application, I will try to get one record from the table Person.Person by quering for BusinessEntityId.

By starting first the blocking application and then the blocked application, we simulate the blocking as in real life. Be sure to run the blocking application first. Don’t start the blocked application until the blocking application reaches the Debugger.Break() statement.

At that point, blocking occures and we could start SQL Profiler ( or create a new session in Extended Events ) in order to capture the blocked process report. After few seconds, the blocked process report is generated. It is an XML document, which consist usually of two parts. One part is about blocked process, and the other part is about blocking process. From time to time, I noticed that SQL Server is not able to determine the SPID of the blocking session. In that case, the blocked process report is still generated but only with information about the blocked session.

Finally, to conclude this section, let us see what our custom solution will send to us. The report should resemble Figure 14.

Figure 14: Custom blocked process report

As you can see from reading this article, part of this information could be excluded, and new information could be added.

Getting the Root Blocker and Finding Out What is Locked

As I wrote earlier, the blocked process report is generated for each block. It is understandable when two sessions are involved in the blocked process. However, what will happen in we have three, four or one hundred sessions? This example will simulate four users.

Start running the Blocking application. The Blocking application will wait on Debugger.Break, which will simulate open and running transaction.

Instead of starting the .NET Blocked application, let us start first SSMS and execute following command as showed in Listing 23.

Listing 23: Opening transaction using SSMS

USE AdventureWorks2014

BEGIN TRAN

;

WITH X

(SELECT

FROM HumanResources.Employee

WHERE BusinessEntityID = 1)

UPDATE X

SET JobTitle = JobTitle

This command completes successfully. After that, try executing the command displayed in Listing 24 in another window. This session will be blocked.

Listing 24: This command will be blocked by .NET ‘Blocking’ application

SELECT

FROM Person.Person

WHERE BusinessEntityID = 1;

At this point, it is time to start the .NET Blocked application. This time the Blocked application will be blocked by an SSMS session. After a few seconds, SQL Server will generate two blocked reports. First, one in which SSMS session is blocked by the .NET Blocking application and second one in which the .NET Blocked application is blocked by an SSMS session.

In my opinion the second blocked process report should be extended by information about root the blocker and this is exactly how solution works. On the highest level, this time ‘Level 1’, the blocked process report displays the root blocker. The blocked process report looks similar like to Figure 15.

Figure 15 the highest level shows the root blocker

Part of the solution is the table valued function called BPR.GetWaitInfo. This function returns a table similar to the one shown in Figure 14. The blocked process report contains information about the blocked session ID, as well as blocking session ID. In addition, GetWaitInfo takes additional parameters, blocking and blocked ECID (If a process is multi-threaded, all the sub-threads have the same parent Server Process ID (SPID). Each sub-thread has its own unique identifier called ECID). Table 3, shown below, displays the function parameters.

Table 3 GetWaitInfo parameters

Parameter name	Description
@blockingSessionID	The blocking SPID, it is rendered as part of the blocked process report as @spid attribute of blocking-process/process element
@blockedSessionID	The blocked SPID, it is rendered as part of the blocked process report as @spid attribute of blocked-process/process element
@blockingEcid	The blocking ECID, it is rendered as part of the blocked process report as @ecid attribute of blocking-process/process element
@blockedEcid	The blocking ECID, it is rendered as part of the blocked process report as @ecid attribute of blocked-process/process element

Basically GetWaitInfo takes parameters from the blocked process report. Recursion is accomplished by using a CTE (common table expression). As an anchor, I use the results of querying the system view sys.dm_os_waiting_task. The whole task is accomplished as shown in Listing 25. After that, the CTE Blocking is joined with other system views in order to produce the required output.

Listing 25: GetWaitInfo implementation

WITH Blocking (BlockedId, Ecid, BlockingId, BlockingEcid, LevelId)

(

--Anchor

SELECT

session_id

,ISNULL(exec_context_id, 0) exec_context_id

,blocking_session_id

,ISNULL(blocking_exec_context_id, 0) blocking_exec_context_id

,0 Level

FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id IS NOT NULL

AND blocking_session_id = @blockingSessionId

AND session_id = @blockedSessionId

AND ISNULL(exec_context_id, 0) = @blockedEcid

AND ISNULL(blocking_exec_context_id, 0) = @blockingEcid

UNION ALL

--Recursive

SELECT

session_id

,ISNULL(exec_context_id, 0) exec_context_id

,blocking_session_id

,ISNULL(blocking_exec_context_id, 0) blocking_exec_context_id

,LevelId + 1 LevelId

FROM sys.dm_os_waiting_tasks r

INNER JOIN blocking b

ON r.session_id = b.BlockingId

AND r.exec_context_id = b.BlockingEcid)

Summary

This solution is in its first release and could be improved and extended in many ways, however, I have used a small part of this solution for many years and did not find any difficulties. It helped me in many situations where it was difficult to find the root cause of blocking. Of course, some of you may ask the question, do we need SSB at all? There is an opportunity to define a SQL Server Agent Job, with a fixed schedule, running, for example, every minute. Personally, I do not like such a solution because you cannot count on SQL Server Agent scheduler and a problem could start and stop between runs of the job and not be registered at all.

So far, I have not found any difficulties with this solution. It is easy to implement and does not impact the system very much. After all, at any moment you could change instance level settings and set back the blocked process threshold to zero. Alternatively, you can setup the solution and when a problem arises, you can change instance level settings to start troubleshooting.

References

SQL Server Concurrency Locking, Blocking and Row Versioning by Kalen Delaney
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues by Jason Strate
Michael J. Swart blog, e-mailing with Michael
Darko Martinovic T-SQL script : Getting resource name from blocked process report
Darko Martinovic T-SQL script : Getting resource content from blocked process report

Register for Simple Talk

Building a Custom Blocked Process Report

Prerequisites

Source Code and Installation of the Custom Blocking Report

Blocking

Event Notification

Building Stored Procedure BPR.HandleBPR for SSB Queue Processing

Understanding the Custom Blocked Process Report

Testing with the Sample Application

Getting the Root Blocker and Finding Out What is Locked

Summary

References

Article tags

About the author

Darko Martinović

Darko's contributions

Articles

Books

Top topics

Darko's latest contributions:

How to Program with MongoDB Using the .NET Driver

Office Development in Visual Studio

Converting a Database to In-Memory OLTP

Prerequisites

Source Code and Installation of the Custom Blocking Report

Blocking

Event Notification

Building Stored Procedure BPR.HandleBPR for SSB Queue Processing

Understanding the Custom Blocked Process Report

Testing with the Sample Application

Getting the Root Blocker and Finding Out What is Locked

Summary

References

Article tags

Recommended

About the author

Darko Martinović

Darko's contributions

Articles

Books

Top topics

Darko's latest contributions:

How to Program with MongoDB Using the .NET Driver

Office Development in Visual Studio

Converting a Database to In-Memory OLTP