July 18, 2008 at 9:59 am
Hi,
Here is the scenario:
Environment is SQL Server 2005.
There is a large table, 500 Million rows in it, in production. There are lots of users accessing it. A simple update has to be done to one of the columns in this table with presumably all rows getting affected. How would you approach this problem without causing any issues to the server or users.
I would appreciate any thoughts on strategies that can be used to deal with a situation like that.
July 21, 2008 at 6:52 am
There's not enough information in the question to give you specfic answers, but some general thoughts I would have when faced with the general situation...
Can you test the update against a different server to see how long it would take without users on the system? With that information in hand you can start making decisions. Check the execution plan to be sure the update takes advantage of indexes. Possibly break the update down, only doing (picking a number at random) 200,000 rows at a time instead of millions. You'll want to watch the log size on an update like this. You might also run into tempdb problems because of reording on indexes and stuff like that which put a load on tempdb.
Those are just the first things that come to mind.
"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
August 1, 2008 at 12:24 pm
I think enabling Snapshot Isolation Read Committed would solve your problem.
But there are other implications to enabling this isolation level, so you will have to test carefully before implementing in production.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply