SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Performance Tuning SSIS

kiaria avatar
kiaria
30 diapositives · 453 Views · Aug 04, 2014

Performance Tuning SSIS. Brian Knight, CEO Pragmatic Works bknight@pragmaticworks.com. HR Departments are no fun. Don’t mention the stalking incident with Clay Aiken What happened in Vegas My prom date with a puppet Most unfortunate incident with a turtle My fear of bounce houses

#performance tuning ssis #performance tuning ssis ppt #powerpoint #presentation
Performance Tuning SSIS - slide 1
01 / 30Performance Tuning SSIS
1 / 17

Télécharger la présentation

Performance Tuning SSIS
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

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

  2. HR Departments are no fun. • Don’t mention the stalking incident with Clay Aiken • What happened in Vegas • My prom date with a puppet • Most unfortunate incident with a turtle • My fear of bounce houses • How to sexually harass the HR rep • What I did to a fish when I was 8 • Any talk about my college years • The surgery I had last summer • The stint I had as a traveling gypsy • Why am I still not allowed back Texas • How what I did in Vegas truly can’t stay in Vegas

  3. About Brian Wasn’t very good with girls Even Kermit the Frog founded a company All 15 still awaiting a publisher. Where he writes about his miniature donkey collection. SQL Server MVP Founder of Pragmatic Works Author of 15 books Blogs at BIDN.com Twitter: @BrianKnight

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

  5. Advanced Session

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

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

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

  9. Network Traffic • Connection Settings • Packet size defaults to 4096 • Increase to 32767 on large data sets SQL Server S W I T C H SQL Server SSIS Package Database LAN LAN LAN LAN

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

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

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

  13. SSIS Data Flow Architecture Synchronous vs. Non Synchronous

  14. Case Study: Patterns 83 seconds 105 seconds

  15. Demo Cascading lookup optimizations Cache file lookup

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

  17. Destination Tuning Demo

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

  19. Measuring Performance Perfmon

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

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

  22. WSRM Creating a soft schedule cap Demo

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

  24. Demo Results

  25. Demo Results

  26. Demo Results

  27. Demo Results

  28. Parallel Load Demo

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

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