Tip & Tricks – SSAS Performance Tuning Flow Chart

In the Analysis Services Performance Guide white paper, Microsoft has introduces a very handy flow chat for tuning SSAS performance.

SSAS Flow Chat 01

The first and key step of the flow chat is to investigate whether the performance problem is caused by query processor or storage engine. If the problem lies in query processor, SSAS developers need to focus on optimising MDX scripts. If the problem lies in storage engine, SSAS developers need considering to optimise dimension design, design aggregations, or use partitions to improve query performance.

The white paper has introduced a method to determine whether the problem lies in the query processor or storage engine with the help of SQL Server Profiler. Firstly, create a trace, select only query subcube verbose and filter the event on event subclass = 22 (non-cache data). Then check whether the majority of time is spent in the storage engine with long running query subcube events. If the answer is yes, there is a high possibility that the problem lies in storage engine. Otherwise, the problem lies in query processor.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s