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:
- 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.
- 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.
- Use BULK insert instead of BCP
if you are running the process on the SQL Server machine.
- 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.
- Use TABLOCK to avoid row at a
time locking.
- Use ROWS PER BATCH = 2500, or
something near this if you are importing multiple streams into one
table.
Really
interesting...