OLAP performance tuning

28 January,2014 by Tom Collins

OLAP performance  and SQL performance are often compared and contrasted. I prefer to view SQL and OLAP performance working in tandem. Many of the concepts are similar and quite often interdependent.

 Some quick tips on how to make OLAP faster with some performance tuning tactics

1)   Focus on writing optimised MDX queries

2)   Consider the use of aggregations for standard reports. Aggregations can cause a drastic improvement on performance

3)   Experiment with Cache warming for daily reports. On some systems starting with  a cold cache  will cause queries to run slower - initially.   The purpose of Cache warming  is to  execute higher used  queries against the recently reprocessed cube. This constructs the cache before the users start querying . For great information on caching check http://blogs.msdn.com/b/sqlcat/

4)     If there is a large amount of historical data , partition on date.  A single partition is used by default. Map out and test multiple partitions and review the aggregation strategy. Work on refining the partitioning implementation. Ask the right questions

5)     The Analysis Service Engine processes and queries OLAP partitions in parallel. It’s interesting to note and compare against SSAS tabular partitions which don’t support parallel processing.

Read More

Top 5 SQL Server DMV for Index Analysis - SQL Server DBA

SQL Server Performance Killers

SQL Server Performance Checklist

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on OLAP performance tuning

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer