Tuesday, January 17, 2012

Part 3: An Introduction to SSAS Performance and SQL Sentry Performance Advisor for Analysis Services

In my last post I discussed SSAS processing. I showed you some counters that can help identify when processing is taking place, and at what rate. I then gave some suggestions as to how you might be able to optimize your processing strategies in order to improve overall performance on your server. In this post I’m going to talk about unoptimized MDX code as the source of your performance issues.
Unoptimized Code
There are quite a few resources available on how to write MDX, so I won’t be going into that here. The point I want to make is that there are ways you can identify queries running on your SSAS instance that may benefit from optimization, even without actually knowing MDX. In this section I’ll mention a handful of key performance counters that will allow you to identify issues related to query optimization. You can then take this knowledge to the developers of the queries with useful feedback as to how to improve performance.
Monitoring MDX
Specific to MDX, there are a few performance counters that can provide a lot of insight. I’ll actually group them into three general issues. The first group are:
· MDX: Total cells calculated
· MDX: Number of calculation covers
· MDX: Total Sonar subcubes
A high number for any of these metrics while a query is executing suggests the query is using cell by cell calculations instead of a block-oriented, also known as “subspace”, evaluation. So what does that mean? To stick with our SQL example from before, you can relate cell by cell calculations to using cursors in SQL. This is often an inefficient and resource intensive way of processing data. Maybe you’ve heard of the term ”RBAR” in SQL development, which stands for “Row By Agonizing Row”. Well you could call this CBAC, or “Cell By Agonizing Cell”.
The MDX performance whitepaper I mentioned in the first post in this series goes into great detail about this. There have been many enhancements to block-oriented evaluation mode since then and updated details can be found in Performance Improvements for MDX in SQL Server 2008 Analysis Services.
Another counter that can indicate serious performance issues with MDX:
· MDX: Total recomputes
This indicates errors in calculations during query execution. A non-zero value here can indicate issues where unnecessary recalculations are taking place and can even lead to a fall back to cell by cell mode in an effort to eliminate the error.
Jeffrey Wang has a great blog post that walks though some examples of where recomputes can have a dramatic impact on performance.
One more set of counters that I think gives us a good look into MDX performance:
· MDX: Total NON EMPTY unoptimized
· MDX: Total NON EMPTY for calculated members
In a nutshell, SSAS data tends to be sparsely populated data. Take data on sales orders for an example. A typical sales order is not likely to include at least one of every item available for purchase. There are likely to be a lot of empty cells for items not purchased for any given order.
You’ll remember earlier I mentioned the performance impact of cell by cell calculations. When you have sparse data, specifying a NON EMPTY algorithm can also speed up performance, but there is actually more than one code path for this algorithm. A slower code path can actually cause performance degradation. This behavior has been improved in SSAS 2008, but can still occur. The above counters can help you identify when that’s the case. More details can be found in the NON_EMPTY_BEHAVIOR section of the Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014.
Improving MDX Performance
So to wrap up this edition of the series, what are some primary strategies for optimizing MDX performance? First, proper aggregations and partitions can make a big difference, especially if the bottleneck is in the storage engine. There is tons of information on aggregation strategy that I won’t repeat here, but to reuse our analogy, aggregation strategy is similar to SQL index strategy. There is a sweet spot. Too many aggregations can be just as bad as too few. Aggregations provide pre-calculated data that can improve query performance. However, aggregations must be maintained and too many will increase the resource requirements during processing as well as storage requirements for all this extra data.
If the bottleneck appears to be in the formula engine, watch for cell by cell calculation, and eliminate empty cells and tuples using the recommended strategies in the linked whitepapers mentioned above.
So at this point you might be wondering, aside from the counters we just mentioned, how do we know where the main bottle neck might be, formula engine or storage engine? Where should we focus our performance troubleshooting? I’ll help you further pinpoint those efforts in the next post.


  1. This is just the information I am finding everywhere. Thanks for your blog, I just subscribe your blog. This is a nice blog.


  2. The blog and data is excellent and informative as well auto repair blogs forums

  3. I think this is a standout amongst the most critical data for me. What"s more, i"m happy perusing your article. Be that as it may, ought to comment on some broad things
    options trading example

  4. Great survey. I'm sure you're getting a great response.
    24 hour locksmith las vegas

  5. I am always searching online for articles that can help me. There is obviously a lot to know about this. I think you made some good points in Features also. Keep working, great job

  6. All the contents you mentioned in post is too good and can be very useful. I will keep it in mind, thanks for sharing the information keep updating, looking forward for more posts.Thanks
    real instagram likes free

  7. The material and aggregation is excellent and telltale as comfortably. Wedding journal ideas

  8. This is a fabulous post I seen because of offer it. It is really what I expected to see trust in future you will continue in sharing such a mind boggling post
    buy real instagram followers cheap

  9. I don’t think many of websites provide this type of information. mechanic film

  10. The writer is enthusiastic about purchasing wooden furniture on the web and his exploration about best wooden furniture has brought about the arrangement of this article. beautiful uncertainty pdf

  11. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. earn money online by playing games without investment

  12. Great Information sharing .. I am very happy to read this article .. thanks for giving us go through info.Fantastic nice. I appreciate this post. difference between hcg drops and shots

  13. This is such a great resource that you are providing and you give it away for free.
    elder automotive group

  14. I’ve been searching for some decent stuff on the subject and haven't had any luck up until this point, You just got a new biggest fan!..
    naturally decaffeinated tea

  15. Nice blog and absolutely outstanding. You can do something much better but i still say this perfect.Keep trying for the best.
    wedding favor ideas

  16. Great post I would like to thank you for the efforts you have made in writing this interesting and knowledgeable article.
    earn money by playing games on android

  17. This is a fantastic website and I can not recommend you guys enough.
    electric car brands

  18. Your content is nothing short of brilliant in many ways. I think this is engaging and eye-opening material. Thank you so much for caring about your content and your readers.
    hypno band does it work

  19. I’ve read some good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to create such a great informative website.
    earn money by playing games on android

  20. This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post. I will visit your blog regularly for Some latest post.
    davika hoorne biography

  21. That is very helpful for increasing my knowledge in this field.
    mobile mechanic business

  22. I would also motivate just about every person to save this web page for any favorite assistance to assist posted the appearance.
    confinement food delivery singapore

  23. Thank you so much for ding the impressive job here, everyone will surely like your post.
    special gaming features

  24. I will be interested in more similar topics. i see you got really very useful topics , i will be always checking your blog thanks
    whatsapp servers download

  25. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place..
    interracial dating definition

  26. Thanks for the best blog. it was very useful for me.keep sharing such ideas in the future as well.
    mechanic shops

  27. I don’t think many of websites provide this type of information.
    how to lose weight fast with exercise

  28. Just pure brilliance from you here. I have never expected something less than this from you and you have not disappointed me at all. I suppose you will keep the quality work going on.
    features of electric scooter

  29. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article.
    twitter follow button

  30. Great Information sharing .. I am very happy to read this article .. thanks for giving us go through info.Fantastic nice. I appreciate this post.
    wedding photographer salary

  31. What can you tell me about http://locksmith-elizabeth-nj.com/ this locksmith device?
    Did you use it before? Please tell me. Your professional thoughts are very important for my security business.

  32. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. upload to instagram from mac

  33. Hi to everybody, here everyone is sharing such knowledge, so it’s fastidious to see this site, and I used to visit this blog daily youtube to high quality mp3

  34. I am very enjoyed for this blog. Its an informative topic. It help me very much to solve some problems. Its opportunity are so fantastic and working style so speedy. convert psd to wordpress theme