Extended Events – łapanie blokowanych procesów II

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