Database Reference
In-Depth Information
Monitoring and tuning MDX queries
The best monitoring tool for MDX queries is
SQL Server Profiler
, which you could
launch by navigating to
Start
|
All Programs
|
SQL Server 2012
|
Performance
Tools
|
SQL Server Profiler
. Profiler records various phases of the query execution,
including the retrieval of data from partitions or dimensions, querying aggregations
or memory cache as well as displaying the results to the application. Based on the
Profiler output, we can surmise whether SSAS spends most of its execution time re-
trieving data from the storage engine or in the formula engine, meaning deriving cal-
culated values after the data retrieval is complete.
How to do it...
Follow these steps for monitoring and tuning MDX queries.
1. Once the Profiler is open, go to
File
|
New Trace
and connect to your Analysis
Services instance. Profiler allows choosing numerous events and columns
that you could include in your trace. Ensure that the
Show all Events
check-
box is checked, then choose the following events:
•
Progress Report Begin
•
Progress Report Current
•
Progress Report End
•
Query Begin
•
Query End
•
Calculate Non Empty End
•
Get Data From Aggregation
•
Get Data From Cache
•
Query Dimension
•
Query Subcube
•
Query Subcube Verbose
2. Check the following columns to be included in the trace before clicking on the
Run Trace
button:
•
Event Subclass
•
TextData
•
NTUserName
Search WWH ::
Custom Search