slide1
Download
Skip this Video
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


  • 135 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

PowerPoint Slideshow about 'SSIS Dataflow Performance Tuning Jamie Thomson' - elijah-contreras


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
slide2
SSIS Dataflow Performance Tuning
  • Buffer Architecture
  • Design for perf!
  • General tuning tips
slide4
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
slide5
Demo 1
  • What is a buffer
slide6
Buffer Architecture
  • Synchronous
      • Aka Row transformations
  • Asynchronous
      • Partially blocking
      • Fully blocking
slide7
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
slide8
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
slide9
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”
slide10
Demo 2
  • Synchronous and Asynchronous components
  • (Count the expression trees)
slide15
“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
slide16
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)
slide17
Demo 3
  • Parse at source or in flow
  • Lookups vs Merge Joins
slide19
Use FastParse if possible
  • Turn off OnPipelineRowsSent
  • Let the database do what its good at
  • Raw files
  • 64bit
slide20
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
slide21
Demo 4
  • Chaining expression components
  • Redirect or Passthrough in a Lookup
  • Fast Parse
slide22
Further reading
  • SSIS Performance Tuning Whitepaper – by Elizabeth Vitt et al
  • Microsoft SQL Server 2005 Integration Services (Chapter 23) by Kirk Haselden
ad