Analysis Services 2005 Performance Guide

Do you want to know how to get the best performance out of Analysis Services? Whether you have an immediate performance issue or just want to know more about what’s going on in there, this guide will be a valuable resource. Many people used the AS 2000 Performance Guide and have been anxiously awaiting the AS 2005 Performance Guide. At last, it’s ready!

This paper (see Table of Contents below) covers a wide variety of topics that are important for AS performance: Writing efficient queries, optimizing the cube design, maximizing processing performance partitioning, aggregation design, and tuning the system for efficient use of resources. It was developed in collaboration with the developers and program managers on the AS team, so it’s the authoritative guide. Take a look! It will be worth your time.

Introduction

Enhancing Query Performance

Understanding the querying architecture

Session management

MDX query execution

Data retrieval: dimensions

Data retrieval: measure group data

Optimizing the dimension design

Identifying attribute relationships

Using hierarchies effectively

Maximizing the value of aggregations

How aggregations help

How the Storage Engine uses aggregations

Why not create every possible aggregation?

How to interpret aggregations

Which aggregations are built

How to impact aggregation design

Suggesting aggregation candidates

Specifying statistics about cube data

Adopting an aggregation design strategy

Using partitions to enhance query performance

How partitions are used in querying

Designing partitions

Aggregation considerations for multiple partitions

Writing efficient MDX

Specifying the calculation space

Removing empty tuples

Summarizing data with MDX

Taking advantage of the Query Execution Engine cache

Applying calculation best practices

Tuning Processing Performance

Understanding the processing architecture

Processing job overview

Dimension processing jobs

Dimension-processing commands

Partition-processing jobs

Partition-processing commands

Executing processing jobs

Refreshing dimensions efficiently

Optimizing the source query

Reducing attribute overhead

Optimizing dimension inserts, updates, and deletes

Refreshing partitions efficiently

Optimizing the source query

Using partitions to enhance processing performance

Optimizing data inserts, updates, and deletes

Evaluating rigid vs flexible aggregations

Optimizing Special Design Scenarios

Special aggregate functions

Optimizing distinct count

Optimizing semiadditive measures

Parent-child hierarchies

Complex dimension relationships

Many-to-many relationships

Reference relationships

Near real-time data refreshes

Tuning Server Resources

Understanding how Analysis Services uses memory

Memory management

Shrinkable vs non-shrinkable memory

Memory demands during querying

Memory demands during processing

Optimizing memory usage

Increasing available memory

Monitoring memory management

Minimizing metadata overhead

Monitoring the timeout of idle sessions

Tuning memory for partition processing

Warming the data cache

Understanding how Analysis Services uses CPU resources

Job architecture

Thread pools

Processor demands during querying

Processor demands during processing

Optimizing CPU usage

Maximize parallelism during querying

Maximize parallelism during processing

Use sufficient memory

Use a load-balancing cluster

Understanding how Analysis Services uses disk resources

Disk resource demands during processing

Disk resource demands during querying

Optimizing disk usage

Using sufficient memory

Optimizing file locations

Disabling unnecessary logging

Conclusion

Appendix A – For More Information

Appendix B - Partition Storage Modes

Multidimensional OLAP (MOLAP)

Hybrid OLAP (HOLAP)

Relational OLAP (ROLAP)

Appendix C – Aggregation Utility

Benefits of the Aggregation Utility

How the Aggregation Utility organizes partitions

How the Aggregation Utility works

- Len Wyatt

Comments

  • Anonymous
    March 15, 2007
    For all those people who wanted it from amore authoritative source, here it is: SQL Server Performance