Improve performance of SSIS using defaultBufferSize

[et_pb_section admin_label=”section”][et_pb_row admin_label=”row”][et_pb_column type=”4_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” text_font_size=”14″ use_border_color=”off” border_color=”#ffffff” border_style=”solid”]

Use the defaultBufferSize setting to improve the throughput of SSIS package.

Key things you need to know:

1. Each component in an SSIS data flow is assigned a memory buffer. The default is about 10MB.
2. When your source is a wide table (i.e. has many or large columns), each row takes up a large amount of memory.
3. If you have a wide source table then the throughput is constrained to the number of rows that SSIS can fit into the buffer.
4. If you increase the buffer size you improve throughput.

In SSIS packages generated by Dimodelo Data Warehouse Studio ( A Data Warehouse Automation tool for the Microsoft SQL Server platform), DefaultBufferMaxRows is set 30000 and DefaultBufferSize to 31457380.

To change the defaults on each SSIS package Dimodelo Data Warehouse Studio generates, do the following:

On the:

•  Advanced tab in the Extract dialog of the Staging table editor
• or ETL dialog of the Dimension/Fact editors

Add the tags shown in the image below (click to expand)

[/et_pb_text][et_pb_image admin_label=”Image” src=”https://www.dimodelo.com/wp-content/uploads/defaultBufferSize.jpg” alt=”maxbuffersize” show_in_lightbox=”on” url_new_window=”off” animation=”off” sticky=”off” align=”left” force_fullwidth=”off” always_center_on_mobile=”on” use_border_color=”off” border_color=”#ffffff” border_style=”solid” /][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left” text_font_size=”14″ use_border_color=”off” border_color=”#ffffff” border_style=”solid”]

In this example the DefaultBufferMaxRows is set 30000 (same as default for Dimodelo generated packages), and the DefaultBufferSize is increased to 94372140 (about 90MB).

Reference: Data Flow Performance

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]