通过

  • 项目

改进数据流的性能

本主题针对如何设计 Integration Services 包提供建议，以避免出现常见性能问题。本主题还提供有关可以用于对包的性能进行故障排除的功能和工具的信息。

配置数据流

若要配置数据流任务以获取更好的性能，可以配置任务的属性，调整缓冲区大小，以及将包配置为可以并行执行。

配置数据流任务的属性

注意注意

必须为包中的每个数据流任务单独设置本节所讨论的属性。

您可以配置数据流任务的下列属性，这些属性都会对性能产生影响：

  • 为缓冲区数据（BufferTempStoragePath 属性）和包含二进制大型对象 (BLOB) 数据的列（BLOBTempStoragePath 属性）指定临时存储位置。默认情况下，这些属性包含 TEMP 和 TMP 环境变量的值。您可能希望指定不同或更快的硬盘驱动器上的其他文件夹来存放临时文件，或将它们分布在多个驱动器上。可以指定多个目录，并用分号来分隔这些目录名。

  • 通过设置 DefaultBufferSize 属性定义任务使用缓冲区的默认大小，并通过设置 DefaultBufferMaxRows 属性来定义每个缓冲区中最大的行数。默认缓冲区大小为 10 MB，最大缓冲区大小为 100 MB。默认最大行数为 10,000。

  • 通过设置 EngineThreads 属性来设置任务在执行过程中可使用的线程数。此属性为数据流引擎提供有关使用线程数的建议。默认值为 5，最小值为 3。但是，不论此属性值为多少，引擎都不会使用超过其所需的线程数。如果需要避免并发问题，引擎所使用的线程数也可能会超过此属性指定的线程数。

  • 指示数据流任务是否以优化模式运行（RunInOptimizedMode 属性）。优化模式会从数据流删除未使用的列、输出和组件，从而提高性能。

    注意注意

    可以在 Business Intelligence Development Studio 中的项目级设置同名属性 RunInOptimizedMode，以指示调试过程中数据流任务以优化模式运行。此项目属性在设计时将覆盖数据流任务的 RunInOptimizedMode 属性。

调整缓冲区大小

数据流引擎通过计算一行数据的估计大小来开始调整其缓冲区大小的任务。然后引擎将估计的单行大小与 DefaultBufferMaxRows 值相乘以获得缓冲区大小的初步工作值。

  • 如果该结果大于 DefaultBufferSize 值，引擎将减少行数。

  • 如果该结果小于内部计算的最小缓冲区大小，引擎将增加行数。

  • 如果结果在最小缓冲区大小和 DefaultBufferSize 值之间，引擎将调整缓冲区大小，以尽可能接近估计行大小乘以 DefaultBufferMaxRows 值得出的结果。

当您开始测试数据流任务的性能时，请使用 DefaultBufferSize 和 DefaultBufferMaxRows 的默认值。对数据流任务启用日志记录，并选择 BufferSizeTuning 事件以查看每个缓冲区中包含多少行。

在开始调整缓冲区大小之前，您可以采取的重要改进措施是通过删除不需要的列并配置相应的数据类型来减少每一个数据行的大小。

在有足够的可用内存时，请使用少量的大缓冲区，而不是大量的小缓冲区。换而言之，可以通过减少存放数据所需的缓冲区总数并在一个缓冲区中放置尽可能多的数据行来改善性能。若要确定缓冲区的最佳数目及其大小，请在试验 DefaultBufferSize 值和 DefaultBufferMaxRows 值的同时监视性能以及由 BufferSizeTuning 事件报告的信息。

不要将缓冲区大小增加到开始对磁盘进行分页的点。与未经过优化的缓冲区大小相比，对磁盘进行分页对性能的阻碍作用更大。若要确定是否在进行分页，可监视 Microsoft 管理控制台 (MMC) 的性能管理单元中的“Buffers spooled”性能计数器。

将包配置为支持并行执行

并行执行能改善具有多个物理或逻辑处理器的计算机的性能。为了支持在包中并行执行不同的任务，Integration Services 使用两个属性：MaxConcurrentExecutables 和 EngineThreads。

MaxConcurrentExcecutables 属性

MaxConcurrentExecutables 属性是包本身的一个属性。此属性定义可同时运行的任务的数量。默认值为 -1，表示物理或逻辑处理器的个数加 2。

若要了解此属性的工作原理，可参考一个包含三个数据流任务的示例包。如果将 MaxConcurrentExecutables 设置为 3，则可以同时运行所有三个数据流任务。但是，假定每个数据流任务都具有 10 个源到目标执行树。将 MaxConcurrentExecutables 设置为 3 不能确保每个数据流任务内的执行树都能并行运行。

EngineThreads 属性

EngineThreads 属性是每个数据流任务的属性。此属性定义数据流引擎可以创建和并行运行的线程数。EngineThreads 属性同样适用于数据流引擎为源创建的源线程和该引擎为转换和目标创建的工作线程。因此，将 EngineThreads 设置为 10 表示该引擎可以创建多达 10 个源线程和多达 10 个工作线程。

若要理解此属性的工作原理，可参考包含三个数据流任务的示例包。每个数据流任务都包含 10 个源到目标执行树。如果将每个数据流任务的 EngineThreads 设置为 10，则可以同时运行所有 30 个执行树。

注意注意

线程不在本主题的讨论范围之内。但是，通用规则是并行运行的线程数不要多于可用的处理器个数。运行的线程数多于可用的处理器个数可能会降低性能，因为此时需要在线程间频繁进行上下文切换。

配置单个数据流组件

若要配置单个数据流组件以优化性能，可以按照某些通用指导原则进行操作。同时还存在针对各种类型的数据流组件的特定指导原则：源数据流组件、转换数据流组件和目标数据流组件等。

通用指导原则

无论采用何种数据流组件，为了改善性能您应该遵循下面两个通用指导原则：优化查询和避免不必要的字符串。

优化查询

大量数据流组件都将在从源中提取数据时，或在查询操作中创建引用表时使用查询。默认查询使用 SELECT * FROM <表名> 语法。这种类型的查询返回源表中的所有列。在设计时使所有列可用，这意味着可以选择任意列作为查找列、传递列或源列。但是，在选择了要使用的列后，您应该修改查询使其只包括那些所选择的列。删除多余的列可以使包中的数据流更高效，因为列越少则创建的行越小。因为行越小，可以置入一个缓冲区的行就越多，对数据集中所有行进行处理的工作量也就越少。

您可以键入查询或使用查询生成器来构造查询。

注意注意

在 Business Intelligence Development Studio 中运行包时，SSIS 设计器的“进度”选项卡将列出警告信息。其中包括当源向数据流提供了某个数据列但下游数据流组件却没有使用它时出现的警告信息。您可以使用 RunInOptimizedMode 属性来自动删除这些列。

避免不必要的排序

排序本身是非常缓慢的操作，因此避免不必要的排序可以提高包数据流的性能。

某些情况下，源数据在下游组件使用其之前已经进行了排序。当 SELECT 查询使用 ORDER BY 子句或者数据按排序顺序插入源中时，即出现这种预排序。对于这种预排序的源数据，您可以提供一个提示说明数据已排序，从而避免使用排序转换来满足特定下游转换的排序要求。（例如，合并和合并联接转换要求使用已排序的输入。）若要提供一个提示说明数据已排序，必须执行下面的任务：

  • 将上游数据流组件输出上的 IsSorted 属性设置为 True。

  • 然后指定数据排序所依据的排序键列。

有关详细信息，请参阅如何为合并转换和合并联接转换排序数据。

如果必须在数据流中对数据排序，则可以将数据流设计为使用尽可能少的排序操作来提高性能。例如，数据流使用多播转换复制数据集。可以在多播转换运行前对数据集进行一次排序，而不是在转换后再对多个输出进行排序。

有关详细信息，请参阅排序转换、合并转换、合并联接转换和多播转换。

源

OLE DB 源

使用 OLE DB 源从视图中检索数据时，选择“SQL 命令”作为数据访问模式并输入 SELECT 语句。访问数据时，使用 SELECT 语句要比选择“表或视图”作为数据访问模式的执行效果更佳。

转换

使用本节中的建议可以改善聚合、模糊查找、模糊分组、查找、合并联接和渐变维度转换的性能。

聚合转换

聚合转换包括 Keys、KeysScale、CountDistinctKeys 和 CountDistinctScale 属性。通过使用这些属性，使转换能够为转换缓存的数据预先分配转换所需的内存量，从而提高了性能。如果知道要从**“分组依据”操作产生的准确或近似组数，则可分别设置 Keys 和 KeysScale 属性。如果知道要从“非重复计数”**操作产生的非重复值的准确或近似数量，则可分别设置 CountDistinctKeys 和 CountDistinctScale 属性。

如果需要在数据流中创建多个聚合，应考虑使用一个聚合转换而不是创建多个转换来创建多个聚合。如果聚合是其他聚合的子集，这种方法能够提高性能，因为转换可以优化内部存储，并且只需扫描传入的数据一次。例如，如果聚合使用 GROUP BY 子句和 AVG 聚合，将它们组合成一个转换可以提高性能。但是，在一个聚合转换内执行多个聚合会序列化聚合操作，因此，当必须独立计算多个聚合时，这种方法可能不会改善性能。

有关详细信息，请参阅聚合转换。

模糊查找和模糊分组转换

有关如何优化模糊查找和模糊分组转换的性能的信息，请参阅白皮书：Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005（SQL Server Integration Services 2005 中的模糊查找和模糊分组转换）。

查找转换

通过输入仅查找所需列的 SELECT 语句，最小化内存中引用数据的大小。这种方法优于选择整个表或视图，因为后者将返回大量不必要的数据。

合并联接转换

合并联接转换包括 MaxBuffersPerInput 属性，该属性建议您要同时为每个输入处于活动状态的最大缓冲区数。不过，您为 MaxBuffersPerInput 属性设置的值仅是一个建议。合并联接转换几乎总是需要将该缓冲区数目增加到大于此建议的值。出于以下原因，通常需要执行此增加：

  • 为从每个输入合并获取足够的数据。

  • 在正在处理数据的两个线程之间避免死锁风险。

注意注意

合并联接转换使用的缓冲区数目可能会变得非常大，而这又可能导致 Integration Services 消耗计算机上的大量内存。

在该转换具有来自每个输入的足够数据并且不存在线程死锁的风险后，该转换恢复使用 MaxBuffersPerInput 属性建议的值。

MaxBuffersPerInput 属性的默认值是 5，这是适合大多数工作情况的缓冲区数。为了提高性能，您可以尝试增加缓冲区的数目。为了减轻内存压力，您可以尝试减少缓冲区的数目。但是，非常小的缓冲区数目可能会对性能有负面影响，并且值 0（零）将禁用所有限制，因此不应使用。

渐变维度转换

渐变维度向导和渐变维度转换是能满足大多数用户需要的通用工具。但是，该向导生成的数据流未针对性能进行优化。

通常，渐变维度转换中最慢的组件是一次对单行执行 UPDATE 的 OLE DB 命令转换。因此，改善渐变维度转换性能最有效的方法是替换 OLE DB 命令转换。可以用目标组件来替换这些转换，目标组件将要更新的所有行保存到一个临时表中。然后，可以添加执行 SQL 任务，该任务同时对所有行执行基于单集的 Transact-SQL UPDATE。

高级用户可以为渐变维度处理设计自定义数据流，此数据流将针对大型维度进行优化。有关此方法的讨论和示例，请参阅白皮书 Project REAL: Business Intelligence ETL Design Practices（Project REAL：Business Intelligence ETL 设计实践）中的章节 "Unique dimension scenario"（唯一维度方案）。

目标

若要改善目标的性能，请考虑使用 SQL Server 目标并测试目标的性能。

SQL Server 目标

当包将数据加载到同一计算机上的 SQL Server 实例时，请使用 SQL Server 目标。此目标针对高速大容量加载进行优化。

测试目标的性能

您可能会发现将数据保存到目标时所花的时间比预期的要长。为了确定速度缓慢是否是由于目标处理数据的能力不足造成的，可以暂时将目标替换为行计数转换。如果吞吐量显著提高，很可能是加载数据的目标导致速度减缓。

监视包的性能

Integration Services 包括可以用于监视包性能的工具和功能。例如，日志记录可以捕获有关包的运行时信息，性能计数器则可以监视数据流引擎。请使用下列建议确定包中对性能影响最大的部分。

查看“进度”选项卡上的信息

SSIS 设计器提供有关在 Business Intelligence Development Studio 中运行包时控制流和数据流的信息。**“进度”**选项卡按执行顺序列出任务和容器，而且还包括每个任务和容器及包自身的开始时间和结束时间、警告以及错误消息。它还按执行顺序列出数据流组件并包括进度信息（显示为完成百分比）和处理的行数。

若要允许或禁止在**“进度”选项卡上显示消息，请在 SSIS 菜单上切换“调试进度报告”**选项。禁用进度报告有助于在 BI Development Studio 中运行复杂包时改进性能。

配置包中的日志记录

Integration Services 包括各种日志提供程序，这些提供程序允许包在运行时将信息记录到不同类型的文件中或记录到 SQL Server 中。您可以为包和各个包对象（例如任务和容器）启用日志项。Integration Services 包括各种任务和容器，每个任务和容器都具有其自己的一组说明性日志项。例如，包括执行 SQL 任务的包可以写入一个日志项，列出该任务执行的 SQL 语句（包括该语句的参数值）。

这些日志项包括诸如包和包对象的开始时间和完成时间这样的信息，从而可以确定运行缓慢的任务和容器。有关详细信息，请参阅记录包执行的日志、在包中实现日志记录和日志记录的自定义消息。

配置数据流任务的日志记录

数据流任务提供了许多可用于监视和调整性能的自定义日志项。例如，您可以监视可能会导致内存泄漏的组件，或者跟踪特定组件运行所用的时间。有关这些自定义日志项的列表和日志记录输出示例，请参阅数据流任务。

使用 PipelineComponentTime 事件

最有用的自定义日志项可能是 PipelineComponentTime 事件。该日志项报告数据流中的每个组件执行五个主要处理步骤中的每个步骤所用的毫秒数。下表对这些处理步骤进行了说明。Integration Services 开发人员应将这些步骤视为 PipelineComponent 的主要方法。

步骤

说明

Validate

该组件查看有效的属性值和配置设置。

PreExecute

该组件在开始处理数据行之前执行一次性处理。

PostExecute

该组件在处理所有数据行之后执行一次性处理。

ProcessInput

转换或目标组件处理由上游源或转换传递的传入数据行。

PrimeOutput

源或转换组件填充数据缓冲区，以传递给下游转换或目标组件。

启用 PipelineComponentTime 事件时，Integration Services 将针对每个组件执行的各处理步骤记录一则消息。以下日志项显示 Integration Services CalculatedColumns 包示例记录的消息的子集。

“Calculate LineItemTotalCost”组件 (3522) 在 ProcessInput 中耗时 356 毫秒。

“Sum Quantity and LineItemTotalCost”组件 (3619) 在 ProcessInput 中耗时 79 毫秒。

“Calculate Average Cost”组件 (3662) 在 ProcessInput 中耗时 16 毫秒。

“Sort by ProductID”组件 (3717) 在 ProcessInput 中耗时 125 毫秒。

“Load Data”组件 (3773) 在 ProcessInput 中耗时 0 毫秒。

“Extract Data”组件 (3869) 在“OLE DB Source Output”输出 (3879) 上的 PrimeOutput 填充缓冲区中耗时 688 毫秒。

“Sum Quantity and LineItemTotalCost”组件 (3619) 在“Aggregate Output 1”输出 (3621) 上的 PrimeOutput 填充缓冲区中耗时 141 毫秒。

“Sort by ProductID”组件 (3717) 在“Sort Output”输出 (3719) 上的 PrimeOutput 填充缓冲区中耗时 16 毫秒。

下列日志项显示数据流任务在下列步骤中消耗了大多数时间，如下所示（按降序排序）：

  • 名为“Extract Data”的 OLE DB 源在加载数据期间耗时 688 毫秒。

  • 名为“Calculate LineItemTotalCost”的派生列转换在对传入行执行计算期间耗时 356 毫秒。

  • 名为“Sum Quantity and LineItemTotalCost”的聚合转换在执行计算和将数据传递到下一转换期间共耗时 220 毫秒，其中有 141 毫秒用于 PrimeOutput，有 79 毫秒用于 ProcessInput。

监视数据流引擎的性能

Integration Services 包括一组性能计数器，用于监视数据流引擎的性能。例如，您可以跟踪所有缓冲区使用的内存总量（以字节为单位），并检查组件是否内存不足。缓冲区是组件用于存储数据的内存块。有关详细信息，请参阅监视数据流引擎的性能。

外部资源

Integration Services 图标(小) 使 Integration Services 保持最新

若要从 Microsoft 获得最新的下载内容、文章、示例和视频，以及从社区获得所选解决方案，请访问 MSDN 或 TechNet 上的 Integration Services 页：

若要获得有关这些更新的自动通知，请订阅该页上提供的 RSS 源。

更改历史记录

更新的内容

  • 阐明了合并联接转换的 MaxBuffersPerInput 属性的工作方式。