MDX - SQL Server Analysis Services 2012 Cube Development - page 236

• StartTime
• Current Time
• Duration
• SPID
• CPUTime
Tip
Note that not all columns are available for each event.
3. Switch to SSMS and run the following statement to clear the storage engine
cache so that your execution results are not skewed:
<Batch
xmlns="http://schemas.microsoft.com/
analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>Adventure Works
DW</DatabaseID>
</Object>
</ClearCache>
</Batch>
4. Run a few MDX queries included in this chapter.
5. Stop the Profiler trace collection and examine the results. Pay particular at-
tention to the duration column of the Query End event, as well as each
Progress Report End event.
Full discussion of query tuning is beyond the scope of this topic. However, normally,
a query bottleneck is either in the storage or calculation engine. Storage engine
queries are relatively straightforward to tune. You will need to pay attention to which
partitions are taking longest to query. You might be able to add aggregations to this
partition, or split the partition if only a small portion of it needs to be checked by
each query. Refer to Chapter 3 , Creating Analysis Services Cubes , for more inform-
ation on designing useful aggregations and partitioning strategies. The calculation