Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.

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.