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 );

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

    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
  • Some subtle differences here - that's for deletes, but same concepts apply for updates. Note that if all you're doing is deleting a bunch of rows in chunks and not doing anything to the transaction log that is generated in each iteration, you're not really changing much about the time that it will take to delete the whole set - in fact you may be making it worse. How many indexes on the table? Any indexed views? Sep 17, 2015 at 15:21
  • @samsmith can you post the table structure with indexes.
    – Kin Shah
    Sep 17, 2015 at 15:26
  • I hope that table is partitioned?
    – Dave
    Sep 17, 2015 at 20:36
  • @Dave Nay, we are on standard edition.... no partitioning for us... Sep 17, 2015 at 22:53

1 Answer 1


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:

  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):


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.