SQL Tips - OLAP - Analysis Services optimization tips

*****

You can use the Usage-Based Optimization Wizard to optimize partition performance based on the history of queries previously sent to the cube.
To run the Usage-Based Optimization Wizard run Analysis Manager, right-click the appropriate cube and choose 'Usage-Based Optimization', then complete the steps in the wizard.

*****

Try to separate the OLTP server with the OLAP server if these servers very hard used by many users.
In this case, placing the OLAP server on its own dedicated server can boost the overall performance of the queries.
Note. When you use HOLAP or ROLAP cubes, placing cubes on a dedicated server can decrease performance if the data warehouse and Analysis Services will be connected via slow link.

*****

Try to use the MOLAP or HOLAP cubes instead of the ROLAP cubes, if you have enough amount of disk space.
The MOLAP and HOLAP cubes provide batter performance in comparison with ROLAP cubes, but can use more disk space to store the cube's data.

*****

Set the 'read only' data warehouse database option to true.
This can reduce the time needed to process the cube and increase the speed of queries against the data.

*****

If the data warehouse database is a read only database, you can create as many indexes as needed to for your Analysis Services queries.
If all of your Analysis Services queries are covered by the indexes, only indexes will be used to get the queries data, and the overall performance of these queries will be greatly maximized.

*****

Create all indexes in the data warehouse database with a 'fill factor' option of 100.
Using a 'fill factor' option of 100 ensures that the index pages will be as full as possible. This can increase the speed of queries and reduce the amount of space used to store the index data.

*****

Make a cube process during periods of low users activity.
Because a cube process can take much time, this operation should be scheduled during CPU idle time and slow production periods.

*****

Declare the foreign key relationships between the fact table and the dimension tables and create the indexes for every foreign key in the dimension tables.
This can greatly maximize the overall performance of the queries against the indexed data.

*****

Increase the level of aggregation for your cubes to boost the performance of the Analysis Services queries.
The larger the level of cube's aggregation will be, the faster the queries will be executed, but the more amount of disk space will be used and the more time it will take to process the cube. So, you should make some monitoring to get the best value for the level of aggregation for your cubes.

*****

Consider placing cubes on their own server to distribute the load, if these cubes are large and very busy.
Placing each cube on its own dedicated server can distribute the workload among these servers and boost the overall performance.

*****

If your OLAP server does not have CPU bottleneck, try to increase the "Maximum number of threads" Analysis Services option.
By default, the "Maximum number of threads" value is equal to two times the number of CPUs in the server computer. If you decide to increase this value, run System Monitor to check that there is no CPU bottleneck. To increase the "Maximum number of threads" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Environment' tab.

*****

If you have a dedicated server for Analysis Services, increase the "Minimum allocated memory" Analysis Services option.
By default, the "Minimum allocated memory" value is equal to one-half of the server computer's memory. Because allocation memory takes some time, if you will increase this value, you can avoid frequently automatic memory allocation. To increase the "Minimum allocated memory" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Environment' tab.

*****

If the OLAP server has a lot of RAM, increase the "Read-ahead buffer size" Analysis Services option.
This option indicates the maximum amount of data placed into memory during each read of the database. The larger this value will be, the less disk read operation will be required to read the cube's data. The default value is 4 Mb. Try to increase this value to 8 Mb and continue monitoring. To increase the "Read-ahead buffer size" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Processing' tab.

*****

If the OLAP server has a lot of RAM, increase the "Process buffer size" Analysis Services option.
This option indicates how much data is processed in memory before an I/O is performed. The larger this value will be, the fewer the I/O operations will be required. The default value is 4 Mb. Try to increase this value to 8 Mb and continue monitoring. To increase the "Process buffer size" option, run Analysis Manager, right-click the server name and choose 'Properties', then go to the 'Processing' tab.

*****

If you install Analysis Services for SQL Server 2000 Enterprise Edition, you can create multiple partitions in a cube.
Using multiple partitions allows the source data and aggregate data of a cube to be distributed among multiple server computers. This can boost performance, because the workload will be distributed across multiple I/O devices.