Bulk insertion loads many rows efficiently, but treating it like an ordinary INSERT can create unnecessary I/O, transaction-log work, page splits, and delays. section 11.9 section 6.2
Avoid these two mistakes:
- 1. Leaving nonclustered indexes active. Each inserted row must update the table and every affected index, increasing I/O, page splits, fragmentation, and transaction-log writes. Disable only the nonclustered indexes, then rebuild them after loading; disabling the clustered index makes the table inaccessible. section 11.9
- 2. Accepting default data-load settings. In SSIS, tune DefaultBufferMaxRows and DefaultBufferSize, and enable OLE DB fast-load options. Test buffer and batch sizes instead of assuming larger is always faster. section 6.2
Nightly advertising-data load
A nightly pipeline loads impressions, clicks, and events from multiple files. With hundreds of millions of impressions and a fixed morning deadline, load optimization determines whether processing finishes in time. A safer SQL Server pattern disables nonclustered indexes on ImpressionsArchive, inserts from the source table, and rebuilds the indexes afterward. section 11.9 section 6.2
