Blocked Process Detection and Analysis

Blocked process detection provides the DBA an excellent tool for improving the apparent responsiveness of SQL Server. This post provides example code for creating a blocked process Extended Events capture session. Also included is a SQL Server Agent Job to capture blocked processes on an ongoing basis. Finally, we delve into some sample queries for analyzing the captured blocked process reports.

Pretty sure they should have done blocked process detection. Wolf-Dietrich-Klebeband-Städtebilder

Not a lot of blocked process detection going on there!
    Wolf Dietrich Klebeband Städtebilder

Configuring SQL Server to report Blocked Process

The following code configures the blocked process threshold. This threshold is used by SQL Server to trigger blocked process detection. Without this threshold set up, SQL Server will never report details about what is causing blocking. Without it, you can only see processes currently being blocked via the Dynamic Management Views, such as sys.dm_exec_sessions. Configuring this setting allows the rest of our code to actually work, and captures far more detail.

The code above only modifies the threshold if it currently disabled. If you have manually set it up, or adjusted it in the past, this code won’t modify that value. By default, it sets the value at 5, which represents 5 seconds. That is, a blocked process event will be fired at most every 5 seconds. You could adjust this to a larger value, but I wouldn’t suggest making it less than 5 seconds. Depending on how much blocking is happening, values lower than 5 seconds can place a noticeable amount of load on the server.

Capturing Blocked Process Details via Extended Events

The following code will create an Extended Events session named blocked_processes. The session is configured to start at server startup, and is automatically started at the end of the script. It’s the heart of our blocked process detection system.

Creating a database to store the captured events

The following code creates a database named dba_admin where we’ll store the captured events for historical analysis purposes. The ring buffer in the Extended Events session is set to a maximum size of 2MB to avoid XML truncation. This database will allow us to capture lots of events. If you have a lot of blocking ongoing all the time, I’d recommend monitoring the size of the database. That will allow you to avoid running low on disk space.

Preserving Historical Blocked Process Details via a SQL Server Agent Job

Next, we’ll create a stored procedure in the dba_admin database that records the blocked process xml. Then we’ll create a SQL Server Agent Job to schedule capturing the events.

The stored procedure:

And the SQL Server Agent Job:

Analyzing Blocked Process Reports

Finally, the code below shows a couple of analysis queries. These queries show a couple of simple methods of “slicing-and-dicing” the Extended Events XML.

Our next post will show some sample blocking, and the results of analyzing the sample blocking.

It’s pretty common to see deadlocks occurring frequently when there is a lot of blocking. See our Deadlock Detection and Analysis post for details on how to detect and analyze them!