Query Performance Tuning - Expert Cube Development with SSAS Multidimensional Models - page 204

• The Storage Engine : This part handles all the reading and writing of data,

for example, during cube processing and fetching all the data that the
Formula Engine requests when a query is run.

When you run an MDX query, then, that query goes first to the Formula Engine,
then to the Storage Engine, and then back to the Formula Engine before the results
are returned back to you. There are numerous opportunities for performance tuning
at all stages of this process, as we'll see.

Performance tuning methodology
When tuning performance there are certain steps you should follow to allow you
to measure the effect of any changes you make to your cube, its calculations or the
query you're running:

• Always test your queries in an environment that is identical to your
production environment, wherever possible. Otherwise, ensure that the size
of the cube and the server hardware you're running on is at least comparable,
and running the same build of Analysis Services.
• Make sure that no-one else has access to the server you're running your tests
on. You won't get reliable results if someone else starts running queries at the
same time as you.
• Make sure that the queries you're testing with are equivalent to the ones that
your users want to have tuned. As we'll see, you can use Profiler to capture
the exact queries your users are running against the cube.
• Whenever you test a query, run it twice; first on a cold cache, and then on a
warm cache. Make sure you keep a note of the time each query takes to run
and what you changed on the cube or in the query for that run.

Clearing the cache is a very important step—queries that run for a long time on a
cold cache may be instant on a warm cache. When you run a query against Analysis
Services, some or all of the results of that query (and possibly other data in the cube,
not required for the query) will be held in cache so that the next time a query is run
that requests the same data it can be answered from cache much more quickly. To
clear the cache of an Analysis Services database, you need to execute a ClearCache
XMLA command.