Using XEvents in SSAS 2012

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

25 thoughts on “Using XEvents in SSAS 2012

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

      • I really don’t understand MSFT, why not integrate it in SSMS for SSAS as they have it done for the SQL engine. Same company but so inconsistent in implementing things. I feel this gets worse by each release. :(
        Thanks very much for digging in this stuff, when MSFT is dropping the ball.

  2. Great post!
    By default, the extended events generate multiple files each 1GB in size, totaling 5GB. Is there a way to control this file size? I would like smaller files being generated, making it faster to read those using sys.fn_xe_file_target_read_file on a schedule.

  3. Thanks very much for this post Chris. I found the Microsoft example yesterday and discovered how unhelpful it is for myself. With your post I’ve been able to set up a trace to get exactly what I want in about 10 minutes.

  4. Hello,
    Many thanks for this,
    I tried to run in a XMLA query window the script bellow, but I have this error coming up :
    The ddl300_300:XEvent element at line 35, column 25 (namespace http://schemas.microsoft.com/analysisservices/2008/engine/100/100) cannot appear under Envelope/Body/Execute/Command/Create/ObjectDefinition/Trace.

    any idea ? it s a namespace issue I don’t know, really, how to fix it.
    I ran the sript on a SSAS 2008R2 SP1

    thanks in advance
    gr.

  5. This post has helped me a lot, since it is still almost impossible to find information from Microsoft on this topic.
    I’ve got a question though: is it possible to specify the columns logged either globally or (preferrably) per event?
    I tried a few things along the lines of

    but none of them seem to work.

  6. Hi Chris,

    Is it be possible to have 2 XEvent trace (with different ids) running on a single

    analysis server?

    I am doing this to track SSAS cube usage stats for 2 different applications running on

    same analysis server (or say same SQLServer Analysis Services). I would explain my

    issue/problem this way:

    I’ve create a package for first application which creates XEvent trace with id

    (1234567890) to .xel files in one of ssas server drive folder. Before loading the trace

    file data it deletes the trace, moves the files to db server folder to load the data to

    sql server

    Now we are trying to create second XEvent trace with id 123456 for second application on

    the same analysis server (or say same SQLServer Analysis Services) to work in the same

    way.

    While the first XEvent trace is running, when I execute second package it deletes the

    second trace and the same can be confirmed with query :- select * from

    $system.discover_traces
    but the package is unable to move the .xel files in the second trace folder files to

    another server path as they are still being accessed by the msmdsrv.exe (sql server

    analysis services) and not releasing them.
    It is succesfully moving the files when I delete the first XEvent trace and first

    package runs fine to move the files when second trace is not running.

    Could you please help me here if its possible to have 2 XEvent trace (with different

    ids) running on a single analysis server and also suggest if there’s any other approach

    to track SSAS Cube Usage Stats?

    Thanks & Regards,
    Anvesh.

  7. Is it be possible to have 2 XEvent trace (with different ids) running on a single analysis server? I am doing this to track SSAS cube usage stats. While the first XEvent trace is running, if I delete the second trace the ssas is still accessing the 2nd trace’s .xel files and not allowing to access them. It is releasing them when I delete 1st trace.

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