我在 SSIS 中有一个日常 ETL 流程，它可以构建我的仓库，以便我们可以提供每日报告。
我有两台服务器 - 一台用于 SSIS，另一台用于 SQL Server 数据库。SSIS 服务器 (SSIS-Server01) 是一台 8CPU、32GB RAM 的机器。SQL Server 数据库 (DB-Server) 是另一台 8CPU、32GB RAM 的机器。两者都是 VMWare 虚拟机。
在其过于简单的形式中，SSIS 从 DB-Server 上的单个表读取 1700 万行（约 9GB），将它们逆透视为 4.08 亿行，进行一些查找和大量计算，然后将其聚合回大约 800 万行，每次都写入同一 DB-Server 上的全新表（然后该表将被移动到分区中以提供每日报告）。
我有一个循环，每次处理 18 个月的数据 - 总共 10 年的数据。根据我对 SSIS 服务器上 RAM 使用情况的观察，我选择了 18 个月 - 18 个月时它消耗了 27GB 的 RAM。如果高于这个值，SSIS 就会开始缓冲到磁盘，性能就会急剧下降。
这是我的数据流http://img207.imageshack.us/img207/4105/dataflow.jpg
我在用微软的平衡数据分发器将数据发送到 8 条并行路径以最大化资源利用率。在开始聚合工作之前，我会先进行合并。
这是来自 SSIS 服务器的任务管理器图
这是另一张显示 8 个独立 CPU 的图表
从这些图片中可以看出，随着读取和处理的行越来越多，内存使用量慢慢增加到 27G 左右。然而 CPU 使用率一直稳定在 40% 左右。
第二张图表显示我们仅使用了 8 个 CPU 中的 4 个（有时是 5 个）。
我正在尝试使该进程运行得更快（它仅使用 40％ 的可用 CPU）。
我该如何让这个过程运行得更有效率（最少的时间、最多的资源）？
答案1
在听取了 bilinkc 的很好的建议之后，尽管不知道瓶颈在哪里，但我还是尝试了另外一些方法。
正如您已经指出的那样，您应该致力于并行性，而不是在同一个数据流中处理更多数据（几个月）。您已经使转换并行运行，但源和目标（以及聚合）并未并行运行！因此，请读到最后并记住，您也应该让它们并行运行，以便利用您的 CPU 能力。并且不要忘记您内存受限（无法在一个批次中聚合无限多个月份的数据），因此要采取的方法（“横向扩展”）是获取数据块，对其进行处理，并尽快将其放入目标数据库。这需要消除公共组件（一个源，一个联合全部），因为每个数据块都受限于这些公共组件的速度。
源码相关优化：
- 尝试在同一个数据流中使用多个源（和目标），而不是平衡数据分发器 - 您在日期列上使用聚集索引，以便您的数据库服务器能够快速检索基于日期范围的数据；如果您在与数据库所在的服务器不同的服务器上运行包，则会增加网络利用率
转换相关的优化：
- 你真的需要在聚合之前执行 Union All 吗？如果不需要，请查看与多个目标相关的优化
- 放键、KeyScale 和 AutoExtendFactor对于聚合组件以避免重新散列 - 如果这些属性设置不正确，那么您将在包执行期间看到警告；请注意，预测固定月份数的批次的最佳值比无限数量（例如在您的情况下为 18 并且不断增加）更容易
- 考虑在 SQL Server 中进行聚合和（取消）透视，而不是在 SSIS 包中执行 - SQL Server 在这些任务中的表现优于集成服务；当然，转换逻辑可能会禁止在包中执行某些转换之前进行聚合
- 如果你可以在数据库中聚合（和透视/取消透视）（例如）月度数据，请尝试使用 SQL 在源查询或目标数据库中执行此操作；根据您的环境，在目标数据库中写入单独的表、构建索引、使用 SQL 进行聚合的 SELECT INTO 可能比在包中执行更快；请注意，并行化此类活动将对你的存储造成很大压力
- 最后你有一个多播；我不知道那里有多少行，但请考虑以下内容：写入右侧的目的地（在屏幕截图上），然后在 SQL 查询中将记录填充到左侧的目的地（以消除第二次聚合并释放资源 - SQL Server 可能会做得更快）
目的地相关优化：
- 使用SQL Server 目标如果可能（包必须与数据库在同一台服务器上运行，并且目标数据库必须是 SQL Server）；请注意，它要求列的数据类型完全匹配（管道 -> 表列）
- 考虑设置恢复模型在您的目标（数据仓库）数据库上简化
- 并行化目标 - 不要使用 union all + 聚合 + 目标，而是使用单独的聚合和单独的目标（到同一张表）；在这里你应该考虑分割您的目标表并将分区放在单独的文件组上；如果您按月处理数据，请按月进行分区并使用 分区切换
似乎我仍不清楚并行性应该如何实现。你可以尝试：
- 将多个源放入单个数据流需要您复制并粘贴每个源的转换逻辑和目标
- 并行运行多个数据流，每个数据流仅处理一个月
- 并行运行多个包，每个包都有一个仅处理一个月的数据流；还有一个主包来控制每个（月）包的执行 - 这是首选方式，因为一旦投入生产，您可能只会运行一个月的包
- 或与以前相同，但使用平衡数据分发器和 Union All 和 Aggregate
在做其他事情之前，您可能需要进行快速测试：获取原始包，将其更改为使用 1 个月，制作处理另一个月的精确副本并并行运行这些包。将其与处理 2 个月的原始包进行比较。对 2 个单独的 6 个月包和单个 12 个月包同时执行相同操作。它应该会以满负荷 CPU 使用率运行您的服务器。
尽量不要过度并行，因为您将对目标进行多次写入，所以您不想启动 18 个并行的月度包，而是启动 3 个或 4 个。
最后，我坚信内存和目标 I/O 压力是需要消除的。
请告知我们你的进展。
答案2
答案3
（重新发布我最初的回复，没有考虑 BDD）
归根结底，所有处理都受四个因素之一的约束
- 记忆
- 中央处理器
- 磁盘
- 网络
第一步是确定限制因素是什么，然后确定你是否可以影响它（获得更多或减少使用）
组件选择
当你超过 18 个月时，服务器内存耗尽的原因与它需要如此长时间处理有关。数据透视和聚合转换是异步组件。来自源组件的每一行都分配有 N 个字节的内存。同一个数据桶访问所有转换，应用其操作并在目标处清空。该内存桶被反复使用。
当异步组件进入竞技场时，管道被拆分。现在必须将传输该行数据的存储桶清空到新存储桶中才能完成管道。在执行树之间复制数据是一项昂贵的操作，需要耗费执行时间和内存（可能会翻倍）。这也减少了引擎在等待异步操作完成时并行化某些执行机会的机会。由于转换的性质，操作速度会进一步减慢。聚合是一个完全阻塞的组件，因此全部数据必须到达并被处理之后，转换才会将单行发布给下游转换。
如果可能的话，您能将数据透视表和/或聚合推送到服务器上吗？这应该会减少数据流所花费的时间以及所消耗的资源。
您可以尝试增加引擎可以选择的并行操作数量。Jamie 的文章，SQL CAT 的文章
如果你真的想知道你的时间在数据流中花在哪里，请记录执行的 OnPipelineRowsSent。然后你可以使用这个询问将其拆开（用 sysssislog 替换 sysdtslog90 之后）
网络传输
根据您的图表，似乎两个盒子上的 CPU 或内存都没有负担。我相信您已经指出源服务器和目标服务器位于一个盒子上，但 SSIS 包托管并处理在另一个盒子上。您需要支付一笔不小的费用才能通过网络传输该数据并再次传输回来。是否可以在源服务器上处理数据？您需要为该盒子分配更多资源，我祈祷这是一个强大的虚拟机，这不是问题。
如果不行，请尝试设置数据包大小将连接管理器的属性设置为 32767，并与网络操作员讨论巨型帧是否适合您。这两个技巧都在“调整您的网络”部分中。
我对磁盘计数器很不擅长，但你应该能够看到等待类型是否与磁盘相关。