CODEWITHSUNDEEP
sql-serverssisssis-2008ssis-2005
user1254579
user1254579

Reputation: 4009

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

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?

Upvotes: 0

Views: 756

Answers (1)

Dave C
Dave C

Reputation: 7392

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.

Upvotes: 1

Related Questions