Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

15.4. Analyzing Query Performance Issues

Analysis Services 2008 has significant query optimization features. However, there are still factors that can affect query performance such as the complexity of the cube's design, aggregations, server configuration properties, hardware resources, and so on. Before you start analyzing query performance you need to understand where time is being spent during the overall execution. You already learned that there are two major components, Formula Engine (FE) and Storage Engine (SE), where the majority of the execution time is being spent. The time spent in the infrastructure component is negligible and hence we can arrive at the following equation:

MDX Query execution time = Formula Engine time + Storage Engine time

In the "SQL Server Profiler" section earlier in the chapter, you learned that query subcube events indicate requests to the SE. Hence the SE time is the duration of time spent for all the query subcube events. The overall query execution time for the query can be obtained from the SQL Server Profiler trace. The time spent by the query in the FE component is equal to the difference of total execution time minus the SE time. These relationships are expressed in the following equations:

Storage Engine time = Time needed to evaluate all query subcube events


Formula Engine time = Total query execution time - Storage Engine time

Assuming you want to analyze and optimize your query execution time, we recommend that you focus your efforts ...