SSIS DataFlowTask DefaultBufferSize and DefaultBufferMaxRows

Question

I have a task which pulls records from Oracle db to our SQL using dataflow task. This package runs everyday around 45 mins. This package will refresh about 15 tables. except one, others are incremental update. so almost every task runs 2 to 10 mins.

the one package which full replacement runs up to 25 mins. I want to tune this dataflow task to run faster.

There is just 400k of rows in the table. I did read some articles about DefaultBufferSize and DefaultBufferMaxRows. I have below doubts.

1. If I can set DefaultBufferSize upto 100 MB, Is there any place to look or analyse how much I can provide.

2. DefaultBufferMaxRows is set to 10k. Even If I give 50k and I provided 10 MB for DefaultBufferSize if which can only hold up to some 20k then what will SSIS do. Just ignore those 30k records or still it will pull all those 50k rocords(Spooling)?

3. Can I use Logging options to set proper limits?

Answer 1

As a general practice (and if you have enough memory), a smaller number of large buffers is better than a larger number of small buffers BUT not until the point where you have paging to disk (which is bad for obvious reasons)

To test it, you can log the event BufferSizeTuning, which will show you how many rows are in each buffer.

Also, before you begin adjusting the sizing of the buffers, the most important improvement that you can make is to reduce the size of each row of data by removing unneeded columns and by configuring data types appropriately.