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?

stackoverflow.com/q/24200/139010 and stackoverflow.com/a/2457862/139010 — Matt Ball, Commented Jun 10, 2013 at 16:35
Order the data to be inserted such that it is in the order of the clustered index. — spender, Commented Jun 10, 2013 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, Commented Jun 10, 2013 at 17:14

Johnny Bones · Accepted Answer · 2013-06-10 16:39:06Z

2

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).

answered Jun 10, 2013 at 16:39

Johnny Bones

8,4647 gold badges61 silver badges127 bronze badges

Add a comment |

Ross Presser · Accepted Answer · 2013-06-10 16:36:26Z

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, 2013 at 16:36

Ross Presser

6,3002 gold badges38 silver badges73 bronze badges

1

Don't suggest such an action without thorough investigation! Someone could loose his job because of this.
– OzrenTkalcecKrznaric
Commented Jun 10, 2013 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
Commented Jun 21, 2013 at 18:11

Add a comment |

Yogee · Accepted Answer · 2013-06-10 17:06:06Z

1

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.

answered Jun 10, 2013 at 17:06

Yogee

1,47214 silver badges22 bronze badges

Add a comment |

Collectives™ on Stack Overflow

High performance data insert into SQL Server

3 Answers 3

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Linked

Related