Friday, January 13, 2012

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

In the last post, I spoke about the fact that SQL Sentry Performance Advisor for Analysis Services (PA for SSAS) “lifts the veil” from the black box known as SQL Server Analysis Services. But, there is little information available that helps someone new to the technology easily interpret all this new information in order to optimize the performance of their servers. In an effort to provide some insight into this new source of information, I wrote about the SSAS engine and how it handles incoming requests. In part 2 of this series, I want to dig deeper into the common sources of bottlenecks and what information is available to troubleshoot these issues.

What can impact MDX performance?

For the purposes of this post, I’ll break down into six categories what can impact MDX performance on your SSAS server.

Those categories are:

· Processing

· Unoptimized code


· Memory

· Disk

· Network

I’ll tackle each one by giving an explanation and refer to specific metrics, primarily performance counters, that can be used to analyze the given issue.


I mentioned in the last post that processing involves the creation and updating of cubes with new data and/or recalculating and reorganizing existing data. I related it to maintenance on SQL Server and many of the same factors you consider when you implement maintenance in SQL Server apply to processing in SSAS.

Processing primarily involves the Storage Engine (SE), which you’ll remember is multi-threaded, and can obviously be a resource intensive operation. Since processing may involve new data or recalculation of existing data, cached data will become outdated and will be flushed. This then means that the next time a query runs that requires that data, the SE will have to query the file system to retrieve needed data from the Windows cache or the disk system to replace the flushed data.  This will result in a performance hit to the query on its first run after processing. Querying while processing is occurring on the same cube can lead to blocks. Querying any cube while processing is occurring can lead to general resource contention on the server.

Monitoring Processing


So how can we tell when processing is occurring on the server? While there are many performance metrics that have varying levels of usefulness, I’ll mention a handful for each topic that will give you better insight for that topic. Don’t infer that if I don’t mention it, it’s not useful or vice versa. The point of this series is to give you a better understanding of what’s going on in SSAS and how to make better use of the information tools like SQL Sentry’s PA for SSAS provide.

· Processing: Rows written/sec tells you the rate of rows written during processing.

· Proc Aggregations: Rows created/sec more specifically tells you the rate aggregation rows are created during processing.

· Proc Indexes: Rows/sec tells the rate of rows from MOLAP files used to create indexes.

So, a non-zero value for these counters confirms that processing is taking place. These metrics can also help determine the effectiveness of any processing tuning efforts.  If your tuning efforts are effective, you should see an increase in these values accordingly.

Improving Processing Related Performance

So how do you improve performance related to processing? I mentioned earlier that you can relate SSAS processing with maintenance in SQL Server. Many of the same techniques applied in SQL Server maintenance apply here as well.

· Scheduling – If possible, schedule processing activities during time of least querying activity on the server to avoid blocks and resource contention.

· Processing Type – Use the most appropriate processing commands. Just as there are different types of backup strategies in SQL Server such as Full, Log, Differential, etc. the same applies in SSAS processing. For example, use ProcessData and ProcessIndex where appropriate instead of ProcessFull.

· Partitioning – If you’re working with Enterprise Edition, be sure to take advantage of the ability to break Measure Group data into partitions. Not every partition may need to be processed every time. Partitioning can serve to reduce the overall amount of work that is done. Remember that the SE is multi-threaded, so architect your data to take advantage of parallel processing capabilities.

For more information see the Analysis Services Processing Best Practices article in TechNet.

We’ve really just scratched the surface with the series.  In the next installment I’ll discuss identifying and understanding issues related to unoptimized MDX, and from there we’ll finish with server resource bottlenecks.  Stay tuned!


  1. This comment has been removed by the author.

  2. So you're responsible for managing a documentation project. Before jumping into the writing, it's a good idea to understand the scope of the project. useful site

  3. here Providing accounting and financial management assignment help is not a task that you should entrust to just anyone.

  4. The main aim of getting online education is to cover all skills usually and accept by the profession. If you’re a medical students then don’t worried about your assignment work. Our doctoral level writers each time available for your academic help with services and get authentic information in affordable price.more

  5. Training is a more helpful type of making an interpretation of complex data into more dependable data that can be access by anybody and with no uncertainty since questions are making things more odd to deal with.capstone services

  6. Sports board also uses education as the platform of sport. In education, sports base knowledge exists. We can write a report related to sports knowledge. Our writers have knowledge about the sport. spss service

  7. Is your business having issues because of the overflow of assorted web sites showing on the Internet? does one wish your website to be visible to clients? square measure you too busy to jot down a web log for your site? Then, this can for certain be the solution to any or all your problems. calorimetry lab report

  8. In the matured telecommunication markets, the service suppliers face stiff competition and their major challenge is client retention. Customers is maintained by correct management of overall client expertise and empowering them. reverse coding in spss

  9. Writing skills gives chance to put thoughts in to words to share with the society. Therefore, education is important for everyone to transfer and to understand the feeling, emotions, experience of each other.doctoral dissertation

  10. Inside the aged telecommunication areas, the particular program vendors confront inflexible opposition and also their particular key concern will be consumer maintenance. Consumers will be preserved simply by appropriate supervision regarding total consumer experience and also empowering these. Visit here

  11. It’s in reality a great and helpful piece of info. I’m satisfied that you simply shared this useful info with us. Please keep us up to date like this. Thank you for sharing.
    buy dissertation online

  12. This is beautiful place i really like her dress thank you so much. Sp flash tools Leapdroid Deezloader

  13. Very nice and interesting article. It`s always great to read about people who make all these useful things for us. Thank you. blockchain

  14. This is beautiful place i really like her dress thank you so much. Sp flash tools deezloader download

  15. Nice blog, thanks a lot for sharing this blog. Visit OGEN Infosystem for the best Website Designing Company and SEO Services in Delhi, India. For more information visit our website-
    Best Website Designing Company in India

  16. Great post! I really enjoyed reading it. Keep sharing such articles. Looking forward to learn more from you.
    Mobile app development company in mumbai

  17. Thanks for your sharing! The information your share is very useful to me and many people are looking for them just like me!if you are looking seo services then click here.
    seo company india
    seo services in india

  18. Thank you very much for sharing the explosion with us ... It's extremely helpful and informative.Please continue to blog new updates.

    water softener for home
    water softener

  19. Great I found informative content Keep up the good work and keep sharing awesome information like this.
    Nursing Assignment Help Online
    Macroeconomics Homework Help

  20. I am glad to read that you come up with outstanding information that definitely allows me to share with others. Thank you for sharing this with us.
    Instant Assignment Help
    Media And Communication Assignment Help

  21. Allassignment help tutorial tools who help students globally at all level academic. We work 24*7 for your convenience, so you don’t need to wait for any certain time, you can reach any time at our site, for any Assignment Help directly get into touch with our expert write college homework for me, you can concern directly with our Assignment Expert. Database Homework Help me at any time with good service.

  22. Good post. I learn something totally new and challenging on sites I stumbleupon everyday. It's always interesting to read through content from other writers and practice something from other web sites.

  23. Excellent post. Very interesting to read. I really love to read such a nice information. Thanks! keep rocking.
    Write My Assignment Ireland

  24. Assignments online globally at all level academic. We work 24*7 for your convenience, so you don’t need to wait for any certain time, you can reach any time at our site, for any Assignment Help directly get into touch with our expert write college homework for me, you can concern directly with our Assignment Expert. UK assignment help websites in uk me at any time with good hire essay helper online.

  25. Online sociology essay writing services have come up with Sociology Assignment Writing Services for sociology coursework writing service students in order for them to score straight A’s in their custom sociology research paper services.

  26. Such a very useful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article.
    Data Science Course in Pune
    Data Science Training in Pune

  27. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Analytics Course in Pune
    Data Analytics Training in Pune

  28. I am impressed by the information that you have on this blog. It shows how well you understand this subject.
    Business Analytics Course in Pune
    Business Analytics Training in Pune

  29. Dentistry research paper writing services have become very popular among dental school coursework writing service students seeking Dentistry Writing Services and dental assignment writing services.

  30. I have been searching to find a comfort or effective procedure to complete this process and I think this is the most suitable way to do it effectively.

    Data Science Course

  31. Great tips and very easy to understand. This will definitely be very useful for me when I get a chance to start my blog.

    Data Science Training

  32. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    Data Science Training Institute in Bangalore

  33. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    Best Data Science Courses in Bangalore

  34. Admiring the time and effort you put into your blog and detailed information you offer!..
    Data Science Course in Bangalore

  35. I would like to say that this blog really convinced me to do it! Thanks, very good post.
    Data Science Training in Bangalore

  36. Thumbs up guys your doing a really good job. It is the intent to provide valuable information and best practices, including an understanding of the regulatory process.
    Cyber Security Course in Bangalore

  37. I am impressed by the information that you have on this blog. Thanks for Sharing
    Ethical Hacking in Bangalore

  38. Very nice blog and articles. I am really very happy to visit your blog. Now I am found which I actually want. I check your blog everyday and try to learn something from your blog. Thank you and waiting for your new post.
    Cyber Security Training in Bangalore

  39. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    Ethical Hacking Course in Bangalore

  40. Wow! Such an amazing and helpful post this is. I really really love it. I hope that you continue to do your work like this in the future also.
    Ethical Hacking Training in Bangalore

  41. You know your projects stand out from the crowd. There is something special about them. I think they're all really cool!

    Data Science Courses

  42. I have read good things here. Definitely worth to bookmark again. I am amazed at how much effort you put into creating such an informative website.

    Data Science Certification

  43. This list of the best love quotes of all time is sure to make it easier to express your emotions and feelings of love to your partner. These famous inspirational love quotes and sayings will help you describe exactly how you are feeling with the most simple wording.

    This has been driven by recent rapid advances in data, analytics, and digital technology, which have created the perfect storm in retail Banking Decision making.

    Academic Session - the time during which a school holds classes; "they had to shorten the school term"

    This includes solar energy, clean forms of transportation, green building and water. Clean tech market today is like Internet was 20 years ago.

    Just before the annual holiday takeoff, Dasher twisted his fetlock. He landed wrong during a hotly contested reindeer game.

    I am obviously biased, but I think it is the best way to grow web traffic without SEO.

    Do you have a piece of furniture that you love, but it’s looking a little ragged? Instead of purchasing a replacement you could have it upholstered. And, it’s pretty solid business idea as well.

    Self care advise is about how you avoid becoming ill, treat common everyday illnesses and seek help when you need it as well as keeping fit and healthy.

  44. Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.

    Security Guard License
    Ontario Security License
    Security License Ontario
    Security License

    Thank you..

  45. Impressive blog to be honest definitely this post will inspire many more upcoming aspirants. Eventually, this makes the participants to experience and innovate themselves through knowledge wise by visiting this kind of a blog. Once again excellent job keep inspiring with your cool stuff.

    Artificial Intelligence training in bhilai

  46. Honestly speaking this blog is absolutely amazing in learning the subject that is building up the knowledge of every individual and enlarging to develop the skills which can be applied in to practical one. Finally, thanking the blogger to launch more further too.

    Data Science Certification in Bhilai

  47. Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    Digital Marketing Course

  48. Finally found very interesting blog with valuable information wafting for next blog update.
    Data Analytics Course Online