Rajendra Gupta
SQL Server Always On Availability Groups internals

Isolation levels behavior in SQL Server Always On Availability Groups

June 18, 2019 by

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

SQL Server Always On Availability Groups internals

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.

Overview of Snapshot Isolation level in SQL Server Always On Availability Groups

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.

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.

sample data

We can check page details for any particular page using the following DBCC Page command.

For my example, Execute the following query:

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.

DBCC  PAGE output in SQL Server Always On Availability Groups

Let’s change the Isolation level to Read Committed Snapshot using the ALTER DATABASE command and update the records in the table.

Rerun the DBCC Page command and view the difference in record size.

DBCC page output in snapshot isolation

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:

Session 2:

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.

Isolation level behaviour in SQL Server Always On Availability Groups

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.

Secondary Replica not enabled for Read workload in SQL Server Always On Availability Groups

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.

Secondary Replica enabled for Read workload in SQL Server Always On Availability Groups

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.

Primary replica with Snapshot isolation but Secondary Replica not enabled for Read workload in SQL Server Always On Availability Groups

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

Primary replica with Snapshot isolation and Secondary Replica enabled for Read workload in SQL Server Always On Availability Groups

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

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 rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
753 Views