SSAS 查询性能最佳实践

在针对SQL Server Analysis Services实例进行MDX查询时，要想获得最佳性能，关键要做到以下几点：合理的多维数据集设计、有效的多维表达式(MDX)以及充足的硬件资源。

（1）优化多维数据集和度量值组设计

在每个维度内为数据定义合适的级联属性关系（例如，天>月>季度>年），并定义相关属性的用户层次结构（称为“自然层次结构”）。自然层次结构中的属性会显现在层次结构存储区中的磁盘上，并被自动视为候选聚合。除非通过级联属性关系将构成各级别的属性关联起来，否则用户层次结构不会被视为自然层次结构。

删除属性之间的冗余关系，以协助查询执行引擎生成合适的查询计划。属性与键属性之间必须存在直接或间接关系，但二者之中只能居其一。仅包含必需的度量值组，尽量将多维数据集空间保持最小。将要同时查询的度量值放入同一个度量值组。要知道，当查询需从多个度量值组检索度量值时，存储引擎必须执行多项操作。为了优化缓存的使用率，可以考虑将大量不是一起查询的度量值分别放入不同的度量值组，但不要过度增加度量值组的数量。尽量少用大型父子层次结构。在父子层次结构中，只为键属性和顶级属性（如All属性）创建聚合，除非禁止这么做。因此，查询时要计算返回中间级单元格的查询，这对于大型父子维度来说，速度会比较慢。如果在设计中遇到有大型父子层次结构（超过250,000个成员）的情况，那可能需要考虑改变源架构，将部分或全部层次结构重组为级别数固定的用户层次结构。优化多对多维度的性能。通过多对多维度查询数据度量值组时，要在数据度量值组与中间度量值组之间执行运行时“联接”，联接媒介是这两个度量值组共有的每个维度的粒度属性。请尽可能减小中间度量值组所基的中间事实数据表的大小。为了优化运行时联接，请检查中间度量值组的聚合设计，确保聚合包含多对多维度的属性。

（2）定义有效的聚合

定义聚合，以减少存储引擎为满足查询需要而从磁盘扫描的记录数。如果SQL Server Profiler跟踪记录显示大多数不能从缓存解析的用户查询是通过分区读取而不是聚合读取来解析的，则可以考虑使用聚合管理器示例应用程序来设计自定义聚合。避免设计过多聚合。过多聚合会降低处理性能，也可能会降低查询性能。虽然最佳聚合数因情况而异，但根据SQL Server最佳实践小组的经验，在几乎所有情况下，最佳聚合数都在一百以内，而非成百上千。启用Analysis Services查询日志来捕获用户查询模式，并在设计聚合时使用此查询日志。有关详细信息，请参阅配置Analysis Services查询日志。

（3）使用分区

定义分区，这样，当查询无法从数据缓存或聚合得到解析时，Analysis Services只需查询较少的数据即可解析它。定义分区还可以增大解析查询的并行度。为了获得最佳性能，将数据分区时所用的方式应符合常用查询的需要。极常见的分区方法是选择一个时间元素，如日、月、季度、年，或选择几个时间元素。选用分区方式时，要避免出现分区后大多数查询都需要从许多分区来解析的情况。在大多数情况下，分区包含的记录应少于2千万条，每个度量值组包含的总分区数应少于2,000个。此外，应避免定义所含记录不足2百万条的分区。分区太多会导致元数据操作速度缓慢，分区太少又会导致错过并行。为实时数据定义单独的ROLAP分区，并将实时ROLAP分区放入其自己的度量值组。

（4）编写有效的MDX

删除结果集中的空元组，以缩短查询执行引擎对结果集进行序列化的时间。

避免在MDX计算中进行运行时检查，这会导致执行路径缓慢。如果使用Case函数和IF函数执行条件检查，执行路径会很慢，因为这类检查在查询解析过程中必须解析多次。请使用SCOPE函数重新编写这类查询，以迅速减少MDX计算涉及的计算空间。有关详细信息，请参阅预算差异- MDX优化研究：计算模式与NONEMPTYBEHAVIOR、在MDX中比较级别以及单元计算中的CONDITION与SCOPE对比和多选友好MDX计算。

（5）有效地使用查询引擎缓存

确保Analysis Services计算机有足够的内存来存储查询结果，以便在解析后续查询时可以再利用。

定义MDX脚本中的计算。MDX脚本中的计算有一个全局范围，因此，与这些查询相关的缓存可以由同一组安全权限下的多个会话共享。但是，在用户查询中使用Create Member和With Member定义的计算成员没有全局范围，与这些查询相关的缓存无法实现跨会话共享。通过使用自选工具执行一组预定义的查询来准备缓存。使用Create Cache语句也可以达到同一目的。有关使用Create Cache语句的详细信息，请参阅如何使用Create Cache语句来准备Analysis Services数据缓存。

（6）确保有柔性聚合来回应查询

请注意，对维度使用ProcessUpdate来增量更新维度会删除受更新和删除影响的所有柔性聚合，且在默认情况下，在下一个完整处理之前不会重新创建柔性聚合。

请务必处理受影响的对象、配置迟缓处理、对受影响的分区执行ProcessIndexes或对受影响的分区执行完整处理，从而重新创建聚合。

（7）优化内存使用率

增加Analysis Services服务器上分页文件的大小或增加内存，以防当分配的虚拟内存量超过Analysis Services服务器上的物理内存量时出现内存不足错误。

（8）尽量向上扩展

所有大型系统都使用64位体系结构。增加内存和处理器资源，升级磁盘I/O子系统，以缓解单个系统上的查询性能瓶颈。尽量避免跨服务器链接维度或度量值组，尽量避免远程分区，这些不是最佳解决之道。

（9）无法再向上扩展时则向外扩展

如果性能瓶颈是由多用户查询工作负荷导致的单个系统上的处理器利用率问题，可以使用一组Analysis Services服务器为查询请求提供服务，籍此提高查询性能。请求负载可均衡地分布在两台或更多Analysis Services服务器上，以支持大量并发用户。