Practices Donald Farmer donaldfarmermicrosoftcom Session Code DAT310 Stating the obvious Look outside of SSIS for perf Demo Understand Your Hardware The basic topology ETL Topology Destination ID: 409486
Download Presentation The PPT/PDF document "SSIS Best" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1Slide2
SSIS Best Practices
Donald Farmer
donald.farmer@microsoft.com
Session Code: DAT310Slide3
Stating the obviousSlide4
Look outside of SSIS for perfSlide5Slide6
DemoSlide7
Understand Your HardwareSlide8
The basic topologySlide9
ETL Topology
Destination
Load
Source
SSIS Data Flow
Extract
TransformSlide10
ETL TopologySlide11
ELT Topology
Destination
Source
SSIS Data Flow
Load
Extract
TransformSlide12
ELT TopologySlide13
TEL Topology
Destination
Source
SSIS Data Flow
Load
Extract
TransformSlide14
TEL TopologySlide15
The basic topology - againSlide16
3 Box ETL TopologySlide17
Co-located ETL TopologySlide18
Co-located ETL TopologySlide19
DemoSlide20
3 basic principlesSlide21
Watch Resources with PerfmonSlide22
A bag of tricks …Slide23
Optimize SQL Data SourceSlide24
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 CacheSlide25
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 specialistsSlide26
Data TypesSlide27
Optimize SQL TargetsSlide28
More tipsSlide29
DemoSlide30
Complete an evaluation on
CommNet
and enter to win an Xbox 360 Elite!Slide31
©
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.