Cost of replaying transaction log on sql server HA replica

Question

I've been reading about the sql azure scale out.

I suppose it makes senses to leverage the replicas hardware for read-only workloads.

But it got me wondering, which is actually doing more work? The primary or the replicas?

In particular it's not clear to me how expensive it is for the the replicas to 'catch up' on the transaction log from the primary.

If I understand correctly, if the primary executes a query like UPDATE dbo.LargeTable SET [SomeColumn] = 10, then it would probably do an index scan to update all the rows, but the transaction log will actually contain one entry per row? That would mean the replica would need to do a key lookup for each row instead of an index scan, which would be a lot more expensive.

I'm sure my logic is wrong somewhere but I'd love to understand more about this.

Is this assumed that catching up on the primary is low cost (relative to the cost incurred by the primary to actually run the transaction)?

Also, are the physical pages of the replicas exactly identical to the primary at the byte level? (i.e. fragmentation is the same too) Or are there just logically the same?

Answer 1

But it got me wondering, which is actually doing more work? The primary or the replicas?

The Primary. The secondary doesn't have to run the SELECT,INSERT,UPDATE, and DELETE statements for the sessions that are modifying the database. The log records resulting from those operations are sent to the secondary which copies them to its log file and then applies them to the database using the REDO process.

The Secondary can sometimes perform more IO than the primary, as the primary writes the log file, and lets the Checkpoint/Lazywriter write the database. But overall the Primary does more work.

That would mean the replica would need to do a key lookup for each row instead of an index scan, which would be a lot more expensive.

The secondary doesn't apply the logical operation. The log record indicates exactly what modifications are made to the database pages.

Also, are the physical pages of the replicas exactly identical to the primary at the byte level?

Yes. In fact a if the primary discovers a corrupt page, it will repair the corruption by getting an uncorrupted copy of the page from a secondary. This is called Automatic Page Repair