Concurrent Inserts - Bulk Insert & Insert

Question

Hi Guys,

I want some opinion here.

The scenario is like this -

We are doing upgrade of our databases.
The database is huge and few of the tables contain more than 110 million records.
We are using bulk insert to achieve this task.
The upgrade will happen on a live system where user can concurrently update the tables which will be bulk inserting parallel.

So can we achieve concurrent insert - insert being performed by various users and at the back end  migration of data using bulk insert?

I tried Bulk inserting some 1 million records and parallel inserted 10 thousand records.
But these 10 thousand records are getting inserted only after completion of bulk insert.
These insert are on tables with indexes and later we are rebuilding it.

Dropping indexes and rebuilding indexes on 110 million record sounds wierd, so only rebuilding it!!!

Ram is 8GB.

Any help is appreciated!!

Thanks,

Answer 1

You could try specifying BATCHSIZE. This splits the BULK INSERT into several smaller transactions. This also helps keeping log size small. I am not sure other inserts will get locks between the batches, though.

Answer 2

I don't want to perform BULK INSERT in batches. I want either the whole transaction gets completed or no transaction.
Please help!

Answer 3

Hi Asterisk

You could consider bulk loading into partitions and then switching the data in when the load has finished. This whitepaper may help.

http://msdn.microsoft.com/en-us/library/dd425070.aspx

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 4

Hi Asterisk,
You can setup Transactional Replication and move the data
This will have a snapshot intially then it will have your transactional data.
and once done you can remove the replication configuration.

Cheers
Bikash Dash
MCDBA/MCITP

Answer 5

Hi Guys,
I appreciate your replies.
Let me try out your suggestions, will get back to you guys soon!

Thanks,

Answer 6

I'm not sure it will help, but in case...:

Did you try the TABLOCK (or similar) option for the load. This should make the import acquire a BU lock, which is designed to allow parallel imports. I'm not sure whether this requires dropping the indexes, though...

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 7

Hi Tibork,
Yes , i have already tried TABLOCK options for the load.
It acquires a wait_type 'LCK_M_X' on BULK INSERT process and blocks the parallel insert , it acts as a blocking id for another insert process.

Thanks,

Answer 8

Hi Tibork,
And this result is on table with indexes.

And with no TABLOCK options, the result for two parallel bulk insert --

Command        session_id          blocking_session_id      wait_type           wait_time           wait_resource
-----------------------------------------------------------------------------------------------------------------------
BULK INSERT    55                           0                                 NULL             0   
BULK INSERT    59                          55                                LCK_M_IX    12750                  OBJECT: 10:1314103722:0

Thanks,

Answer 9

Hi Asterisk

The Paper I linked to above, The Data Loading Performance Guide, explains (as Tibor says) that you cannot use BU on a table with indexes.

It also has templates for solving the following scenarios:

Bulk loading an empty, nonpartitioned table
Bulk loading into a nonpartitioned table that already has data
Bulk loading a partitioned table
Deleting all rows from a partition or table
Deleting a large amount of rows in a partition or table
Updating a large part of the data in a partition or table

I'm not sure what more you might need...

HTH

Ewan

---

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.