Tuesday, January 10, 2012

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

Over the past couple years, since SQL Sentry introduced Performance Advisor for Analysis Services, I’ve heard many people say they see SSAS as a “black box” on their servers with no real visibility into troubleshooting performance issues. Performance Advisor (PA) lifts the shroud from Analysis Services, but for some it may not be obvious how to interpret all the information gained from this new visibility.

There are many resources out there that cover OLAP architecture and MDX, but much less in the way of an explanation to all the various metrics that are available and how they correlate with each other. In response, I developed a presentation I’ve given several times now at SQL Saturdays that I believe serves to “bridge the gap” for DBA’s that may be new to SSAS, or possibly even developers who lack a dedicated administrator and need to be able to tell where the bottleneck might be on their SSAS server.  I was also privileged to be invited to an interview on RunAs Radio where I spent some time discussing this with Richard Campbell and Greg Hughes.

Finally I’ve had the time to put much of this same advice down here. This is the first part of a couple posts I’ll dedicate to the topic. Anyone working with SSAS should find this useful, but users of PA for SSAS should find themselves even more proficient in troubleshooting SSAS performance issues. We’ll start with a very basic introduction to the service itself and how a query is handled, and move into specific metrics and identifying bottlenecks shortly.

Basic Architecture

It’s important to understand that SSAS is its own engine. The executable is msmdsrv.exe. It’s licensed with SQL Server, but in large production environments you aren’t likely to be running it on the same machine as a SQL Server. There are two primary types of activity that occur here; querying and processing.

Types of Activity

Querying can be done by various methods, but the most common type of queries are Multidimensional Expressions (MDX). This is just a standard language, similar to SQL, optimized for calculations. I won’t go into the how’s and why’s of MDX itself as there are many resources already available on its mastery. While this series won’t tell you how to write MDX, by the time we’re done you should be able to tell, with a few basic pieces of information described later, whether there is room for query optimization or if the bottleneck lies elsewhere.

Processing is essentially the maintenance activity of your cubes. It’s how cubes are created and updated with new data and/or how existing data is recalculated and reorganized. Just as with SQL Server, what level of processing you perform and when is essential to ensuring proper performance without interfering with peak querying times. We’ll dive into those options later.

The Formula Engine

Under the covers there are two primary “engines” within SSAS, the Formula Engine (FE) and the Storage Engine(SE). The FE accepts MDX requests and parses the query to process. It will send requests to the SE when data is needed. It then performs calculations on the retrieved data to provide a final result set. An interesting thing about the FE is that it is single threaded. If you are watching the CPU’s of your multi-core server with SSAS while a request is being handled and you only see one core spike, it’s probably because the FE was doing the work at that time.

The Storage Engine

The SE on the other hand is multi-threaded. The SE reads and writes data to and from the file system for the server. This can include working against the Windows file cache. This is an important distinction between Analysis Services and the relational engine in SQL Server. SQL Server’s relational engine has an internal cache, and then goes to disk for any other needed IO. It does not utilize the Windows file cache for database files. SSAS, on the other hand, does. This means that when SSAS is interacting with the file system on your server, it does not necessarily mean this will result in physical disk IO. SSAS utilizes its own caches as well as the file system cache to get what it needs.

SSAS Caches

So how does SSAS cache data? Each engine within SSAS has its own set of caches. The FE caches store flat values (the Flat Cache) as well as calculated data (the Calculation Cache). It’s important to understand that these caches are often scoped, which means the data might only be available for a limited time or only for a particular user, even in cache. The flat cache in particular is in fact restricted to a max of 10% of the TotalMemoryLimit property in SSAS, which we will discuss later.
So, that said, when we talk about SSAS cache optimization, warming, etc., we are more often dealing with the SE caches. I know that’s a bit of a simplification, but this is an introduction after all. ;)
So the SE consists of a Dimension Cache and a Measure Group Cache to store the data retrieved from the corresponding sources in the file system itself.

Anatomy of an MDX Query

image
So now that we understand the basic components under the covers in SSAS, how are they used to handle a request? First, the query is accepted and the FE parses it. If the appropriate data is already available in the proper scope in the FE cache, some or all may come from there. The FE then requests any other needed data from the SE. The SE then retrieves whatever it can from its caches. If still more data is needed, the SE queries the file system on the server. The data from the file system may, at least in part, come from the Windows file cache. Remember we mentioned this distinction earlier. Anything else results in physical disk IO. These steps may be iterated as needed while data is moved from SE caches to FE caches, etc., but you get the picture. Another graphical representation of this process can be found in Appendix A of the Microsoft Best Practices Article “Identifying and Resolving MDX Query Performance Bottlenecks in SQL server 2005 Analysis Services”, authored by Carl Rabeler and Eric Jacobsen. I highly recommend reviewing this article for a very in-depth look into this topic. Even though the title mentions SQL Server 2005, it’s still very relevant and useful.

What can impact MDX performance?

So now that we have a good understanding of how SSAS handles MDX requests, the question we need to answer is, “What can impact MDX performance, and how can we tell”? That is the question we will answer in the next post.

47 comments:

  1. hi! this article is very useful. thanks! but what about baselines in ssas (like dabase engine)? or how to interpreter information from counters. it would be a great w.p

    ReplyDelete
  2. Data analysis is can be basically defined as the procedure of examining data, processing it, modifying and revising the information with only one purpose in mind to make the entire procedure of making a decision worthwhile with the help of pertaining data and coming to a solid conclusion. See more statistical analysis services

    ReplyDelete
  3. QuickBooks is a small business accounting software program businesses use to manage sales and expenses and keep track of daily transactions. You can use it to invoice customers, pay bills, generate reports for planning, tax filing etc. For further queries you can contact QuickBooks customer support number

    ReplyDelete
  4. if you are looking for antivirus then we suggest you download McAfee activate for total protection. if you had a premium version of mcafee and face any kind of technical error then you can visit our website.

    ReplyDelete
  5. You can download quickbooks tool hub to repair you quickbooks or You can visit our website where we tell you to repair your quickbooks. you can simply remove any kind of error which shows by quickbooks

    ReplyDelete
  6. if you are using Quickbooks software and face issues or errors while using it then you can simply use quickbooks install diagnostic Tool by which you can repair your Quickbooks software as well as it automatically fixed bugs. for more further information you can visit our website.

    ReplyDelete
  7. Students find Human Resource Writing Services as being of great assistance since they are able to complete their human resource assignment writing services and human resource research paper writing services on time.

    ReplyDelete
  8. QB tool hub is a place where you can find all the tools needed or required that can remove errors in Quickbooks. learn other types of error that can be problem in working efficiently in Quickbooks.

    ReplyDelete
  9. Check Quickbooks Error Solutions for any Quickbooks error related queries, find the errors in your Quickbooks and how to rectify it.

    ReplyDelete
  10. I was very happy to find this site. I really enjoyed reading this article today and think it might be one of the best articles I have read so far. I wanted to thank you for this excellent reading !! I really enjoy every part and have bookmarked you to see the new things you post. Well done for this excellent article. Please keep this work of the same quality.
    Data Science Course in Bangalore

    ReplyDelete
  11. I am a Tech Analyst at QuickBooks Tool Hub , it is a web-based accounting tool for catering the financial needs of the business, QuickBooks Desktop Tool Hub performs book-keeping, invoice preparation in a professional manner, managing tax and budget planning for good financial health.
    It has the customer support number for 24/7 for problem assisting and error resolving solution.

    ReplyDelete
  12. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science in bangalore

    ReplyDelete
  13. Thanks for posting the best information and the blog is very helpful.digital marketing institute in hyderabad

    ReplyDelete
  14. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science course

    ReplyDelete
  15. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete

  16. Hi to all, it’s in fact a nice for me to pay a visit this web page, it consists of useful Information

    배트맨토토구매가능게임

    ReplyDelete
  17. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    artificial intelligence training in chennai

    ReplyDelete
  18. There are many reasons that cause Quickbooks database server manager network diagnostics failed, the very common reason is disabled Quickbooks database service. When Quickbooks database server manager is outdated this is one of the reasons, if windows firewall is blocking Quickbooks from accessing the required ports. To solve this error you can use the Quickbooks file doctor tool, you can update the Quickbooks database server manager and resolve that error, by configuring windows firewall you can solve this error, etc.

    Quickbooks database server manager network diagnostics failed

    ReplyDelete
  19. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Course Syllabus

    ReplyDelete
  20. This is a very nice one and gives in-depth information. I am really happy with the quality and presentation of the article. I’d really like to appreciate the efforts you get with writing this post. Thanks for sharing.
    AWS Training in Bangalore

    ReplyDelete
  21. 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.

    data science certification in bangalore

    ReplyDelete
  22. Glad to chat your blog, I seem to be forward to more reliable articles and I think we all wish to thank so many good articles, blog to share with us.
    digital marketing courses in hyderabad with placement

    ReplyDelete
  23. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    artificial intelligence training in chennai

    ReplyDelete
  24. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science training in chennai

    ReplyDelete
  25. The kind and 릴게임 easy-to-understand explanation made it easy to understand difficult topics.
    Your writing skills are great. I want to learn great writing skills.

    ReplyDelete
  26. Positive site, where did u come up with the information on this posting? I'm pleased I discovered it though, ill be checking back soon to find out what additional posts you include.
    고스톱

    ReplyDelete
  27. Yes I am totally agreed with this article and I just want say that this article is very nice and very informative article. I will make sure to be reading your blog more. You made a good point but I can't help but wonder, what about the other side? !!!!!!Thanks
    토토사이트

    ReplyDelete
  28. I believe this is among the so much significant information for me.
    And I'm happy reading your article.
    한국야동

    ReplyDelete
  29. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    토토사이트

    ReplyDelete
  30. You made some decent factors there. I looked on the web for the issue and located most individuals will go together with together with your website. 경마

    ReplyDelete
  31. Your article looks really adorable, here's a site link i dropped for you which you may like. 슬롯머신

    ReplyDelete
  32. Having read this I believed it was really enlightening. I appreciate you spending some time and energy to put this content together. 스포츠토토

    ReplyDelete
  33. Wonderful illustrated information. I thank you about that. No doubt it will be very useful for my future projects. Would like to see some other posts on the same subject! 휴게텔

    ReplyDelete
  34. Really impressed! Everything is very open and very clear clarification of issues. It contains true facts. Your website is very valuable. Thanks for sharing.
    best real estate company


    ReplyDelete
  35. At the point when you utilizing the Quickbooks and deal with issue Quickbooks won't open and you need to the arrangement of this error, Quickbooks instrument center comes as the rescuer for all the product clients whose framework continues to ruin with the errors issue. It is the center point of the most unique apparatuses. With this instrument, we can handle organization documents, establishment, and multi-client errors in insignificant time.

    ReplyDelete
  36. Your website is very valuable. Thanks for sharing.
    flat for sale

    ReplyDelete
  37. I actually enjoyed reading it, you could be
    a great author.I will remember to bookmark your blog and will
    eventually come back from now on. I want to encourage you to continue your great
    writing, have a nice weekend!토토

    ReplyDelete
  38. Your post is really great.

    with regards to overseeing monetary side of any organization there are two major names which come the sage accounting vs quickbooks two programming projects help to develop business in various ways relying upon the prerequisites and plans in future

    ReplyDelete
  39. Hey!!!!! I am a technician and our company have many professional technicians so they wrote a blog on the mainly facing issue while using quickbooks and i.e. quickbooks error #15215 quickbooks error #15215 A QBs Repairing Guide
    this blog really helps you alot as it is a mixture of all the blogs

    ReplyDelete
  40. I’ve been absent for a while, but now I remember why I used to love this website. Thanks, I will try and check back more often. How frequently you update your site?
    먹튀검증

    ReplyDelete
  41. you very well explained the topic For providing the important and Knowledgeablequickbooks file repairHow to repair the repair file in quickbooks quickly and easily, it has been explained in detail so that it can be easily seen and repaired.

    ReplyDelete