I had this requirement to log and read the extended events , I did achieve this through the Xpath queries and it worked as expected . Sometime has passed and now the .xel file has grown in size and the Xpath query takes long time to give back the results . I have heard there is .net code which will help to read the .xel file with more efficiency . Please help me the source code for the same .
Extended Events data can be read programmatically using QuerableXEventData from any .NET application, including PowerShell.
Below is a C# console example that extracts XE data from a trace that includes the rpc_completed
event. The constructor overload here specifies the file pattern of the XE files to process. The project includes assembly references to Microsoft.SqlServer.XE.Core
and Microsoft.SqlServer.XEvent.Linq
(located in C:\Program Files\Microsoft SQL Server\140\Shared\
on my system).
using System;
using Microsoft.SqlServer.XEvent.Linq;
namespace ExtendedEventsExample
{
class Program
{
static void Main(string[] args)
{
var xeFilePathPattern = @"C:\TraceFiles\rpc_completed*.xel";
using (var events = new QueryableXEventData(xeFilePathPattern))
{
foreach (var xe in events)
{
if (xe.Name == "rpc_completed")
{
var xeName = xe.Name;
var xeTimestamp = xe.Timestamp;
var xeStatementField = (String)xe.Fields["statement"].Value.ToString();
var xeDurationField = (UInt64)xe.Fields["duration"].Value;
var xeClientAppNameAction = (String)xe.Actions["client_app_name"].Value;
Console.WriteLine(
$"Name: {xeName}" +
$", Timestamp: {xeTimestamp}" +
$", Statement: {xeStatementField}" +
$", Duration: {xeDurationField}" +
$", ClientAppName: {xeClientAppNameAction}"
);
}
}
}
}
}
}
I've found this method to be reasonably fast even with large trace files. You might consider using rollover files to limit trace file size. Example here.