Return to Answer

It may be worthwhile determining if a batch crosses a partitioning boundary and manually changing the batch windows so that a batch only ever handles a single partition. Assuming the thing you use to identify a row matches the partitioning key, which isn't always the case, this is easy; if the clustering key is something else (like leading on datetime), it's a little more complicated.

And the batch can be interrupted, because if you stop it and start it again tomorrow or next week, it will pick up where it left off. If you have the processing power, you could get creative and have multiple processes working through the queue simultaneously, as long as they were each configured to work on their own partition (but you'd only really see any gains here if they weren't saturating the I/O, e.g. the partitioning is physical as opposed to just logical).

It may be worthwhile determining if a batch crosses a partitioning boundary and manually changing the batch windows so that a batch only ever handles a single partition. Assuming the thing you use to identify a row matches the partitioning key, which isn't always the case, this is easy; if the clustering key is something else (like leading on datetime), it's a little more complicated.

Anyway, that batch can be interrupted, because if you stop it and start it again tomorrow or next week, it will pick up where it left off (this is why I don't use #temp tables here). If you have the processing power, you could get creative and have multiple processes working through the queue simultaneously, as long as they were each configured to work on their own partition (but you'd only really see any gains here if they weren't saturating the I/O, e.g. the partitioning is physical as opposed to just logical).

DECLARE @BatchNumber int;
SELECT @BatchNumber = MIN(BatchNumber)
  FROM dbo.UpdatingQueue 
  WHERE Processed = 0;

WHILE @BatchNumber IS NOT NULL
BEGIN

  -- need try/catch here obviously

  UPDATE mbt SET { whatever your update is }
  FROM dbo.MyBigTable AS mbt
    INNER JOIN dbo.UpdatingQueue AS q
    ON mbt.MyKey >= q.RangeStart AND mbt.MyKey <= q.RangeEnd
    WHERE q.BatchNumber = @BatchNumber
      AND q.Processed = 0;
    
  UPDATE dbo.UpdatingQueue
    SET Processed = 1 
    WHERE BatchNumber = @BatchNumber;
    
  SET @BatchNumber += 1;
  
  SELECT @BatchNumber = MIN(BatchNumber)
    FROM dbo.UpdatingQueue
    WHERE Processed = 0;

  -- add a delay, explicit checkpoint 
  -- / log backup here, what have you
END

DECLARE @BatchNumber int;
SELECT @BatchNumber = MIN(BatchNumber)
  FROM dbo.UpdatingQueue 
  WHERE Processed = 0;

WHILE @BatchNumber IS NOT NULL
BEGIN

  -- need try/catch here obviously

  UPDATE mbt SET { whatever your update is }
  FROM dbo.MyBigTable AS mbt
    INNER JOIN dbo.UpdatingQueue AS q
    ON mbt.MyKey >= q.RangeStart AND mbt.MyKey <= q.RangeEnd
    WHERE q.BatchNumber = @BatchNumber
      AND q.Processed = 0;
    
  UPDATE dbo.UpdatingQueue
    SET Processed = 1 
    WHERE BatchNumber = @BatchNumber;
  
  SELECT @BatchNumber = MIN(BatchNumber)
    FROM dbo.UpdatingQueue
    WHERE Processed = 0;

  -- add a delay, explicit checkpoint 
  -- / log backup here, what have you
END

And the batch can be interrupted, because if you stop it and start it again tomorrow or next week, it will pick up where it left off.

And the batch can be interrupted, because if you stop it and start it again tomorrow or next week, it will pick up where it left off. If you have the processing power, you could get creative and have multiple processes working through the queue simultaneously, as long as they were each configured to work on their own partition (but you'd only really see any gains here if they weren't saturating the I/O, e.g. the partitioning is physical as opposed to just logical).