SQL Saturday - Aug 4, 2012 - Baton Rouge
Instructor: Tim Costello (Interwork - Dallas)
SSIS: ProTips - Performance Tuning
- 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)
- 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)
- 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
- 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