Configure BlockedProcessReport in SQL Server

In this blog we will discuss on how to configure Blocked Process report on a particular database. This will be useful for troubleshooting any processes which are getting blocked for more than a specific time threshold. Even though blocking is common behavior in sql, but if the block duration is longer than expected, then we can experience performance issues. We have many ways to identify the blocking in sql (which are not going to be discussed here) but I am going to cover one method using event notifications.

By default, the “blocked process threshold” is zero, meaning that SQL Server won’t generate the blocked process reports. We need to configure by the “blocked process threshold” to a specific value (in seconds) using the sp_configure option. For example, if we set it to 15 seconds, then the event will fire three times if a session is blocked for 45 seconds.

Lets see the step by step process how to configure the blocked process report:

First the database should be enabled for service broker.

step1

We need to create QUEUE,SERVICE,ROUTE and EVENT NOTIFICATION to capture the blocking/deadlock events.

step2

step3

Please note in the above step , we have created a Event Notification at Server level , and on the database where you want to track the blocking for more than the configured value. All the above 3 steps are specific to DB level where as creating notification is at server level.  You can get the list of  already configured event notifications on your server with the below query:

SELECT * FROM sys.server_event_notifications

Now lets create a table to hold the blocking information

IF OBJECT_ID('[dbo].[BlockedProcessReports]') IS NULL
CREATE TABLE [dbo].[BlockedProcessReports]
(
blocked_process_id int IDENTITY(1,1) PRIMARY KEY,
database_name sysname,
post_time datetime,
blocked_process_report xml
);
GO

let’s create a stored procedure to read the messages receives from service broker queue  and capture the event in the above table we created:

CREATE PROCEDURE [dbo].[ProcessBlockProcessReports]
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON
DECLARE @message_body XML
,@message_type INT
,@dialog UNIQUEIDENTIFIER
,@subject VARCHAR(MAX)
,@body VARCHAR(MAX)

WHILE (1 = 1)
BEGIN
BEGIN
BEGIN TRANSACTION
-- Receive the next available message from the queue
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@message_body=CAST(message_body AS XML), -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.BlockedProcessReportQueue
), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

INSERT INTO [dbo].[BlockedProcessReports]
(
database_name
,post_time
,blocked_process_report
)
SELECT DB_NAME(CAST(@message_body AS
XML).value('(/EVENT_INSTANCE/DatabaseID)[1]',
'int'))
,CAST(@message_body AS XML).value('(/EVENT_INSTANCE/PostTime)[1]',
'datetime')
,CAST(@message_body AS XML)

END

-- Commit the transaction. At any point before this, we could roll
-- back - the received message would be back on the queue AND the response
-- wouldn't be sent.
COMMIT TRANSACTION
END;
GO

The final step is to activate the queue to call the stored procedure

ALTER QUEUE BlockedProcessReportQueue
WITH
ACTIVATION
(STATUS=ON,
PROCEDURE_NAME = [dbo].[ProcessBlockProcessReports],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);
GO

We will test this by creating a blocking scenario manually and lets check whether we can see the blocking information on the table we created:

Lets create an exclusive lock and shared locks on a table and see our table for blocking info.
create table
concurrent screen
table data
block xml.JPG

As you can see above we can see the queries which are involved in blocking from the above xml.

Hope you have enjoyed the post and share your comments

Advertisements

Dissecting Blocked-process-Report in SQL Server

Objective:
This post explains How to analyze the blocked-process-report captured using Event Notification in detail,However, this does not cover the configuration/set up of the Event notification in SQL Server.

Let us look at a typical report captured. The report has been logically divided into three sections to make our analysis simple.

First Section:
BPR_Section1

The above image shows the first section which contains
EventType – Event type of the Report which is BLOCKED_PROCESS_REPORT
PostTime – It is the date and time that the event posts
SPID – Session ID(Server Process ID) information

Second Section:
BlockedProcess

Second section contains two major tags – Blocked-process and Blocking-process.

In Blocked Process, we can first look at the waitresource attribute. Here in the above image waitresource=”KEY: 160:72057595494989824 (09dec3797d4c)”.


KEY- represents the lock on the object is at Key level

160- The second section is the database id.

		To get the database name, you can use the below query:
		Select DB_NAME(160)--DBNAME

72057595494989824- This section can be used to identify the associated object with the blocking as below.
				
		Select OBJECT_NAME(object_id) From sys.partitions where hobt_id =72057595494989824


(09dec3797d4c)	- This represents the actual data. This can be queried on the associated objects using %%lockres%% 

		Select * From <> Where %%lockres%% = '(77576a23d4db)'


Note: %%lockres%% is an undocumented virtual column to identify the key hash value. As this is an undocumented feature, Microsoft will no longer support its usage in production environment, hence no warranties.

From the above, we would be able to get the type of lock,Databasename, the associated object and data.

Further, let us look at the inputbuf tag.


Proc [Database Id = 160 Object Id = 2114374238]
Proc [Database Id = 160 Object Id = 1354487904] 

- This section provides the query/procedure information.
Select * From sys.procedures where object_id=2114374238 
Select * From sys.procedures where object_id=1354487904 

Third Section:

BPR_Section3

Finally, the third section includes other basic information like DatabaseID, TransactionID, Duration, StartTime, EndTime, ObjectID, IndexID, ServerName, Mode, LoginSID, EventSequence, IsSystem and SessionLoginName.

Hope, this post will help you to identify some important information from the blocked process report that can be used for troubleshooting your blocking/locking issues.

Have your thoughts on the same as part of our learning an sharing....Have a good day ahead!