2

I need to update billions of rows in a partitioned table; it's also likely that the updates will move data between partitions. I'm trying to figure out how much additional log space I would require to do this as a single transaction (I realize that I would be better off batching this work, but am trying to avoid that). I want to experiment with a smaller set so I can see what it would take at scale. Is there any system management view I could monitor that would help me figure this out?

3
  • Does this answer your question? How to monitor SQL Server data and log file growth?
    – mustaccio
    Sep 24, 2021 at 17:51
  • @Aaron, long story but if I'm looping I have to know which records have been changed and I don't have anything on the record I can use for a dirty indicator. Adding a column for this purpose would be a big change all on its own. Sep 24, 2021 at 18:15
  • Great article there! Thank you. Problem here is that the update is not idempotent, I can only execute it once per record. I was going to use a last updated column as the control, selecting N remaining untouched records and populating it as I went so I knew which records had been touched. It occurs after looking at your examples that I'm being too strict in my thinking. I can loop on one of the natural keys instead, with a transaction surrounding each one. Thanks for the help - please put your article as the answer if you'd like. Sep 24, 2021 at 18:36

1 Answer 1

6

I don't think you want to try updating billions of rows in a single transaction, and frankly I don't think it's worth the effort to try to extrapolate how much time that would take or how much transaction log churn it would burn through.

Batching is the way to do this... it allows you to minimize the impact on the transaction log, pause the process between batches for verification or maintenance, allows users free access to the portions of the table not currently locked by the current batch, and provides for a much smoother rollback in the case of a problem with the batch (or any problem on the server, some of which won't really impact you until the service restarts, like someone tripping over the power cord).

There is no reason to store a dirty flag in the table. Assuming you have an immutable key (and you aren't intentionally changing the key, or at least you are changing it to some predictable thing that won't make a current row conflict with the future version of a different row), you can simply scan the table once to pre-determine batches/ranges of rows that you will update exactly once. So let's say you have a table with a bigint key, like this:

CREATE TABLE dbo.MyBigTable
(
  MyKey bigint NOT NULL,
  -- OtherColumns here,
  CONSTRAINT PK_MyBigTable PRIMARY KEY (MyKey)
); -- Partition stuff here

And you have a billion rows in there, you can determine the low and high boundaries of each batch you want to update with a single pass of the table:

DECLARE @BatchSize int = 10000; -- find your sweet spot (see article below)

;WITH x AS
(
  SELECT rn      = ROW_NUMBER()       OVER (ORDER BY MyKey),
         ln      = ROW_NUMBER()       OVER (ORDER BY MyKey DESC),
         MinKey  = FIRST_VALUE(MyKey) OVER (ORDER BY MyKey),
         MyKey
    FROM dbo.MyBigTable
)
SELECT BatchNumber = ROW_NUMBER() OVER (ORDER BY rn),
       RangeStart  = COALESCE(LAG(MyKey,1) OVER (ORDER BY MyKey)+1, MinKey),
       RangeEnd    = MyKey,
       Processed   = 0
INTO dbo.UpdatingQueue
FROM x WHERE rn % @BatchSize = 0 OR ln = 1
ORDER BY BatchNumber;

CREATE UNIQUE CLUSTERED INDEX CIX_UpdatingQueue 
  ON dbo.UpdatingQueue(RangeStart, RangeEnd);

Now your batch can say:

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

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

I put together a quick example of this here:

But you should also bookmark this article, which talks about some other aspects of batching (even though the article is geared to deletes, it applies to updates, too):

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.