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.
CREATE TABLE Test
ID INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
INSERT INTO Test VALUES (REPLICATE('A', 1000))
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.
Execute this command, and it gives you page numbers for the particular objects.
We can check page details for any particular page using the following DBCC Page command.
DBCC PAGE(‘DB’,PageNo, PageType, OptionType)
For my example, Execute the following query:
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.
ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT ON
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.
SET TRANSACTION ISOLATION LEVEL Repeatable Read
select * from Test where ID=10
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)
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
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.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at email@example.com
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- Overview of SQL Server 2019 General Availability and installation - November 19, 2019
- Query Amazon Athena external tables using SQL Server - November 15, 2019
- Read SQL Server error logs using the xp_readerrorlog command - November 14, 2019