SQL Server bulk insert performance

Question

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.

Answer 1

Below are some good ways to improve BULK INSERT operations :

1. Using TABLOCK as query hint.
2. Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
3. Changing the Recovery model of database to be BULK_LOGGED during the load operation.
4. If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
5. Using Trace Flag 610 at the beginning of BULK INSERT operation.

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.

• Preben Huybrechts: Trace Flag 610 is on by default for SQL Server 2016+ when ALLOW_PAGE_LOCKS is ON for the indexes see docs
• Kin Shah: OP has a tag of SQL server 2012. this is good info though.