High performance data insert into SQL Server

Question

I'm inserting about 7 million rows into a table in SQL Server. The table has about 9-10 columns and it has a clustered index on 2 keys (columns) and 3 more unclustered indexes. I'm trying to do high-performance/bulk insert of data, but it gets pretty slow after inserting 3 million records with the disk usage going up to 99%. Here is what I did to speed up this process:

1) Disabled all nonclustered indexes. I didn't disable the 2 column clustered index because data cannot be inserted after the clustered index is disable?

2) I'm using C# SqlBulkCopy to do bulk insert of 5,000 records each time.

3) The table doesn't have any constraints and triggers

Is there anything I should be doing to speed up this data insert process?

http://stackoverflow.com/q/24200/139010 and http://stackoverflow.com/a/2457862/139010 — Matt Ball, Jun 10 '13 at 16:35
Order the data to be inserted such that it is in the order of the clustered index. — spender, Jun 10 '13 at 16:59
I won't downvote any of the careless "switch to simple recovery" comments, but do it only if you REALLY know what you're doing. Otherwise disregard such proposals. — OzrenTkalcecKrznaric, Jun 10 '13 at 17:14

score 2 · Accepted Answer · answered Jun 10 '13 at 16:39

Get rid of ALL your indexes. Every time you write with an index on, the physical page has to be re-structured when you write each record. Dump all your indexes using code (DROP INDEX), insert your data and then re-build your indexes using code (CREATE INDEX).

score 1 · Answer 2 · answered Jun 10 '13 at 16:36

1

Make sure the database is in recovery mode BULK LOGGED or SIMPLE, at least until you have all the records inserted. This will keep the transaction log file from swelling.

answered Jun 10 '13 at 16:36

Ross Presser

6,027
1
34
66

1

Don't suggest such an action without thorough investigation! Someone could loose his job because of this. – OzrenTkalcecKrznaric Jun 10 '13 at 17:10
Getting rid of indexes, the accepted answer, is equally if not more dangerous than _temporarily_ changing the recovery mode to do a big insert. – Ross Presser Jun 21 '13 at 18:11

score 1 · Answer 3 · answered Jun 10 '13 at 17:06

As spender told, your cluster index may be a problem. You may be inserting data such a way that your cluster index is not sorted. So SQL server must be creating lot of pages with only one entry in it and doing optimizing once it sees a lot of sparsely filled packets. Try using auto generated int indexes. If you cannot do that, sort the entire bulk data (based on clustered index column - order is important) before inserting them into SQL. This should minimize the disk usage.

High performance data insert into SQL Server

3 Answers3