Table locked while Bulk Insert in sql sever 2005

Question

Table locked while Bulk Insert in sql sever 2005

Ganesh Babu-465615

SSC Enthusiast

Points: 105
More actions
August 1, 2008 at 2:26 am

#210786

Hello,
I have a table with 2.5 million records. I will insert bulk of records in this table using BULK INSERT every day(more than 4 times). While Bulk insert I could not select the records from this table. Its taking too long time to execute the query in SQL Query Analyzer. In my web application I have a report showing from this table, it thrown an error "Timeout Expired". After insertion finished, I can select the records from the table and can see the report in my application without any problem.
Please tell me why its happening like this. Is the table locked? (I think so.) If yes, how to know whether this table is locked or not?. Because I will show the friendly message to the user who are seeing that important report in my web application. How to avoid this locking?
Additional Information
* Table has 2.5 million rows with 30 columns and has a Primary Key field.
* Table has a Indexed View with 25 columns
* Indexed View has a Unique Clustered Index and has non-clustered index of remaining 24 column (to speed up my reports.[querying all the fields thats why])
Please give your valuable suggestions.
Thanks in advance.
Ganesh.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

Once you pass a certain number of rows, the engine has to lock the table to be effecient. There's no getting around that fact. So, instead, you have to look at different mechanisms for doing the load.

Assuming you're adding to the table, not replacing it every time, one thing you could try is using a partitioned table. This mechanism would allow you to load the data to a different physical storage location that is marshalled together with other physical storage to make a single table. There's a whole bunch of information here at SSC and in the BOL.

You could try using A/B switch mechanism if you have to replace the table every time. This would entail having two copies of the table. Have the users access one while you load the other. Once the load is complete, rename the tables to swap them out. That will lock the table temporarily, but should pretty radically reduce the time that users can't access it to a number of milliseconds instead of however long your data load is. That might be problematic with a materialized view though. You'll need to test it out to be sure it will work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ganesh Babu-465615 SSC Enthusiast Points: 105 More actions · Answer 2

Ganesh Babu-465615

SSC Enthusiast

Points: 105

August 1, 2008 at 7:29 am

#851868

Great! Thanks for your valuable suggestion.

Ganesh.

Ganesh Babu-465615 SSC Enthusiast Points: 105 More actions · Answer 3

But now I noticed that, the whole database gets locked. I could not query any other tables.:(.

Is this locking related to Remote Query.? Bcz I will query as Remote Query using linked server.

Please suggest me.

Ganesh.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

Hrmmm...

Unless this is the only table in the database, I'd be really shocked to see a database lock based solely on inserts to a single table. I'm guessing there's a lot more complexity to this import than you've mentioned so far. What kind of isolation level does this database have? Are you changing options on the DB during the load?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Ganesh Babu (8/1/2008)
But now I noticed that, the whole database gets locked. I could not query any other tables.:(.

Off-hand, it sounds like it is having to expand the database. Make sure that your database is pre-extended for this data & work and do not shrink it in-between.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Ooh. I hadn't thought of that.

It wouldn't show up as a database lock though, would it?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Afraid that I do not know, Grant. Maybe someone else does?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 8

Now that I'm thinking about it (and I'm also asking around) unless the database is only a single filegroup, it's unlikely that it would show up as an actual locked db because the individual file or file group would be expanding and the rest of the database would be accessed as normal. I'm still checking around to see if someone has a more definitive answer.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mdean1962 SSC Journeyman Points: 91 More actions · Answer 9

Have you tried disabling the indexes prior to the insert; then enabling them once inserted? This should provide a significant boost.

Tom.Bauto Old Hand Points: 370 More actions · Answer 10

You should check what type of locking procedure you are using; and by the time you want to view the data on your table; try to make use of NOLOCK so that you can freely select the data.

by the way i suggest that you use a data warehousing for your database; now that you mentioned you had a record with 2million rows and you use to insert bulk records. data warehousing will solve your problem. if you use .net on your web or SQL2005 try to implement the cube. you can reply on that.

_____________________________________________
[font="Verdana"]ToM™[/font] (Possibility will come out of nothing)

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 11

rbarryyoung (8/1/2008)
Ganesh Babu (8/1/2008)
But now I noticed that, the whole database gets locked. I could not query any other tables.:(.
Off-hand, it sounds like it is having to expand the database. Make sure that your database is pre-extended for this data & work and do not shrink it in-between.

Surely though, if that were the issue, then the lock would only be there while the expansion was taking place and not throughout the load?

pxt_ce SSC Eights! Points: 830 More actions · Answer 12

Frnds....

Can we use Horizontal Partitioning on table ? this may reduce the processing time..

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 13

Ganesh - verify that when you try to query another table that your process is actually blocked (check sp_who2 for a blocked process). If it is not being blocked, it is likely that the process is SUSPENDED because it is waiting for the database to expand or simply waiting for your disk drives to catch up.

Avoid ToM's suggestion of using NOLOCK - especially during a bulk insert. It is very likely during a bulk insert of a significant size to cause data to be read multiple times and data to be skipped because of data being moved around in your indexes. This is a pretty common mis-use of the NOLOCK query hint.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

Grant Fritchey (8/1/2008)
Ooh. I hadn't thought of that.
It wouldn't show up as a database lock though, would it?

No... in fact, it wouldn't show up as anything because you simply can't do anything while the database is growing. I think they fixed this in Katmai, but I'm pretty sure that growth periods are exclusive periods in 2k5.

The other thing is that importing directly into a table that is also being used for OLTP is a form of death by SQL. A staging table should be used so that you can verify the data without the excessive time it takes for constraints and a bunch of other stuff to happen.

Also, check the Clustered index... if it doesn't make it so the data is inserted at the logical end of the table, then you're going to get a bunch of page splits... doesn't take very many for SQL Server to say "Ok be that way... I'm locking the whole table".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)