Wednesday, January 26, 2022

 SELECT [n].[value] ('(@name)[1]', 'varchar(50)') AS [event_name]


      ,[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') AS batch_text 


       ,[n].[value] ('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_name]


      ,[n].[value] ('(@package)[1]', 'varchar(50)') AS [package_name]


      ,[n].[value] ('(@timestamp)[1]', 'datetime2') AS [utc_timestamp]


      ,[n].[value] ('(data[@name="duration"]/value)[1]', 'BIGINT') AS [duration]


      ,[n].[value] ('(data[@name="cpu_time"]/value)[1]', 'BIGINT') AS [cpu]


      ,[n].[value] ('(data[@name="physical_reads"]/value)[1]', 'BIGINT') AS [physical_reads]


      ,[n].[value] ('(data[@name="logical_reads"]/value)[1]', 'BIGINT') AS [logical_reads]


      ,[n].[value] ('(data[@name="writes"]/value)[1]', 'BIGINT') AS [writes]


      ,[n].[value] ('(data[@name="row_count"]/value)[1]', 'BIGINT') AS [row_count]


      ,[n].[value] ('(data[@name="last_row_count"]/value)[1]', 'BIGINT') AS [last_row_count]


      ,[n].[value] ('(data[@name="line_number"]/value)[1]', 'BIGINT') AS [line_number]


      ,[n].[value] ('(data[@name="offset"]/value)[1]', 'BIGINT') AS [offset]


      ,[n].[value] ('(data[@name="offset_end"]/value)[1]', 'BIGINT') AS [offset_end]


      ,[n].[value] ('(data[@name="statement"]/value)[1]', 'nvarchar(max)') AS [statement]




      ,[n].[value] ('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_name]


    FROM


       (


           SELECT CAST([fn_xe_file_target_read_file].[event_data] AS XML) AS [event_data]


               FROM [sys].fn_xe_file_target_read_file ('D:\Extended Event File Logs\LightPerformanceEvents*.xel', NULL, NULL, NULL)


       ) [ed]


        CROSS APPLY [ed].[event_data].nodes ('event') AS [q]([n])


WHERE [n].[value] ('(@timestamp)[1]', 'datetime2') > DATEADD(day, -1, GETDATE())


--AND [n].[value] ('(@name)[1]', 'varchar(50)') = 'sql_batch_completed'


--AND (


--[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') LIKE 'select Temp5.ChildLocationId%' --OR


--[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') LIKE 'FLSP_CHILDLOCATION_DASHBOARD_DYMAMIC%' or

--

--[n].[value] ('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') LIKE '%APP_LOGS%'


--)


ORDER BY utc_timestamp DESC


No comments:

Post a Comment