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

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.


  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

  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

  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

  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.

  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

  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.

  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.

  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.

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

  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

  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.

  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

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

  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

  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


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


  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

  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

  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

  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

  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