How do we collect long-running queries via Extended Event and how do we read its data

How do we collect long-running queries via Extended Event and how do we read its data
How do we collect long-running queries via Extended Event and how do we read its data

Many times we will need to find how many and which SQL queries were executed for a long time in a period of time in SQL Server. The easiest solution with the least performance impact is to create one Extended Event.

All we need to do is create it with a T-SQL command. After it is created, it will record in the path that we have declared to it in an XML file all the queries that lasted more than 1 second.

To create the Extended Event

We must not forget to change the path to the one where we want the data to be stored.

Then we run it in a simple query window:

CREATE EVENT SESSION [CaptureDuration] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(package0.collect_cpu_cycle_time,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([package0].[greater_than_equal_uint64]([duration],(10000000)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(package0.collect_cpu_cycle_time,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([package0].[greater_than_equal_uint64]([duration],(10000000)))),
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
    ACTION(package0.collect_cpu_cycle_time,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([package0].[greater_than_equal_int64]([duration],(10000000)))) 
ADD TARGET package0.event_file(SET filename=N'D:\Xevents\longqueries_file.xel',max_file_size=(20),max_rollover_files=(50))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

alter event session CaptureDuration on server state=start;
go

How we read the results

In the path we defined during its creation XEvent a .xel and a .xem file were created. Through the dynamic view sys.fn_xe_file_target_read_file with parameters these files we result in all the XML file with the information.

But since it is impractical to read an XML by eye, the file must somehow be filtered into a more readable form.

So I sat down and made a query that does this job.

For starters, through the name we created, Xevent finds the path that is dynamically located and defines it as a parameter in the query.

Then we define the XML as one Common Table Expression. By doing SELECT we can get the value from each property we need.

In the where statement we can filter specific bases, the duration and the period of time it was executed.

This part needs attention, as the default xml timestamp is in UTC timezone, so to put the real time that the server has, we add the time difference that the server has with the UTC timezone which is done with functions (DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() )):

declare
@xel as varchar(max),
@xem as varchar(max) 

set @xel=(select 
SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel
from sys.server_event_session_fields f
inner join sys.server_event_sessions n on f.event_session_id = n.event_session_id
WHERE f.NAME = 'filename' 
and n.name like '%CaptureDuration%') --Set xevent Name

set @xem=(select 
SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xem' as xem
from sys.server_event_session_fields f
inner join sys.server_event_sessions n on f.event_session_id = n.event_session_id
WHERE f.NAME = 'filename' 
and n.name like '%CaptureDuration%')  --Set xevent Name

;WITH XEvents AS
(
select  object_name, CAST(event_data AS XML) AS A FROM sys.fn_xe_file_target_read_file(
@xel
,@xem
, NULL, NULL)
)
SELECT 
A.value ('(/event/action[@name=''database_name'']/value)[1]', 'VARCHAR(MAX)') AS DB_Name,
DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(/event/@timestamp)[1]', 'DATETIME')) AS [Time],
A.value ('(event/data[@name="duration"]/value)[1]','bigint') / 1000000  AS [Duration_Seconds],
A.value ('(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS sql_text,
--,A.value('(event/data[@name="cpu_time"]/value)[1]','bigint') /1000000 AS cpu_seconds
--,A.value('(event/data[@name="physical_reads"]/value)[1]','bigint') AS physical_reads
--,A.value('(event/data[@name="logical_reads"]/value)[1]','bigint') AS logical_reads
--,A.value('(event/data[@name="row_count"]/value)[1]','bigint') AS row_count
A   AS xml_report
From XEvents
where  1=1

and DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(/event/@timestamp)[1]', 'DATETIME'))  between '2020-05-20 10:45:00.000' and '2020-05-22 12:45:00.000'
and A.value ('(/event/action[@name=''database_name'']/value)[1]', 'VARCHAR(MAX)') ='DBATools'
and A.value('(event/data[@name="duration"]/value)[1]','bigint') / 1000000  >= 15 --sec

The result

The result of the query brought us for this period of time to the database that we asked for the queries that were running and their duration:

How do we collect long-running queries via Extended Event and how do we read its data

Accordingly, to keep the blockings, we can follow the steps listed in this the article.

Sources:

Share it

1 thought on “Πώς συλλέγουμε τα queries με μεγάλη διάρκεια μέσω Extended Event και πως διαβάζουμε τα δεδομένα του

Leave a reply