One of the common recommended step to improve the performance of an SSIS package is Buffer Size tuning. In this blog, I share the method that I have used for my projects, however there may be some other alternative ways you would find in other blogs.
When SSIS caches data in memory as it’s going through the ETL process, it uses something called buffers to control how much data in can pick up and transform/modify at once.
DefaultMaxBufferSize – the default is 10 MB. The maximum size is 100MB, which SSIS stores as MaxBufferSize
DefaultMaxBufferRows – The default is 10,000 rows.
DefaultMaxBufferRows * (times) single row size <= DefaultMaxBufferSize
You can find it using calculation. However, this appropriate SSIS Package Buffer Size can be identified using SYSSSISLOG log data. Enable the following events in the package as shown below.
BufferSizeTunning – The data-flow engine may change the size of the buffer away from the default values. This event gives details of the new sizes with the reasons as given below.
PipelineInitialization – This event gives the various initialization details of the data flow task as given below.
Set very larger number for DefaultMaxBufferRows such as 1,000,000 and keep it default buffer size as 10MB. Run the packages with log.
If you run the query as shown below, log data will show the appropriate buffer size. Even though we set big value for DefaultMaxBufferRows, SSIS engine will adejust based on its own optimization technique.
Same time, if you set low value for DefaultMaxBufferRows, the DefaultMaxBufferRows will be adjusted by SSIS Engine.
For example, rows in buffer type 11 would cause a buffer size less than allocation minimum, which is 65536 bytes. There will be 1638 rows in buffers of this type.
Uma
SSIS is the most useful and interesting component to provide more solutions to complex IT problems and is very useful for creating visual dashboards.
ReplyDeleteSSIS Postgresql Read