Loading Extended Events ring buffer to SQL table using powershell

Question

I am running a RPC_Completed extended event which outputs to a ring buffer. While trying to read the data from the ring buffer target data, xQuery is taking too much of time.

The faster route was to try parse XML with powershell to a dataset and then use SQLBulkCopy to load it into a SQL Table.

I am not able to match the schema of my XML file to that of the table.

Basically I am looking for a XSD file that represents this XML file to load data into a SQL Table.

I only want timestamp, CPU time and Object name from the file.

 Create table RPCCompleted(
 time_stamp datetime2,
 obj_name varchar(50),
 cpu_time int
 );

The table i'm trying to input this into. Thank you for you time ! Cheers

https://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/ https://www.itprotoday.com/sql-server/use-powershell-load-xml-data-sql-server

<RingBufferTarget truncated="0" processingTime="0" 
 totalEventsProcessed="20" eventCount="20" droppedCount="0" 
 memoryUsed="42024">
 <event name="rpc_completed" package="sqlserver" timestamp="2022-02- 06T12:25:54.370Z">
 <data name="cpu_time">
 <type name="uint64" package="package0" />
 <value>0</value>
 </data>
 <data name="duration">
 <type name="uint64" package="package0" />
 <value>304</value>
 </data>
 <data name="physical_reads">
 <type name="uint64" package="package0" />
 <value>0</value>
 </data>
 <data name="logical_reads">
 <type name="uint64" package="package0" />
 <value>6</value>
 </data>
 <data name="writes">
 <type name="uint64" package="package0" />
 <value>0</value>
 </data>
 <data name="result">
 <type name="rpc_return_result" package="sqlserver" />
 <value>0</value>
 <text>OK</text>
 </data>
 <data name="row_count">
 <type name="uint64" package="package0" />
 <value>1</value>
 </data>
 <data name="connection_reset_option">
 <type name="connection_reset_option" package="sqlserver" />
 <value>0</value>
 <text>None</text>
 </data>
 <data name="object_name">
 <type name="unicode_string" package="package0" />
 <value>sp_executesql</value>
 </data>
 <data name="statement">
 <type name="unicode_string" package="package0" />
 <value>exec sp_executesql N'SELECT
session.name AS [Name],
''XEStore[@Name='' + quotename(CAST(SERVERPROPERTY(N''ServerName'') AS sysname),'''''''') + '']'' + ''/Session[@Name='' + quotename(session.name,'''''''') + '']'' AS [Urn],
session.startup_state AS [AutoStart],
running.create_time AS [StartTime],
(CASE WHEN (running.create_time IS NULL) THEN 0 ELSE 1 END) AS [IsRunning]
FROM
sys.server_event_sessions AS session
LEFT OUTER JOIN sys.dm_xe_sessions AS running ON running.name = session.name
WHERE
(session.name=@_msparam_0)and((CAST(SERVERPROPERTY(N''ServerName'') AS sysname)=@_msparam_1))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'Sp_Mon',@_msparam_1=N'WIN-9SOQOAAQ7AK'</value>
 </data>
 <data name="data_stream">
 <type name="binary_data" package="package0" />
 <value />
 </data>
 <data name="output_parameters">
 <type name="xml" package="package0" />
 <value />
 </data>
 <action name="nt_username" package="sqlserver">
 <type name="unicode_string" package="package0" />
 <value>WIN-9SOQOAAQ7AK\Administrator</value>
 </action>
 <action name="database_id" package="sqlserver">
 <type name="uint16" package="package0" />
 <value>1</value>
 </action>
 </event>
</RingBufferTarget>

• Charlieface: You should put that in the question, not as a comment, and please post the full <RingBufferTarget> XML. What is your table schema that you are trying to insert?
• Charlieface: And what table are trying to insert, what info do you want from the XML?
• Mobious: Thanks @Charlieface. I've been sitting on this since Saturday morning. It would be great if you could suggest something :)
• J.D.: What's your ultimate goal here?... getting the CPU time for every query running every 5 minutes? If so, there's probably simpler / more efficient ways to pull just that metric than the extended events you're using.
• Mobious: I'm trying to fetch CPU Time by a specific sproc every 5 mins. also the no of times it was executed.

Answer 1

It's unclear why XQuery was taking so long, but I can't imagine pulling the entire XML into Powershell and processing it there will be any faster.

Perhaps there was some inefficiencies with your XQuery. You should be able tor retrieve what you want with this:

WITH xe(Events) AS (
 SELECT CAST(xest.target_data as xml)
 FROM sys.dm_xe_session_targets xest 
 JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address 
 WHERE xest.target_name = 'ring_buffer' AND xes.name = 'YourSessionName' 
)
SELECT
 timestamp = x1.evnt.value('@timestamp','datetimeoffset'),
 cpu_time = x1.evnt.value('(data[@name="cpu_time"]/value/text())[1]','bigint'),
 object_name = x1.evnt.value('(data[@name="object_name"]/value/text())[1]','sysname')
FROM xe
CROSS APPLY xe.Events.nodes('RingBufferTarget/event') x1(evnt);

db<>fiddle

Output:

timestamp	cpu_time	object_name
2022年02月06日 12:25:54.3700000 +00:00	0	sp_executesql

It may alternatively be faster to store the data in a variable first:

DECLARE @Events xml = (
 SELECT TOP (1) CAST(xest.target_data as xml)
 FROM sys.dm_xe_session_targets xest 
 JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address 
 WHERE xest.target_name = 'ring_buffer' AND xes.name = 'YourSessionName' 
);
SELECT
 timestamp = x1.evnt.value('@timestamp','datetimeoffset'),
 cpu_time = x1.evnt.value('(data[@name="cpu_time"]/value/text())[1]','bigint'),
 object_name = x1.evnt.value('(data[@name="object_name"]/value/text())[1]','sysname')
FROM @Events.nodes('RingBufferTarget/event') x1(evnt);

• Mobious: Thanks but i have the same query , this will slow down as XE keeps running. If you see the second link i shared with the question powershell is timed faster. But having trouble creating the schema file for my use case.
• Charlieface: That particular code is a poor example, as it's testing with a single large file, which needs to be fully read before processing can begin, it also has an implicit conversion from varchar. Also, it does not use text() (which is faster than just implicitly converting the whole node) and has a large number of columns. Have you actually tested the above against your real data?
• Mobious: Yes i have tested it and its no good as the targetdata grows. Even for a few minute of run time. P.S. The need is to fetch this data from the ring buffer every 5 mins. So when i say its slow i mean it runs for more than 10-12 mins.
• Mikael Eriksson: @Mobious you should not parse the XML from sys.dm_xe_session_targets directly. Put the XML in a XML variable and parse that instead. DECLARE @X XML; SELECT @X = CAST(XEST.TARGET_DATA AS XML).
• Mikael Eriksson: Don’t know about the downvote. Explanation of performance impact can be found here. sql.kiwi/2012/09/… Deferred compute scalar. The xml is cast and validated for each access.