Managing Server Side Analysis Services Profiler Trace


I have been asked how to run Analysis Services Profiler Trace as Server side Trace. There are few ways of running trace on server like astrace tool. In this post, I am going to show you how you can create server side trace using xmla and stop trace.

 

First Step is to create XMLA for your profiler trace events. Here are steps:

-Open SQL Server Profiler Trace

-Connect to Analysis Services Instance you would like to capture events for.

-Choose your events

image

-Click Run and then stop trace in case you don’t want to add any overhead of running GUI.

-Now we have events in hand we want to collect.

-Next step is to export these events as XMLA file.

– Click File->Export->Script Trace Definition->For Analysis Services 2005-SQL11->Save as Profiler.XMLA

 

image

-Now we have generated script to create trace on server side. With few modification, you can specify rollover information. Quick note, although you choose rollover options in Profiler trace GUI, when you export Trace events as XMLA script, it doesn’t save rollover info to xmla file. You need to manually attributes.

-Let’s open XMLA in Management Studio first:

-Connect your Analysis Services instance through Management Studio.

-Click XMLA button shown below and drag and drop your xmla file into this window.

image

 

-You will see similar Trace definition like below:

image

 

Here are text version of attributes in case you may want to copy directly from this posting:

<ID>DBA Performance Trace</ID>

<Name>DBA Performance Trace</Name>

<!–UPDATE YOUR OUTPUT PATH!!!!!!!–>

<LogFileName>C:\OLAP_TRACE.trc</LogFileName>

<LogFileAppend>1</LogFileAppend>

<AutoRestart>0</AutoRestart>

<!–Logfilesize is in MB–>

<LogFileSize>5000</LogFileSize>

<LogFileRollover>1</LogFileRollover>

-You can add File location, rollover options just after Name attribute like below

image

-Delete following tag (first line of your script) from script as you don’t need to specify <?xml> tag in your xmla command. Delete following line from your script then execute script

image

-Once you execute script, Server Side trace is created. There are two ways of checking running traces:

1- DMVs : select * from $system.discover_traces

 

image

2-Discover Commands:

<!–By Default you will get trace for Flight Recorder–>
<!–This script will give you a list off all running traces–>
<Discover xmlns=”urn:schemas-microsoft-com:xml-analysis”>
    <RequestType>DISCOVER_TRACES</RequestType>
    <Restrictions>
        <RestrictionList>
        </RestrictionList>
    </Restrictions>
    <Properties>
        <PropertyList>
        </PropertyList>
    </Properties>
</Discover>

 

image

-Now ,we created trace and monitor. Now it is time to stop our server side trace.

Following Script stops Profiler Trace

<!–How to Drop the Trace which you had created–>

<Delete xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine” xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”>

<Object>

<TraceID>DBA Performance Trace</TraceID>

</Object>

</Delete>

image

if we look at running traces on server again, we will see that our server side trace is stopped now. You can go to path and review your trace file. Only trace you will see running is Flight Recorder for Analysis Services.

image

You can also create SQL Server Agent job and add Job Step which executes XMLA Command to manage server side trace. I have seen some customers creating these jobs to manager server side tracing for Analysis Services.

 

If you have any questions feel free to contact me

 

Thanks

 

Kagan Arca

Comments (12)

  1. Lukasz says:

    Hi Kagan,

    Is there any way to output events into csv rather than trc file?

    Thanks

  2. Kagan Arca says:

    Hi Lukasz,  Not I am aware of. however, you can save trace file as table then export from table to CSV format? If you can give more details what you are trying to achieve, may be we may look for other solutions?

    Thanks

    Kagan

  3. vk says:

    Hi Kagan,

    I am trying to automate the server side trace on analysis server.

    I have analysis services on one server and database services on one server, wanted to know how to keep a sql agent job to run XMLA. Require an Attribute for saving trace data to SQL tables.

    Thanks

  4. Hi vk – seems like saving to table option is not exposed to end users . Here is list of available attributes to specify for profiler trace: msdn.microsoft.com/…/ee301477(v=sql.105).aspx

    If you really want to store trace into database directly, you might want to use astrace in codeplex: sqlsrvanalysissrvcs.codeplex.com

    My personal opinion is, I would store them as files in file store and move over and load them to database. Reason is, if there is any connectivity problem to your sql server, this might affect your SSAS server as it wont be able log events and it may have an overhead. But on the other hand, there might be some cost around storage to store SSAS trace files. Does this help?

    Thanks

    Kagan

  5. vk says:

    Thanks for your reply…

    I will store the trace in common share..but my concern is scheduling the server side trace, as SSAS doesnt have job activity monitor to schedule, i am looking for a process where trace should run 24*7.

    My objective to run AS trace is to find out who all accessed the cubes 24*7. It will be helpfull if you provide the info

  6. First of all, I wouldn't save trace to common share directly as this may fail due to network/ connectivity issues. I would save locally and once profiler completes writing, then you can copy to share or another machine. By this way, you can capture what you want without any issues. About creating job for SSAS, you should be able create a job step which Analysis Services Command Type in your step and use code above. You can also create stop job with Analysis Services Command Type step which basically deletes traces. By this way, you can automate tracing through sql server agent job.

    Thanks

    Kagan

  7. José says:

    Hi All,

    This approach is very useful and can be used for many thing but is an important step missing. How can I import Analysis Service Trace File into a database? There are some functions in SQL but only support  SQL server trace files.

    For sure I'm not the only one with this problem. Anyway find out any approach/method to import SSAS trace files into database.

    I really appreciate some help.

    Thanks,

    José  

  8. Nigel says:

    José,

    I'm just in the process of doing the same myself so can't yet say how effective it is, but try this:

    http://www.sqlis.com/…/Trace-File-Source-Adapter.aspx

    Regards,

    Nigel

  9. Mike says:

    I downloaded the 2012 Trace File Source Adapter but its not showing up in my toolbox.  In previous versions you had to add items but you don't have that option in 2012.  I also restarted SSIS service.

  10. samyy says:

    When i run select * from $system.discover_traces in SSAS then i am able to view the running traces but creation time column shows incorrect time. how to troubleshoot this?

  11. Michal says:

    Does anybody know how to create Xmla script to save trace data to SQL table directly?

  12. marta says:

    I have a problem, how  to save analysis services trace to db table using a script as fn_trace_gettable does not work with AS trace. does anybody can help, please ?