Top 10 SSIS Performance Best Practices

The SQL CAT team has loads of experience in loading large volumes of data using SQL Server Integration Services. Thanks to the team for putting this wonderful post together.

Here are highlights plus some of my own tips:

  1. Do all transforms inSSIS
    1. SSIS is an in-memory engine and transform will be faster here than in TSQL.
  2. Understand resource utilization
    1. Use correct performance counters to monitor and measure throughput and stress on resources.
  3. Baselineextract performance
    1. You need a baseline to measure improvements in performance.
  4. OptimizeTSQL,SSIS lookups and destination components
    1. Join, Group By and Sort operations are best in TSQL.
    2. Optimize destination components to ensure you bulk load data into SQL
  5. Tune your network
    1. Depending on networking you might get better performance adjusting your packet size property in SSIS
  6. Pay special attention to data types
    1. Narrow data types use less memory.
  7. Use set based operations
    1. Set based update statements are faster than row-by-row updates over OLEDB.
    2. TSQL Merge is faster than SSIS for slowly changing dimension operations.
  8. Use partitioning patterns
    1. Break the problem into smaller pieces and conquer.
    2. Create ranges of equal size.
  9. Use minimally logged operations
    1. Seriously consider enabling SQL trace flag 610.
    2. Use Truncate instead of Delete.
  10. Distribute workload
    1. Implement dynamic multi-threading for your ETL.

 

Advertisements
This entry was posted in SQL Server 2014 and tagged , , . 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