SQL Server 2005: Load 1TB in less than 1 hour

When you have the right hardware resources, SQL Server 2005 is extremely performant and able to make unbelievable tasks.

The last experiment from the SQL Server Development Customer Advisory Team has showed that SQL Server 2005 Enterprise Edition was able to load 1TB of data (BULK INSERT) in less that one hour... incredible!

Interesting to remember the lessons learned from this test, that the Team has shared with us:

  1. Run as many load processes as you have available CPUs.  If you have 32 CPUs, run 32 parallel loads.  If you have 8 CPUs, run 8 parallel loads. 
  2. If you have control over the creation of your input files, make them of a size that is evenly divisible by the number of load threads you want to run in parallel.  Also make sure all records belong to one partition if you want to use the switch partition strategy.
  3. Use BULK insert instead of BCP if you are running the process on the SQL Server machine. 
  4. Use table partitioning to gain another 8-10%, but only if your input files are GUARANTEED to match your partitioning function, meaning that all records in one file must be in the same partition.
  5. Use TABLOCK to avoid row at a time locking.
  6. Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.

Really interesting...

Print | posted on Tuesday, May 23, 2006 11:52 AM