Large rolling update on huge table: Getting slower and slower

Question

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

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

It probably would have been better to create the index as filter (i.e. with a WHERE clause) as that would get smaller as you updated more records that would then get filtered out of it:

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX
  ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC )
  WHERE deleted is null
  OR    deletedDate is null;

However, you are still going to run into the issue of scanning that index every time that loop runs. With 100 million rows in the table and updating 4000 per iteration, that is 25,000 times you are scanning the table (or index) looking for rows to update. It would be best if you reduced the number of times you queried the table.

Please see my answer to one of your other questions regarding this project for a setup that should make this process go much faster:

sql server: updating fields on huge table in small chunks: how to get progress/status?

In that answer, I show how to query the large table only 100 times. Each time it grabs the Clustered Index Key Field(s) and uses them for all of the UPDATE queries so that the updates are fast. And that setup also allows for getting the current progress of the operation quickly (and without having to query the table!) and cleanly cancelling the process.

It should be noted that this question relates to the following questions (listed in chronological order):

Large rolling update on huge table: Getting slower and slower

1 Answers1