Home / Integration Services / SSIS Maximum insert commit size

SSIS Maximum insert commit size

The default value of the Maximum insert commit size is 2147483647, the largest value a 4 byte INT will hold.  This value is default set in SQL 2012+ but you need to set it manually in versions lower then 2012. In most cases you will leave this setting alone.

The value of 0 indicates that all data is committed in a single batch after all rows have been processed.

Committing everything in a single batch can have a very important impact on performance. Sometimes the impact is in the 60% to 70% improvement range. Unfortunately, it will adversely affect both the Temp DB and the transaction log. In isolation, this is a good idea. In a real world-wide situation with multiple processes running simultaneously, the impact on the Temp DB and transaction log can cause more pain than reducing load time.

This setting can really improve performance, but it requires a thoughtful and balanced approach to implementing it in production. In controlled situations, like nightly datawareouse loads, this can really help reduce your load times.

Check Also

Exporteren SSIS Data naar Excel 2007

Hieronder volgt een korte beschrijving hoe je vanuit SSIS een koppeling kunt maken met Excel …

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *