SQL Server Utilities - Capture SQL Server’s Blocked Process Report event

For the TraceBlockedProcessReport stored procedure presented in this post see my Traces project at GitHub.

This is from the Microsoft’s official documentation

“The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.”

In other words SQL Server’s SQL Trace infrastructure provides a built-in out of the box option to capture blocking and contention related data that occurs in the database engine. it’s available as part of the product and all that’s left for us is to take advantage and use it.

I have been using the Blocked Process Report event for years now as part of my database maintenance policy for all client serves that we maintain, it is a part of other traces that we keep running in the background constantly, allowing for real-time Monitoring and Historical Analysis.

By default the Blocked Process Report event is turned off, see here a code example of how to activate it.

Once the the Blocked Process Report event is turned on all you need is to set a trace that captures the event data to file. This will result in having all the information that is needed to troubleshoot Blocking and Contention that takes place at the system and gives you another pixel, among others to build and produce a view of what has happened in the system at various points in time. The simplest most straight forward example would be a case where at a particular point in time users experienced a degraded performance using the application. The Blocked Process Report trace will allow you to eliminate blocking as the source to the issue or be aware that there was database contention at the time that could possibly be the reason for the issue. Being able to either eliminate Blocking as the source or on the other hand investigate Blocking if existed puts you in a different position troubleshooting the issue.

To get the TraceBlockedProcessReport stored procedure that I am using see my Traces project at GitHub.

Here is a sample execution of the stored procedure resulting in a running capturing the Blocked Process Report event data to files located at the path D:\Traces while defining a maximum number of 10 files managed in FIFO (first in first out) where each file is guaranteed not to exceed the 50 mb size.

EXEC DBA.dbo.TraceBlockedProcessReport @MaxFileSize = 50, @FileCount = 10, @Path = N’D:\Traces\TraceBlockedProcessReport’;

To have the Blocked Process Report event always running in the background and survive a service restart see this post presenting a startup stored procedure called Initialize

For other traces that I find to be beneficial see these posts:

https://sqlserverutilities.com/use-sql-trace-to-capture-database-exceptions

https://sqlserverutilities.com/use-sql-trace-to-capture-database-errors