Following are some of the best
practices in SSIS which is helpful in performance optimization
practices in SSIS which is helpful in performance optimization
- Hardware Infrastructure: Disks, RAM, CPU, Network
- SQL Infrastructure: File Groups, Indexing, Partitioning
- Retrieve only the data
that you need - With
NOLOCK hints to avoid locking overhead - Perform
simple logic at Source - Don’t
use excessive data conversions - Choose most suitable
connection - Tune Source if possible
- Heap insert is typically
faster than Cluster index - Avoid blocking components
in large datasets - Choose wisely between
SSIS and T-SQL - Do minimally logged
operations - Partitioning the table
- Smartly use the
transactions in package - Use Package Configuration
for managing packages easily - Standard Naming
Convention - Use Annotaions in
Packages - Use Memory not Disk
- Use stored procedures
instead of in-line SQL