A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad.

Collecting Blocked Process Reports and Deadlocks Using Extended Events

When you want to find blocking, you probably turn to the blocked process report. You mess around with profiler on your SQL Server 2012 box. You probably feel a little bit dirty for clunking around in that old interface, but it gets the job done.

There’s a better way… Well, there is at least a less awful way: Extended Events.

ADD EVENT sqlserver.blocked_process_report(
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',

/* Make sure this path exists before you start the trace! */

With that, you’ve created an Extended Events session to grab blocked processes and deadlocks. Why both? The blocked process report makes use of the deadlock detector. Since large amounts of blocking are frequently synonymous with deadlocking, it makes sense to grab both at the same time. There are a few other things we’ll need to do to make sure you can collect blocked processes:

EXEC sp_configure ‘show advanced options’, 1 ;
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
/* Start the Extended Events session */

At this point, you’ll be collecting the blocked process report with Extended Events. There’s no profiler session to set up, just start and stop the Extended Event session at your leisure.

Reading the Block Process Report from Extended Events

We’re saving the blocked process report to disk using Extended Events. Now what?

We need to get that blocked process data out of the Extended Events files and somewhere that we can better analyze it.

WITH events_cte AS (
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
      AS blocked_process_report,
      AS deadlock_graph
  FROM    sys.fn_xe_file_target_read_file
     null, null)
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name]
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  [duration (ms)],
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;

In this query, you read from an Extended Events session that’s being saved to disk and perform XML shredding to get client information. It isn’t a pretty query, but it does the job very well.

Viewing the Extended Events Deadlock Graphs

Extended Events deadlock graphs use a slightly different XML schema than what SSMS expects. You should see an error along the lines of “There is an error in XML document”. For folks using SQL Server 2012 and earlier, you can either parse the XML by hand or use SQL Sentry Plan Explorer.

Viewing the Extended Events Blocked Process Report

But what about the blocked process report? After all, your users are complaining about blocking, right?

Michael J. Swart has created tools to view the blocked process report. It’d be awesome if you could use it, but Michael’s blocked process report viewer uses the output of a server side trace to read blocking information. These Extended Events files are different enough that you can’t use them outright. You can, however, create a table that will let you use the blocked process report viewer:

    EndTime DATETIME,
    TextData XML,
    EventClass INT DEFAULT(137)

WITH events_cte AS (
           'datetime2')) AS [event_time] ,
            AS blocked_process_report
    FROM    sys.fn_xe_file_target_read_file
         null, null)
        CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
INSERT INTO bpr (EndTime, TextData)
FROM events_cte
WHERE blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NOT NULL
ORDER BY [event_time] DESC ;

EXEC sp_blocked_process_report_viewer @Trace='bpr', @Type='TABLE';

While you still have to read the XML yourself, this will give you a view into how deep the blocking hierarchies can go. Collecting this data with Extended Events mean that you won’t have to sit at your desk, running queries, and waiting for blocking occur.

Extended Events – Not That Hard

Extended Events aren’t difficult to use. They provide a wealth of information about SQL Server and make it easier to collect information from complex or difficult to diagnose scenarios. You really can collect as much or as little information as you want from SQL Server. When you get started, the vast majority of your work will be spent either looking up Extended Events to use or formatting the output of the queries into something meaningful.

Jeremiah Peschka
When I’m not working with databases, you’ll find me at a food truck in Portland, Oregon, or at conferences such as DevLink, Stir Trek, and OSCON. My sessions have been highly rated and I pride myself on their quality.
Jeremiah Peschka on sabtwitterJeremiah Peschka on sablinkedin
↑ Back to top
  1. Thanks for plugging the blocked process report viewer.
    I haven’t revisited the tool in a while. Extended events for blocked processes didn’t exist when I wrote the blocked process report viewer. Maybe it’s time to revisit it. If I do, I’ll let you know.

  2. Minor typo, you have:

    EXEC sp_configure ‘blocked process report’, ‘5’;

    But it should be:

    EXEC sp_configure ‘blocked_process’, ‘5’;


  3. Hello Jeremiah,

    Really useful post. I am testing it right now and will use it in a customer, who’s complaining about performance problems.

    Just a couple of minor things:

    SQL throws an error when starting the event session if the path selected for the event files does not exist. The “problem” is that you don’t see any error when creating the event session, but as I said, when starting it:

    Msg 25602, Level 17, State 22, Line 1
    The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 3: ‘The system cannot find the path specified.
    ‘ while creating the file ‘c:\myFoñder\XEventSessions\blocked_process_0_130391967440060000.xel’.

    The second question is that I think that you should add this

    sp_configure ‘show advanced options’, 1 ;

    just before

    EXEC sp_configure ‘blocked process threshold’, ‘5’;

    BTW, it works for me with this literal instead of ‘blocked_process’, in spite of what Elijah pointed out.

    Kind regards,


    • Thanks for pointing that out! I’ve added the code to show advanced options into the example. I have to admit – it’s pretty rare to encounter a SQL Server that isn’t using that setting any more. I’ll also admit to never having seen SQL Server use an underscore in an sp_configure setting. Something tells me that’s from the dark ages of SQL Server, or else it exists to give warm fuzzies to the Oracle crowd.

  4. Pingback: (SFTW) SQL Server Links 14/03/14 • John Sansom

  5. Hi

    Thanks for the post. Just want to make sure, is this valid for SQL SERVER 2008R2 SP2 as well.


    • Murali – when in doubt, try it out. 😉 It’ll take less time to give it a shot on your system than it would to post this comment, believe it or not. Enjoy the journey!

    • Hi Murali,

      I can tell you that the event sqlserver.blocked_process_report does not exist in SQL Server 2008 (R2).
      You can check for available events with this script:

      select as package, as object_name, xo.description, xo.object_type
      from sys.dm_xe_objects xo
      join sys.dm_xe_packages p
      on xo.package_guid = p.guid

      Also the event action name sqlserver.database_name produces the following error:
      Msg 25623, Level 16, State 3, Line 1
      The event action name, “sqlserver.database_name”, is invalid, or the object could not be found



  6. Great article, thanks. It’s got me started on using extended events to track down some issues that one of our clients is having with performance on their system.

    Can you recommend any resources for further reading?

    I am trying to work out how, using the sqlserver.lock_released event, I can find the table which a page or extent level lock relates. I’d have thought it would have been straightforward, and something that others would have done before, but can’t find information.


    • Hi David,

      I haven’t checked into this myself, but there’s some great information in Lock:Released documentation as well as a few tutorials over on John Huag’s blog here and here.

      Basically, object_id is your… object_id, res0 is page and res1 is the file number.

      I’d be careful setting up extended events sessions capturing locks. You could also use sp_BlitzIndex and look at the aggressive indexes or even query sys.dm_db_index_operational_stats and get two samples to help you figure out which index is having heavy locking.

      Hope that helps!

      • Hi Jeremiah,

        Thanks for the advice. sys.dm_db_index_operational_stats gives me a lot of what I need.

        I also think that I can use the TransactionID field to match page level locks with the Intent, IS, mode lock taken on the table. That gives me a clearer picture of what’s happening.



  7. Pingback: Recording Deadlocks via Extended Events and XML | :: NickBurns

  8. Hi Brent & Jeremiah,

    I am looking for an option to change the Extended event log files location. But did not see any option to change. There is anyway i can change the location.


    • I’m not entirely sure what you mean by “Extended Event log files location”.

      The system_health session is always running and it is an in memory only Extended Event session.

      For any other Extended Event session, you can specify the location using (SET filename = N'c:\temp\XEventSessions\blocked_process.xel'). Was that what you meant?

  9. MSG 25602 you will probably get it if you do have clustering in your environment and you created the path on the node where the instance is not running on. Sln : check on which node the instance is hosted and create it the path on that node. and note that the blocked_process.xel and xem are folders not files…

    hope that will help..


  10. Hi Brent,

    Really interesting post!

    When I wanted to run this on our server I received the following error when executing the first script: Msg 25641, Level 16, State 0, Line 1
    For target, “package0.asynchronous_file_target”, the parameter “filename” passed is invalid. Target parameter at index 0 is invalid

    Any ideas?


    • Hi Timothy,

      Jeremiah here (Brent didn’t write the post). If you can post full details over at and drop the link in here, we can take a look at it. An error message without the command doesn’t help you get an effective solution. Right now my guess is sunspots.

  11. I would like to create this on my system for queries running longer than 30 Seconds. Do you recommend running Extended Events for sqlserver.sql_statement_starting and sqlserver.sql_statement_Completed continuously in an OLTP system>

  12. Thanks Ladies and Gents
    Especially Jeremiah, you all rock. I see the use of extended events likened to the understanding of powershell and how it pertains to being a better DBA. If you are not investing time in learning about these two technologies then you are just coasting. These two are rock star material.
    Thanks again for aiding my learning.

  13. hi,

    how we can i check the bad system health and blocking by using system_health Extended event session?

    could please help me out on this

  14. Thank you brent. i will do a check on that

  15. This script is the bomb diggity. Yes… I said bomb diggity. The only thing that had to be changed was the location of the files – and it works like a charm. I especially like the fact it couples blocking in with the deadlocks.

  16. I got this working on 2012 when i changed the last line

    EXEC sp_blocked_process_report_viewer @Trace=’bpr’, @Type=’TABLE’;


    EXEC sp_blocked_process_report_viewer @SOURCE=’bpr’, @Type=’TABLE’;

    otherwise, with an unmodified cut& paste ,i got the error:

    Msg 201, Level 16, State 4, Procedure sp_blocked_process_report_viewer, Line 6
    Procedure or function ‘sp_blocked_process_report_viewer’ expects parameter ‘@Source’, which was not supplied.

  17. first sql text gave me this on a 2008:

    Msg 25623, Level 16, State 1, Line 1
    The event name, “sqlserver.blocked_process_report”, is invalid, or the object could not be found

    select @@version

    Microsoft SQL Server 2008 R2 (SP3) – 10.50.6220.0 (X64)

    Any idea how to fix this error?

  18. Hello Michael,
    Thank you very much for your prompt reply. 🙂

  19. Jeremiah,

    This post and one by Kendra were most helpful! In order to drill deeper into an ongoing deadlock problem, I took your query from this post and reworked it to provide more detail from the deadlock_graph and blocked_process_report XML fields.

    The primary change is an addition of two columns, [Blocked/Deadlocked] and [Blocker/Deadlocked], that are derived from the XML fields. In the case of the deadlock_graph, the blocker can be incorrect if there are multiple victims, and the selection is relying on pulling data from the second ‘process’ in the ‘process-list’.

    For the Blocked Processes, there are a lot of records that have inputbuf values like ‘Proc [Database Id = 7 Object Id = 1454628225]’. For these I added outer apply ‘functions’ to break out the ID values so that they could be returned as meaningful text using the Object_Name() functions.

    It is not perfect, but provides more info quickly, which I use in a Power Pivot workbook to drill into problems and look for patterns.

    Here is the script:

    WITH events_cte
    AS (SELECT [xevents].[event_data]
    , [xevents].[event_data].value(‘(event/@timestamp)[1]’, ‘datetime2’)) AS [event time]
    , [xevents].[event_data].value(‘(event/action[@name=”client_app_name”]/value)[1]’, ‘nvarchar(128)’) AS [client app name]
    , [xevents].[event_data].value(‘(event/action[@name=”client_hostname”]/value)[1]’, ‘nvarchar(max)’) AS [client host name]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”database_name”]/value)[1]’, ‘nvarchar(max)’) AS [database name]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”database_id”]/value)[1]’, ‘int’) AS [database_id]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”object_id”]/value)[1]’, ‘int’) AS [object_id]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”index_id”]/value)[1]’, ‘int’) AS [index_id]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”duration”]/value)[1]’, ‘bigint’)/1000 AS [duration (ms)]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”lock_mode”]/text)[1]’, ‘varchar’) AS [lock_mode]
    , [xevents].[event_data].value(‘(event[@name=”blocked_process_report”]/data[@name=”login_sid”]/value)[1]’, ‘int’) AS [login_sid]
    , [xevents].[event_data].[query](‘(event[@name=”blocked_process_report”]/data[@name=”blocked_process”]/value/blocked-process-report)[1]’) AS [blocked_process_report]
    , [xevents].[event_data].[query](‘(event/data[@name=”xml_report”]/value/deadlock)[1]’) AS deadlock_graph
    FROM [sys].[fn_xe_file_target_read_file](‘C:\temp\XEventSessions\blocked_process*.xel’, ‘C:\temp\XEventSessions\blocked_process*.xem’, NULL, NULL)
    (SELECT CAST([event_data] AS XML) AS [event_data] ) AS [xevents]

    WHEN [blocked_process_report].value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN ‘Deadlock’
    ELSE ‘Blocked Process’
    END AS [ReportType]
    , [event time]
    , CASE [client app name]
    WHEN ”
    THEN ‘ — N/A — ‘
    ELSE [client app name]
    END AS [client app _name]
    , CASE [client host name]
    WHEN ”
    THEN ‘ — N/A — ‘
    ELSE [client host name]
    END AS [client host name]
    , [database name]
    , COALESCE(OBJECT_SCHEMA_NAME([object_id], [database_id]), ‘ — N/A — ‘) AS [schema]
    , COALESCE(OBJECT_NAME([object_id], [database_id]), ‘ — N/A — ‘) AS [table]
    , [index_id]
    , [duration (ms)]
    , [lock_mode]
    , COALESCE(SUSER_NAME([login_sid]), ‘ — N/A — ‘) AS [username]
    ,case when PATINDEX(‘%PROC [[]Database Id =%’, [Obj].[Blocked] ) = 0
    then [Obj].[Blocked]
    else DB_NAME([Blocked_DB]) + ‘.’ + OBJECT_SCHEMA_NAME([Blocked_Object], [Blocked_DB]) + ‘.’ + OBJECT_NAME([Blocked_Object], [Blocked_DB])
    end as [Blocked/Deadlocked]
    ,case when PATINDEX(‘%PROC [[]Database Id =%’, [Obj].[Blocker] ) = 0
    then [Obj].[Blocker]
    else DB_NAME([Blocker_DB]) + ‘.’ + OBJECT_SCHEMA_NAME([Blocker_Object], [Blocker_DB]) + ‘.’ + OBJECT_NAME([Blocker_Object], [Blocker_DB])
    end as[Blocker/Deadlocked]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN deadlock_graph
    ELSE blocked_process_report
    END AS Report
    FROM [events_cte]
    (SELECT CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN deadlock_graph.value(‘(/deadlock/process-list/process/executionStack/frame/@procname)[1]’,’varchar(max)’)
    ELSE blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)
    END AS [Blocked]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE cast(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) + 13
    ,(PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) -1)
    – (PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) + 13)) as int)
    END AS [Blocked_DB]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE CAST(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) + 11
    , (PATINDEX(‘%]%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’))-1)
    – (PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)[1]’,’varchar(max)’)) +10 )) as int)
    END AS [Blocked_Object]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN deadlock_graph.value(‘(/deadlock/process-list/process[2]/executionStack/frame/@procname)[1]’,’varchar(max)’)
    ELSE blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)
    END AS [Blocker]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE cast(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) + 13
    ,(PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) -1)
    – (PATINDEX(‘%Database Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) + 13)) as int)
    END AS [Blocker_DB]
    ,CASE WHEN blocked_process_report.value(‘(blocked-process-report[@monitorLoop])[1]’, ‘nvarchar(max)’) IS NULL
    THEN null
    ELSE CAST(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)
    , PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) + 11
    , (PATINDEX(‘%]%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’))-1)
    – (PATINDEX(‘%object Id =%’, blocked_process_report.value(‘(blocked-process-report/blocking-process/process/inputbuf)[1]’,’varchar(max)’)) +10 )) as int)
    END AS [Blocker_Object]
    ) as [Obj]

    — where [event time] > ‘4/27/2016 1:08:10 PM’–‘2016-04-15 19:05:30.9450000’
    ORDER BY [event time] DESC;

  20. Oops. It was this article by Jes Borland that I was referring to…

  21. I have a, perhaps, silly question. It is true that the two tools you endorse: 1) SQL Sentry Plan Explorer and 2) Mr. Swart “blocked process report viewer” replace the query you provided? I’m speaking about the query you provide under the “Reading the Block Process Report from Extended Events” section.

    In other words, you provided the aforementioned query as a starting point, but the two tools, 1 & 2, above, are the best tools to use to analyze the data your extended event session definition captures?

    • Mark – to rephrase what you’re saying:

      * You need to gather deadlock data
      * You need to view deadlock data

      The tools used for those two tasks are different.

      • Brent,

        Thanks for your reply.

        I’m trying to understand Jeremiah’s article. If I understand this article, he’s saying:
        1) create an extended event to capture block and deadlock–and here’s the code
        2) use can this query to read the data we’re not collecting with our new extended event session. I’m referring to the code that’s below this statement: “We need to get that blocked process data out of the Extended Events files and somewhere that we can better analyze it.”
        3) However, just below this query, Jeremiah then has these two sections:
        Viewing the Extended Events Deadlock Graphs
        Viewing the Extended Events Blocked Process Report
        4) So it appears that the tools he describes in these two sections is a better option to the query given in 2)
        5) Is my understanding correct? I should avail myself to these two tools in lieu of his query in 2)?

Leave a Reply

Your email address will not be published. Required fields are marked *