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

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Latest Articles