SSAS - Best Practices and Performance Optimization

Problem

There are several aspects that can hurt performance of your Analysis Services cubes. Some problems could be on the source system, some could be because of poor design of your cube and MDX queries and some could be related to network issues. In this tip series I am going to talk about some of the best practices which you should consider during design and development of your Analysis Services cubes.

Solution

In Part 1 of this tip series, I talked about a typical SSAS application architecture and different components of the SSAS internal architecture and about roles of different components and areas for performance improvement. In Part 2, I talked about source system design and network considerations. In Part 3, I talked about cube design and development. In this tip, I am going to share with you best practices and performance optimization techniques for Server Resources and Reporting Services.

Server Resources Tuning

In this section I am going to talk about some of the SSAS instance properties which impacts the overall performance of your cube processing and query response.

Threading or parallel processing in SSAS

SSAS has been designed to perform its operations in parallel and because of this it can create multiple threads to execute multiple requests in parallel. Since creating and destroying threads is an expensive affair, SSAS maintains two sets of worker thread pools to return threads which are currently not being used, so that SSAS can again pick them up for serving other requests. These two pools are called the Query Thread Pool and the Process Thread Pool.

If you remember from the SSAS architecture which I talked about in the first tip of this series, the XMLA listener listens for incoming requests and creates (or pulls out a query thread from the query pool if one is already available) a query thread which checks for data/calculations in the formula engine cache. If required, the XMLA listener creates (or pulls out a process thread from the process pool if one is already available) a process thread which is used to retrieve data from the storage engine cache/disk. The process thread also stores the data in the storage engine cache which it retrieved from the disk whereas the query thread stores the calculations in the formula engine cache to resolve/serve future queries.

So now we understand that the more threads SSAS can create, the more requests can be served in parallel. But how many threads can be or should be created? You can control this with these SSAS instance properties settings:

ThreadPool\Query\MinThreads and ThreadPool\Query\MaxThreads

These two properties determine the minimum (not created all at once, but once created this minimum number of threads will be maintained) and maximum query threads that can be created by SSAS. By default, on SQL Server 2008, the maximum number of query threads that be created is either 10 or 2*number of cores on the machine (whichever is higher). The higher number of query threads that can be created signifies that more requests can be served in parallel.

ThreadPool\Process\MaxThreads and ThreadPool\Process\MinThreads

These two properties determine the minimum (not created all at once, but once created this minimum number of threads will be maintained) and maximum process threads that can be created by SSAS. By default, on SQL Server 2008, the maximum number of process threads that be created is either 64 or 10*number of cores on the machine (whichever is higher). The higher number of process threads that can be created signifies the number of requests that can be served in parallel (if they need data which are not available in the formula engine cache).

OLAP\Process\BufferMemoryLimit

SSAS uses buffers during the fact partition processing to temporarily store the data in memory and then writes it to the disk at the end of processing. In normal cases, the default value for this property would be sufficient but you might need to change/increase it in cases when granularity of the measure group is more summarized than the fact table in the relational data warehouse, to allow it to store and do aggregation before writing to disk.

OLAP\Process\AggregationMemoryLimitMin and OLAP\Process\AggregationMemoryLimitMax

The default minimum value is 10% and the maximum value is 80%. This indicates minimum and maximum percentage amount of SSAS memory available for creation of aggregation in each partition. If you have more partitions to process in parallel you should consider keeping it low to increase the performance of ProcessIndexes (creation of aggregates in each partition in parallel).

DataDir and LogDir

By default data files and log files are stored in the program files location. You should consider changing these to some other faster disk drives.

Log\FlightRecorder\Enabled

If you have background working with SQL Server you might be aware of the default trace in SQL Server. The concept of a flight recorder is the same in SSAS as the default trace for SQL Server. By default it is ON and keeps logging SSAS activity in a short-term log file. This proves to be very helpful if you are doing some processing and querying performance troubleshooting, but also creates overhead because of the I/O overhead. So ideally you should keep it OFF in production environments and should enable it only in cases where needed.

Scale up or scale out whenever or wherever possible

You should consider a 64 bit platform when designing your system. This way you can later add memory and processors if the user base is increasing or whenever you see performance issues. Sometimes when you have an increase in the user base to an extent that the machine is not performing as expected then you might consider creating network load balancing clusters with two or more machines. With this, requests can be load balanced and served by these two or more SSAS instances running on several physical machines.

SSRS - Report Design and Development

SQL Server Reporting Services (SSRS) is another technology/tool/component which comes with SQL Server. You can create SSRS reports based on any data sources including SSAS cube. When you are creating SSRS reports there are a couple of best practices which you should follow for a better performing reporting experience.

Instead of filtering data at report level, bring filtered data from the source itself

SSRS allows you to bring data from the source and apply filters locally, though it may sound fine it is not recommended unless you need the entire data for the report. The better approach is to filter the data at the source and only bring data locally that is needed. You can create parameterized reports (with report parameters or report cascading parameters) and pass these query parameters to the source for data filtering at the source.

Enable report caching or snapshot whenever or wherever applicable

The slowest part of report execution is pulling data from the source. SSRS allows enabling report caching for the report so a report gets cached in the SSRS database and if the same report request comes again, the stored copy can be rendered in the desired format and served instead of processing it from scratch. The difference between report caching and a report snapshot is that a report is cached whenever the first request for that report comes in whereas a snapshot can be created on a defined schedule. To learn more about report caching click here and to learn more about report snapshot click here.

Schedule your reports for standard or data driven subscriptions

A report subscription is very much like a report snapshot generated on a defined scheduled. but with additional functionality of delivering it to the intended audience by the report server (via email, a file share, or through a custom extension like sending a report directly to a printer).

SSRS allows creating two types of report subscriptions: a Standard Subscription is used by an individual user and a Data Driven Subscription is used for sending out reports to group of users and those lists of users and report formats can come from the database. To learn more about report subscriptions click here.

SSRS in scale out deployment

If you have or are going to have a large user base accessing your reports, you might consider scaled out architecture for your SSRS deployment. This ensures higher availability and load balancing between multiple report servers. To learn more about this click here and here.

SSRS catalog databases are regular SQL Server databases and hence all the best practices around databases apply to these as well. Also you should consider installing your report server on a 64 bit machine, so that you have more addressable memory for your report execution and rendering, to learn more click here.

Next Steps

Review Analysis Services Performance Guide whitepaper.
Review Analysis Services Operations Guide whitepaper.
Review SQL Server Integration Services SSIS Best Practices tips.

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2012-05-04

SSAS - Best Practices and Performance Optimization - Part 4 of 4