SSIS Tuning–Packet Size

Tuning the packet size property in connection managers can often yield significant performance improvement.  By default the packet size is 0 for OLE DB connections which defaults to the instance packet size.  In most cases the default value is 4096.  The default packet size for ADO.NET is 8000.  On modern networks, both settings are less than ideal.  Increasing the packet size usually increases performance.  With OLE DB I have seen greater than a 70% improvement in throughput by tuning this property.

I recommend increasing the packet size and testing to find the optimal setting for your particular environment. 

To change the packet size, double click on the connection manager and click on All.  Change the packet size from 0 to the desired setting.

image

image

For OLE DB try increasing from 0 to 8192.  Capture the performance then try increasing to 16,384. Increase towards the 32K limit and find the best setting for your particular environment.  My experience has been that performance will continue to increase beyond 16K but with diminishing results.

ADO.NET is defaulted to 8,000. Try increasing to 16,384 and then to 32,767.

Advertisements
This entry was posted in Performance, SQL Server Integration Services. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s