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) is a fourteen-year veteran of SentryOne, and has held roles in Solutions Engineering, Quality Assurance, Product Management, and Advanced Analytics prior to assuming his current role. He has almost twenty years' experience working with SQL Server, and holds numerous professional certifications including the Microsoft Professional Program, Data Science Certification. His current responsibilities are focused on ensuring everyone throughout the SentryOne family is educated on our customers, their needs, and the solutions we provide that allow us to improve the lives of Microsoft Data Professionals and their customers everywhere.