Way to import extended event data into table efficiently

Question

create event session [my_xevent_session] on server
add 
event sqlserver.rpc_completed(
    set   
        collect_statement = 1  
        --collect_output_parameters = 1
    action (
        sqlserver.client_app_name,sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.server_principal_name
    )  
    where sqlserver.is_system = 0
    AND [duration] > 100000
    and sqlserver.database_id > 4
),
add
event sqlserver.sql_batch_completed(
    set   
        collect_batch_text = 1
    action (
        sqlserver.client_app_name,sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.nt_username,  
        sqlserver.server_principal_name,
        sqlserver.sql_text 
    )  
    where  sqlserver.is_system = 0 
    AND [duration] > 100000
    and sqlserver.database_id > 4
)
add target package0.ring_buffer  (SET max_events_limit=(4000),max_memory=(4096)),
add target package0.event_file (
    set 
        filename    = N'MyFileName',
        max_file_size       = 2,        
        max_rollover_files  = 500,  
        increment       = 1 

) with (
    EVENT_RETENTION_MODE    = ALLOW_MULTIPLE_EVENT_LOSS,
    MEMORY_PARTITION_MODE   = NONE,     
    MAX_DISPATCH_LATENCY    = 5 SECONDS,
    TRACK_CAUSALITY     = ON,
    STARTUP_STATE       = ON
)

GO

Hello, I'm making monitoring system using extended events. Process is simple, 1. Creating Extended Events Session 1.1. One Target is ring_buffer to import 1.2. Second Target is xelfile to backup, and not to lose data if ring_buffer is under pressure. 2. Make Agent Job to Import 1.1

and the problem is 1.1. I originally found xquery is kinda heavy to control. and decided to use temporaly table. so the import process is following.

Select and casting target data into xml and inserting it into temp table ( where session_name = @my_target_session)
make xquery dynamically and store it into my_table(I created tables by the events) (where lasttimestamp > @lasttimestamp, didn't use xquery, using condition outside of xquery)
Check last timestamp by events and store it to use it next-time.
and-so-on

and! this is amazing,. I monitored few days and realize xquery occuring 4gb of blob logical reads. My monitor script could kill normal DB server! wow! I can't let that happen! and made primary xml index found out it's not helping at all. also learnt about selective index but not helping at all.

CREATE SELECTIVE XML INDEX [my_selective_index] ON [dbo].[my_temp_table]
(
    [target_xml]
)
FOR
(
    [eventnode] = '/RingBufferTarget/event' as XQUERY 'node()', 
    [eventname] = '/RingBufferTarget/event/@name' as XQUERY 'xs:string' SINGLETON, 
    [timestamp] = '/RingBufferTarget/event/@timestamp' as XQUERY 'xs:dateTime' SINGLETON
)
GO

so I googling few days also found out Paul Randal's great descripting of xquerying.

Optimising plans with XML readers

I understood this article as that my xquery has to many scalar function(which is converting xml into nvarchar) so overhead is not avoidable.

select getdate() as [check_date], 
 xml.value('(./@name)[1]','nvarchar(60)') as [event_name], 
 dateadd(hour, 9, xml.value('(./@timestamp)[1]', 'datetime')) AS [timestamp], 
 xml.value('(data[@name="wait_type"]/text)[1]','nvarchar(max)') as [wait_type], 
 xml.value('(data[@name="opcode"]/text)[1]','nvarchar(max)') as [opcode], 
 xml.value('(data[@name="duration"]/value)[1]','bigint') as [duration], 
 xml.value('(data[@name="signal_duration"]/value)[1]','bigint') as [signal_duration], 
 xml.value('(data[@name="wait_resource"]/value)[1]','nvarchar(max)') as [wait_resource], 
 xml.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') as [client_app_name],
 xml.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') as [client_hostname], 
 xml.value('(action[@name="database_id"]/value)[1]', 'int') as [database_id], 
 xml.value('(action[@name="server_principal_name"]/value)[1]', 'nvarchar(max)') as [server_principal_name], 
 xml.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as [sql_text], 
 xml.value('(action[@name="attach_activity_id_xfer"]/value)[1]', 'char(36)') as [attach_activity_id_xfer], 
 xml.value('(action[@name="attach_activity_id"]/value)[1]', 'char(36)') as [attach_activity_id_guid] , 
 substring(reverse(xml.value('(action[@name="attach_activity_id"]/value)[1]', 'nvarchar(max)')), 0,charindex('-', reverse(xml.value('(action[@name="attach_activity_id"]/value)[1]', 'nvarchar(100)')))) as [attach_activity_id_seq]   
 from [my_temporaly_xml_table_here] with (nolock)  
 CROSS APPLY target_xml.nodes('/RingBufferTarget/event[  @name=sql:variable("@p_event_name") and @timestamp > sql:variable("@p_lasttimestamp") ]') AS x(xml)

So I changed my plan as solution in link, let's use a variable and xquerying events data one by one. I made one more temporary table and add more steps

Select and casting target data into xml and inserting it into temp table ( where session_name = @my_target_session)
Querying event elements using selective index
Select top 1 xml_data from no.2 into variable
make xquery dynamically and store it into my_table (no condition, put event xml into variable and xquerying it)
Check last timestamp by events and store it to use it next-time.
and-so-on

Query using on no.2 is following.

SELECT xml.query('.') 
FROM dbo.my_second_temp_table xt WITH (nolock) 
CROSS APPLY target_xml.nodes('/RingBufferTarget/event[@name=sql:variable("@p_event_name") and @timestamp > sql:variable("@p_lasttimestamp")]') AS x(xml);

and this decreased logical read from 4Gb to 600-700MB which is amazing(Thank you Paul!) but I think not enough. I could use it but want to know that if there is any idea to save more memory.

Way to import extended event data into table efficiently

0 Answers0