Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

Buffer Size in SSIS

Behind the scenes in SSIS, the data flow engine uses a buffer-oriented architecture to efficiently load and manipulate datasets in memory.  The benefit of this in-memory processing is that you do not need to physically copy and stage data at each step of the data integration.  Rather, the data flow engine manipulates data as it is transferred from source to destination.

So how many buffers does it create?  How many rows fit into a single buffer?  How does it impact performance?

The number of buffers created is dependent on how many rows fit into a buffer, and how many rows fit into a buffer depends on a few other factors.  The first consideration is the estimated row size, which is the sum of the maximum sizes of all the columns from the incoming records.  The second consideration is the DefaultBufferSize property of the data flow task.  This property specifies the default maximum size of a buffer.  The default value is 100MB and its upper and lower boundaries are constrained by two internal non configurable properties of SSIS which are MaxBufferSize (100MB) and MinBufferSize (64KB).  It means the size of a buffer can be as small as 64KB and as large as 100MB.  The third factor is DefaultBufferMaxRows which is again a property of the data flow task which specifies the default number of rows in a buffer.  Its default value is 10,000.

Although SSIS does a good job in tuning for these properties in order to create an optimum number of buffers, if the size exceeds the DefaultBufferSize then it reduces the rows in the buffer.  For better buffer performance you can do two things.  First you can remove unwanted columns from the source and set the data type in each column appropriately, especially if your source is flat file.  This will enable you to accommodate as many rows as possible in the buffer.  Second, if your system has sufficient memory available, you can tune these properties to have a small number of large buffers, which could improve performance.  Beware if you change the values of these properties to a point where page spooling begins, it adversely impacts performance.  So before you set a value for these properties, first thoroughly test in your environment and set the values appropriately.

You can enable logging of the BufferSizeTuning event to learn how many rows a buffer contains and you can monitor the “Buffers spooled” performance counter to see if SSIS has begun page spooling.

More info:

Improving the Performance of the Data Flow

Integration Services: Performance Tuning Techniques

SQL Server 2005 Integration Services – Performance – Part 30

SSIS – An Inside View Part 3

Comments

Posted by sabotta on 2 December 2011

You may find this video helpful: "Understanding SSIS Data Flow Buffers (SQL Server Video)" at technet.microsoft.com/.../ff686905.aspx

Posted by maktalajar on 2 December 2011

Very good article

Posted by Anonymous on 3 December 2011

Pingback from  Buffer Size in SSIS | SQL Server | Syngu

Posted by angel9506 on 12 April 2014

The words in this article is so close to the session "Best Practice #7 - DefaultBufferMaxSize and DefaultBufferMaxRows" published in 2009 on www.mssqltips.com/.../sql-server-integration-services-ssis-performance-best-practices

Isn't it interesting?

Leave a Comment

Please register or log in to leave a comment.