Roadmap › SSIS — SQL Server Integration Services › Module: Deployment, Scheduling and Performance
Lessons in this course (13)
13. Performance tuning
Advanced★ Must knowLesson 13 of 13
- Source: SQL command with only needed columns, filters and
ORDER BYon the server (setIsSorted/SortKeyPositionon the source output instead of a Sort transformation); useNOLOCKonly if dirty reads are acceptable. - Avoid blocking transformations (Sort, Aggregate, Fuzzy) on big data — push to SQL.
- Replace OLE DB Command (row-by-row) with staging + set-based UPDATE/MERGE.
- Lookups: full cache with minimal columns; Cache Connection Manager for reuse.
- Destination fast load: Table lock, sensible
Rows per batchandMaximum insert commit size, disable/drop non-clustered indexes during large loads and rebuild after; check constraints off when safe. - Buffers: tune
DefaultBufferMaxRows/DefaultBufferSize(orAutoAdjustBufferSize=True2016+); narrow data types reduce row width = more rows per buffer. - Parallelism:
MaxConcurrentExecutables(package) andEngineThreads(data flow); split independent loads into parallel tasks/packages. - Avoid unnecessary data conversion; match types to destination early.
- Run SSIS on/near the SQL Server to reduce network hops; ensure enough memory (buffer spooling to disk = slow — watch "Buffers spooled" counter).
- Incremental loads instead of full reloads.