Analysis Services 查询性能十大最佳实践

Analysis Services 查询性能十大最佳实践

作者：Carl Rabeler

在针对 SQL Server 2005 Analysis Services 实例进行 MDX 查询时，要想获得最佳性能，关键要做到以下几点：合理的多维数据集设计、有效的多维表达式 (MDX) 以及充足的硬件资源。本文列出了 Microsoft SQL Server 开发小组推荐的十个最常用、最有效的 Analysis Services 查询性能优化方法。有关其他与查询性能相关的 Analysis Services 最佳实践介绍，请参阅 Analysis Services 性能指南和 Analysis Services 2005 OLAP 设计最佳实践。

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

• 在每个维度内为数据定义合适的级联属性关系（例如，天 > 月 > 季度 > 年），并定义相关属性的用户层次结构（称为“自然层次结构”）。自然层次结构中的属性会显现在层次结构存储区中的磁盘上，并被自动视为候选聚合。除非通过级联属性关系将构成各级别的属性关联起来，否则用户层次结构不会被视为自然层次结构。在 SQL Server 2005 Service Pack 2 (SP2) 中，对未定义为自然层次结构的每个用户层次结构，Business Intelligence Development Studio 中都会出现一条警告。
• 删除属性之间的冗余关系，以协助查询执行引擎生成合适的查询计划。属性与键属性之间必须存在直接或间接关系，但二者之中只能居其一。
• 仅包含必需的度量值组，尽量将多维数据集空间保持最小。
• 将要同时查询的度量值放入同一个度量值组。要知道，当查询需从多个度量值组检索度量值时，存储引擎必须执行多项操作。为了优化缓存的使用率，可以考虑将大量不是一起查询的度量值分别放入不同的度量值组，但不要过度增加度量值组的数量。
• 尽量少用大型父子层次结构。在父子层次结构中，只为键属性和顶级属性（如 All 属性）创建聚合，除非禁止这么做。因此，查询时要计算返回中间级单元格的查询，这对于大型父子维度来说，速度会比较慢。如果在设计中遇到有大型父子层次结构（超过 250,000 个成员）的情况，那可能需要考虑改变源架构，将部分或全部层次结构重组为级别数固定的用户层次结构。
• 优化多对多维度的性能（若使用）。通过多对多维度查询数据度量值组时，要在数据度量值组与中间度量值组之间执行运行时“联接”，联接媒介是这两个度量值组共有的每个维度的粒度属性。请尽可能减小中间度量值组所基的中间事实数据表的大小。为了优化运行时联接，请检查中间度量值组的聚合设计，确保聚合包含多对多维度的属性。

若要了解如何优化维度来提高查询性能，请参阅 SQL Server 2005 Analysis Services 性能指南和 Analysis Services 2005 OLAP 设计最佳实践这两篇文章。有关分析设计方案与最佳实践是否相符的帮助信息，请参阅 2007 年 2 月发布的 Community Technology Preview (CTP) 版本的 SQL Server 2005 最佳实践分析器（最终版本即将发布）。

定义有效的聚合

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

若要了解如何设计聚合来提高查询性能，请参阅 SQL Server 2005 Analysis Services 性能指南和 Analysis Services 2005 OLAP 设计最佳实践这两篇文章。

 

使用分区

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

若要了解如何设计分区来提高查询性能，请参阅 SQL Server 2005 Analysis Services 性能指南、Microsoft SQL Server 客户咨询小组博客和 Analysis Services 2005 OLAP 设计最佳实践。

编写有效的 MDX

• 删除结果集中的空元组，以缩短查询执行引擎对结果集进行序列化的时间。
• 避免在 MDX 计算中进行运行时检查，这会导致执行路径缓慢。如果使用 Case 函数和 IF 函数执行条件检查，执行路径会很慢，因为这类检查在查询解析过程中必须解析多次。请使用 SCOPE 函数重新编写这类查询，以迅速减少 MDX 计算涉及的计算空间。有关详细信息，请参阅预算差异 - MDX 优化研究：计算模式与 NON_EMPTY_BEHAVIOR、在 MDX 中比较级别以及单元计算中的 CONDITION 与 SCOPE 对比和多选友好 MDX 计算。
• 尽可能使用 Non_Empty_Behavior，以便查询执行引擎能够使用大容量计算模式。然而，如果使用 Non_Empty_Behavior 的方法不当，将返回错误的结果。有关详细信息，请参阅 预算差异 - MDX 优化研究：计算模式与 NON_EMPTY_BEHAVIOR 和平均值、比率、除以零与 NON_EMPTY_BEHAVIOR。
• 对成员属性使用 EXISTS 而非筛选，以避免执行路径缓慢。使用 NonEmpty 函数和 Exists 函数时，查询执行引擎能够使用大容量计算模式。
• 使用服务器端 ADOMD.NET，而非字符串操作函数（如 StrToMember 和 StrToSet），在 Analysis Services 存储过程中执行字符串操作。
• 尽量使用同一个多维数据集中的多个度量值组，而不使用 LookupCube 函数。
• 重新编写包含任意形状的 MDX 查询，以尽量减少子查询过多的情况。任意形状的集是指一组无法解析为只有单个层次结构的集的交叉连接的成员。例如，集 {(Gender.Male, Customer.USA), (Gender.Female, Customer.Canada)} 即为任意集。您可以经常使用 Descendants 函数解析任意形状，与使用其他函数编写的返回相同结果的查询相比，该函数使用的子查询较少。
• 尽可能重新编写会导致过多预提取的 MDX 查询。“预提取”是指查询执行引擎为了提高效率从存储引擎请求了超过实际需要的信息来解析当前查询。通常，预提取是最高效的数据检索方法。但偶尔也有例外。在某些情况下，您可以重新编写查询，在 FROM 子句中使用嵌套 select 语句，而不是在 WHERE 子句中使用集，从而消除过多的预提取。无法消除过多预提取时，可能需要禁用预提取，并使用 Create Cache 语句来准备加温。有关详细信息，请参阅如何使用 Create Cache 语句来准备 Analysis Services 数据缓存。
• 在使用集之前在交叉连接中先筛选该集，以便在执行交叉连接前减少多维数据集空间。

有效地使用查询引擎缓存

• 确保 Analysis Services 计算机有足够的内存来存储查询结果，以便在解析后续查询时可以再利用。若要监视，请使用 MSAS 2005: Memory/Cleaner Memory Shrinkable DB 和 MSAS 2005: Cache/Evictions/sec Performance Monitor 计数器。
• 定义 MDX 脚本中的计算。MDX 脚本中的计算有一个全局范围，因此，与这些查询相关的缓存可以由同一组安全权限下的多个会话共享。但是，在用户查询中使用 Create Member 和 With Member 定义的计算成员没有全局范围，与这些查询相关的缓存无法实现跨会话共享。
• 通过使用自选工具执行一组预定义的查询来准备缓存。使用 Create Cache 语句也可以达到同一目的。有关使用 Create Cache 语句的详细信息，请参阅如何使用 Create Cache 语句来准备 Analysis Services 数据缓存。有关如何使用 SQL Server 2005 Integration Services 准备缓存的信息，请参阅在 Integration Services 中生成自己的 Analysis Services 缓存准备器。
• 重新编写包含任意形状的 MDX 查询，以充分利用缓存。例如，在某些情况下，可以重新编写要求无缓存磁盘访问的查询，在 FROM 子句中使用嵌套 select 语句，而不使用 WHERE 子句，以使这些查询可以完全从缓存解析。在其他情况下，使用 WHERE 子句可能要好一些。

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

• 请注意，对维度使用 ProcessUpdate 来增量更新维度会删除受更新和删除影响的所有柔性聚合，且在默认情况下，在下一个完整处理之前不会重新创建柔性聚合。
• 请务必处理受影响的对象、配置迟缓处理、对受影响的分区执行 ProcessIndexes 或对受影响的分区执行完整处理，从而重新创建聚合。

若要了解如何确保柔性聚合不被删除，请参阅 SQL Server 2005 Analysis Services 性能指南。

优化内存使用率

• 增加 Analysis Services 服务器上分页文件的大小或增加内存，以防当分配的虚拟内存量超过 Analysis Services 服务器上的物理内存量时出现内存不足错误。
• 使用 SQL Server 2005（32 位）时，请在 SQL Server 2005 Enterprise Edition（或 SQL Server 2005 Developer Edition）中使用 Microsoft Windows Advanced Server® 或 Datacenter Server，以便 Analysis Services 能够寻址高达 3 GB 的内存。若要使 Analysis Services 能够在这两个版本中寻址超过 2 GB 的物理内存，请在 boot.ini 文件中使用 /3GB 开关。如果在 boot.ini 文件中设置 /3GB 开关，则服务器至少应拥有 4 GB 内存，以确保 Windows 操作系统也有足够的内存用于系统服务。
• 运行多个 Analysis Services 实例或同一台计算机上还运行其他应用程序时，请将 Memory/LowMemoryLimit 属性值减至 75% 以下。
• 运行多个 Analysis Services 实例或同一台计算机上还运行其他应用程序时，请将 Memory/TotalMemoryLimit 属性值减至 80% 以下。
• Memory/LowMemoryLimit 属性值和 Memory/TotalMemoryLimit 属性值之间需要有一定差距，通常为 20%。
• 在多用户环境中检测到查询失败时，请与 Microsoft 支持部门联系，请求他们协助修改 MemoryHeapType。
• 当运行在非一致性内存访问 (NUMA) 体系结构上时，如果 VirtualAlloc 返回需要的时间非常长，或似乎停止响应，请升级到 SQL Server 2005 SP2，并联系 Microsoft 支持部门，请求协助对预分配 NUMA 内存进行合适的设置。

若要了解何时应考虑更改默认内存使用量，请参阅 SQL Server 2005 Analysis Services 性能指南和 Microsoft SQL Server 客户咨询小组博客。

优化处理器使用率

• 为了增大拥有多个处理器的服务器在查询过程中的并行度，请考虑根据服务器的处理器数目修改 Threadpool\Query\MaxThreads 和 Threadpool\Process\MaxThreads 选项的值。
• 通常，建议将 Threadpool\Query\MaxThreads 设置为一个小于或等于服务器上处理器数两倍的值。例如，如果服务器有八个处理器，则通常将它设置为不超过 16 的值。实际上，增大 Threadpool\Query\MaxThreads 选项的值不会显著提高给定查询的性能。然而，增大此属性值可以增加同时处理的查询数。
• 通常，建议将 Threadpool\Process\MaxThreads 选项设置为一个小于或等于 10 倍于服务器上处理器数的值。此属性控制存储引擎在查询操作及处理操作期间所使用的线程数。例如，如果服务器有八个处理器，则通常将它设置为不超过 80 的值。请注意，如果给定服务器上的处理器数不足八个，那么，即使默认值为 64，也无需减小该默认值来扼制并行操作。
• 虽然修改 Threadpool\Process\MaxThreads 和 Threadpool\Query\MaxThreads 属性值可以增大查询过程中的并行度，但也必须注意 CoordinatorExecutionMode 选项的影响。例如，如果服务器有四个处理器，并接受 CoordinatorExecutionMode 的默认设置 -4，则在所有服务器操作中一次总共可执行 16 个作业。因此，如果并行执行 10 个查询，且总共需要 20 个作业，则在给定时间只能启动 16 个作业（假设当时未执行任何处理操作）。达到作业阈值时，后续作业将排队等候，直到能够创建新作业为止。因此，如果作业数量是操作瓶颈的话，增大线程数并不一定会改善总体性能。

尽量向上扩展

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

无法再向上扩展时则向外扩展

• 如果性能瓶颈是由多用户查询工作负荷导致的单个系统上的处理器利用率问题，可以使用一组 Analysis Services 服务器为查询请求提供服务，籍此提高查询性能。请求负载可均衡地分布在两台或更多 Analysis Services 服务器上，以支持大量并发用户（这称作服务器场）。负载平衡群集通常线性扩展。
• 使用 Analysis Services 服务器群集来提高查询性能时，先在单台处理服务器上执行处理，再使用 XMLA Synchronize 语句将处理与查询服务器同步，然后使用 Robocopy、其他文件复制实用工具或 SAN 存储解决方案的高速复制功能来复制数据库目录。