Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Queries for Formula-Engine Bottlenecks
Tuning queries with a formula-engine bottleneck is much more difficult than handling those with a storage-engine bottleneck. For a query bottlenecked at the formula engine, executing the query a second time on warm cache will take almost the same amount of time as on the cold cache, because SSAS must do the majority of its work after data is retrieved from partitions. To clear the cache prior to executing the query, you can use a statement similar to the following:
<Batch xmlns="http:"//schemas.microsoft.com/analysisservices/2003/engine>
<ClearCache>
<Object>
<DatabaseID>database_identifier</DatabaseID>
</Object>
</ClearCache>
</Batch>
For queries bound at the formula engine, Profiler will include progress report events showing readings of many subcubes from cache. If we examine the duration column for Profiler events, we'll find that the majority of its time is spent reading subcubes instead of retrieving data from partitions. Such output is normally created for queries that have the following characteristics:
- They involve complex calculated members defined within the query or in the MDX script.
- They examine and/or return very large number of cells; for example, queries that include several nested CROSSJOIN operators.
Unfortunately, no hard-and-fast rules exist for tuning such queries. Profiler is helpful in detecting the lack of a storage-engine bottleneck. To confirm that your query is bound at the formula engine, you could also review processor utilization by using the Task Manager. The formula engine is single-threaded, so you'll only see one CPU busy while executing a query with a formula-engine bottleneck, even on multiprocessor hosts. If you're using a querying tool that allows modifying MDX, you can try to use a different set of functions and expressions to retrieve the same data faster. Examining the query to see if it brings back more cells than necessary could also be worthwhile. For example, empty cells usually add no value to the report, so you could use the NONEMPTY function to limit query output to cells that have nonempty values.