Table locking on large inserts/updates is KILLING me!
-
I have a database that's about 80 GB ready to go live with the first application using it. I'm still merging large amounts of data from a legacy system and running a few nightly jobs to keep a few of my largest tables current (for example, I have a table of 30 million people and I run a weekly update to keep their time zone current).
My problem is that this time zone update locks the entire table, which I obviouslly can't have during production. Same thing when I'm doing large inserts. I know there are different locking isolation levels (row, page, table), but don't know how to implement it for one of these big updates. I've only really seen examples of using a WITH (NOLOCK) when doing selects. How can I control the locking isolation level for inserts and updates?
Please help!
Thanks,
Jonathon
-
Jonathon,
If you have to update the Whole Table (No where Clause) there is very little you can do. If you write a single update a table scan is going to be performed no matter what and that is SLOW!
If you can divide the Update into several chunks (Where Clause) and ensure that is using an index seek you will be surpised of the difference.
The next thing may be to splitt the large table into several an use a LPV (Local Partitioned View) and that will allow you to hadle multiple updates queries in parallel but it is a little painful to administer
Last but not least pay attention to the hardware. If you are using Raid 5 and you could upgrade to Raid 1+0 that will be a huge Plus!
Oh BTW after you finish such large modifications to the table make sure you rebuild or at least defrag your indexes (it goes without saying that you have to make sure you are using the right ones
)HTH
* Noel -
Jonathon,
Another strategy might be to actually drop the non-clustered indexes on the table that include the time zone column, run the update, and then rebuild the indexes that you dropped. Give it a try on a test environment.
James
James Stover, McDBA -
If your going to update the table every day, then I'd use non-clustered indexes only.
Another thing to try is to rebuild your data in a staging table that looks exactly like your original table. When all of the updates have been done, rename the tables which will provide interference/locking for only about 60 milliseconds. Keep in mind that triggers don't travel well when renaming tables. It's best if you drop the triggers, rename the tables, and rebuild the triggers. The whole thing should take something well under a second. Indexes travel very well because multiple tables can use the same index name without getting all cross-wired.
--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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply