Slowness in query of target data

• webrunner

  SSC-Dedicated

  Points: 31882

  More actions

  February 17, 2022 at 8:56 pm

  #3985097

  Hello experts,

  I'm trying to write a query to convert event session target data to a more readable SQL recordset, following this Brent Ozar example, among others:

  https://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/

  I set up an event session to capture the TLS version used for client connections based on the info here:

  https://www.sqltreeo.com/docs/find-out-which-tls-version-is-used-for-sql-server-connections

  -- Insert data into temp table.
  SELECT CAST(target_data as xml) AS targetdata
  INTO #capture_tls_data
  FROM sys.dm_xe_session_targets xet
  JOIN sys.dm_xe_sessions xes
      ON xes.address = xet.event_session_address
  WHERE xes.name = 'tls'
    AND xet.target_name = 'ring_buffer';
   
   -- Check temp table contents.
  SELECT * 
  FROM #capture_tls_data;
  
  -- Use XML functions and CROSS APPLY to convert the data.
  SELECT xed.event_data.value('(/RingBufferTarget/event/data/value)[1]', 'nvarchar(500)') AS value1,
  xed.event_data.value('(/RingBufferTarget/event/data/value)[4]', 'nvarchar(500)') AS value4
  FROM #capture_tls_data
    CROSS APPLY targetdata.nodes('//RingBufferTarget/event/data') AS xed (event_data);

  Currently the trace has around 22,000 rows when converted by that last query above, and it takes 10-15 minutes to run. I figure part of this is expected because I bet extracting XML into rows is probably inherently time consuming.

  But does anyone know of a way to optimize this?

  Thanks for any help.

  -- webrunner

   

  • This topic was modified 2 years, 10 months ago by  webrunner.
  • This topic was modified 2 years, 10 months ago by  webrunner. Reason: Fixing a typo

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  February 17, 2022 at 11:27 pm

  #3985142

  First, you're using full paths to the nodes, even though you've shredded them with the .nodes method:

  SELECT xed.event_data.value('(/RingBufferTarget/event/data/value)[1]', 'nvarchar(500)') AS value1,

  xed.event_data.value('(/RingBufferTarget/event/data/value)[4]', 'nvarchar(500)') AS value4

  FROM #capture_tls_data

  CROSS APPLY targetdata.nodes('//RingBufferTarget/event/data') AS xed (event_data);

  The query will pull the 1st and 4th /RingBufferTarget/event/data nodes in the entire document every time.  Each row in the output will be the same.

  Try this:

  WITH SSLEventDetail AS(
    SELECT xed.event_data.value('(@name)[1]', 'varchar(128)') AS EventType,
           xed.event_data.value ('(@timestamp)[1]', 'datetime') AS EventTime,
           xed.event_data.value ('(data[@name="file_name"]/value)[1]', 'varchar(512)') AS [file_name],
           xed.event_data.value ('(data[@name="function_name"]/value)[1]', 'varchar(512)') AS [function_name],
           xed.event_data.value ('(data[@name="text"]/value)[1]', 'varchar(512)') AS [result]
           --, xed.event_data.query('.') AS EventXML
      FROM #capture_tls_data
     CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed(event_data)
    )
  SELECT * FROM SSLEventDetail 
   WHERE [result] LIKE '%tls%' 
   ORDER BY EventTime asc;

  Eddie Wuerch
  MCM: SQL