question

PoulCJunge-4819 avatar image
0 Votes"
PoulCJunge-4819 asked dgosbell answered

Does Microsoft.SqlServer.XEvent.XELite.XELiveEventStreamer work with SSAS ring_buffer or event_stream?

I'm trying to extract extended events from a stream on a SSAS server with the following code:

 Microsoft.SqlServer.XEvent.XELite.XELiveEventStreamer xEvents = new XELiveEventStreamer("Data Source=.;Initial Catalog=master;Integrated Security=SSPI", "pju_RingBuffer");
 var cancellationToken = new CancellationTokenSource();
            
 xEvents.ReadEventStream(
             xevent =>
             {
                 Console.WriteLine(xevent.Name);
                 return Task.CompletedTask;
             },
             cancellationToken.Token
             );
            
 Console.WriteLine("Hit anykey");
 Console.ReadKey();
 cancellationToken.Cancel();

But nothing comes out.

It works for ring_buffers on a SQL server or if I save the SSAS server events to a .xel file.

A related question... Is the documentation for the Microsoft.SqlServer.XEvent.XELite package available anywhere?

sql-server-generalsql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

dgosbell avatar image
0 Votes"
dgosbell answered dgosbell commented

AMO is for modelling and administration as far as I know. Not tracking activity.

This is not correct. AMO can do modelling, administration and tracing. This is the library I use in DAX Studio for capturing server side trace events. It cannot capture XEvents, but it can capture profiler events. So any event you can capture with SQL Profiler can be captured with AMO.


One more thing... You mention the ReadMe.... I have been looking and looking for just a small piece of documentation for this package on the Nuget page.
Where did you find it?

Interesting, I did not realise at the time but when I googled for "XELiveEventStreamer examples" it took me to an older release on nuget which had a readme with example code. The following link https://www.nuget.org/packages/Microsoft.SqlServer.XEvent.XELite/2019.7.2.9# had the sample code below:



 static void OutputXELStream(string connectionString, string sessionName)
 {
     var cancellationTokenSource = new CancellationTokenSource();
    
     var xeStream = new XELiveEventStreamer(connectionString, sessionName);
    
     Console.WriteLine("Press any key to stop listening...");
     Task waitTask = Task.Run(() =>
         {
             Console.ReadKey();
             cancellationTokenSource.Cancel();
         });
    
     Task readTask = xeStream.ReadEventStream(() =>
         {
             Console.WriteLine("Connected to session");
             return Task.CompletedTask;
         },
         xevent =>
         {
             Console.WriteLine(xevent);
             Console.WriteLine("");
             return Task.CompletedTask;
         },
         cancellationTokenSource.Token);
    
    
     try
     {
         Task.WaitAny(waitTask, readTask);
     }
     catch (TaskCanceledException)
     {
     }
    
     if (readTask.IsFaulted)
     {
         Console.Error.WriteLine("Failed with: {0}", readTask.Exception);
     }
 }



· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Extended Events can capture SQL Profiler events through an Extended events session. But I suspect the AMO trace is using the old SQL profiler functionality.

I Can be wrong about that. It's not clear from the documentation.

But I'm worried about the overhead using the old functionality.


Another thing is. As far as I can tell, you are able to start and stop the traces with AMO
But I need something that can read the output from the trace.

0 Votes 0 ·

But I suspect the AMO trace is using the old SQL profiler functionality.

This is correct

But I'm worried about the overhead using the old functionality.

There is supposed to be a slightly higher overhead with profiler events vs XEvents but there is currently no .net core interface for SSAS XEvents so it's currently your only option. It depends on what events and data you capture as to what sort of overhead you will see. Some events are more expensive to capture than others.

0 Votes 0 ·

Another thing is. As far as I can tell, you are able to start and stop the traces with AMO
But I need something that can read the output from the trace.

AMO can do all this. The ASTrace sample application here https://github.com/microsoft/Analysis-Services/tree/master/AsTrace is a windows service which reads events from a SSAS trace and writes them to a SQL database. I believe that sample is still using the full framework, but I think the core event handling code will work fine in .net core

0 Votes 0 ·

I can't see where AsTRace uses AMO classes....
It's utilizing Microsoft.SqlServer.Management.Trace to extract events from the trace and into a table.

Anyways...

On top of my worries about the extra overhead from profiler events, I'm also concerned that Microsoft.SqlServer.Management.Trace is not available after SQL server 2016.

2016 is our current version. But hopefully not for long.

0 Votes 0 ·
Show more comments
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered PoulCJunge-4819 commented

I have not try this in code with SSAS , can't judge wether it work or not, I add a SQL Server General tag for this case so some community member with XEvent coding experience could see this question. Hope you don't mind.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't mind at all. Thx. :-)

0 Votes 0 ·
dgosbell avatar image
0 Votes"
dgosbell answered PoulCJunge-4819 edited

I have not seen very much documentation at all on consuming SSAS XEvents from code. I'm not sure if that XELiveEventStreamer works with SSAS, but you might be able to use something like the following example https://gist.github.com/albertospelta/358ccc134f6ceb397950719e3b857c48

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thx for your input.

QueryableXEventData is not available in .Net Core

Actually it has been completely removed from the SqlManagementObjects package.

https://github.com/microsoft/sqltoolsservice/issues/807




0 Votes 0 ·

So you had not previously mentioned that you were trying to do this with .Net core.

I tried creating a simple .net core 5 console app but I could not get your approach to work either. So then I tried using the code from the getting started section of the ReadMe on nuget https://www.nuget.org/packages/Microsoft.SqlServer.XEvent.XELite and it attempts to start but then throws TDS exceptions. TDS is the protocol used by the relational engine while SSAS used the XMLA protocol. So I believe that the XELite package only works with the Relational SQL Server engine, not with SSAS.

Your other option for tracing events in SSAS would be to use AMO to capture profiler events as there is an AMO package for .net core https://www.nuget.org/packages/Microsoft.AnalysisServices.NetCore.retail.amd64/

0 Votes 0 ·

Thx for digging further into this.

AMO is for modelling and administration as far as I know. Not tracking activity.


If you convert your reply to an answer I'll mark it as answer to my question.

Unfortunately the answer seems to be 'No' :-)

One more thing... You mention the ReadMe.... I have been looking and looking for just a small piece of documentation for this package on the Nuget page.
Where did you find it?

0 Votes 0 ·
tomerr avatar image
0 Votes"
tomerr answered

Hi,

I'm the owner of XELite. XELite works with xevents. The channel for getting the xevents into XELite is TDS, as mentioned earlier. I'm not sure what SSAS Tabular instance exposes as a channel, but if its TDS, XELite should be able to connect to it.

For the documentation, we'll fix the readme with the examples back in the next version.

Tomer

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

dgosbell avatar image
0 Votes"
dgosbell answered

@TomerRotstein-7779 thanks for chiming in here.

I'm not sure what SSAS Tabular instance exposes as a channel, but if its TDS, XELite should be able to connect to it.

Unfortunately while SSAS does expose XEvents it uses a protocol called XMLA not TDS

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.