Integration Services：高性能策略

核心提示：本文描述了如何成功的运用性能提高策略来对Microsoft® SQL Server™ 2005 Integration Services的数据整合方案进行优化，Elizabeth Vitt在商业智能领域拥有超过十年的商业系统开发，Integration Services：高性能策略，项目管理，咨询和

本文描述了如何成功的运用性能提高策略来对Microsoft® SQL Server™ 2005 Integration Services的数据整合方案进行优化。

Elizabeth Vitt在商业智能领域拥有超过十年的商业系统开发，项目管理，咨询和培训经验。她在数据仓库，ETL和OLAP设计和实施方面还有着丰富的教学经验。

目录

简介

性能建模

操作

定义操作

优化操作

容量

定义容量

优化容量

应用

理解应用的使用

评估选择

区域

定义区域选择

评估区域影响

总结

关于作者

Elizabeth Vitt, Intellimentum

Hitachi Consulting

简介

性能是数据集成解决方案里关乎成败的重要因素。性能不仅决定了是否能够有效的满足当前的数据集成需求，而且影响了解决方案的可扩展性，以及对未来商业需求变化的适应能力。例如，性能问题可以增加数据容量，减少批处理窗口，扩展数据复杂性，形成数据的质量标准。

尽管性能问题在每个项目中都扮演着至关重要的角色，讽刺的是，人们最容易忽视它，往往把它放在开发周期的最后环节才加以考虑。而到了这个时候，设计都已经基本定型了，交付日期也日益临近，客户越来越期盼获得一个良好的产品，你已经没有机会再去进行大的设计调整了。

对一个现有的设计进行性能优化当然也很有意义，但是你会发现，一旦设计细节确定了，你可以进行优化的余地也就被限制了。尽管这些事后的优化工作是不可避免的，但你仍然可以通过在设计过程中就加入整体的性能策略，来尽量减少最后再进行性能优化的麻烦。这些性能策略可以帮助你在整个开发周期内做出更优的设计决策，在可管理的时间和计划内提交出性能更好的解决方案，从而节省了项目的宝贵时间。

Microsoft® SQL Server™ 2005 Integration Services (SSIS)，是一个全功能的数据整合引擎和开发环境，用来创建高性能的数据解决方案。它提供了一个用于数据整合的性能策略，可以通过调整四项性能元素——操作、容量、应用和区域，帮助你设计更好的SSIS解决方案。

性能建模

影响特定数据整合解决方案的因素有很多，一个成功的性能策略需要能够照顾到各种可能的情况，同时也要能够提供可以具体执行的详细信息。这一点恰恰是SSIS性能策略的目标：提供了可以帮助访问数据负载的指导，确定影响数据整合性能的主要因素，最大化各种性能优化技术的效果。以下是性能策略中关键的四个性能元素：

◆操作 – 确认潜在的优化可能性。首先，把你的整合解决方案分解成特定任务或者工作单元，然后对每个任务中所有的数据操作进行完全的定义。这些操作中的一部分很好确认，而另一些有关最佳性能优化机会的操作就不是很容易确认了。

◆容量 – 完全定义了这些操作后，接着确认流程中每一步进程预期的数据容量，明确这些容量是如何影响数据操作性能的。这也是确认数据容量如何随时间进行预期变化的好机会。

◆应用 – 对操作和容量进行规格说明，选择最适合的SQL Server应用或技术。永远会有很多的方法可以完成特定的任务；关键是确认哪一个应用可以花费最小的代价，而为你提供足够的功能。

◆区域 – 最后一个因素就是区域，指的是数据整合操作的运行时环境。Location排在最后是因为其它性能影响因素的参数会影响到你对运行时环境的最佳设置。

一个简单的记忆方法，OVAL，可以帮助你记住这四个影响性能策略的组件。

下面的章节展示了这些方法所带来的好处，你可以使用这些组件来优化一个简单的数据集成方案。在这一情景下，你在使用SQL Server Integration Services来聚集一个5000000条记录的普通文本文件，并且把结果记录读入到目标的SQL Server RDBMS表中。

操作

操作是性能策略中关键的因素。特别强调的是需要理解所有的数据操作，这些操作是解决方案的一部分，你可以确认优化和调整的机会。这意味着超出了手边显而易见的任务，需要从数据源到每一个数据集成操作，确认整个操作流程的顺序，直到目标为止。

定义操作

示范操作如何工作，查阅表1中所列的各种操作。这一表格描述了当使用SSIS去读取并往SQL Server 目的表中聚集普通文本文件数据时，所包含的每一步操作步骤。

表1：操作读取了一个普通文本文件，将其聚合成了SQL Server数据表

操作步骤	性能考虑
1.　　　从文本文件中读取数据	在这一步中，SSIS建立了一个对源文件的连接并读取数据。记录读取的速度很大程度上依赖于SSIS的外部因素，如磁盘速度，I/O资源争夺，网络速度，以及计算机资源。尽管这些因素对于SSIS应用来讲是外部的，还是需要了解他们是如何影响整体性能的。如果在这步中出现了性能瓶颈，你就需要考虑另外的方法了。例如，减少I/O资源争夺，将源文件分开，然后使用顺序读操作。
2.　　　将文本文件中的数据加载到SSIS 数据管道中	在这一步中，SSIS解析源文件，并读入名叫缓存的SSIS内存构造中。在设计时，通过收集象数据类型，排序，解析路由等元数据，SSIS建立了解析文件的规则。在运行时，文件根据这些规则被读取和解析。这些规则越精确，这一过程就越有效率。
3.　　　聚合数据	在这一步中，SSIS把已经读入SSIS数据管道中的数据进行了聚集。为了执行这一聚集，SSIS创建了一个缓存，你可以将此缓存设置成帮助提高性能，并且使SSIS利用的内存资源最大化。
4.　　　在SQL Server打开一个事务	在数据聚集完成后，一系列后续步骤使SSIS把聚集的数据读入到目标的SQL Server数据库表中。从这点看，所有在SQL Server 中建立的帮助优化批量读取的最佳实践都会生效，例如设置数据库恢复模型，启动快速读取，使用表锁定，并且配置提交大小。
5.　　　将数据传递到SQL Server界面
6.　　　将数据读入每个SQL Server目的表
7.　　　提交事务

　

优化操作

对于这一序列中的每一步操作，你都可以使用性能分析，来指导你寻找优化和提升整个过程的机会。例如，在操作2种，当SSIS从文本文件中把数据读入数据管道时，所有的字段都被读成字符串类型，甚至当里面包含整数时也是。如果你的目的是使用已命名，确定类型的列将这一文件读入到的SQL Server目标数据表中，数据转换必须在数据读取的某个过程中就进行了。

为了减少数据集的大小，提升整体数据读取的性能，你可以在这一操作过程的早期就设置数据类型。在表1给出的例子中，对数据设置类型的最佳时间是操作2的过程中，当文件被读入到SSIS数据管道时。如果你不在这时设置数据类型，数据就将以其原始的字符串状态转过所有的步骤，直到它被读入SQL Server前才被完成了转换。

今后再调整文件解析，你也可以获得操作2中SSIS快速解析模型的好处。这一模型对包含一般数据格式的简单数据读取使用快速的解析路由，而并不需要特定的现场解析，这一格式不包含特殊的字符，也不包含十六进制字符。

数据类型优化并不仅仅适用于文件数据源。在SSIS数据读取过程的早期适当的设置数据类型，能够对所有的数据源都带来性能方面的好处。

这只是一个你可以优化数据读取操作的例子。你可以将这一方法在整个过程中的每一步都实施，你就可以为每个性能考量确定出更好的设计方法。

容量

容量是第二个关键，重要的性能影响因素。高级别的容量会对系统的资源，如内存和CPU产生更大的影响。你越是理解容量是怎样对你的数据操作产生影响的，你就越可以调整这些资源的应用，从而产生更有效率的操作。

定义容量

SSIS数据管道引擎使用一个缓存导向的结构，可以在内存中高效的读取和操作数据集。这一内存中处理过程的价值在于，对于数据读取的每个步骤来说，数据都没有必要被物理的拷贝和存储。取而代之，数据管道引擎在将数据从源向目标转移的过程中，使用缓存来管理数据。

为了优化这个管道，你的目标是，在尽可能少的缓存消耗中，传送尽可能多的记录，特别是在你处理大量数据容量的时候。为了达到这一目标，你必须首先对数据的长度（行数）和宽度（列数）进行分析。数据宽度基本上是各列数据类型的长度和，加上SSIS分配的固定长度的内存前缀。数据类型越小，对内存的使用就越有效。

优化容量

当你优化数据容量时，你首先应该评估所有的列，这是数据整合操作的一部分。很多时候尽管只需要这些列中的一部分，却还是将整个源文件的数据都读取了。不需要的列占据了系统的资源，应当尽可能的排除掉。

另外一个要考虑的因素是，不管你的方案是否需要增量或完全的数据读取，从容量的角度出发，完全的数据读取是高消耗的。如果你可以用增量的数据读取替代完全的数据读取，你将极大的减少数据读取操作的容量。这点在一个应用周期中容量不断增大的情况下特别适用。

在SSIS内，你可以通过三个主要的方法来优化容量：收缩数据集的大小，最大化吞吐量，以及定义并行操作。

a.收缩数据集的大小 - 为了演示收缩数据集，设想下面的例子。假定这包含5000000条记录的源文件有100列，都是在1到999之间的数字。如果你对这些列都保留默认的字符串数据类型，每个条记录的每个列需要50Bytes。这意味着每一行计算后需要大约5000Bytes，再加上SSIS需要的内存前缀。所以，对于一个5000000条记录的容量，每行5000bytes，文件的总大小约是23GB。如果你将这些列更精确的定义为2-byte大小的整型，再来考虑数据集大小间的区别。这将使每行的大小变为200bytes，总的大小就缩减为954MB，比字符串数据类型节省了大约22GB。通过数据类型大小的简单改变，你就可以大大减少要读入内存的数据量。注意，以上例子也说明了数据类型之间的相对差别和收缩数据集大小节省的空间，这并不是一个定死的大小调整方针。

b.最大化吞吐能力 – 除了设置数据类型，你还能够通过配置SSIS缓存设置——如DefaultMaxBufferSize和DefaultMaxBufferRows，来最大化吞吐能力。这些设置控制了在数据读取操作过程中创建缓存的大小，以及每次传递的记录行数。但是，对于调整这些参数的细节不在本白皮书内探讨。

c.定义并行操作 – 优化容量的第三个方法是考虑如何对操作进行并行化处理，以获得最大的效率。SSIS支持包，任务以及转换的并行化处理。当你设计并行处理流程时，你要考虑对数据读取操作实施怎样的并行度。例如，如果你需要读取并聚集多个文件的数据到SQL Server中，你可以考虑对以下一个或多个操作并行处理：文件读取，数据聚集，目标数据表装载。尽管SSIS支持所有这些操作的并行处理，你仍旧需要搞清楚系统的资源和它们并行支持的级别。你可能创建了一个高并行度的SSIS包，而随后就意识到你的系统没有足够的CPU和内存来充分体现这些并行的优势。

也许这个读取5000000条源记录的例子很简单，这些相同的原则却可以被直接应用到更大型和更复杂的数据读取任务中，这个任务可以包含多个数据源，需要复杂的数据转换，而且必须被装载到多个目的表中。

应用

根据定义好的一组数据整合操作以及对所需数据容量进行的评估，可以明确出应用因素，来帮助你决定那种数据整合应用最符合那些需求。

理解应用的使用

你可能会经常选择不同的应用程序去解决数据整合问题。关键是要确定哪些应用可以在给你带来足够功能的同时，成本较低。

例如，你面临着使用SQL Server Transact-SQL的BULK INSERT语句或BCP工具来往SQL Server中读取文本文件的工作，与SSIS相比，这样可以在成本较低的情况下满足数据读取功能的需求。当你有较小的数据集和简单的数据整合需求时，上述情况很可能发生，这时启动SSIS操作的开销就能抵消使用BCP或BULK INSERT所获得的性能收益。

帮助你做出正确的选择，我们把每一应用的主要功能组件列出来，以便你能够根据主要的需求做出相应的评价。例如，以下的使用指导可以帮助你在BULK INSERT/BCP或SSIS中做出选择。

◆BULK INSERT / BCP使用指导 – 当你的应用场景有以下的特征时，应该使用BULK INSERT / BCP：

◆有单一的数据源，并且是文件形式

◆有单一的目标，并且是SQL Server

◆没有数据转换的需求，比如直接从源读取到目标

◆没有工作流程管理，注意流程管理并不适合BULK INSERT，因为它只是一个将数据读取进SQL Server的单独操作

◆SSIS使用指导 - 当你的应用场景有以下的特征时，应该使用SSIS：

◆多个不同种类的数据源和目标

◆有数据转换需求：聚集，查询，创建新列

◆与其他的管理任务（如邮件或FTP）相结合

◆通过工作流程管理控制多个任务和转换的顺序

评估选择

根据使用指导，你可以更好的评估哪种应用选择更符合你的数据需求。

在文件读取的例子中，你的需求不仅仅是往SQL Server中读入5000000条记录，还包括对这些记录的数据聚集。考虑到这些需求，确认以下哪个方法可以完成这项任务。注意，这里仅仅列出两个可行的方法。

◆方案1 - BULK INSERT解决方案 – 你当然可以使用BULK INSERT去从源文件读取数据，并装载到SQL Server中。但是，你还需要增加额外的操作，在把数据装载到目标表前对其进行聚集。一种方法是使用BULK INSERT把数据放入一个暂时的表，然后使用Transact-SQL对暂时表中的数据进行聚集，再用Transact-SQL把数据读入目标表中。

◆方案2 – SSIS解决方案– SSIS提供了一站式的解决方案，你可以在聚集数据的同时将其装载到目标表中。并且，在SSIS中你还能添加工作流程来控制这些操作的顺序，甚至对执行过程进行日志，记录操作执行的过程，以及进行出错处理，找出不符合要求的记录。

当你对比上述两个方案时，SSIS解决方案可以不使用暂时表，并且把所有的数据读取逻辑和工作流程装入单一的SSIS包中。而对于方案1，你不仅需要使用Transact-SQL来对数据进行聚集，还需要把数据逻辑封装在一个存储过程中。所以，根据这一对比，SSIS解决方案是这一读取-聚集案例最优的选择，也就是可以提供足够的功能，并且有能力集中管理工作流程。

区域

最后，你需要确认数据操作执行的区域。当你从源向目标读取数据时，你可能会有足够的灵活性去执行特定的操作。很有可能，你的决定会是基于一系列你必须权衡的因素，根据你特定的数据读取需求。

定义区域选择

一般来说，你有三个区域可以选择：(1) 源数据服务器，(2)目标数据服务器，(3) 专门的解析，转换和读取（ETL）应用服务器。现实环境下，你的选择可能会受许可证的限制。但是，考虑到你的应用需求对各个区域的影响，你应该确定最适合你执行需求的区域，你甚至可能会发现有从新评估许可证策略的需要。

评估区域影响

为了说明对区域的影响，假定源数据文件和数据读取操作所在的服务器，以及最后存入数据的目标SQL Server都不在一起：

1.目标数据服务器 – 当你的目标是SQL Server时，在目标数据服务器上执行SSIS操作会带来很大的性能优势。这种情况的最大好处就是可以在SSIS包中使用SQL Server Destination组件。这一组件可以优化在内存里的数据读取，比标准的OLE DB Destination组件获得8%到15%的性能提升。要记住，SQL Server Destination组件不支持数据类型的转换。最终，你需要在往目标表加载数据之前的SSIS步骤中，确认完成数据转换的操作。这也就是，在内存中的数据类型和SQL Server的数据类型是直接相对的。

你当然也要了解SSIS与SQL Server在目标服务器上是怎样争夺资源的。为了减少争夺，你可以为SQL Server或者SSIS设置资源分配。例如，你可以降低SSIS并发线程的最大数目，以避免CPU的瓶颈。总体来说，如果你的数据读取操作需要你找出最快的方法，把数据读入到SQL Server，那么在目标服务器上运行SSIS就是比较好的选择，可以通过SQL Server的内存数据读取功能获得性能的提升。

2.源数据服务器 – 当你在源数据服务器上运行SSIS操作时，你可以在转换数据并通过网络向目标加载数据前，通过减小源数据集的大小来提高性能。例如，如果你需要聚集数据，那么输出记录的条目数一般会小于输入记录的条目数。并且，你可以通过在先前的SSIS过程中恰当的设定数据类型来减小数据集的大小。

想要获得这一好处，你必须接受一些折中处理。如果其他的应用程序运行在源服务器上，SSIS可能会和这些应用争夺机器资源。如果资源比较有限，你很可能会牺牲掉你所获得的性能呢个提升。还有，如果你的目标服务器是SQL Server，你将不能使用经过优化的SSIS SQL Server Destination组件的内存数据读取功能。SQL Server Destination组件只有当SSIS数据操作在目标SQL Server服务器上执行时才会发生作用。除此之外，你必须要使用标准的OLE DB　destination组件，然而，它没有内存数据读取功能所带来的好处。

考虑到这些牺牲，如果你能把大量数据记录减少成为较小的数据集，当你关心网络性能瓶颈的时候，在源数据服务器上执行SSIS操作是有好处的。

3.专门的SSIS服务器 – 当你有多种不同类型的数据源和目标，对较大量数据进行复杂转换时，使用专门的SSIS服务器是最佳的方案。在这一专门的服务器上，SSIS可以使用全部机器资源而不必跟其他应用争抢，这样会获得最佳的优势。

也有不好的方面，你当然也无法使用SSIS SQL Server Destination组件。而且，从网络方面看，你必须要传送数据两次：从源服务器到SSIS服务器，然后再从SSIS服务器到目标服务器。如果你的网络贷款可以轻松的支持这些传送，这一方案提供了最大的SSIS灵活性。它也适合于那些有复杂转换需求的情况，这时SSIS不会反过来影响其他应用，或SQL Server本身。

总结

当你设计数据整合解决方案时，你可以把性能调整看成一种艺术，或科学，但你更关注的是结果。为了帮助你更好的完成性能调整的工作，本文中描述的性能策略提供了一个方法框架，使您可以创建出高性能的数据整合解决方案。

通过指导你对操作，容量，应用和区域进行评估，这一策略可以帮助你预见并优化各种大规模的数据读取情景，无论数据简单还是复杂。

希望了解更多信息，请访问以下网址：http://www.microsoft.com/sql/

关于作者

Elizabeth Vitt, Intellimentum

Elizabeth Vitt在商业智能领域拥有超过十年的商业系统开发，项目管理，咨询和培训经验。她的业界经历包括为零售、制造和金融服务等行业实施商业智能解决方案。她在数据仓库，ETL和OLAP设计和实施方面还有着丰富的教学经验。Vitt女士是微软有关商业智能产品官方课程内容的特约作者，由MSPress出版了《商业智能：更快更好的做出决策》。伴随着SQL Server 2005的正式发表，Vitt女士帮助早期的客户成功的实施了SQL Server 2005方案。