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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE EVENT SESSION [blocked_process] ON SERVER ADD EVENT sqlserver.blocked_process_report( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name)) , ADD EVENT sqlserver.xml_deadlock_report ( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name)) ADD TARGET package0.asynchronous_file_target (SET filename = N'c:\temp\XEventSessions\blocked_process.xel', metadatafile = N'c:\temp\XEventSessions\blocked_process.xem', max_file_size=(65536), max_rollover_files=5) WITH (MAX_DISPATCH_LATENCY = 5SECONDS) GO /* 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:
1 2 3 4 5 6 7 8 9 10 11 |
EXEC sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO /* Enabled the blocked process report */ EXEC sp_configure 'blocked process threshold', '5'; RECONFIGURE GO /* Start the Extended Events session */ ALTER EVENT SESSION [blocked_process] ON SERVER STATE = START; |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
WITH events_cte AS ( SELECT xevents.event_data, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), 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) CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents ) SELECT 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], index_id, [duration (ms)], lock_mode, 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE TABLE bpr ( EndTime DATETIME, TextData XML, EventClass INT DEFAULT(137) ); GO WITH events_cte AS ( SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [event_time] , xevents.event_data.query('(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]') AS blocked_process_report FROM sys.fn_xe_file_target_read_file ('C:\temp\XEventSessions\blocked_process*.xel', 'C:\temp\XEventSessions\blocked_process*.xem', null, null) CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents ) INSERT INTO bpr (EndTime, TextData) SELECT [event_time], blocked_process_report 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.
58 Comments. Leave new
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.
Well, It’s been a couple years, but I finally revisited the tool. The tool can now look at blocked process reports collected with extended events. Hooray
That’s great! Thanks Michael!
Minor typo, you have:
EXEC sp_configure ‘blocked process report’, ‘5’;
RECONFIGURE
GO
But it should be:
EXEC sp_configure ‘blocked_process’, ‘5’;
RECONFIGURE
GO
Regards,
EWG
Ah, thanks for that. I’ve updated it to ‘blocked process threshold’, since that’s how sp_configure reports the parameter name.
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 ;
GO
RECONFIGURE ;
GO
just before
EXEC sp_configure ‘blocked process threshold’, ‘5’;
RECONFIGURE
GO
BTW, it works for me with this literal instead of ‘blocked_process’, in spite of what Elijah pointed out.
Kind regards,
Jaime
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.
Hi
Thanks for the post. Just want to make sure, is this valid for SQL SERVER 2008R2 SP2 as well.
Thanks
Murali
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 p.name as package, xo.name 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
Regards,
Jaime
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.
thanks
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.
Cheers
David
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.
Thanks
Van
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?Thanks Jeremiah, ok, It looks like i am not explaining right. Anyway i think i found to modify the location. Actually what i am seeing is number of files in the format “servername_instance__SQLDIAG_0_1827686386 and the file type is “Extended Event log file”
While quick research found the below link and T-SQL
ALTER SERVER CONFIGURATION
SET DIAGNOSTICS LOG PATH = ‘C:\logs’;
http://msdn.microsoft.com/en-us/library/ff878700.aspx
Thanks
Ah, cool, glad you found your answer. That’s clustering, something which I know nothing about.
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..
Thanks
SK.
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?
Thx!
Timothy
Hi Timothy,
Jeremiah here (Brent didn’t write the post). If you can post full details over at dba.stackexchange.com 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.
Hi Jeremiah,
I’m experiencing the same issue and posted as indicated :
https://dba.stackexchange.com/questions/159837/cant-start-system-health-extended-events-session
Thanks in advance !
Jeremiah is blogging over here these days. Looks like you already got a pretty good answer!
Thanks!
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>
If you want to constantly do something like this, you should buy monitoring software.
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.
Aww, thanks so much! I’m glad you were able to get a lot of the post and the helpful comments. Good luck in your learning.
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
Ponraj – when you have a question that’s unrelated to the blog post you’re commenting on, try asking it over at http://DBA.StackExchange.com instead. Good luck!
Thank you brent. i will do a check on that
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.
I got this working on 2012 when i changed the last line
EXEC sp_blocked_process_report_viewer @Trace=’bpr’, @Type=’TABLE’;
into
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.
Right, I had to change the @Trace parameter to @Source. It’s a breaking change, but I made when the source no longer had to be a trace. See Look at Blocked Process Reports Collected With Extended Events.
Your change will work, but interestingly, because of the change the whole script can be substituted with
exec sp_blocked_process_report_viewer
@Source = ‘blocked_process’, — the name that Jeremiah gave to his xe session
@Type = ‘XESESSION’;
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?
Hi WW,
This is not the forum to discuss the blocked process report viewer tool. Please report any issues with to mjswart@gmail.com
or comment at http://michaeljswart.com/2016/02/look-at-blocked-process-reports-collected-with-extended-events/
or submit an issue at https://sqlblockedprocesses.codeplex.com/
Whoops never mind, I thought you were discussing a bug about the blocked process report viewer, I now understand that you’re trying to create an BPR extended events session in 2008. You need at least 2012.
Hello Michael,
Thank you very much for your prompt reply. 🙂
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]
, DATEADD([mi], DATEDIFF([mi], GETUTCDATE(), CURRENT_TIMESTAMP)
, [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)
CROSS APPLY
(SELECT CAST([event_data] AS XML) AS [event_data] ) AS [xevents]
)
SELECT 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]
, [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]
CROSS APPLY
(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;
Oops. It was this article by Jes Borland that I was referring to… https://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/
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)?
Mark – I think rather than me going on further, it’s probably best if you pick up the tools and start working with them.
I know SQL Server generates its own xel files which are recycled regularly, but don’t they contain deadlock information?
SQL Server only generates those if you ask for them.
I was able to successfully query the xml file but I can’t seem to be splitting up the blocking sql statement from the blocking sql statement. Can you please advice
SELECT BLR.event_data.value(‘(@timestamp)[1]’, ‘datetime’) AS [timestamp],
BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@hostname)[1]’, ‘nvarchar(max)’) AS Blocked_HostName,
BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@clientapp)[1]’, ‘nvarchar(max)’) AS Blocked_AppName,
BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@loginname)[1]’, ‘nvarchar(max)’) AS Blocked_LoginName,
BLR.event_data.value(‘(data[@name=”database_id”]/value)[1]’, ‘int’) AS blocked_db_id,
BLR.event_data.value(‘(data[@name=”object_id”]/value)[1]’, ‘int’) AS blocked_object_id,
BLR.event_data.value(‘(data[@name=”index_id”]/value)[1]’, ‘int’) AS blocked_index_id,
BLR.event_data.value(‘(data[@name=”lock_mode”]/text)[1]’, ‘varchar(25)’) AS lock_mode,
BLR.event_data.value(‘(//process[@id[//blocked-process/process[1]/@id]]/@waittime)[1]’, ‘nvarchar(max)’) AS waittime_insec,
BLR.event_data.value(‘(//process[@status[//blocking-process/process[1]/@status]]/@hostname)[1]’, ‘nvarchar(max)’) AS Blocking_Hostname,
BLR.event_data.value(‘(action[@name=”server_instance_name”]/value)[1]’, ‘varchar(max)’) AS blocking_InstanceName,
BLR.event_data.value(‘(//process[@status[//blocking-process/process[1]/@status]]/@clientapp)[1]’, ‘nvarchar(max)’)as blocking_AppName,
BLR.event_data.value(‘(data[@name=”database_name”]/value)[1]’, ‘varchar(25)’) blocking_db_name,
BLR.event_data.value(‘(//process[@status[//blocking-process/process[1]/@status]]/@loginname)[1]’, ‘nvarchar(max)’) AS blocking_UserName,
BLR.event_data.value(‘(action[@name=”server_principal_name”]/value)[1]’, ‘varchar(max)’) AS blocking_PrincipalUserName,
BLR.event_data.value(‘(data[@name=”blocked_process”]/value)[1]’, ‘varchar(max)’) AS blocked_process
FROM Blocking_Report br
CROSS APPLY targetdata.nodes(‘//RingBufferTarget/event’) AS BLR (event_data)
For random questions, head over to dba.stackexchange.com
Thanks!
i want to make a job which would run in every 4 hrs and output goes to .csv file on any location.
output must contain the long running queries along with process id and along with some more detailing which is useful.
Hello good folks! I’ve implemted the XE , at the same time keeping an eye on number of blocks by watching sys.dm_db_index_operational_stats. Now I see the No_Of_Blocks tick up, but nothing gets logged to the file.
I have started the Event (as I see the file getting created). But is there anything I am missing here? Thanks 🙂
Stijn – troubleshooting this is a little beyond what we can do in blog post comments. For Q&A, head on over to https://dba.stackexchange.com.
no problem, done 🙂 fingers xed
You can view dead lock reports using SSMS.
Just save the XML with a file name with an .XDL suffix.
Then just open it using SSMS.
Hello,
Is the event session stopped if the SQL Server Service restart?
Can you please tell me how to automate the event session to start without accessing the SQL Server?
—-
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;
—–
Thanks
Simply
“Is the event session stopped if the SQL Server Service restart?” – What happened when you tried?
“Can you please tell me how to automate the event session to start without accessing the SQL Server?” – google for Jobs. ( The SQLserver kind, not the Monsterboard ones.)
Let me know if it’s me or something changed.
I tried using this and got all the way to this point:
EXEC sp_blocked_process_report_viewer @Trace=’bpr’, @Type=’TABLE’;
It complained because it was expecting @Source as a parameter.
Msg 201, Level 16, State 4, Procedure sp_blocked_process_report_viewer, Line 0 [Batch Start Line 0]
Procedure or function ‘sp_blocked_process_report_viewer’ expects parameter ‘@Source’, which was not supplied.
Completion time: 2021-02-04T12:12:32.3010195-05:00
I changed it to this and it works.
EXEC sp_blocked_process_report_viewer @Source=’bpr’, @Type=’TABLE’;
Hi,
One missing point, Should we not revert/disable what we enabled to get the logs after troubleshooting?
Cheers
Hi,
I need extended events script for Azure SQL databases. Please help me on this.
I know this is an older thread, but this was epic! And to Michael J Swart, your work is amazing as well. Thank you both for saving SOOOOO much of my time.
Hi,
Any one else is getting this error?
Msg 9415, Level 16, State 1, Line 1
XML parsing: line 39, character 109, well formed check: no ‘<' in attribute value