SSIS Dataflow Performance Tuning
This presentation is the property of its rightful owner.
Sponsored Links
1 / 20

SSIS Dataflow Performance Tuning 1 st October 2010 Jamie Thomson PowerPoint PPT Presentation


  • 62 Views
  • Uploaded on
  • Presentation posted in: General

SSIS Dataflow Performance Tuning 1 st October 2010 Jamie Thomson. SSIS Dataflow Performance Tuning. Buffer Architecture Design for perf ! General tuning tips. Buffer Architecture. What is a buffer?. An area of memory Created by asynchronous components Does not move or change shape

Download Presentation

SSIS Dataflow Performance Tuning 1 st October 2010 Jamie Thomson

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Ssis dataflow performance tuning 1 st october 2010 jamie thomson

  • SSIS Dataflow Performance Tuning

  • 1st October 2010

  • Jamie Thomson


Ssis dataflow performance tuning 1 st october 2010 jamie thomson

  • SSIS Dataflow Performance Tuning

  • Buffer Architecture

  • Design for perf!

  • General tuning tips


Ssis dataflow performance tuning 1 st october 2010 jamie thomson

  • Buffer Architecture


Ssis dataflow performance tuning 1 st october 2010 jamie thomson

  • What is a buffer?

  • An area of memory

  • Created by asynchronous components

  • Does not move or change shape

  • Data in a buffer can be changed by components

  • Is what you see in a data viewer


Ssis dataflow performance tuning 1 st october 2010 jamie thomson

  • Buffer Architecture

  • Synchronous

    • Aka Row transformations

  • Asynchronous

    • Partially blocking

    • Fully blocking


  • Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Synchronous components

    • Same buffer used for input and output

    • Number of rows in = Number of rows out

    • Generally very quick

    • Examples:

      • Derived Column

      • Conditional Split

      • Multicast


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Asynchronous components

    • Creates new buffers for output

    • Different “shaped” input and output buffers

    • Number of rows in <> Number of rows out

    • Generally slower

    • Examples:

      • Aggregate

      • Sort


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Execution trees

    • …is a section of data flow starting from an asynchronous output and terminating at inputs on transforms that have no synchronous outputs

    • -Kirk Haselden, P546 of “Microsoft SQL Server Integration Services”


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Demo 1

    • Synchronous and Asynchronous components

    • -

    • Count the expression trees


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Execution trees


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Inside an execution tree – What we think happens

    Buffers

    don’t

    move


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Inside an execution tree – What actually happens


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Design for perf!


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • “The Data Flow Task is performant by design, without any tuning or optimization the default settings generally deliver great performance”

    • Kirk Haselden, Microsoft SQL Server 2005 Integration Services, Chapter 23 – Data Flow Task Internals and Tuning


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Only do what you have to

    • Remove unrequired columns (heed the warnings)

    • Fixed-width files – only parse what you need

    • ALWAYS use a SQL statement

    • Only parse when needed (or leave as strings)


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Demo 2

    • Parse at source or in flow

    • &

    • Lookups vs Merge Joins


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • General tuning tips!


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Use FastParse if possible

    • Turn off OnPipelineRowsSent

    • Let the database do what its good at

    • Raw files

    • 64bit


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Keep columns narrow

    • Point BufferTempStoragePath/BLOBTempStoragePath at fast drives

    • Increase DefaultBufferMaxSize & DefaultBufferMaxRows

    • Optimise the destination (fast load, table lock, simple/bulk logged recovery/disable indexes)

    • Identify bottlenecks


    Ssis dataflow performance tuning 1 st october 2010 jamie thomson

    • Further reading

    • SSIS Performance Tuning Whitepaper – by Elizabeth Vitt et al

    • Microsoft SQL Server 2005 Integration Services (Chapter 23) by Kirk Haselden


  • Login