SSIS: ProTips - Performance Tuning

SQL Saturday - Aug 4, 2012 - Baton Rouge

Instructor: Tim Costello (Interwork - Dallas)

SSIS: ProTips - Performance Tuning

1. Benchmark before you begin
• Performance WireTap script task component written by Todd McDermid
• output window show start and end row - to show the execution time
• lightweight (even if left in when running in production, it is lightweight enough not to hurt overall performance)
2. Its All About the Buffers
• properties
• recommend keep defaults (start with low hanging fruit first)
• dont mess with buffers first - check other stuff for possibilities
• better to have small # of large buffers, than large # of small buffers
• Synchronous tranformations - VERY fast (same buffer from beginning to the end)
• Asynchronous transformations (creates new buffer)
3. Optimize your souces
• remove unneeded columns
• if pulling from a database, use the SQL command (your own query) rather than the table name in the drop down - there is a BIG benefit to this
4. Better tool for the job?
• is SSIS the right tool for what you need?
• if going from a database to a database - do it in the database, not SSIS

Other things discussed: 3 types of data flow components:

• Full-blocking transformations
• Semi-blocking transformations
• Non-blocking transformations