Buffer management

Question

Buffer management

ssisnovice

SSC-Addicted

Points: 481
More actions
February 1, 2012 at 8:15 am

#148955

Hi, I am dumb at understanding the buffer and its size(100mb max) in SSIS
We have a packages which extracts the data from oracle 11g and loads into sql server2005 destination.
There are around 3million records in a source table which is around 2gb in size of data. In order to extract 3million rows from a table will the SSIS Data flow engine create multiple buffers of 100mb each or will it issue a multiple selects to db with a single buffer for each data set of 100mb.
In case of straight load without any validation will the insert in destination table happened after the buffer is filled or as and when the rows coming into the buffer. Thanks for your time.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

SSIS selects the data from Oracle. As Oracle returns the data (which isn't everything at once for such a big dataset), SSIS starts to fill up the buffer. Once a buffer is full, the source component passes it on to the next component and starts to fill up the next buffer. So the destination can start to write data, while the source is still reading.

It helps to look at the dataflow as a "pipeline of data". If you open the source, data starts to flow through.

Interesting read:

The SSIS tuning tip that everyone misses

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ssisnovice SSC-Addicted Points: 481 More actions · Answer 2

Hi koen verbeeck,

Thanks a lot, now i got understood.

So whether each buffers max size could be 100mb or the 100mb is the max limit for any number of buffers that were created as part of execution of a package.

Thanks again for your timely response.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

A bit more info on buffer size:

Adjust buffer size in SSIS data flow task

Another excellent article on SSIS performance:

Top 10 SQL Server Integration Services Best Practices

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ssisnovice SSC-Addicted Points: 481 More actions · Answer 4

Hi Koen verbeeck,

thanks for providing such great link

The SSIS tuning tip that everyone misses.

I went through the whole blog. it was very usefull.