W pierwszej części artykułu opisywałem cały proces XE dotyczący łapania blokowanych procesów. Dodaję rozszerzone zapytanie odczytujące dane z pliku używając funkcji sys.fn_xe_file_target_read_file.
drop table IF exists #events_cte
; 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
into #events_cte
FROM sys.fn_xe_file_target_read_file
('K:\XE\Capture_BlockedProcessReport*.xel',NULL, 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
, monitorLoop = blocked_process_report.value('(//@monitorLoop)[1]', 'nvarchar(100)')
,
-- session ID
blocked_spid = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@spid)[1]', 'int')
,
--sub session id np. dla wątków
blocked_ecid = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@ecid)[1]', 'int')
, blocked_taskpriority = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@taskpriority)[1]', 'int')
, blocked_logused = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@logused)[1]', 'int')
, blocked_waitresource = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@waitresource)[1]', 'nvarchar(100)')
, blocked_resourcename = master.dbo.GetResourceName(blocked_process_report.value('(/blocked-process-report/blocked-process/process/@waitresource)[1]', 'nvarchar(100)'), NULL)
, blocked_waittime = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@waittime)[1]', 'int')
, blocked_ownerId = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@ownerId)[1]', 'bigint')
, blocked_transactionname = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@transactionname)[1]', 'nvarchar(100)')
, blocked_lasttranstarted = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lasttranstarted )[1]', 'datetime')
, blocked_XDES = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lasttranstarted )[1]', 'nvarchar(100)')
, blocked_lockMod = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lockMode)[1]', 'nvarchar(100)')
, blocked_schedulerid = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@schedulerid)[1]', 'int')
,
/*
Process Status – is it running, sleeping or suspended?
If it’s sleeping, it could be the sign that client does not work with transations correctly
– either did not commit one of the nested transactions or, perhaps, mixed them with UI activity.
Suspended status could be the sign of the blocking chain which is another story
*/
blocked_status = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@status)[1]', 'nvarchar(100)')
, blocked_priority = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@priority)[1]', 'int')
,
/*
TranCount – if it’s more than one, it would tell us that we have nested transactions and again,
perhaps, client does not handle them correctly.
*/
blocked_trancount = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@trancount)[1]', 'int')
, blocked_lastbatchstarted = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lasttranstarted )[1]', 'datetime')
, blocked_lastbatchcompleted = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lastbatchcompleted )[1]', 'datetime')
, blocked_lastattention = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lastattention )[1]', 'datetime')
, blocked_hostpid = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@hostpid)[1]', 'int')
, blocked_isolationlevel = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@isolationlevel)[1]', 'nvarchar(100)')
, blocked_xactid = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@xactid)[1]', 'bigint')
, blocked_currentdbname = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@currentdbname)[1]', 'nvarchar(100)')
, blocked_lockTimeout = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lockTimeout)[1]', 'bigint')
, blocked_clientoption1 = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@clientoption1)[1]', 'bigint')
, blocked_clientoption2 = blocked_process_report.value('(/blocked-process-report/blocked-process/process/@clientoption2)[1]', 'bigint')
,
--frame
blocked_line = blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@line)[1]', 'int')
, blocked_stmtstart = blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@stmtstart)[1]', 'int')
, blocked_stmtend = blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@stmtend)[1]', 'int')
, blocked_sqlhandle = blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]', 'nvarchar(100)')
, blocked_inputbuf = blocked_process_report.value('(/blocked-process-report/blocked-process/process/inputbuf)[1]', 'nvarchar(1000)')
,
-- ----------------------------------------------------------------------------
blocking_spid = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@spid)[1]', 'int')
, blocking_ecid = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@ecid)[1]', 'int')
, blocking_status = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@status)[1]', 'nvarchar(100)')
, blocking_priorit = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@priority)[1]', 'int')
, blocking_trancount = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@trancount)[1]', 'int')
, blocking_lastbatchstarted = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lastbatchstarted)[1]', 'datetime')
, blocking_lastbatchcompleted = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lastbatchcompleted)[1]', 'datetime')
, blocking_lastattention = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lastattention)[1]', 'datetime')
, blocking_clientapp = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@clientapp)[1]', 'nvarchar(100)')
, blocking_hostname = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@hostname)[1]', 'nvarchar(100)')
, blocking_hostpid = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@hostpid)[1]', 'int')
, blocking_loginname = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@loginname)[1]', 'nvarchar(100)')
, blocking_isolationlevel = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@isolationlevel)[1]', 'nvarchar(100)')
, blocking_xactid = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@xactid)[1]', 'bigint')
, blocking_currentdbname = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@currentdbname)[1]', 'nvarchar(100)')
, blocking_lockTimeout = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lockTimeout)[1]', 'bigint')
, blocking_clientoption1 = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@clientoption1)[1]', 'bigint')
, blocking_clientoption2 = blocked_process_report.value('(/blocked-process-report/blocking-process/process/@clientoption2)[1]', 'bigint')
,
--frame
blocking_line = blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@line)[1]', 'int')
, blocking_stmtstart = blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@stmtstart)[1]', 'int')
, blocking_stmtend = blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@stmtend)[1]', 'int')
, blocking_sqlhandle = blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[1]', 'nvarchar(100)')
, blocking_inputbuf = blocked_process_report.value('(/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(1000)')
FROM #events_cte
