SQL Server 2014 Standard Edition
We have a table with 100million+ rows.
We need to update values in a couple of columns.
We did the below, and it has been running for 18 hours now, and has only done 17 million rows. It is getting slower and slower.
It is a reasonably hot server and disk system (EMC RAID10 and all that). The CREATE INDEX took about 20 minutes (an unfortunate outage we had to tolerate).
What approach can we use to get through this faster? (on line strongly preferred)
CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC ); GO DECLARE @CHUNK_SIZE int SET @CHUNK_SIZE = 4000 -- to stay under lock escalation threshhold UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01' where deleted is null or deletedDate is null WHILE @@ROWCOUNT > 0 BEGIN UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01' where deleted is null or deletedDate is null END