SQL Server 2008 White Paper: Analysis Services Performance Guide
Summary: This white paper describes how application developers can apply query and processing performance-tuning techniques to their SQL Server 2008 Analysis Services OLAP solutions.
Introduction
Since 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.
Contents
1 Introduction
2 Understanding the Query Processor Architecture
2.1 Session Management
2.2 Job Architecture
2.3 Query Processor
2.3.1 Query Processor Cache
2.3.2 Query Processor Internals
2.4 Data Retrieval
3 Enhancing Query Performance
3.1 Baselining Query Speeds
3.2 Diagnosing Query Performance Issues
3.3 Optimizing Dimensions
3.3.1 Identifying Attribute Relationships
3.3.2 Using Hierarchies Effectively
3.4 Maximizing the Value of Aggregations
3.4.1 Detecting Aggregation Hits
3.4.2 How to Interpret Aggregations
3.4.3 Building Aggregations
3.5 Using Partitions to Enhance Query Performance
3.5.1 Introduction
3.5.2 Partition Slicing
3.5.3 Aggregation Considerations for Multiple Partitions
3.5.4 Distinct Count Partition Design
3.5.5 Partition Sizing
3.6 Optimizing MDX
3.6.1 Diagnosing the Problem
3.6.2 Calculation Best Practices
3.7 Cache Warming
3.8 Improving Multiple-User Performance
3.8.1 Increasing Query Parallelism
3.8.2 Memory Heap Type
3.8.3 Blocking Long-Running Queries
3.8.4 Network Load Balancing and Read-Only Databases
4 Understanding and Measuring Processing
4.1 Processing Job Overview
4.2 Baselining Processing
4.2.1 Performance Monitor Trace
4.2.2 Profiler Trace
4.3 Determining Where You Spend Processing Time
5 Enhancing Dimension Processing Performance
5.1 Understanding Dimension Processing Architecture
5.2 Dimension-Processing Commands
5.3 Dimension Processing Tuning Flow Chart
5.4 Dimension Processing Performance Best Practices
5.4.1 Use SQL Views to Implement Query Binding for Dimensions
5.4.2 Optimize Attribute Processing Across Multiple Data Sources
5.4.3 Reduce Attribute Overhead
5.4.4 Use the KeyColumns, ValueColumn, and NameColumn Properties Effectively
5.4.5 Remove Bitmap Indexes
5.4.6 Turn Off the Attribute Hierarchy and Use Member Properties
5.5 Tuning the Relational Dimension Processing Query
6 Enhancing Partition Processing Performance
6.1 Understanding the Partition Processing Architecture
6.2 Partition-Processing Commands
6.3 Partition Processing Tuning Flow Chart
6.4 Partition Processing Performance Best Practices
6.4.1 Optimizing Data Inserts, Updates, and Deletes
6.4.2 Picking Efficient Data Types in Fact Tables
6.5 Tuning the Relational Partition Processing Query
6.5.1 Getting Rid of Joins
6.5.2 Getting Relational Partitioning Right
6.5.3 Getting Relational Indexing Right
6.5.4 Using Index FILLFACTOR = 100 and Data Compression
6.6 Eliminating Database Locking Overhead
6.7 Optimizing Network Throughput
6.8 Improving the I/O Subsystem
6.9 Increasing Concurrency by Adding More Partitions
6.10 Adjusting Maximum Number of Connections
6.11 Adjusting ThreadPool and CoordinatorExecutionMode
6.12 Adjusting BufferMemoryLimit
6.13 Tuning the Process Index Phase
6.13.1 Avoid Spilling Temporary Data to Disk
6.13.2 Eliminate I/O Bottlenecks
6.13.3 Add Partitions to Increase Parallelism
6.13.4 Tune Threads and AggregationMemorySettings
7 Tuning Server Resources
7.1 Using PreAllocate
7.2 Disable Flight Recorder
7.3 Monitoring and Adjusting Server Memory
8 Conclusion
Tags: performance, must read, paper