Analysis Services (SSAS) Performance and Monitoring Tips

Troubleshooting performance problems on Analysis Services can be complicated.

The reasons for poor performance can range from a poorly designed model to network issues, or maybe a server that is not configured for the workload.

As an administrator, you want to proactively prevent or minimize problems, and when they occur, you want to diagnose the cause and take corrective actions to fix the problem.

This Wiki article tries to point to the best content on the Web, so you can find the information you need, understand performance problems, and set up a monitoring solution. If it's related to SSAS performance, post it here!

Throughout this article, you'll see links to performance content for versions of Analysis Services ranging from 2005 to 2014. That's okay - much of the guidance on basic model design and the performance of the formula and storage engine applies no matter what version you are on.

This SSAS Performance Wiki is a work in progress, so please links to the performance tips and resources that helped you. Just click the "Edit" button above this article to add or improve the existing content.

SQL CAT papers

Information that used to be hosted on the SQL CAT site is now available through the TechNet Wiki. Here are a few articles that we found useful. See the SQL CAT blog for updates!

• Performance Guidance for SQL Azure

  http://blogs.msdn.com/b/sqlcat/archive/2013/06/17/performance-guidance-for-sql-server-in-windows-azure-virtual-machines.aspx

• Setting the Processing Group property to ByTable provides more optimal processing than the default value of ByAttribute

  http://blogs.msdn.com/b/sqlcat/archive/2007/10/19/using-byattribute-or-bytable-processing-group-property-with-analysis-services-2005.aspx

• SSAS Small and big query interaction

  http://blogs.msdn.com/b/sqlcat/archive/2007/10/19/ssas-small-big-query-interaction.aspx

White Papers

1. SQL Server 2008 White Paper: Analysis Services Performance Guide

   http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=1730

   This guide covers all the important performance tuning tricks for MOLAP models.

2. Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services

   http://msdn.microsoft.com/en-us/library/dn393915.aspx

   This guide describes the inner workings of tabular model processing and querying in great detail, and provides examples f how to tune a model for specific workloads.

3. Hardware Sizing a Tabular Solution (SQL Server Analysis Services)

   http://msdn.microsoft.com/en-us/library/jj874401.aspx

   In this white paper, you'll learn how to plan capacity for a tabular model and ensure that neither processing nor queries are starved for memory.

Downloads and Samples

• CodePlex: SSAS Community Samples

  http://sqlsrvanalysissrvcs.codeplex.com

  This page links to a lot of different projects in CodePlex that have been developed and vetted by the community. Be sure to check out AS Trace and the stored procedures for SSAS.

Blogs and Articles

article title

link

Related Issues

Data Loading Performance Guide

http://msdn.microsoft.com/en-us/library/dd425070(v=SQL.100).aspx