Subscribing to Traces

Written by Chris Webb on 11 December 2010. Posted in MDX

Reposted from Chris Webb's blog with the author's permission.

There’s a few blog posts out there that describe how to define and run server-side traces for SSAS (such as this one) but recently I was looking into how to subscribe to a server-side trace that’s already running, and while there’s a good thread on the forum I didn’t find much other information out there on the subject. Time for a blog post, then.

First of all, you need a trace running on the server before you can subscribe to one. Luckily, you can find out what traces are running by querying the discover_traces DMV as follows:

select * from $system.discover_traces

Unless you’ve specifically turned it off, you’ll see at least one trace running on each SSAS instance: the flight recorder trace, which has the ID FlightRecorder. You can then subscribe to a trace by executing an XMLA command like this:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
  </Header>
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <Subscribe xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <Object xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
            <TraceID>FlightRecorder</TraceID>
          </Object>
        </Subscribe>
      </Command>
      <Properties>
        <PropertyList>
        </PropertyList>
      </Properties>
    </Execute>
  </Body>
</Envelope>

.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; }

Or, alternatively, anywhere you can run an MDX query (for example from an MDX query window in SQL Management Studio) you can run just the Subscribe:

<Subscribe xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <TraceID>FlightRecorder</TraceID>
 </Object>
</Subscribe>

Once you’ve executed either of the above, the command will run indefinitely until you cancel the session it’s running on (it may look like it’s hanging, but it isn’t). To cancel the session you’ll need to find its SPID, which again you can find by querying the discover_commands DMV:

select * from $system.discover_commands order by command_start_time

You can then take the SPID and execute an XMLA Cancel command like this one:

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <SPID>1887</SPID>
</Cancel>

Once you’ve done this you’ll see the data from your trace returned at last:

I had thought this might be an easy-ish way of getting trace data into PowerPivot for analysis, but unfortunately as soon as you kill the session the subscribe is running on you see an error in the PowerPivot window. But it will work in SSRS if you use an OLEDB connection and can find an easy way of killing a session from inside an SSRS report – I’m sure it’s possible, using this technique, but it won’t be elegant. To be honest, though, in most cases saving the trace to a table in SQL Server, or to a file, will be more convenient if you want to use trace data for analysis.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .

Tags: management, performance

Subscribing to Traces

Similar Articles