Michael J. Swart

RSS Feed
RSS Feed
Email Me
Email Me
About Me
About Me

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 github at https://github.com/mjswart/sqlblockedprocesses:

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.

17 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

  8. […] Who’s the lead blocker in a set of blocked process reports? […]

    Pingback by Build Your Own Tools | Michael J. Swart — September 26, 2016 @ 8:54 am

  9. Thank you, Michael! It’s 2018 and it still runs like a charm.

    It literally saved my job! hahahahah

    Regards,
    Thiago

    Comment by Thiago Anitelle — February 1, 2018 @ 12:25 pm

  10. Wow! Glad to hear it Thiago!

    Comment by Michael J. Swart — February 1, 2018 @ 12:27 pm

  11. Hi Michael!

    Please, imagine the following scenario:

    1) a session (s1) opens a transaction and runs one statement (ex: UPDATE table A)
    2) the same session then executes another statement on another table (ex: SELECT from table B)
    3) another session (s2) tries to DELETE a row on table A and gets blocked
    4) a blocking event is raised showing that session s2 is being blocked by session s1
    5) despite the info about the blocked query is accurate (DELETE from table A waiting for aquiring some lock) the shows the SELECT from table B on the input buffer

    In cases like this, how would you proceed to learn what was the statement of the original blocking query?

    The closest I reached was using the waitresource info (OBJECT, PAGE or KEY), which gives me the exact resource being locked by session s1. However I need the statement executed that caused the blocking.

    Is there any way to do it besides running a Trace/xEvents?

    Thanks for your valuable time.

    Comment by Thiago Anitelle — March 5, 2018 @ 1:33 pm

  12. Hi Thiago,
    As you’ve already discovered, the blocking transaction has moved on from the particular query that locked table A.
    In my case,

    • I either deal with small transactions (in terms of number of statements) which makes discovering the first statement by inspecting the text of a procedure or batch easier.
    • Or I focus on tuning the current query. Make the SELECT from table B faster so that the transaction is faster

    But that doesn’t help you. I don’t know of any way off the top of my head (other than trace/xevents) that can indicate which query of a transaction took the lock. And I think maybe that’s the best we can do. I imagine it would be very difficult for SQL Server to track the relationship and history of the relationship between locks and queries. Especially for very large transactions.

    Comment by Michael J. Swart — March 6, 2018 @ 11:52 am

  13. Michael, thanks again for your insights and I wish a lot of success for you.

    Comment by Thiago Anitelle — March 6, 2018 @ 12:36 pm

  14. Michael,

    I am in a blocked Process overload.

    I started with WMI events and Alerts but for some reason could not get them to work. I found your Blocked Process Viewer on Brent Ozar’s page and started looking into it and I think I have missed a step and I am not sure where.

    Is there a literal step by step instructions for someone who is a little burned out (alottaBurned out) to follow to get this instituted correctly?

    Comment by Swoozie — March 9, 2018 @ 12:05 pm

  15. There’s two parts. Collecting blocked process report data and then viewing it.
    Collect the data using Jeremiah’s script:
    Finding Blocked Processes and Deadlocks using SQL Server Extended Events
    Specifically the section called Collecting Blocked Process Reports and Deadlocks Using Extended Events.
    Once you believe you should have captured something, try looking to see if anything is there by expanding the extended event session.
    Then try viewing it using the blocked process report viewer.

    You can see all of this in action in a talk I did at PASS https://youtu.be/DYVBCGy8RwY?t=22m49s

    Let me know if you still have trouble

    Comment by Michael J. Swart — March 12, 2018 @ 8:46 am

  16. dbo.sp_blocked_process_report_viewer @Type= ‘XESESSION’, @source=’system_health’

    Msg 2627, Level 14, State 1, Procedure dbo.sp_blocked_process_report_viewer, Line 156 [Batch Start Line 10]
    Violation of PRIMARY KEY constraint ‘PK__#Reports__5E3C2CB2F7AF2085’. Cannot insert duplicate key in object ‘dbo.#ReportsXML’. The duplicate key value is (86231, 67, 0).
    The statement has been terminated.

    Any idea on how to avoid this error?

    Microsoft SQL Server 2017 (RTM-CU15-GDR)

    Thanks in advance, Willem

    Comment by Willem — October 30, 2020 @ 6:46 am

  17. Msg 2627, Level 14, State 1, Procedure dbo.sp_blocked_process_report_viewer, Line 156 [Batch Start Line 10]
    Violation of PRIMARY KEY constraint ‘PK__#Reports__65843942934329’. Cannot insert duplicate key in object ‘dbo.#ReportsXML’. The duplicate key value is (xxxx, xx, x).
    The statement has been terminated.

    Any inputs on fixing the above error?

    Comment by Anil — March 17, 2023 @ 3:20 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress