PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Building a Custom Blocked Process Report

Blocking in SQL Server is normal behavior, but excessive blocking can lead to many complaints and is a symptom of poor performance. SQL Server contains a vast amount of information that can help when troubleshooting performance issues like blocking, but understanding and gathering that information when you need it is a lot of work. Darko Martinovic explains his solution for a custom blocking 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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