Going through my
reading material today, I came across
this post by the Microsoft SQL Server Development Customer Advisory Team on
using SQL Server 2005 Enterprise Edition to
load 1TB (yes terabyte) in less than an hour. Mind you they did have an a HP
Itanium2 Superdome with 64 CPUs, 256GB RAM and a SAN rated with 14GB per second
throughput. Wow! Its interesting to note that the bottleneck in their processing
was CPU.
Run as many BULK INSERT processes as you have CPUs
The number and size of files they processed are very different to what I
process, but I agree with the best practices that Kevin suggests: run as many
BULK INSERT processes as you have CPUs. SQL Server will attempt to distribute
the load across your available CPUs as it will consume one CPU per BULK INSERT
thread. If you are bulk loading thousands of smaller files, I’ve found the same
is still true. Running more BULK INSERTs than CPUs does not appear to increase
performance.