Because
Microsoft SQL Server Analysis Services query and processing performance tuning
is a fairly broad subject, this white paper organizes performance tuning
techniques into the following three segments.
Enhancing Query Performance - Query performance directly impacts
the quality of the end user experience. As such, it is the primary benchmark
used to evaluate the success of an online analytical processing (OLAP)
implementation. Analysis Services provides a variety of mechanisms to
accelerate query performance, including aggregations, caching, and indexed data
retrieval. In addition, you can improve query performance by optimizing the
design of your dimension attributes, cubes, and Multidimensional Expressions
(MDX) queries.
Enhancing Processing Performance - Processing is the operation that
refreshes data in an Analysis Services database. The faster the processing
performance, the sooner users can access refreshed data. Analysis Services
provides a variety of mechanisms that you can use to influence processing
performance, including efficient dimension design, effective aggregations,
partitions, and an economical processing strategy (for example, incremental vs.
full refresh vs. proactive caching).
Tuning Server Resources – There are several engine settings
that can be tuned that affect both querying and processing performance.
Because this
paper is very long (80 pages) reading it in an online format such as the
Library becomes impractical, so we offer it as a downloadable Microsoft Word
document.