Following are some of the best
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
No comments:
Post a Comment