In my previous article, Data synchronization in SQL Server Always On Availability Groups, we explored the internal data synchronization for both Asynchronous and Synchronous data commit mode. In the following image, on the secondary replica, we can see two important processes.
- Log Receive: It receives the log records sent by the primary replica and writes to Log cache on each secondary replica
- Redo Thread: Redo thread on secondary replica writes all log records changes to the data page and index page
Impact of Readable secondary replica in SQL Server Always On Availability Groups
Suppose, you have a readable secondary replica to distribute read workload to the secondary replica. The overall objective is to keep the minimum load on the primary replica.
We might have a few questions at this point.
- Does read workload on secondary replica make any performance impact on a primary replica?
- Does primary replica get impacted due to locking on a secondary replica?
- What is the impact of isolation levels on the secondary read workload?
Let’s evaluate the impact of a readable secondary replica over primary replica with the following points
- In the Asynchronous data commit mode, the primary replica does not wait for an acknowledgement from the secondary replica. It does not make any impact on the transaction time
- In Synchronous data commit mode, primary replica waits for an acknowledgement from the primary replica. Once the log record is hardened on the secondary, it sends an acknowledgement to the primary replica
Readable secondary replica in SQL Server Always On Availability Groups does not impact the acknowledgement receipt from secondary to the primary replica. SQL Server always gives priority to internal threads. In this case, Redo thread always gets a priority on secondary replica than the user threads (due to read workload). If there is a high IO intensive workload, it might cause IO bottleneck. We should always follow the best practice of segregating data and log file into different data store or physical disks.
We might see an issue in which REDO threads get slow due to the read workload. We might see blocking for REDO thread. In these cases, you might impact the Recovery Point Objective and Recovery Time Objective for database services.
Overview of Snapshot Isolation level
SQL Server default isolation level is READ COMMITTED in SQL Server. An isolation level changes the behavior of locking in SQL Server. In the Snapshot Isolation Level, SQL Server maintains a row version for each transaction in TempDB. SQL Server maintains a sequence number of the transaction. It does not acquire locks on the data pages for a snapshot transaction. It allows other transactions to execute without any blocking scenario.
In the following screenshot, we can see that one user-initiated a transaction to update a record while other user wants to read that data. In the Read Committed Snapshot Isolation level, SQL Server provides a copy of the page to read the data.
In the Read Commit Snapshot Isolation level, SQL Server requires an additional 14 bytes for maintaining row versions in TempDB.
Let’s understand using a simple example.
Execute the following query to create a table and insert few records in it.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Test ( ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, Code CHAR(1000), ) GO INSERT INTO Test VALUES (REPLICATE('A', 1000)) GO 100 |
Once data insertion is complete, we can check the page details using DBCC IND and DBCC PAGE commands.
Specify DBCC IND parameters in the following format.
|
1 |
DBCC IND(‘DB’,’Object’,-1) |
Execute this command, and it gives you page numbers for the particular objects.
|
1 2 |
DBCC IND('Test',Test,-1) GO |
We can check page details for any particular page using the following DBCC Page command.
|
1 |
DBCC PAGE(‘DB’,PageNo, PageType, OptionType) |
For my example, Execute the following query:
|
1 2 |
DBCC TRACEON(3604) DBCC page ('Test',1,348,3) |
Here Option 3 shows both page header information and data. You also need to enable trace flag 3604 to show the output of the DBCC command. We can see record size 1011 for this page.
Let’s change the Isolation level to Read Committed Snapshot using the ALTER DATABASE command and update the records in the table.
|
1 2 3 |
ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT ON GO Update Test Set Code='B' |
Rerun the DBCC Page command and view the difference in record size.
The difference in record length= Record length in Read Committed Snapshot Isolation level – Record length in Read Committed Isolation level.
The difference in record length=1025-1011=14 bytes.
In the above calculations, we can see it adds 14 bytes for the record on the data page in Read Commit Snapshot isolation level.
Isolation level behavior in SQL Server Always On Availability Groups
Let’s understand the behavior of locking in SQL Server first. To simulate the issue, open two sessions in SSMS.
Session 1:
|
1 2 3 |
SET TRANSACTION ISOLATION LEVEL Repeatable Read Begin tran select * from Test where ID=10 |
Session 2:
|
1 2 |
Begin tran Update Test set Code='C' where ID=10 |
Session 1 places a shared lock on the qualifying row data and the second transaction wants to update the same record. The Update statement gets blocked due to this scenario.
In the following image, you can understand that transaction 2 is blocked due to transaction 1 and cannot complete the transaction until transaction 1 is completed.
This situation might occur in SQL Server Always On as well. Suppose this time secondary database runs a query on session 1 (changes isolation level to repeatable read). In the session on the primary replica database, another session wants to update the record.
How will the transaction behave in this situation?
All transaction isolation levels on the secondary replicas in SQL Server Always On Availability Groups are mapped to Snapshot Isolation to avoid any blocking. As you know, snapshot isolation uses row versioning.
Isolation level on Secondary Replica | Isolation level mapping |
Read Uncommitted (RU) | Snapshot Isolation ( SI) |
Read Committed (RC) | |
Repeatable Read (RR) | |
Snapshot Isolation (SI) | |
Serializable (SR) |
SQL Server databases in Secondary replica also ignore all locking hits. However, you should test the workload to avoid any issues in the production environment.
As you know that snapshot isolation level uses row versioning. If any row is modified, SQL Server stores its version in the TempDB, and it adds a 14 bytes pointer to track the row version. Let’s explore the following scenarios for the impact of the snapshot isolation level on the Secondary replica database.
Scenario 1: Secondary Replica not enabled for Read workload in SQL Server Always On Availability Groups
In this case, we cannot connect to the secondary replica database for executing read workloads. In this case, there is no additional overhead on the primary replica.
In the following image, you can see no impact on the primary and secondary replica. We cannot use the secondary replica read workload in this case.
Scenario 2: Secondary Replica enabled for Read workload in SQL Server Always On Availability Groups
In this case, users can execute the read workload on the secondary database. SQL Server adds a 14 byte overhead for each row on the primary as well as on Secondary replica. Once the secondary replica, gets transaction log records for DML operation (Update, Delete), REDO thread generates a row version in the TempDB.
In this case, the following process is followed:
- Secondary replica databases use the snapshot isolation level; therefore, it generates a row version in the tempdb of the secondary database. The primary replica does not maintain the row version in this case. It does not make any impact on the tempdb on the primary replica
- The secondary replica also adds 14 byte overhead for each updated row. As you know, both the primary and secondary replica must be identical therefore, The Primary replica database also adds 14 byte overhead for the new or modified rows
In the following image, you can see secondary replica adds 14 bytes overhead along with versioning in the TempDB. The primary replica also gets its overhead of 14 bytes.
Scenario 3: Primary replica with Snapshot isolation but Secondary Replica not enabled for Read workload in SQL Server Always On Availability Groups
In this case, we have a primary replica database with a snapshot isolation level. Primary replica maintains a row version along with the 14 bytes overhead. The secondary replica also gets this 14 byte overhead, but it does not maintain the row versions in the TempDB.
Scenario 4: Primary replica with Snapshot isolation and Secondary Replica enabled for Read workload in SQL Server Always On Availability Groups
- Primary replica maintains the row versioning in the TempDB along with the 14 bytes overhead
- The secondary database also get the 14 bytes overhead
- The secondary database is also available for read access therefore, it also maintains the row versions
Conclusion
This article explores the impact of readable secondary replica in SQL Server Always On Availability Groups along with Isolation level behavior on a primary and secondary replica.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023