Sql-server – Cost of replaying transaction log on sql server HA replica

high-availabilitysql servertransaction-logtransactional-replication

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?

Best Answer

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

Related Solutions

Effective Techniques to Reduce SQL Server Disk Space Use When Updating Large Table

I've just gone through a similar process just couple of weeks ago. After several tries, with couple of the bigger tables (one of them more than 100million rows, near 80Gb) I came up with these steps to speed up things and keep transaction log small:

ALTER DATABASE [my_db] SET RECOVERY SIMPLE WITH NO_WAIT (check here for more info)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (check here for more info)
drop nonclustered indexes: this step will avoid the hassle with the need of updating them on each operation (check Brent Ozar blog for more details)
use batches: as suggested by mrdenny, do whatever you need to do, update, delete, inserts, in smaller chunks.

Here is a sample of batch processing for an update, 1000 rows at a time:

 UPDATE TOP(1000) your_table
 SET    col1 = new_value
 WHERE  <your_condition>
 WHILE  @@rowcount > 0
 BEGIN
     UPDATE TOP(1000) your_table
     SET    col1 = new_value
     WHERE  <your_condition>;
 END;
 GO

restore nonclustered indexes
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ALTER DATABASE [my_db] SET RECOVERY FULL;

Sql-server – Transaction log full due to log_backup

This error occurs because the transaction log becomes full due to LOG_BACKUP. Therefore, you can’t perform any action on this database, and In this case,

the SQL Server Database Engine will raise a 9002 error.

To solve this issue, you have to do the following:

Take a Full database backup.
Shrink the log file to reduce the physical file size.
Create a LOG_BACKUP.
Create a LOG_BACKUP Maintenance Plan to take backup logs frequently.

Note: The Shrink operation effects on SQL Server Performance during executing shrink command. it also causes index fragmentation and can slow the performance of queries that search a range of the index.

So, it’s recommended before goes live you should prepare LOG_BACKUP Maintenance Plan to back up the log file frequently to avoid the shrink operation on Production.

For more details check The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

Hope this helps you

Best Answer

Related Solutions

Effective Techniques to Reduce SQL Server Disk Space Use When Updating Large Table

Sql-server – Transaction log full due to log_backup

Related Question