performance tuning ssis l.
Skip this Video
Loading SlideShow in 5 Seconds..
Performance Tuning SSIS PowerPoint Presentation
Download Presentation
Performance Tuning SSIS

Loading in 2 Seconds...

play fullscreen
1 / 33

Performance Tuning SSIS - PowerPoint PPT Presentation

  • Uploaded on

Performance Tuning SSIS. Brian Knight, CEO Pragmatic Works About the Ugly Guy Speaking. SQL Server MVP Founder of Pragmatic Works Co-Founder of, and Written more than a dozen books on SQL Server. Mobile data.

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

PowerPoint Slideshow about 'Performance Tuning SSIS' - booker

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
performance tuning ssis

Performance Tuning SSIS

Brian Knight, CEO Pragmatic Works

about the ugly guy speaking
About the Ugly Guy Speaking
  • SQL Server MVP
  • Founder of Pragmatic Works
  • Co-Founder of, and
  • Written more than a dozen books on SQL Server
integration services in action





GeoSpatial Data:

Semi structured




Data mining







Legacy data: binary files


SQL Server Integration Services

Application database

Integration Services in Action

Integration is a seamless, manageable operation

Source, prepare, & load data in single, auditable process

Scale to handle heavy and complex data requirements


today s problems with integration
Today’s Problems with Integration
  • Integration today
    • Increasing data volumes
    • Increasingly diverse sources
  • Requirements reached the Tipping Point
    • Low-impact source extraction
    • Efficient transformation
    • Bulk loading techniques
tuning decisions
Tuning Decisions
  • Choose the right tool for the job
  • Don’t be afraid to use T-SQL
  • Will parallelism work?
source optimization
Source Optimization
  • Flat files – When available, use Fast Parse
  • OLE DB sources – Change network packet size
  • Use T-SQL whenever possible in the OLE DB Source
    • Joining
    • NULL handling
    • Where clauses
impact of compression on etl
Impact of Compression on ETL

* Not official Microsoft results.

tuning the source

Tuning the Source

Connection manager tuning

Flat file tuning

OLE DB Source tuning


ssis data flow architecture
SSIS Data Flow Architecture

Synchronous vs. Non Synchronous

case study patterns
Case Study: Patterns

83 seconds

105 seconds

source data extraction
Source Data Extraction
  • Extracting data from the source is expensive
    • Efficient extraction is key to improving ETL performance
    • Involves bulk loading data into staging areas or warehouse
  • Time consuming & resource intensive
      • Triggers (synchronous IO penalty)
      • Timestamp columns (Schema changes)
      • Complex queries (delayed IO penalty)
      • Custom (ISV, mirror, snapshot, …)
  • Incremental data load is key to efficient extraction
    • Need to know what changed at source since a point in time
  • Expensive lookups to determine changed columns
    • Providing information up front about which columns changedwill improve efficiency
sql server 2008 change data capture cdc
SQL Server 2008: Change Data Capture (CDC)
  • Information about what changed at the source
  • Changes captured from the log asynchronously
  • Enabled per table
  • CDC APIs provide access to change data


Data Warehouse

Change Tables

change data capture

Change Data Capture

Traditional CDC with SSIS

Integrating CDC in 2008


lookup component
Lookup Component
  • Three modes of operation
    • Full Cache: for small lookup datasets
    • No Cache: for volatile lookup datasets
    • Partial Cache: for large lookup datasets
  • Tradeoff memory vs. performance
    • Use Cascaded Lookups
    • Merge Join may be alternative
sql server 2008 lookup transform
SQL Server 2008: Lookup Transform
  • Hydrate cache files for large data sets
  • Can reuse cache
  • Can load cache during day and use in nightly ETL


Cascading lookup optimizations

Cache file lookup

data destinations
Data Destinations
  • Use “Fast Load” or SQL Server Destination
  • Table Lock on insert operations
  • Trace flags for improvement
  • Old principles still apply
building a work queue system
Building a Work Queue System

Create a work queue table.

Create a loop to shift over the work queue constantly checking out work

Spawn x times with a batch file

managing resources
Managing Resources
  • Logging events to watch pipeline internals
    • PipelineExecutionPlan, PipelineExecutionTree, BufferSizeTuning
  • System Monitor to track I/O issues
    • Buffers In Use tracks how many buffers are presently being used
    • Buffers Spooled tracks how many 10 mb buffers have been spooled to disk

SQL Server 1

SQL Server 2

  • Consider the following configuration…

Where should SSIS run?

    • (Licensing issues aside)

SSIS Server

  • Windows System Resource Manager (WSRM) can throttle CPU and memory
    • Creates a soft throttle
    • Can be scheduled so SSIS gets priority on weekends and nights
    • Only activates policy if resources begin to become constrained (about 70%)
    • WSRM is free with Windows Server 2003 Enterprise Edition and included in Windows Server 2008


Creating a soft schedule cap


  • Planning
    • Don’t underestimate the power of the whiteboard!
  • Use the right tool for the right job
    • Leverage the power of the engine
  • Patterns and Practices
    • Understand best practices
    • But don’t be afraid to experiment
the end already
The End Already?