How to calculate the OLEDB Destination Rowsperbatch and MaximuminsertCommitSize to get maximum Performance

Question

In SSIS ,If we select the FAST LOAD Option then we have to set the 2 properties

OLEDB Destination Rowsperbatch and MaximuminsertCommitSize

Normally ,we go with default values ;but it has been understood that it gives a maximum performance if we can calculate these values(Just seen in a package which has been developed by some old collegues ,who already left the team).

How to calculate theses

 OLEDB Destination Rowsperbatch and 
    MaximuminsertCommitSize

propertiesd value to get maximum Performance?

Answer 1

There is no set formula for this. It's basically - understanding the impacts they have, and trial and error (preferably on a dev environment).

If you are inserting large files -- you should not use the defaults, but no one will be able to give a set formula for what you should use.

Every server/database is different. But large commit sizes can result in huge tempdb growth.. so understanding and caution is needed.

Here's a good link to Best Practices from this SO post.