question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked Zoehui-MSFT answered

In SSIS what is the best value to set DefaultBufferMaxRows

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-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @ChristopherJack-1763,

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.