slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SSIS Dataflow Performance Tuning Jamie Thomson PowerPoint Presentation
Download Presentation
SSIS Dataflow Performance Tuning Jamie Thomson

Loading in 2 Seconds...

play fullscreen
1 / 22

SSIS Dataflow Performance Tuning Jamie Thomson - PowerPoint PPT Presentation


  • 181 Views
  • Uploaded on

SSIS Dataflow Performance Tuning 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

SSIS Dataflow Performance Tuning 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
    1. SSIS Dataflow Performance Tuning • Jamie Thomson

    2. SSIS Dataflow Performance Tuning • Buffer Architecture • Design for perf! • General tuning tips

    3. Buffer Architecture

    4. 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

    5. Demo 1 • What is a buffer

    6. Buffer Architecture • Synchronous • Aka Row transformations • Asynchronous • Partially blocking • Fully blocking

    7. 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

    8. 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

    9. 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”

    10. Demo 2 • Synchronous and Asynchronous components • (Count the expression trees)

    11. Execution trees

    12. Inside an execution tree – What we think happens Buffers don’t move

    13. Inside an execution tree – What actually happens

    14. Design for perf!

    15. “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

    16. 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)

    17. Demo 3 • Parse at source or in flow • Lookups vs Merge Joins

    18. General tuning tips!

    19. Use FastParse if possible • Turn off OnPipelineRowsSent • Let the database do what its good at • Raw files • 64bit

    20. 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

    21. Demo 4 • Chaining expression components • Redirect or Passthrough in a Lookup • Fast Parse

    22. Further reading • SSIS Performance Tuning Whitepaper – by Elizabeth Vitt et al • Microsoft SQL Server 2005 Integration Services (Chapter 23) by Kirk Haselden