How do we collect long-running queries via Extended Event and how do we read its data
Certifications:
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 one 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:
Accordingly, to keep the blockings, we can follow the steps listed in this the article.
Its an awesome script to capture the LRQ