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.