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.
We need to create QUEUE,SERVICE,ROUTE and EVENT NOTIFICATION to capture the blocking/deadlock events.
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)', 'int')) ,CAST(@message_body AS XML).value('(/EVENT_INSTANCE/PostTime)', '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.
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
2 thoughts on “Configure BlockedProcessReport in SQL Server”
Can you give me one good reason why I would choose this method and not using extended events to capture deadlocks?
This is a post introduce BlockedProcessReport, not a comparison for various other methods like extended events to capture deadlocks. I personally use extended events/error logs/query execution etc. So, it is troubleshooter’s choice.