Query Blocked Process Report Extended Events files

xe.blocked-process-report.query.sql

WITH cte_locks AS
(
	SELECT
		XQ.event_node.value('(@name)[1]', 'varchar(50)') AS event_name
	,	XQ.event_node.value('(@package)[1]', 'varchar(50)') AS package_name
	,	DATEADD(hh , DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), XQ.event_node.value('(@timestamp)[1]', 'datetime2')) AS [timestamp]
	,	XQ.event_node.value('(data[@name="database_name"]/value)[1]', 'sysname') as [database_name]
	,	XQ.event_node.value('(data[@name="duration"]/value)[1]', 'bigint') as [duration_μs]
	,	XQ.event_node.value('(data[@name="object_id"]/value)[1]', 'int') as [object_id]
	,	XQ.event_node.value('(data[@name="index_id"]/value)[1]', 'int') as [index_id]
	FROM	(
			SELECT	CAST(event_data AS xml) AS event_data
			FROM	sys.fn_xe_file_target_read_file('C:\Temp\blocked*.xel', NULL, NULL, NULL)
			) AS ED
	CROSS
	APPLY	event_data.nodes('event') as XQ(event_node)
)
SELECT
	L.[timestamp]
,	L.[database_name]
,	L.[duration_μs]
,	L.[duration_μs] / 1000 AS [duration_ms]
,	L.[duration_μs] / 1000000 AS [duration_sec]
,	CASE
	WHEN L.[index_id] = 0 THEN N'Clustered/Heap'
	WHEN L.[index_id] = 256 THEN N'Secondary'
	ELSE N'Unknown'
	END AS [HoBT]
,	O.[name] AS [object_name]
,	O.[type_desc] AS [object_type_desc]
FROM	cte_locks AS L
LEFT
JOIN	sys.objects AS O
  ON	L.[object_id] = O.[object_id]
ORDER BY 
	L.[timestamp]