1

I have SSIS packages that do a simple data transfer between 2 SQL Servers. There's one parent package and 6 child packages that have been built the same way. I have setup child packages to be run as separate processes (ExecuteOutOfProcess=True property). Also I have enabled logging of User:BufferSizeTuning property in each child package.

Everything works fine in DEV server which is pretty the same as PROD server. But in the PROD server I'm getting the following two messages from the User:BufferSizeTuning property (taken from sysssislog table):

  • Memory pressure was alleviated, buffer manager is not throttling allocations anymore
  • Buffer manager is throttling allocations to keep in-memory buffers around 199MB

Furthermore the job in PROD server usually runs about 2-3 hours (in some cases 11 hours!!) when in DEV - ~30 mins. Both server are SSIS 2008 servers.

jambis
  • 122
  • 2
  • 10
  • can you provide the values for DefaultBufferMaxRows and DefaultBufferMaxSize have the been changed from the defaults: 10,000 and 10,485,760 (10 MB), respectively it could be that the prod server has less available memory then the dev server even though the total ram in the system is less – Jason Horner Jan 12 '13 at 19:59
  • Note SSIS requires physical memory not virtual (e.g. in Task Manager, watch Physical Memory / Free). BTW by forcing Execution Out of Process you are probably wasting memory. I would use the Control Flow to achieve parallel execution in one process. – Mike Honey Jan 14 '13 at 07:17
  • @JasonHorner, yes I changed these values for each child package accroding to this guidance [link](http://technet.microsoft.com/en-us/library/cc966529.aspx). – jambis Jan 14 '13 at 08:53
  • @Mike Honey, I checked the amount of the physical memory during a run and it didn't even reach 50% of the total RAM. I'll try to disable the Execution Out of Process and I'll let you know. – jambis Jan 14 '13 at 08:58
  • Yikes! I'm having almost exactly the [same issue](http://stackoverflow.com/questions/20606292/ssis-package-runs-for-500x-longer-on-one-server)! Found this question after enabling BufferSizeTuning and getting similar "throttling" messages. I don't get the first message though. Weird, our execution times are so similar also--mine runs 2.5 hours, 7 hours, or 13 hours, almost quantized that way, it's very strange! – S'pht'Kr Dec 24 '13 at 10:05

0 Answers0