1 / 33

Performance Tuning SSIS

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

aurek
Download Presentation

Performance Tuning SSIS

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Performance Tuning SSIS Brian Knight, CEO Pragmatic Works bknight@pragmaticworks.com

  2. About the Ugly Guy Speaking • SQL Server MVP • Founder of Pragmatic Works • Co-Founder of BIDN.com, SQLServerCentral.com and SQLShare.com • Written more than a dozen books on SQL Server

  3. Mobile data GeoSpatial Components GeoSpatial Data: Semi structured Merges Data-cleansing components Data mining components Standard sources Custom source Warehouse Legacy data: binary files Reports 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 Cube

  4. Advanced Session

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

  6. Tuning Decisions • Choose the right tool for the job • Don’t be afraid to use T-SQL • Will parallelism work?

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

  8. Impact of Compression on ETL * Not official Microsoft results.

  9. Tuning the Source Connection manager tuning Flat file tuning OLE DB Source tuning Demo

  10. The Pipeline presents the buffer to each downstream component Transform Components

  11. SSIS Data Flow Architecture Synchronous vs. Non Synchronous

  12. Case Study: Patterns 83 seconds 105 seconds

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

  14. 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 OLTP Data Warehouse Change Tables

  15. Change Data Capture Traditional CDC with SSIS Integrating CDC in 2008 Demo

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

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

  18. Demo Cascading lookup optimizations Cache file lookup

  19. Data Destinations • Use “Fast Load” or SQL Server Destination • Table Lock on insert operations • Trace flags for improvement • Old principles still apply

  20. Destination Tuning Demo

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

  22. Demo Results

  23. Demo Results

  24. Demo Results

  25. Demo Results

  26. Parallel Load Demo

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

  28. Measuring Performance Perfmon

  29. SQL Server 1 SQL Server 2 Location • Consider the following configuration… Where should SSIS run? • (Licensing issues aside) SSIS Server

  30. WSRM • 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

  31. WSRM Creating a soft schedule cap Demo

  32. Summary • 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

  33. The End Already? Questions http://www.bidn.com/people/brianknight @BrianKnight bknight@pragmaticworks.com http://www.youtube.com/pragmaticworks

More Related