Developing Matt

My Technical Journal

SSIS Performance Suggestions by Brian Knight

with 2 comments

I took a great online free course by Brian Knight

If you are working with large amounts of data, change your packet size to accommodate and minimize network hit.  With small amounts of data, keep the packet size default (4k)

Use synchronouse components and avoid asynchronouse components (sort and aggregate) as it has to see ALL the rows to perform it’s job and it halts everything until finished.

Avoid OLEDB command transform as it requires Rob by Agonizing Row (RBAR).  Instead stage the data to a staging table and use an Execute SQL task (with sql) to update your data.  His example was updating 800 records.  With an oledb command it took 4 minutes, with the latter method, 2 seconds.

Data flow has a default buffer size and it was recommended to consider lowering it.  The defualt: defaultBufferMaxRows size to 10,000 rows and the DefaultBufferSize to 10485760 (10MB).  Lowering it enables streaming data faster to the other components.  It all depends.  For large packages, he discovered his best range is 7500 – 8000.  But it depends on the data is and how wide the data is.  For wider data consider lowering it.

For the connection manager, fast load is turned on.  Check the maximum insert commit size.  If 0, it will commit all the transactions in one big hit. 

If you are using the package on the sql server then use the sql server destination.  It will increase performance by up to 10%.

Traceflag 610 turns on minimal logging.  The command is DBCC Traceon (610,-1).  This was introduced in 2005:  What this does is that it turns on minimal logging for insert.  Check the rules (sometimes the data has to be sorted by an clustered index).  By turning it on, it will speed up inserts and decrease fragmentation.

Thank you Mr. Knight for these great suggestions.  Very helpful and informative. 

Advertisements

Written by matt

April 29, 2010 at 3:32 pm

Posted in SSIS

Tagged with

2 Responses

Subscribe to comments with RSS.

  1. Thanks Matt for documenting all of these. You made my day and explained it quite well.

    Brian Knight

    April 30, 2010 at 6:11 am

  2. *~” I am really thankful to this topic because it really gives great information *”:

    Encryption Softwares

    January 25, 2011 at 10:35 am


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

%d bloggers like this: