liveBook · Manning

Data load optimization

As explained in 100 SQL Server Mistakes and How to Avoid Them

Data load optimization is the tuning of data-ingestion settings and operations to reduce load duration and resource overhead.

Data load optimization tunes SSIS data-flow buffers and OLE DB fast-load settings so ingestion finishes within its operational window. section 6.2

Avoid the mistake of leaving ingestion at default settings:

  • Configure DefaultBufferMaxRows for rows per buffer and DefaultBufferSize for buffer capacity; the defaults may not deliver optimal performance. section 6.2
  • Enable OLE DB destination fast-load options to optimize bulk loading into SQL Server. section 6.2

Nightly advertising analytics

Hundreds of millions of impressions, tens of millions of clicks, and millions of events had to load overnight before business hours. Optimization saved minutes and prevented ETL overruns. section 6.2

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains buffer and fast-load settings with performance tests, but stops short of a complete tuning procedure.
Examples
Uses financial reporting loads, a million-row flat file, and large-scale nightly advertising event processing.
What it assumes
Assumes familiarity with SSIS data flows, CSV files, OLE DB destinations, and SQL Server.
What you can do after
Operate and configure SSIS loads using buffer properties, fast-load options, and measured performance comparisons.
How it connects
Connects data-flow buffers with CSV ingestion, OLE DB destinations, SQL Server, and ETL processing.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.