Michael J. Swart

February 25, 2016

Look at Blocked Process Reports Collected With Extended Events

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:25 am

SQL Server Concurrency

I just met a friend at a SQL Saturday who let me know that he recognizes my name because it was attached to a project I wrote five years ago. The “Blocked Process Report Viewer”. I was impressed. I’m glad to know that it’s still used. So I decided to update it for 2016.

I’ve extended the Blocked Process Report Viewer to look at blocked process reports that were collected with extended events session.

bpr2

Where To Find It

The code for the procedure is where it always is on CodePlex at https://sqlblockedprocesses.codeplex.com/:

download

How To Use It

The viewer can consume blocked process report events captured by any extended events session as long as that session has a target of ring_buffer or event_file. For example, if you set up your extended events session the way Jeremiah Peschka did in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. Then you would use the viewer like this:

exec sp_blocked_process_report_viewer
    @Source = 'blocked_process', -- the name that Jeremiah gave to his xe session
    @Type = 'XESESSION';

which gives you something like

The value of the blocked process report viewer is that it organizes all the blocked processes into “episodes”. Each episode has a lead blocker which is the process in the front of the traffic jam. Look at that process closely.

Feedback

Let me know how it goes. Tell me if there are any errors or performance issues. I’m especially interested in the performance of the viewer when given extended events sessions that use ring_buffer targets.

7 Comments »

  1. Great! I was making use of the previous one, let’s see how the updated version works. Just one correction, on your sample, @type should be ‘XESESSION’, there is one ‘E’ missing 😉

    Comment by Yarik — February 25, 2016 @ 10:54 am

  2. Thanks Yarik, I’ve fixed it.

    Comment by Michael J. Swart — February 25, 2016 @ 11:41 am

  3. […] Michael J Swart has updated the Blocked Process Report Viewer: […]

    Pingback by Blocked Process Report Viewer – Curated SQL — February 26, 2016 @ 8:00 am

  4. Yet another great tool/tips from Michael!!

    Thanks!

    Comment by Rudy Panigas — February 29, 2016 @ 1:23 pm

  5. Thank you Rudy

    Comment by Michael J. Swart — March 1, 2016 @ 8:45 am

  6. Thanks for this very useful tool. I read about this tool before but never got chance to look into it. Today, I decided to give it a try and it was working fine. I used to run this session for longer period of time times , so value for “monitorloop” has lot of different values.

    For example , value for “monitorloop” is 218841 on “2016-01-05 09:36:52.287” while value for 1506548 is for “2016-03-21 00:44:17.727”

    “monitorloop” is defined as “nvarchar(100)” which breaks ordering. I have changed “monitorloop” column’s datatype to decimal(38,0) for now and its working fine for me now. I have checked this on Microsoft SQL Server 2014 (SP1-CU4).

    Comment by Chintak — March 21, 2016 @ 9:03 am

  7. Thanks for the feedback Chintak.
    I’ll get this in as a fix this week.
    Michael

    Comment by Michael J. Swart — March 21, 2016 @ 9:07 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress