I’m running sql server 2012 and the installed ram on that machine is 128 GB. The performance of the bulk insert is very low. The bulk insert of 10 million records takes about 2 hours. We have checked the cpu, disk io, memory and everything seems to be ok. I’ve just checked the wait_type and found out the CXPACKET wait_time_ms is 79346890761 and max_wait_time_ms is 2164694 and signl_wait_time_ms 2849080871. Are these numbers too high and could it be the reason for slow performance? The degree of parallelism is set to 0.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Below are some good ways to improve BULK INSERT operations :
The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.
Some good reference with interesting stats are given in The Data Loading Performance Guide here . Have a Look at BATCHSIZE and ROWS_PER_BATCH as well in the guide.