Insert high-performance data in SQL Server

Question

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

1) Disabled all non-clustered indexes. I did not disable the 2-column clustered index because the data cannot be inserted after disabling the clustered index?

2) I use C # SqlBulkCopy to do bulk insertion of 5000 records each time.

3) There are no restrictions and triggers in the table

Do I have to do something to speed up this data insertion process?

Answer 1

Get rid of ALL of your indexes. Each time you write with an index, the physical page must be restructured when each record is written. Reset all your indexes using the code (DROP INDEX), insert your data and then recreate your indexes using the code (CREATE INDEX).

Answer 2

Ensure that the database is in BULK LOGGED or SIMPLE recovery mode, at least until all records are inserted. This will cause the transaction log file to fail.

Answer 3

As the source said, your cluster index can be a problem. You can insert data so that your cluster index is not sorted. Therefore, the SQL server must create many pages with one record and do the optimization as soon as it sees a lot of rarely populated packages. Try using indexes with automatic int generation. If you cannot do this, sort all bulk data (based on a clustered index column - order is important) before embedding them in SQL. This should minimize disk usage.