Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | > Analysis Services Performance

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


Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, November 07, 2014 - 8:05:35 AM - Dan Back To Top

Arshad, this is very helpful indeed, I have used most of what you discussed here in developing SSAS cube, thanks. I do have a question though, how do I go about solving a performance issue that relates to accessing a cube that performs well and is responsive locally but is slow over a WAN, apart from a Citrix solution , I can't think of any other option, but your advice will be appreciated.


Thursday, January 30, 2014 - 4:56:38 AM - Keshav Back To Top

Hi Arshad, 

Can you advise me in one problem. It is reagrding SQL Analysis Services for Cube. Users access the Cube over MsExcel and try to setup the connection to Cube hosted on SQL Analysis Server.

They report a performance issue intermitttent in nature. First time connection takes long enough time to connect around 150 secs then once connection is made and they design Pivot tables through Cube dimensions. If they try connecting again , this time connection establises in 5-6 secs. Using one of the troubleshooting tool I found SOAP events "Authenticate"/"AuthenticateResponse" takes long processing time at Server.

 Can you advise what limitaing factors could be here, which I should look at to improve performance.


Wednesday, June 19, 2013 - 1:53:23 PM - Raj Back To Top

Mind Blowing Detailed  Explanations and examples,This information is very Very helpful.Thank you so much 

 

 


Wednesday, March 20, 2013 - 11:04:22 AM - Nguyent Back To Top

How to schedule the job to run SQL Profiler for SSAS

 


Thursday, November 22, 2012 - 12:15:57 PM - Nestor Back To Top

Awesome series of articles!

Very good read, thanks!


Learn more about SQL Server tools