关于SSIS内存使用的一些疑问

1、数据流中聚合转换的内存使用
   假设需要聚合的数据总量是50000000行，合计物理大小为10G，而分配给SSIS的最大可用内存是4G(SSIS和数据库引擎使用的是不同内存区域？),这种情况下，会出现什么情况？出错或者效率极差？
   据说在大数据量压力下，聚合转换、查找、排序组件性能很差？
2、将聚合操作放在数据流的源中
   思路：首先定义一个sql任务，内容是一个包含group by的聚合查询，将完整结果集传递给一个Object变量，变量作为数据流的源，能不能解决问题1的情况？
3、整个过程放在一个sql任务中
   sql任务脚本类似这样：
    insert into XXX(......)
    select .....
    from XXX
    group by .....

以上3种方式在数据大小远远大于可用物理内存的情况下，哪种方法比较好？

45 个解决方案

#1

友情帮顶

#2

学习 

#3

等水哥來解決.

#4

UP,STUDY

#5

学习了要

#6

数据量大了，什么问题都来了
感觉2好点

等高人

#7

印象中Jinjia是SSIS达人阿，恳请指点一下。

#8

关注中。。。

#9

关注，帮顶！

#10

顶者有分

#11

学习

#12

ETL的时候数据动不动就上G,甚至十G的,不可能期望计算机的内存要和数据量一样大吧,SSIS以及SQL SERVER自身会对内存进行一些调配的.

比较2,3,我觉得3可能会更快,更直接. 2在处理的过程中还是用到了3的SELECT GROUP操作.

不过没有试过,你可以用一个小点的数据量比较一下.

#13

测试环境：4*4核CPU，8G物理内存，5000W+行数据
结果：1最快，且CPU平均负载仅10-15%，约04：10，
     2和3 CPU平均负载50-60%，执行时间分别为04:48,05:00
     
片面测试，可能并非最终性能优劣排名。

招唤达人指点。

#14

不错,实践出真知啊...

#15

方法二效率高

#16

引用楼主 pbsh 的帖子:

1、数据流中聚合转换的内存使用 
  假设需要聚合的数据总量是50000000行，合计物理大小为10G，而分配给SSIS的最大可用内存是4G(SSIS和数据库引擎使用的是不同内存区域？),这种情况下，会出现什么情况？出错或者效率极差？ 
  据说在大数据量压力下，聚合转换、查找、排序组件性能很差？ 
2、将聚合操作放在数据流的源中 
  思路：首先定义一个sql任务，内容是一个包含group by的聚合查询，将完整结果集传递给一个Object变量，…

例子：我曾经的服务器4G内存，处理100G数据量。NB吧

出错到不会。效率低点，经常超时是可能的。
毕竟你还有虚拟内存，两个加一起有12G，够把数据聚合的了。

你的方法都不能解决根本问题。
我的建议是你在建一表吧，就叫聚合区间表（08年表，09年表），每天晚上把一个区间的数据聚合后导入到相应的表里面，查询只在这个聚合后的区间表里查询。

Thanks 

#17

引用 16 楼 taoistong 的回复:

引用楼主 pbsh 的帖子:
1、数据流中聚合转换的内存使用 
  假设需要聚合的数据总量是50000000行，合计物理大小为10G，而分配给SSIS的最大可用内存是4G(SSIS和数据库引擎使用的是不同内存区域？),这种情况下，会出现什么情况？出错或者效率极差？ 
  据说在大数据量压力下，聚合转换、查找、排序组件性能很差？ 
2、将聚合操作放在数据流的源中 
  思路：首先定义一个sql任务，内容是一个包含group by的聚合查询，将完整结果…

5000W已经是按月和半月分过区了，呵呵

#18

ssis的那几个数据处理组件对海量数据基本是没有用的,包括合并和联合都会内存溢出.

聚合这个功能,ssas才是王道,你可以用ssis把原始数据迁移之后,用ssas处理聚合.而不是去把group by过的数据插入到新的表.

#19

引用 18 楼 jinjazz 的回复:

ssis的那几个数据处理组件对海量数据基本是没有用的,包括合并和联合都会内存溢出. 

聚合这个功能,ssas才是王道,你可以用ssis把原始数据迁移之后,用ssas处理聚合.而不是去把group by过的数据插入到新的表.

总算把剪老大等来了。。。

公司的BI是使用第三方BI工具做的，没有使用SSAS。
我确实需要对这些源数据进行预计算。
我使用脚本任务把group by 定义成动态语句，作为OLEDB源，逐个分区表进行计算，这样的话主要开销应该不再管道上吧？

#20

据说在大数据量压力下，聚合转换、查找、排序组件性能很差？  这话对的

#21

我感觉，原则上要处理的数据尽量在sql的SP，view里处理完，ssis只是用来数据传送（除非是关于数据类型的转换，基本不怎么影响效率）。个人觉得ssis在数据处理方面做了很细的限制，或莫名的出些错，所以还是处理好了再传输吧

#22

那你就先把结果group by 出来直接传送吧.

#23

如果把完成结果集放在包域的Object变量里，是不是说明这个结果集会一直被保存在内存中，直到SSIS包结束？

#24

引用 22 楼 jinjazz 的回复:

那你就先把结果group by 出来直接传送吧.

如果我只是使用脚本任务定义一个变量存放Group By语句，然后该变量作为数据流任务的源，和直接引用表作为源，增加聚合组件，有区别吗？

#25

哪位大虾有使用SSIS处理海量数据的经验，请分享一下，谢谢。
可能单笔记录5000w-1E,物理大小过百G的最好。

#26

引用 25 楼 pbsh 的回复:

哪位大虾有使用SSIS处理海量数据的经验，请分享一下，谢谢。
可能单笔记录5000w-1E,物理大小过百G的最好。

我都是处理原始纪录的，而且也不会单比这么多。可以设置一些时间戳，只导入增量的。你还是要好好考虑你的数据流设计是否合理。在bi的etl流中做聚合运算时十分没有必要的，因为这个本来就是olap的强项没必要提前用不擅长这项工作的sql去处理

#27

引用 26 楼 jinjazz 的回复:

引用 25 楼 pbsh 的回复:
哪位大虾有使用SSIS处理海量数据的经验，请分享一下，谢谢。 
可能单笔记录5000w-1E,物理大小过百G的最好。 



我都是处理原始纪录的，而且也不会单比这么多。可以设置一些时间戳，只导入增量的。你还是要好好考虑你的数据流设计是否合理。在bi的etl流中做聚合运算时十分没有必要的，因为这个本来就是olap的强项没必要提前用不擅长这项工作的sql去处理

特殊情况，数据半月处理一次。由于BI是使用Microstrategy，所以我只能在OLAP数据库中尽可能的帮他做一些预计算的聚合。由于数据流众多，都写成存储过程也会很多，看着也很烦。

#28

准确地说，单张源表平均2000w行，一次处理20-30张。我倒不怕他处理很久，只要在半天能完成就可以了。主要是服务器别因为这个崩溃了。

#29

Microstrategy和ssas应该在olap的功能上差不多吧，只要支持多为数据集查询就没有必要为她预处理。海量数据的group by对于服务器和内存以及tempdb的压力都是巨大的，如果是64位服务器还有可能完成，32位机器直接死掉是必然的。

#30

服务器是64位的。
如果不帮他预处理，基本就是一个结果，崩溃。

#31

1、数据流中聚合转换的内存使用 
  假设需要聚合的数据总量是50000000行，合计物理大小为10G，而分配给SSIS的最大可用内存是4G(SSIS和数据库引擎使用的是不同内存区域？),这种情况下，会出现什么情况？出错或者效率极差？ 
  据说在大数据量压力下，聚合转换、查找、排序组件性能很差？ 
2、将聚合操作放在数据流的源中 
  思路：首先定义一个sql任务，内容是一个包含group by的聚合查询，将完整结果集传递给一个Object变量，变量作为数据流的源，能不能解决问题1的情况？ 
3、整个过程放在一个sql任务中 
  sql任务脚本类似这样： 
    insert into XXX(......) 
    select ..... 
    from XXX 
    group by ..... 

以上3种方式在数据大小远远大于可用物理内存的情况下，哪种方法比较好？

运行sql事件侦查器

#32

引用 31 楼 ChinaJiaBing 的回复:

运行sql事件侦查器

sql server profiler可以跟踪SSIS组件？
SSIS是否跟sql server使用相同的内存区域？例如共有8G内存，自定sql server最多可用5G，那么SSIS组件使用的是这5G呢还是剩余的3G?

#33

sql任务脚本类似这样：
    insert into XXX(......)
    select .....
    from XXX
    group by ....
---------------------

你指望5kw数据表里面运行这样的语句？ssis不占用sql的内存吧

比如

oledb源-组件-oledb目标
----------------------

oledb源中执行select... 这个占用sql资源，如果是简单的select，再多的行业是分批执行，不会有太大压力最多数据量大慢点。如果包含聚合函数，那就会带来巨大压力。
组件中执行聚合转换、查找、排序占用的应该是SQL Server Integration Services服务的执行进程MsDtsSrvr.exe的内存空间。
oledb目标的压力应该可以忽略不计，只是普通的insert而已。

#34

引用 33 楼 jinjazz 的回复:

sql任务脚本类似这样： 
    insert into XXX(......) 
    select ..... 
    from XXX 
    group by .... 
--------------------- 

你指望5kw数据表里面运行这样的语句？ssis不占用sql的内存吧 

比如 

oledb源-组件-oledb目标 
---------------------- 

oledb源中执行select... 这个占用sql资源，如果是简单的select，再多的行业是分批执行，不会有太大压力最多数据量大慢点。如果包含聚合函数，那就会带来…

多谢剪老大的解答。
我知道在SSIS中使用执行SQL脚本任务或调用存储过程都是适用SQL Server的内存配额，就是想知道类似聚合转换、查找转换等组件使用的是那里的内存，MsDtsSrvr.exe的内存空间可不可以人为分配？如果oledb源中执行select... 这个占用sql资源，这就说明数据流组件可以处理大批量的问题。
对于海量数据，总有地方需要进行聚合，难道都要一次全部都读进内存才能进行聚合？岂不是很傻？

#35

这个他怎么实现的就不知道了，但性能差是一定的，至少它的性能不会比sqlserver里面group by好，否则sqlserver就需要去面壁了。而一个sql语句group by都解决不了的负载问题，组件是一定解决不了的。这个还是要回到开始的讨论，需求和设计不是无限度的，需要合理。

#36

引用 35 楼 jinjazz 的回复:

这个他怎么实现的就不知道了，但性能差是一定的，至少它的性能不会比sqlserver里面group by好，否则sqlserver就需要去面壁了。而一个sql语句group by都解决不了的负载问题，组件是一定解决不了的。这个还是要回到开始的讨论，需求和设计不是无限度的，需要合理。

我的意思是：在数据流的OLEDB源中使用定义好的包含Group BY语句的Select语句，和相同的语句在存储过程或查询分析其中执行，应该都是由数据库引擎在SQLＳＥＲＶＥＲ内存分配区域来执行，效果应该一样吧？

大数据量的聚合操作，如果没有ＳＳＡＳ，应该没有其他更合理的方式了吧。

#37

应该是一样的，在olap上Microstrategy应该不会比ssas差吧，不过我没用过Microstrategy不了解。即便是聚合也要看负载，ssas服务压力大了也会崩溃。比如你一个维度上有2000万个成员（相当于group by之后的结果有2000万记录），这个服务器也会出问题

#38

引用 37 楼 jinjazz 的回复:

应该是一样的，在olap上Microstrategy应该不会比ssas差吧，不过我没用过Microstrategy不了解。即便是聚合也要看负载，ssas服务压力大了也会崩溃。比如你一个维度上有2000万个成员（相当于group by之后的结果有2000万记录），这个服务器也会出问题

根据业务需求，某一个层次的聚合真的可能会出现聚合后有2000W行的情况。

#39

数据挖掘的概念都是建立在切片钻取这些分析手段上的，聚合结果只有展现在客户端才有意义而不是存储端，显然任何客户端都不需要一次提取2000万给客户浏览，存储2000w聚合数据还不如存储原始数据。

#40

關注。。。

#41

引用 39 楼 jinjazz 的回复:

数据挖掘的概念都是建立在切片钻取这些分析手段上的，聚合结果只有展现在客户端才有意义而不是存储端，显然任何客户端都不需要一次提取2000万给客户浏览，存储2000w聚合数据还不如存储原始数据。

如果从5000W聚合到2000W，你认为有没有意义呢？客户就需要这2000W粒度的东西。首先明确一点，这5000W已经是分过区的数据。

#42

没有意义..

2000万又不可能直接拿来展现，还是需要切片的，切片对于5000万和2000万都是一样的，何必多这么一步呢

#43

如果数据可以聚合到源的1/10，总比切片后再临时聚合效果好吧？毕竟查询的基数不同了。

#44

olap也都是预处理数据的，你做不做聚合对于后期的mdx查询没有任何区别的。只是数据量大生成cube的时间会长点，但cube可以设置分区增量存储（ssas中，但我相信任何一个bi服务都会支持分区的cube），你做了聚合如果不是聚合在时间字段上，反而是帮倒忙的，因为非增量的部分也都变化了，cube只能全部重新生成。

#45

我也用SSAS做过，分区和增量都用到，你说得我也理解。
只是我目前的项目比较特殊，没有SSAS预处理数据。Microstrategy所有的数据都是基于源数据库，BI数据库中只是存放Cube架构，没有预处理后的数据。所有的聚合等操作都是基于源数据库进行运算的，所以才考虑在源数据库中进行大量的人工预处理操作。
剪老大，我发了申请好友信息，如果可以我们可以用MSN等直接交流下，谢谢。