Speed up merge join

Christopher Jack 1,611 Reputation points
2020-12-06T18:12:50.893+00:00

The merge join in my SSIS package is taking up a long time.

The max buffers per input is set to 30 - would it heIp increase this further?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,248 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2020-12-07T02:08:26.08+00:00

    Hi @Christopher Jack ,

    When you begin testing the performance of your data flow tasks, use the default values for DefaultBufferSize and DefaultBufferMaxRows. Enable logging on the data flow task, and select the BufferSizeTuning event to see how many rows are contained in each buffer.

    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.

    If AutoAdjustBufferSize is set to true, the engine data flow engine uses the calculated value as the buffer size, and the value of DefaultBufferSize is ignored.

    1.Please reduce the size of each row of data by removing unneeded columns and by configuring data types appropriately.

    2.Please set property AutoAdjustBufferSize as True in ssis package.

    Hope the following links will be helpful:

    Adjust the Sizing of Buffers

    Improving data flow performance with SSIS AutoAdjustBufferSize property

    Best Regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?