Extended Events – łapanie blokowanych procesów II

Opublikowane przez Dariusz Brejnak w dniu

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.

view source
print?
001drop table IF exists  #events_cte
002 
003; WITH events_cte AS (
004 
005  SELECT
006    xevents.event_data,
007    DATEADD(mi,
008    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
009    xevents.event_data.value(
010      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
011    xevents.event_data.value(
012      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
013      AS [client app name],
014    xevents.event_data.value(
015      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
016      AS [client host name],
017    xevents.event_data.value(
018      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
019      AS [database name],
020    xevents.event_data.value(
021      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
022      AS [database_id],
023    xevents.event_data.value(
024      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
025      AS [object_id],
026    xevents.event_data.value(
027      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
028      AS [index_id],
029    xevents.event_data.value(
030      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
031      AS [duration (ms)],
032    xevents.event_data.value(
033      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
034      AS [lock_mode],
035    xevents.event_data.value(
036      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
037      AS [login_sid],
038    xevents.event_data.query(
039      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
040      AS blocked_process_report,
041    xevents.event_data.query(
042      '(event/data[@name="xml_report"]/value/deadlock)[1]')
043      AS deadlock_graph
044 
045  into #events_cte
046  FROM    sys.fn_xe_file_target_read_file
047    ('K:\XE\Capture_BlockedProcessReport*.xel',NULL, null, null)
048    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
049)
050 
051 
052SELECT CASE
053           WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL THEN 'Deadlock'
054           ELSE 'Blocked Process'
055       END                                                                 AS ReportType
056     , [event time]
057     , CASE [client app name]
058           WHEN '' THEN ' -- N/A -- '
059           ELSE [client app name]
060       END                                                                 AS [client app _name]
061     , CASE [client host name]
062           WHEN '' THEN ' -- N/A -- '
063           ELSE [client host name]
064       END                                                                 AS [client host name]
065     , [database name]
066     , COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema]
067     , COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ')        AS [table]
068     , index_id
069     , [duration (ms)]
070     , lock_mode
071     , COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ')                      AS username
072     , CASE
073           WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL THEN deadlock_graph
074           ELSE blocked_process_report
075       END                                                                 AS Report
076     , monitorLoop =                                                       blocked_process_report.value('(//@monitorLoop)[1]', 'nvarchar(100)')
077     ,
078       -- session ID
079       blocked_spid =                                                      blocked_process_report.value('(/blocked-process-report/blocked-process/process/@spid)[1]', 'int')
080     ,
081       --sub session id np. dla wątków
082       blocked_ecid =                                                      blocked_process_report.value('(/blocked-process-report/blocked-process/process/@ecid)[1]', 'int')
083     , blocked_taskpriority =                                              blocked_process_report.value('(/blocked-process-report/blocked-process/process/@taskpriority)[1]', 'int')
084     , blocked_logused =                                                   blocked_process_report.value('(/blocked-process-report/blocked-process/process/@logused)[1]', 'int')
085     , blocked_waitresource =                                              blocked_process_report.value('(/blocked-process-report/blocked-process/process/@waitresource)[1]', 'nvarchar(100)')
086     , blocked_resourcename =                                              master.dbo.GetResourceName(blocked_process_report.value('(/blocked-process-report/blocked-process/process/@waitresource)[1]', 'nvarchar(100)'), NULL)
087     , blocked_waittime =                                                  blocked_process_report.value('(/blocked-process-report/blocked-process/process/@waittime)[1]', 'int')
088     , blocked_ownerId =                                                   blocked_process_report.value('(/blocked-process-report/blocked-process/process/@ownerId)[1]', 'bigint')
089     , blocked_transactionname =                                           blocked_process_report.value('(/blocked-process-report/blocked-process/process/@transactionname)[1]', 'nvarchar(100)')
090     , blocked_lasttranstarted =                                           blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lasttranstarted )[1]', 'datetime')
091     , blocked_XDES =                                                      blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lasttranstarted )[1]', 'nvarchar(100)')
092     , blocked_lockMod =                                                   blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lockMode)[1]', 'nvarchar(100)')
093     , blocked_schedulerid =                                               blocked_process_report.value('(/blocked-process-report/blocked-process/process/@schedulerid)[1]', 'int')
094     ,
095       /*
096       Process Status – is it running, sleeping or suspended?
097       If it’s sleeping, it could be the sign that client does not work with transations correctly
098       – either did not commit one of the nested transactions or, perhaps, mixed them with UI activity.
099       Suspended status could be the sign of the blocking chain which is another story
100       */
101       blocked_status =                                                    blocked_process_report.value('(/blocked-process-report/blocked-process/process/@status)[1]', 'nvarchar(100)')
102     , blocked_priority =                                                  blocked_process_report.value('(/blocked-process-report/blocked-process/process/@priority)[1]', 'int')
103     ,
104       /*
105        
106       TranCount – if it’s more than one, it would tell us that we have nested transactions and again,
107       perhaps, client does not handle them correctly.
108       */
109       blocked_trancount =                                                 blocked_process_report.value('(/blocked-process-report/blocked-process/process/@trancount)[1]', 'int')
110     , blocked_lastbatchstarted =                                          blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lasttranstarted )[1]', 'datetime')
111     , blocked_lastbatchcompleted =                                        blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lastbatchcompleted )[1]', 'datetime')
112     , blocked_lastattention =                                             blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lastattention )[1]', 'datetime')
113     , blocked_hostpid =                                                   blocked_process_report.value('(/blocked-process-report/blocked-process/process/@hostpid)[1]', 'int')
114     , blocked_isolationlevel =                                            blocked_process_report.value('(/blocked-process-report/blocked-process/process/@isolationlevel)[1]', 'nvarchar(100)')
115     , blocked_xactid =                                                    blocked_process_report.value('(/blocked-process-report/blocked-process/process/@xactid)[1]', 'bigint')
116     , blocked_currentdbname =                                             blocked_process_report.value('(/blocked-process-report/blocked-process/process/@currentdbname)[1]', 'nvarchar(100)')
117     , blocked_lockTimeout =                                               blocked_process_report.value('(/blocked-process-report/blocked-process/process/@lockTimeout)[1]', 'bigint')
118     , blocked_clientoption1 =                                             blocked_process_report.value('(/blocked-process-report/blocked-process/process/@clientoption1)[1]', 'bigint')
119     , blocked_clientoption2 =                                             blocked_process_report.value('(/blocked-process-report/blocked-process/process/@clientoption2)[1]', 'bigint')
120     ,
121 
122       --frame
123       blocked_line =                                                      blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@line)[1]', 'int')
124     , blocked_stmtstart =                                                 blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@stmtstart)[1]', 'int')
125     , blocked_stmtend =                                                   blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@stmtend)[1]', 'int')
126     , blocked_sqlhandle =                                                 blocked_process_report.value('(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]', 'nvarchar(100)')
127     , blocked_inputbuf =                                                  blocked_process_report.value('(/blocked-process-report/blocked-process/process/inputbuf)[1]', 'nvarchar(1000)')
128     ,
129       -- ----------------------------------------------------------------------------
130       blocking_spid =                                                     blocked_process_report.value('(/blocked-process-report/blocking-process/process/@spid)[1]', 'int')
131     , blocking_ecid =                                                     blocked_process_report.value('(/blocked-process-report/blocking-process/process/@ecid)[1]', 'int')
132     , blocking_status =                                                   blocked_process_report.value('(/blocked-process-report/blocking-process/process/@status)[1]', 'nvarchar(100)')
133     , blocking_priorit =                                                  blocked_process_report.value('(/blocked-process-report/blocking-process/process/@priority)[1]', 'int')
134     , blocking_trancount =                                                blocked_process_report.value('(/blocked-process-report/blocking-process/process/@trancount)[1]', 'int')
135     , blocking_lastbatchstarted =                                         blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lastbatchstarted)[1]', 'datetime')
136     , blocking_lastbatchcompleted =                                       blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lastbatchcompleted)[1]', 'datetime')
137     , blocking_lastattention =                                            blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lastattention)[1]', 'datetime')
138     , blocking_clientapp =                                                blocked_process_report.value('(/blocked-process-report/blocking-process/process/@clientapp)[1]', 'nvarchar(100)')
139     , blocking_hostname =                                                 blocked_process_report.value('(/blocked-process-report/blocking-process/process/@hostname)[1]', 'nvarchar(100)')
140     , blocking_hostpid =                                                  blocked_process_report.value('(/blocked-process-report/blocking-process/process/@hostpid)[1]', 'int')
141     , blocking_loginname =                                                blocked_process_report.value('(/blocked-process-report/blocking-process/process/@loginname)[1]', 'nvarchar(100)')
142     , blocking_isolationlevel =                                           blocked_process_report.value('(/blocked-process-report/blocking-process/process/@isolationlevel)[1]', 'nvarchar(100)')
143     , blocking_xactid =                                                   blocked_process_report.value('(/blocked-process-report/blocking-process/process/@xactid)[1]', 'bigint')
144     , blocking_currentdbname =                                            blocked_process_report.value('(/blocked-process-report/blocking-process/process/@currentdbname)[1]', 'nvarchar(100)')
145     , blocking_lockTimeout =                                              blocked_process_report.value('(/blocked-process-report/blocking-process/process/@lockTimeout)[1]', 'bigint')
146     , blocking_clientoption1 =                                            blocked_process_report.value('(/blocked-process-report/blocking-process/process/@clientoption1)[1]', 'bigint')
147     , blocking_clientoption2 =                                            blocked_process_report.value('(/blocked-process-report/blocking-process/process/@clientoption2)[1]', 'bigint')
148     ,
149 
150       --frame
151       blocking_line =                                                     blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@line)[1]', 'int')
152     , blocking_stmtstart =                                                blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@stmtstart)[1]', 'int')
153     , blocking_stmtend =                                                  blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@stmtend)[1]', 'int')
154     , blocking_sqlhandle =                                                blocked_process_report.value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[1]', 'nvarchar(100)')
155     , blocking_inputbuf =                                                 blocked_process_report.value('(/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(1000)')
156FROM #events_cte
Kategorie: DBAMS SQL Server
Tagi:

Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl

Podobne wpisy

DBA MS SQL Server

CU 19 dla SQL Server 2022 (KB5054531)

Jest już dostępny pakiet aktualizacji zbiorczej 19 (CU19) dla Microsoft SQL Server 2022, który zawiera 13 poprawek wydanych po aktualizacji zbiorczej programu SQL Server 2022 Update 18. Aktualizuje on składniki w następujących kompilacjach: Zalecam przetestowanie #CU19 przed Dowiedz się więcej…

DBA MS SQL Server

Lista węzłów, dla których został zdefiniowany FailOver Cluster.

Skrypt oparty jest na sys.dm_os_cluster_nodes, który zwraca jeden wiersz dla każdego węzła w konfiguracji wystąpienia klastra pracy awaryjnej (Failover Cluster). Jeśli bieżące wystąpienie jest wystąpieniem klastra pracy awaryjnej (Failover Cluster), zwraca listę węzłów, w których Dowiedz się więcej…

DBA MS SQL Server

Jak przeszukać ErrorLog ?

Mamy czasem problem by przejrzeć błędy w ErrorLogu MS SQL Server. Poniżej prosty skrypt, który przeszukuje wpisy w errorlogu od danego dnia, szukając podciąg znaków (opcja).