Scenario
* 20-30 million rows in each 3-5gb CSV file
* SQL Server 2008 SP2
* 4 environments (dev, test, pre-prod, prod)
* All environments running win 2008 ent 64-bit SP2
+ dev & test: virtual with 4gb RAM Xeon 4-proc
+ pre-prod: virtual with 12gb RAM Xeon 8-proc
+ prod: physical machine 36gb RAM Xeon 8-proc
When processing these larger datasets in dev thru pre-prod environments, errors and performance issues occur that seem to result from size of files (out-of-memory, extremely long process times, etc.). We have truncated the CSVs to only load a portion of the records in dev & test, but in pre-prod I want full load to accurately simulate the prod environment.
Assuming I cannot increase my memory pool on prep-prod, what can be done with the SSIS packages, SQL server, etc. to improve performance?