[翻译]SQL Server 2005 Analysis Services性能指南

介绍

增强查询性能

理解查询构架

会话管理

MDX查询执行

数据查找：维度

数据查找：度量值组

优化维度设计

定义属性关系

有效使用层次

聚合最大化

聚合如何优化查询

存储引擎如何使用聚合

为什么不创建所有可能的聚合

如何解释聚合单元命名

哪些聚合将被创建

如何影响聚合设计

提供聚合备选单元

提供Cube数据的统计信息

采用合理的聚合设计策略

使用分区提升查询性能

在查询时如何使用分区

设计分区

多分区的聚合考虑

编写高效率的MDX查询

精确计算空间

移除空元组

使用MDX汇总数据

利用查询执行引擎缓存

使用计算成员的最佳方法

调校处理性能

理解处理构架

处理作业概要

维度处理作业

维度处理指令

分区处理作业

分区处理指令

执行处理作业

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