Need to determine log space required for a large transaction (MSSQL)

Question

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?

Answer 1

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:

• Example db<>fiddle

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

• Break large delete operations into chunks