Manage traces in SQL Server 2005 Analysis Services with XMLA commands
Baya Pavliashvili, Contributor
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,
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in July 2008
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:
(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, 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
 |
| More tips on Analysis Services and SQL Profiler: |
|
|
|
|
|
|
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:
(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:
(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:
(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:
(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:
(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
Baya Pavliashvili is a DBA manager with HealthStream, the leader in online healthcare
education. In this role, he oversees database operations supporting over one million users. His
primary areas of expertise include performance tuning, replication and data warehousing. You can
reach Pavliashvili at [email protected].
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation