An Introduction to SSAS Performance via SQL Sentry
On our old team blog, I wrote a series of posts explaining some performance aspects of SQL Server Analysis Services, and how SQL Sentry software helps point out potential areas for improvement or reconfiguration. Since many customers have asked for details around SSAS, and since the information remains relevant today, I thought I would put together a handy index post.
In order to better understand SSAS performance optimization and troubleshooting, it is first important to understand the basic architecture of SSAS, all the moving parts and how they interact. Part 1 covers these topics along with an introduction to the types of activity that occur in SSAS and an introduction to the deeper topics covered in the rest of the series.
We start by discussing what can impact MDX query performance. This installment focuses on processing activity, how to identify if processing is impacting your MDX performance, and some suggestions for how you might minimize that impact with a more efficient processing strategy.
Part 3 goes more in depth specifically around identifying potential issues where the MDX code itself may not be optimized. Even without knowledge in writing MDX queries yourself, SQL Sentry provides visibility to the user if this is the problem, and can help you make suggestions to the developers to get them started making improvements to MDX query performance.
Beyond strategies for the queries themselves, it also helps to understand where Analysis Services is spending most of its time working to provide the results to your request. This post covers several of the more useful trace events that you can track, or view within Performance Advisor, to identify how your requests are being handled by Analysis Services so you know where to focus your performance improvement efforts.
The final installment of this series covers bottlenecks with your server’s physical resources. It covers how to identify if there are resource bottlenecks, where they lie, and what you can do about them to keep Analysis Services running at peak efficiency.
For current product information, see SQL Sentry Performance Advisor for Analysis Services.
Steve (@SQL_Steve) directs the advanced analytics initiatives at SentryOne. Among his certifications, he holds the Microsoft Professional Program Certificate in Data Science. Steve shares tips and tidbits picked up over years of providing support and training to our customers. His blog often provides a common sense explanation of the in-depth information the SentryOne platform can provide, as well as some tips for further enhancing the vast feature set already provided out of the box.