As I discussed in a previous tip, you can use SQL Server Profiler to monitor, troubleshoot and tune Microsoft Analysis Services (MSAS) 2005. If you only need this tool occasionally, then using SQL Profiler's graphical interface for starting, changing and stopping traces will suffice.
If you're managing numerous instances of MSAS, then the better option might be to automate your tracing by using XMLA commands. You can submit XMLA commands to Analysis Services through SQL Server Management Studio or through the ASCMD.exe utility. In this tip I'll demonstrate how to use XMLA to manage traces in SQL Server 2005 Analysis Services.
Creating, altering and deleting traces
You can use CREATE … TRACE command to start a new trace. The command allows specifying trace identifier, its name, location where the log files (with .TRC extension) will be saved, and it defines events and columns you wish to monitor with your trace. Trace identifier must be unique on the current instance of Analysis Services. You can grab the basic syntax of the statement directly from SQL Profiler. The full statement looks similar to the following:
[IMAGE] (Click here to download script.)
Note the LogFileSize and LogFileRollover tags. The former specifies the maximum size of each .trc file; the latter advises MSAS whether it should start a new file when the existing file reaches its maximum size. If LogFileRollover is set to 1, MSAS will create a new file and append a sequence number to its name, once the maximum log file size is reached. Otherwise, the trace will be quietly stopped as soon as the log file reaches its maximum size. The AutoRestart tag advises MSAS whether to start the trace each time the service is started. Although useful, ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
if you're not careful, this option could easily cause problems.
On a busy server, Analysis Services traces can grow large very quickly, particularly if you're monitoring numerous detailed events, such as a "Query Subcube Verbose" event. If you allow such trace to restart each time MSAS service is started, you could soon rut out of disk space on a drive where you store the trace files. If you advise MSAS not to restart the trace, the trace could be stopped either explicitly by issuing the DELETE command or implicitly by stopping the service. The Filter tag allows you to specify criteria for including or excluding events in the trace. For example, the sample trace above includes only those events that have duration of 100 milliseconds or more. It also excludes any events associated with a particular SQL Server Profiler trace.
Once you submit the CREATE command to Analysis Services instance, the trace will start recording events – unlike Profiler's graphical interface XMLA, which doesn't require a "start trace" command.
If you change your mind after creating your trace and want to modify the trace definition, you can exploit the ALTER command. For example, we could use the following command to set auto restart attribute to false. Keep in mind that although you might wish to change only one attribute of the trace, you must still include the rest of the tags with the ALTER statement:
[IMAGE] (Click here to download script.)
If you want to stop the trace, use the DELETE command, which has a fairly straightforward syntax – you only have to specify the identifier of a previously created trace, as shown here:
[IMAGE](Click here to download script.)
This statement stops the trace but does not delete .trc files. You can manually delete these files once you've reviewed them.
Reviewing current traces
What if you want to delete the trace you created several weeks ago with the auto restart option, but you've forgotten the identifier for this trace? Don't worry, you can run DISCOVER_TRACES command, like the following statement, to retrieve traces currently running on your analysis server:
[IMAGE](Click here to download script.)
In addition to the XSD schema (which is irrelevant for the purposes of this tip), the command above returns the following output:
[IMAGE](Click here to download script.)
This output shows three traces currently executing on the given Analysis Services instance – Flight Recorder trace, sample trace I just created and a trace started using SQL Profiler. Flight Recorder is a default trace started automatically when MSAS service starts. Using out-of-the-box configuration, this trace collects minimal troubleshooting information; if you need this trace to collect additional information, edit the flightrecordertracedef.xml file found in the "bin" folder of Analysis Services' installation directory. If you configure Analysis Services to log MDX queries into a SQL Server database, you will notice an additional internal MSAS trace created specifically for query logging. If I were to rerun the same DISCOVER_TRACES command as before, I would see the following row for the query log trace:
[IMAGE]
(Click here to download script.)
Regardless of how the trace is created, you can review its output using SQL Profiler. Once you open the trace file, you can save its contents into a SQL Server table for more detailed analysis.
Choosing appropriate events and columns to trace
How does one go about specifying appropriate event and column identifiers in CREATE or ALTER trace statements? Unfortunately, column and event identifiers aren't documented. However, as usual, we can ask our dear friend SQL Profiler to help. Start two traces within SQL Profiler, one with the default events and columns, the second one with the events and columns you wish to monitor with traces you'll create through XMLA. Don't forget to set any desired filters and order output columns to your liking. Then examine the CREATE statement that was sent to the first SQL Profiler window when you created the second trace. Indeed the effort of memorizing the column and event identifiers is unnecessary; you can simply copy and paste event and column identifiers from SQL Profiler, then adjust as you see fit within XMLA.
ABOUT THE AUTHOR:
[IMAGE]Baya Pavliashvili is a database consultant helping his customers develop highly available and scalable applications with SQL Server and Analysis Services. Throughout his career he has managed database administrator teams and databases of terabyte caliber. Baya's primary areas of expertise are performance tuning, replication and data warehousing. He can be reached at baya@bayasqlconsulting.com.
Copyright 2008 TechTarget
