Thursday, December 14, 2017

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

Archives

Powered by mod LCA