Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Using XEvents in SSAS 2012

with 10 comments

One of the few new features in SSAS 2012 Multidimensional is the ability to use Extended Events (XEvents) for monitoring purposes. I was, naturally, curious about how to use them but quickly found that the documentation in Books Online was completely useless – the code example given just doesn’t work. This started a number of discussions with people like Akshai Mirchandani, Nick Medveditskov, Rob Kerr, Greg Galloway, Francesco De Chirico who all helped me get to the point where I had something working, and I’m very grateful to them. This is a short summary of what I’ve learned so far; hopefully some working XMLA samples will be useful to anyone else who is researching this subject.

First of all, here’s a working XMLA command that creates an XEvent trace:

<Create xmlns=http://schemas.microsoft.com/analysisservices/2003/engine 
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xmlns:ddl2=http://schemas.microsoft.com/analysisservices/2003/engine/2
xmlns:ddl2_2=http://schemas.microsoft.com/analysisservices/2003/engine/2/2
xmlns:ddl100_100=http://schemas.microsoft.com/analysisservices/2008/engine/100/100
xmlns:ddl200_200=http://schemas.microsoft.com/analysisservices/2010/engine/200/200
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"> <ObjectDefinition> <Trace> <ID>XEvent Demo Trace</ID> <Name>XEvent Demo Trace</Name> <ddl300_300:XEvent> <event_session name="xeas" dispatchLatency="1" maxEventSize="4" maxMemory="4"
memoryPartitionMode="none" eventRetentionMode="allowSingleEventLoss"
trackCausality="true"> <event package="AS" name="DiscoverBegin" /> <event package="AS" name="DiscoverEnd" /> <event package="AS" name="QueryBegin" /> <event package="AS" name="QueryEnd" /> <event package="AS" name="CommandBegin" /> <event package="AS" name="CommandEnd" /> <event package="AS" name="LockAcquired"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockReleased"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockWaiting"> <action package="Package0" name="callstack"></action> </event> <target package="Package0" name="event_file"> <parameter name="filename" value="c:\demo.xel" /> </target> </event_session> </ddl300_300:XEvent> </Trace> </ObjectDefinition> </Create>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The important parts are the list of events, which are the same as the events that can be seen in Profiler (for a full list see here), and the target node which specifies the .xel file to output to.

Once this has been executed in SQL Server Management Studio, you can verify that the trace is running by using the following DMV:

select * from

$system.discover_traces

Here’s what the output is on my machine:

image

There are three traces shown running here on my SSAS instance and the last in the list is the XEvent trace I’ve just created.

Having run a few queries to make sure there is something in the .xel file, the trace can be stopped by executing the following XMLA:

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <TraceID>XEvent Demo Trace</TraceID>
    </Object>
</Delete>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

You can now open the .xel file specified in the original trace definition in SQL Server Management Studio, browse through it, sort, group events and so on:

image

Instead of outputting to a .xel file, it’s also possible to output to a .etl file by changing the target. Here’s an XMLA command that does this:

<Create xmlns=http://schemas.microsoft.com/analysisservices/2003/engine 
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xmlns:ddl2=http://schemas.microsoft.com/analysisservices/2003/engine/2
xmlns:ddl2_2=http://schemas.microsoft.com/analysisservices/2003/engine/2/2
xmlns:ddl100_100=http://schemas.microsoft.com/analysisservices/2008/engine/100/100
xmlns:ddl200_200=http://schemas.microsoft.com/analysisservices/2010/engine/200/200
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"> <ObjectDefinition> <Trace> <ID>XEvent Demo Trace</ID> <Name>XEvent Demo Trace</Name> <ddl300_300:XEvent> <event_session name="xeas" dispatchLatency="1" maxEventSize="4" maxMemory="4"
memoryPartitionMode="none" eventRetentionMode="allowSingleEventLoss"
trackCausality="true"> <event package="AS" name="DiscoverBegin" /> <event package="AS" name="DiscoverEnd" /> <event package="AS" name="QueryBegin" /> <event package="AS" name="QueryEnd" /> <event package="AS" name="CommandBegin" /> <event package="AS" name="CommandEnd" /> <event package="AS" name="LockAcquired"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockReleased"> <action package="Package0" name="callstack"></action> </event> <event package="AS" name="LockWaiting"> <action package="Package0" name="callstack"></action> </event> <target package="Package0" name="etw_classic_sync_target"> <parameter name="default_etw_session_logfile_path" value="c:\demo.etl" /> </target> </event_session> </ddl300_300:XEvent> </Trace> </ObjectDefinition> </Create>

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

This gives you something that can opened in Windows Performance Analyzer:

image

Written by Chris Webb

May 5, 2012 at 11:00 pm

Posted in Analysis Services

Tagged with

10 Responses

Subscribe to comments with RSS.

  1. Great post Chris. The main point of using extended events is that is has less overhead on the server being examined when tracing compared to running the same in Profiler.

    thomasivarssonmalmo

    May 6, 2012 at 7:08 pm

  2. Is there no way to define these using management studio or similar GUI as we do with SQL extended events? While the ability to get this information using the new tracing model is really great, it seems a cumbersome way to manage the traces.

    bhavikmerchant

    May 12, 2012 at 2:58 am

  3. Great post. It seems like there’s a minor bug in the sample. I had to use ” around the http-part to get it work :

    <Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine&quot; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot;..

    Bobby Henningsen

    May 21, 2012 at 3:11 pm

  4. [...] months ago, Chris Webb wrote this interesting post where you can find all the information to start to play with Extended [...]

  5. […] Webb (b | t)  has one of the only tutorials available (here) for using Extended Events with Analysis Services…and from the looks of it he had to claw his […]

  6. Chris – can the logging information be sent to a SQLSERVER datatabase table instead of a file?

    Kenneth Borrowdale

    October 23, 2013 at 11:40 pm

    • I don’t know, sorry

      Chris Webb

      October 24, 2013 at 11:21 pm

      • Hey Chris, the answer to this is no across the board with XE because that was one of the worst performing aspects of the old Profiler for SQL Trace, writing directly to a table. You’d have to read the xel file in the UI and then export the results into a table after the fact to do that.

        Jonathan Kehayias

        October 25, 2013 at 4:58 am

  7. […] Chris Webb (Blog|Twitter) – Using XEvents In SSAS 2012 […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,866 other followers

%d bloggers like this: