In SSIS what is the best value to set DefaultBufferMaxRows

Christopher Jack 1,611 Reputation points
2021-10-06T06:43:39.41+00:00

Hi,

I have been following the advice here

https://centricconsulting.com/blog/performance-tuning-ssis-how-to-dynamically-set-the-default-buffer-max-rows-property/

using

    SELECT

  SUM (max_length)  [row_length]

  FROM  sys.tables t

  JOIN sys.columns c

  ON t.object_id=c.object_id

  JOIN sys.schemas s

  ON t.schema_id=s.schema_id

  WHERE t.name = 'salesline'

AND s.name = 'dbo'

That returns the value 236 .. so from following the advice in the link above it would be

10485760/236 which equals 44431 which is a lot less than the default value of 100000

Is that right and should I change it to 44431 ?

Thanks for help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,759 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,456 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-10-06T11:37:35.037+00:00
    0 comments
  2. ZoeHui-MSFT 33,126 Reputation points
    2021-10-07T00:56:03.887+00:00

    Hi @Christopher Jack ,

    Define the default size of the buffer that the task uses, by setting the DefaultBufferSize property, and define the maximum number of rows in each buffer, by setting the DefaultBufferMaxRows property.

    Set the AutoAdjustBufferSize property to indicate whether the default size of the buffer is calculated automatically from the value of the DefaultBufferMaxRows property. The default buffer size is 10 megabytes, with a maximum buffer size of 2^31-1 bytes. The default maximum number of rows is 10,000.

    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.

    Reference: data-flow-performance-features

    Regards，

    Zoe

    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 October

    0 comments