(For more resources related to this topic, see here.)

Understanding how Analysis Services processes queries

We need to understand what happens inside Analysis Services when a query is run. The two major parts of the Analysis Services engine are:

• The Formula Engine: This part processes MDX queries, works out what data is needed to answer them, requests that data from the Storage Engine, and then performs all calculations needed for the query.
• 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.

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.

To do this in SQL Management Studio, open up a new XMLA query window and enter the following:

<Batch >
 <ClearCache>
 <Object>
  <DatabaseID>Adventure Works DW 2012</DatabaseID>
</Object>
 </ClearCache>
</Batch>

Remember that the ID of a database may not be the same as its name—you can check this by right-clicking on a database in the SQL Management Studio Object Explorer and selecting Properties. Alternatives to this method also exist: the MDX Studio tool allows you to clear the cache with a menu option, and the Analysis Services Stored Procedure Project (http://tinyurl.com/asstoredproc) contains code that allows you to clear the Analysis Services cache and the Windows File System cache directly from MDX. Clearing the Windows File System cache is interesting because it allows you to compare the performance of the cube on a warm and cold file system cache as well as a warm and cold Analysis Services cache. When the Analysis Services cache is cold or can’t be used for some reason, a warm filesystem cache can still have a positive impact on query performance.

After the cache has been cleared, before Analysis Services can answer a query it needs to recreate the calculated members, named sets, and other objects defined in a cube’s MDX Script. If you have any reasonably complex named set expressions that need to be evaluated, you’ll see some activity in Profiler relating to these sets being built and it’s important to be able to distinguish between this and activity that’s related to the queries you’re actually running. All MDX Script related activity occurs between Execute MDX Script Begin and Execute MDX Script End events; these are fired after the Query Begin event but before the Query Cube Begin event for the query run after the cache has been cleared and there is one pair of Begin/End events for each command on the MDX Script. When looking at a Profiler trace you should either ignore everything between the first Execute MDX Script Begin event and the last Execute MDX Script End event or run a query that returns no data at all to trigger the evaluation of the MDX Script, for example:

SELECT {} ON 0
FROM [Adventure Works]

Designing for performance

Many of the recommendations for designing cubes will improve query performance, and in fact the performance of a query is intimately linked to the design of the cube it’s running against. For example, dimension design, especially optimizing attribute relationships, can have significant effect on the performance of all queries—at least as much as any of the optimizations. As a result, we recommend that if you’ve got a poorly performing query the first thing you should do is review the design of your cube to see if there is anything you could do differently. There may well be some kind of trade-off needed between usability, manageability, time-to-develop, overall ‘elegance’ of the design and query performance, but since query performance is usually the most important consideration for your users then it will take precedence. To put it bluntly, if the queries your users want to run don’t run fast, your users will not want to use the cube at all!