0

I'm trying to make scripts to import datas of extended event in sql server into tables.

following query is currently I using,

 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)

and these are my indexes, I decided to use selective index for performance and It looks like well executed by using selective index but turns out not well. unacceptable logical reads are currently occuring for this query.

well, of course hundreds of events are basically firing in production server.

CREATE SELECTIVE XML INDEX sxidx_001
ON [my_temporaly_xml_table_here] (xml)  
FOR  
(  
--eventnode =  '/RingBufferTarget/event' as xquery 'node()', 
eventdatanode =  '/RingBufferTarget/event/data' as xquery 'node()', 
eventactionnode =  '/RingBufferTarget/event/action' as xquery 'node()', 
dataname =  '/RingBufferTarget/event/data/@name' as xquery 'xs:string' singleton,
actionname =  '/RingBufferTarget/event/action/@name' as xquery 'xs:string' singleton,
timestamp=  '/RingBufferTarget/event/@timestamp' as xquery 'xs:dateTime' singleton


)

any suggestions for better performance?

mcdasa
  • 1
  • For better performance, consider using QueryableXEventData via Powershell instead of parsing XML in T-SQL. https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.xevent.linq.queryablexeventdata.aspx. If that will work for you, I'll post an example. – Dan Guzman Aug 22 '17 at 12:04
  • @DanGuzman why bit to use it it shows an better performance! can you gimme an sinple example? and one question is that this class works on powershell 2.0 too? – mcdasa Aug 22 '17 at 15:10

0 Answers0