Pragmatic Works Blog

Webinar Follow Up - Help, My SSIS packages need a performance tune up!

Written by Tammye Michau | Feb 04, 2015

SSIS Performance Tuning Tips and Tricks

On Tuesday we hosted the SSIS Performance Tuning Tips and Tricks webinar with James Phillips.  He showed us how to tune up our SSIS packages to make them perform better, faster, and more efficiently. 

Through demonstrations, we were given insight on what to avoid when setting up packages.  James stated you could speed up the performance of your packages by avoiding:

  • Setting up aggregates
  • Sorts on your packages
  • The built in SCD task
  • Non-cached look ups

There are various SSIS package settings that can either bring you frustration or success.  James goes in to detail about them in this discussion.  Some of the basic settings that will give your packages greater performance are:

  • Engine threads setting to stop thread switching (See below Image 1 for placement inside a package to adjust the setting)
  • What the DefaultBufferMaxRows should be set to use a script on the table to get a recommendation for a table (See Image 1 for placement inside of a package to adjust the setting)
  • The DefaultBufferSize should be in focus with the available memory on your server, example below shows 100 megabites (See Image 1)

(Image 1)

You can find all of James Phillips’ solutions and slides from this webinar on his blog.  James has over 15 years of experience in the business intelligence field and his knowledge and experience are demonstrated throughout this training.  The SSIS Performance Tuning Tips and Tricks webinar can be found on our Training page.

Our upcoming webinar, Using BI Techniques for Database Statistics with Brad Gall, will be on February 5, 2015.  Brad is a Business Intelligence Consultant with Pragmatic Works and has over 10 years of experience specializing in data solutions with SQL and Sharepoint.  In this training session, he will show how to store and consume data with PowerPivot using real work stats such as:

  •          database growth
  •          stored procedure cache stats
  •          server wait stats

Brad will also review a very high level Dimensional Model through DBA metrics.  You can register for this webinar on our Training page.  Don’t miss it!

As a reminder, Pragmatic Works offers FREE training every Tuesday and Thursday starting at 11:00 AM.  We bring you information on some of the hottest topics in business intelligence such as Big Data, SQL Server, PowerPivot, and Performance Tuning.  You can visit our website to find a comprehensive list of our webinars at Pragmatic Works Training.  If you would like to keep up with our upcoming events, be sure to subscribe to our mailing list.

What performance issues are you experiencing with your SSIS packages?