1 / 31

SSIS Best Practices

SSIS Best Practices. Donald Farmer donald.farmer@microsoft.com Session Code: DAT310. Stating the obvious. Look outside of SSIS for perf. Demo. Understand Your Hardware. The basic topology. ETL Topology. Source. Destination. SSIS Data Flow. Load. Extract. Transform. ETL Topology.

kasie
Download Presentation

SSIS Best Practices

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. SSIS Best Practices Donald Farmer donald.farmer@microsoft.com Session Code: DAT310

  2. Stating the obvious

  3. Look outside of SSIS for perf

  4. Demo

  5. Understand Your Hardware

  6. The basic topology

  7. ETL Topology Source Destination SSIS Data Flow Load Extract Transform

  8. ETL Topology

  9. ELT Topology Source Destination SSIS Data Flow Load Extract Transform

  10. ELT Topology

  11. TEL Topology Source Destination SSIS Data Flow Transform Load Extract

  12. TEL Topology

  13. The basic topology - again

  14. 3 Box ETL Topology

  15. Co-located ETL Topology

  16. Co-located ETL Topology

  17. Demo

  18. 3 basic principles

  19. Watch Resources with Perfmon

  20. A bag of tricks …

  21. Optimize SQL Data Source

  22. Optimize Lookup Transformation • Change SELECT statement to only use the columns you need • Optimizes memory usage • Consider adding NOLOCK • In SSIS 2008: • Use Shared Lookup Cache

  23. Network Tuning • Change the network packet size in the connection manager • Higher values typically yield faster throughput • Max value: 32767 • Experiment with Shared Memory vs. TCP/IP • If using Win 2008 • Network affinity • Enable Jumbo Frames on the Network • Consult your network specialists

  24. Data Types

  25. Optimize SQL Targets

  26. More tips

  27. Demo

  28. Complete an evaluation on CommNet and enter to win an Xbox 360 Elite!

  29. © 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

More Related