新三国草船借箭:SQL Server Analysis Services 性能优化之 Processing...

处理优化

要理解如何提升统一维度模型处理的性能你首先要理解分析服务处理操作的原理。分析服务支持ROLAOP和MOLAP这两种对维度的存储方式，而对划分还支持HOLAP的方式。假设数据源是关系数据库。
分析服务发送单独的关系查询到检索维度和事实数据。关系数据源从它的的文件存储器读出数据并且发送记录给分析服务。分析服务从关系数据源读取数据并且以一种独有的格式存储以方便快速取得数据。在维度处理过程中分析服务发送单独的查询去处理每个维度的属性。每个属性的成员的键和名字有索引的被存储下来进行快速读取。那些属性的关联的特性同样有索引。如果一个属性有一个关联的属性，那么关联的属性应该先被处理。分析服务处理属性是在资源可用并指定并行的情况下并行进行的。由于所有的属性都与键属性相关联，所以维度的键属性是最后一个被处理的。当处理划分时，分析服务从关系数据源读取事实数据并且存储为特有的格式。然后分析服务建立索引从而有效而迅速的取得数据。如果划分里设计了聚合，那么聚合在索引之后建立。

如果存储类型是ROLAP，分析服务只存储元数据，在关系数据源和分析服务数据库中没有数据转换，而且分析服务端也没有真正意义上的数据存储。因此ROLAP处理更快，但使用这种类型，在查询时会有影响，查询结果的速度会比MOLAP慢。这是因为查询数据需要从数据源来检索数据，然后分析服务进行聚合最后展示给最终客户。

当选择HOLAP类型时，数据源中的数据会聚合会计算并存储在分析服务中，这种存储方式只用在你对分析服务复制的数据有空间约束时。这种方式相比MOLAP不会节约很多处理时间。对比MOLAP性能上的优势，我们建议使用MOLAP这种存储方式。对于小规模的活动的划分（当前时间段的数据）我们建议使用ROLAP模式去得到最近的数据。

你可以在初始化处理时进行用户查询，分析服务利用事务中的锁去保证数据视图的原子性和约束。这种锁被称为数据库提交锁。一般情况下正常工作甚至根本不需要知道有它的存在，但是在复杂的用户使用场景下，知道这种处理可以帮助解释系统行为，否则看起来像是异常。它同样可以帮助你在系统负载的基础上进行高效的处理操作。

在查询时，服务器使用了一个读DB提交锁。这保证了数据库不会查询时被修改或者删除。在处理过程中，对象的一个新的版本被建立，这些对象包括维度，事实表划分等。在磁盘中原始的的版本没有被马上重写；一个映射拷贝被建立。一旦新的版本生成，在处理事务中，获得一个写DB提交锁。然后新的文件自动的替换掉旧的文件，此时，锁也被释放掉。过程可能在一秒之内结束，因为锁一般都非常小。但无论如何，获得这个锁都需要等待正在执行的锁的所有者让渡他们的锁。因此一个执行时间较长的查询可以阻碍整个处理命令的执行。

强制提交超时（ForceCommitTimeout）服务器特性定义了一个处理命令必须去等待一个DB提交锁的时间。默认是30秒并且定义在配置文件中。在超时时间后，所有的事务的锁都将被强制关掉，错误信息返回的是“操作已经被取消（The operation has beeb cancelled.）”，在其他的场景中，这条信息同样有可能被返回，但是当要解决问题的时候这种情况是要记住的原因。特别是读锁的所有者可能是一个或多个长时间的运行的查询，对于系统来说，强制关掉可能是更好的选择。

当处理命令中有多个长时间运行的查询共存能够导致锁链。当一个处理命令在等待一个长时间的查询，新的查询必须等到处理结束。尽管查询的新的读锁与存在的被授权的读锁匹配，授权给新的需求会导致处理命令饿死，所以锁进入队列。最后结果表现为挂起。如果你尝试利用SSMS来连接分析服务，当你的处理命令中有一个长时间运行的查询在等待其他长时间运行的查询结束的时候，你就会看到这种结果。这是由于SSMS建立了一个默认的数据库，它需要读DB提交锁。下面是建立一个锁链的事件列表：

1， 长时间运行的查询获得并且保留读DB提交锁

2，处理命令完成然后等待获得写DB提交锁

3， 新的查询等待获得读DB提交锁

建立划分加速处理

我们期望分析服务安装在一个多核机器上，以便利用其多处理器的优势得到更好的性能。当一个事实表在一个量度值组中定义了多个划分，由于分析服务的固有的并行性，划分会被并行处理。当然同样也能修改并行性。因此多个划分相对于一个单一的划分减少了量度值组的处理时间。我们建议你有很大量的数据时都为量度值组建立多个划分，你应该考虑大小5GB或者2000万行的划分。基于你的商业场景，你可能会需要建立超过这种规模的划分。

建立太多的划分会可能损害处理的性能。分析服务并行处理对象是基于你系统的资源。如果所有的划分并行处理它们会竞争资源。此外如果分析服务发送请求去检索所有划分的数据，那么相关的数据源也会降低处理性能。分析服务允许你去控制并行处理的对象个数。你应该确保基于用户查询设计正确的划分数量和并行处理合理的规模。

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

选择小而合适的数据类型和大小

选择整型为表的键列可以提高处理和查询的性能。使用单一的整型键列（而不是字符串或者复数键）会因为减小的大小，网络使用率，关系数据源内部键的处理，还有分析服务可以通过简单的本地机器指令完成工作而提高处理性能。当处理维度或者多维数据集时，分析服务查找维度成员的键。分析服务常规查找整型的键列，相比较其他类型能够运行的快几十甚至数百倍。

SQL Server 和分析服务的安装

当你安装SQL Server时，你可以把它和分析服务安装在同一个机器，也可以不在一起。当你的统一纬度模型从SQL Server检索数据时，对于处理（Processing）这里有一些权衡你需要考虑的。如果你把它们都装在同一台机器上，SQL Server和分析服务可能会竞争资源。你必须确保你又足够的处理器和内存配置。无论何时当年你的分析服务维度规模巨大（数百万成员）的时候都会影响处理速度。如果SQL Server和分析服务在竞争资源，可能会有重要分页的数据导致操作速度急剧下降。

对于32位机器我们推荐打开/3GB标志。默认情况下每个在WINDOWS运行的程序最多只能访问2GB的地址。如果打开了/3GB标志，你可以让你的分析服务服务程序访问到3GB的地址空间。这样增加了可访问的内存并且促进了大型维度的处理和聚合的建立。要打开/3GB选项，要打开你的boot.ini 文件，然后加上/3GB选项，重启电脑，格式：multi (0)disk (0)rdisk (0)partition (2)\WINNT="????" /3GB。

除了在32位机上打开这个选项，只要你把SQL Server和分析服务都装在同一个机器上，而且你的模型中包含有大规模维度，考虑用更大的内存容量（比如8GB）。SQL Server 可以通过Address Windowing Extensions（AWE）来访问额外的内存。因此添加额外内存可以保证两个服务器都有足够的内存去提供好的性能。另外一个选择是在64位机上有更大的内存使用SQL Server和分析服务。

如果你把SQL Server和分析服务安装在不同的机器上，他们不会竞争资源，但是你必须保证它们之间有这高速网络连接，例如千兆比特以太网。拥有好的网络连接可以保住当查询返回大量数据时减少网络传输时间。

优化关系数据源

在MOLAP划分处理时，分析服务没有连接维度表就发送事实表浏览查询改善了事实表的读取。但当你设置ROLAP划分时，建议你建立合理的索引，因为可以操作维度表之间的连接，从而发送给关系数据源的查询会更快的得到结果。

避免过度的聚合设计

聚合设计就是在处理划分时定义需要创建的聚合。如果合理的聚合被创建会提高查询性能，但是如果过度的聚合就会增加处理划分的时间了。分析服务会额外的临时文件并且需要为每个划分往磁盘写更多的数据。一个普遍的经验法则是你创建聚合去提高10%到30%的性能。如果你需要更多的查询性能提升，你应该使用以使用为基础的聚合即UBA（usage-base aggregation），设计针对用户访问多维数据集的这种需求为基础的聚合。你同样可以对每个划分采用不同的聚合，可以为特定的划分减少不必要的聚合，这样来提高多维数据集的处理速度。查询比较多的划分中设计更多的聚合，相反使用较少的划分应该设计较少的聚合。

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

合适的时候使用增量处理

通常，关系数据库中的数据会变化。这种变化可能导致在存在的表中增加新的行或者更改已经存在的行。如果你设置维度和划分为ROLAP这种存储方式，那么你讲从关系数据源检索用户查询所需要的数据。当由于上一个查询，导致结果被缓存在分析服务的时候，分析服务默认的不会去关系数据库中提取数据。你可以使用ROLAP模式，强制分析服务总是从数据库中取得数据。当然，如果你的多维数据集和维度都是MOLAP的存储模式，查询只从处理过的存储在分析服务中的MOLAP数据中得到结果。除非你更新分析服务中的MOLAP数据，否则最近对关系表中的数据更新是无法呈现给用户的。

有很多在分析服务上更新数据的方式。你可以只处理和变化的关系表相关的数据集和维度，它们才是需要更新的。有几种处理选项来优化你的处理。Process Incremental和Process Add是帮助你处理维度和划分的，能让你只更新必要的新的数据。不是所有划分和维度中的数据都被更新的，只有那些在上一轮处理中修改过的需要更新。

在一个划分的增量处理中，分析服务从关系数据源中检索新的数据并且把它加入到临时的一个划分中。然后建立这些新数据的聚合，最后临时的划分合并到已经存在的划分中。当数据合并完成，就可以看到新的数据反映到查询结果中了。因为分析服务只检索关系表中新加入的数据，划分的增量处理选项帮助你快速的处理划分。但是，如果你的划分由于关系数据源的中的数据变化而频繁的处理，我们建议你每隔一段时间醉一次完成的Process，因为完整的Process会对多维数据进行排序和优化。同磁盘碎片整理程序类似，在磁盘上对数据排序将提高查询性能。

使用ProcessIncremental或者ProcessAdd选项可以增量的处理维度。ProcessIncremental 从关系数据源中检索数据，比较分析服务中的已经存在的维度成员的数据，然后当这些数据有变化就更新。如果有新的成员，它们就被加入到维度中，并不影响划分。但是，如果维度成员更新后属性间的关系改变了（如果一个雇员的婚姻状况从单身到已婚），那么相关划分的聚合就将被删除。数据集仍然对查询可见，但是丢弃了聚合之后的查询性能是有影响的。对于同一个维度增量处理比完全处理话费的时间要长，这是因为分析服务对于已经存在的成员作了检查更新这样的额外工作。但是无论如何，它提供了一种便捷使维度对查询可见而且数据集不用重新处理。Process Add允许你加入新的维度成员到已经存在的处理过的维度中。当你有新的成员要加入到相关维度表中时，建议使用这一选项。当你需要周期性的往产品表中加入新的产品时，Process Add 是有用处的。你需要确定在上一次维度更新中加入列的那些关系查询，这些查询从数据源中检索数据，通过Process Add数据源视图基础被DDL定义好，称为离线绑定。

并行处理

有太多的并行任务会由于上下文转换和内存抖动损害性能，好在分析服务提供了多种选项去控制处理的并行任务数量。基于多维数据集和聚合的复杂性，建议每个CPU处理两到三个对象。

你可以通过修改特定服务器属性来控制并行处理任务的数目。对于处理，主要影响处理性能的服务器属性是CoordinatorExecutionMode。服务器属性可以在SQL Server Management Studio或者配置文件msmsdsrv.ini. 中修改。这个属性可以设置对于一个特定的任务（job）可以并行处理的对象数目的上限。如果值为正数，那么它就是被使用的值，如果是负数，那么将会乘以处理器的数目然后去绝对值来使用。例如默认的数字是-4，那么在一台4核处理器的机器上表明并行处理的最大上限是每个查询16个，设置这个属性之后你可以避免服务器过载。

另一个属性，线程池处理最大线程数，标记了在线程池中能够存在的最大线程数。不建议使用它成为限制并发性的手段，因为内部实现中服务器执行任务通常会查询其他任务并等待结束。服务器被设计的足够智能可以感知需要更多的进程来避免因为超过最大线程数导致的死锁。

当一个数据库中所有的对象被并行处理时，与划分处理一样，分析服务查询维度，有时候太多的连接和查询会增加处理时间。你可以限制分析服务的连接数。你能用最大连接数（Maximum Number of Connections）这个数据源属性来控制并发的连接数目。

认识资源的瓶颈

分析服务处理查询性能需要配置好的硬件才能得到最好的结果。处理性能需要足够的内存，CPU速度，和良好的硬盘读写速度。这三个条件扮演了重要的角色。分析服务允许你在服务器操作中监视系统资源的使用情况。再确认了系统的瓶颈之后，你能够采取恰当的手段去缓解服务器性能的热点。

一些简单的硬件条件，如增加服务器内存，增加更多的CPU,或者使用高速读写硬盘能够潜在的提升系统的处理性能。如果内存是主要的瓶颈，我们强烈建议你考虑64位机器去提高性能和处理能力，特别对于有巨大维度的多维数据集（超过一千万维度成员）

在有数百个连续划分时，在一台32位机器上并行处理划分会导致这些划分为了内存资源互相竞争。这种情况下如果系统没有足够的内存可能导致错误。你应该按以下方法分隔这些划分的处理过程：在一个时间处理更少的划分，并且交错处理所有的划分；或者使用上面提到过的那些并行处理的方法；或者在处理数据之前建立处理索引分隔所有的划分，包括处理数据，索引，聚合，而不是做一次完整的处理；默认情况下OLAP\ProcessPlan\MemoryLimit 这个设置成65，意味着65%的可用内存。如果/3gb这个标志没有打开，那么分析服务只能使用2GB的65%。如果你认为需要更多的内存可以尝试调高到75到80。

• 增加 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 内存进行合适的设置。

• 为了增大拥有多个处理器的服务器在查询过程中的并行度，请考虑根据服务器的处理器数目修改 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 存储解决方案的高速复制功能来复制数据库目录。