2
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.

  1. Select and casting target data into xml and inserting it into temp table ( where session_name = @my_target_session)
  2. 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)
  3. Check last timestamp by events and store it to use it next-time.
  4. 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

  1. Select and casting target data into xml and inserting it into temp table ( where session_name = @my_target_session)
  2. Querying event elements using selective index
  3. Select top 1 xml_data from no.2 into variable
  4. make xquery dynamically and store it into my_table (no condition, put event xml into variable and xquerying it)
  5. Check last timestamp by events and store it to use it next-time.
  6. 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.

  • 1
    Simple: Stop using SQL Server as the most expensive XML DOM parser and create a real one using any language such as C#, Java, C++, etc. – Sean says Remove Sara Chipps Aug 27 '17 at 17:36
  • @SeanGallardy yeap, I know but don't want to build something without database itself. I want to do something only with database itself. – mcdasa Aug 28 '17 at 1:00
  • So I finally decide to parse event XML in powershell. And send it into temp table. And loop into temp table and xquery it one by one Which is most efficient way at least now. – mcdasa Jan 30 '18 at 8:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.