.netsql-serverxpathextended-events

How to read extended events through the .net code


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 .


Solution

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