Analysis Services optimization tips

Here are fifteen little known tips that you can use to ensure your
Analysis Services are performing in the most efficient
manner possible.

1. 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.

2. Try to separate the OLTP server from the OLAP server if these
   servers are heavily trafficked 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 a slow link.

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

4. 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.

5. If the data warehouse database is a read only database, you can
   create as many indexes as needed 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.

6. 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.

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

8. 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.

9. 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 more disk space will be
   used and it will take more time to process the cube. So, you
   should do some monitoring to get the best value for the level of
   aggregation for your cubes.

10. 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.

11. 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.

12. 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 frequent
    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.

13. 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.

14. 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 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.

15. 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.

»


See All Articles by Columnist Alexander Chigrik