This article aims to demonstrate the lesser-known impact of Always On Readable secondaries. In this article, we will not be going into how readable secondaries are configured and how read-only connections are directed to readable secondaries. Instead, we will take a peek under the hood and see what is happening when it is already configured. It is important to note that readable secondaries uses snapshot isolation and that comes at a price.

SQL Server Always On Availability Groups offers us an opportunity to offload read-only workloads onto readable secondaries. This is a great idea as we can now offload Reporting style queries to secondary servers with no impact to the primary server. Seems simple enough with these steps:

1. Turn on readable secondaries.
2. Direct read-only workloads to readable secondaries.
3. Everyone is happy.

Not so fast...

I was recently tasked with configuring readable secondaries for our organization to leverage this feature. Through research and testing, I have uncovered a few very important side effects that I believe every DBA should be aware of before implementing this solution.

It's very important to be aware that any read-only transaction run against a readable secondary database runs under Snapshot Isolation. This is to avoid blocking contention on readable secondaries as changes from the primary replica are applied. This happens even if your databases are not configured for snapshot isolation and this also cannot be turned off. Any DBA who has worked with optimistic concurrency isolation levels will know that the Snapshot Isolation level as well as the Read-Committed Snapshot Isolation level does come at a price.

So, keep in mind that Always On Readable secondaries and Snapshot isolation work together.

There are two aspects that I would like to focus on, TempDB and additional database space usage.

Let's dive in...

TempDB

We have already mentioned that any read-only queries run against a readable secondary employs Snapshot isolation. Snapshot isolation works by using a method called row versioning. In essence, before any change is made to a row, SQL Server copies a version of that row to a designated space in TempDB, called the Version Store, before the change is made. This is so that any read requests for that row will be directed to the version store in  TempDB, thereby avoiding contention on the original page. The versioned row will remain in TempDB for as long as the transaction that is changing the row is open.

At this point, readable secondaries are already configured. We can confirm this by running the below on the primary replica:

select
    ag.name AGName,
    ar.replica_server_name,secondary_role_allow_connections_desc
from sys.availability_replicas as ar
join sys.availability_groups as ag
    on ag.group_id = ar.group_id

This yields the below result:

Note the column secondary_role_allow_connections_desc. If your secondary replica has either a value of Read-Intent or ALL - This means that readable secondaries are in place.

Let's have a look by means of a very basic but conclusive test. First, on the primary replica we create a table and populate it with 10 rows:

use AdventureWorks2019
go
create table table_1 (Col1 int)
go
DECLARE @Counter int 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
    insert into table_1
values (@Counter)
    SET @Counter  = @Counter  + 1
END

Now, before we can show the effect on TempDB on the readable secondary replica, let's have a look at the version store usage before we modify any rows.

Run this on your readable secondary:

SELECT 
  DB_NAME(database_id) as 'Database Name',
  reserved_page_count,
  reserved_space_kb 
FROM sys.dm_tran_version_store_space_usage
where DB_NAME(database_id) = 'AdventureWorks2019'

Everything being equal with nothing else happening against your database, you will see a result like this:

We can confirm with certainty that there is in fact no version store usage with the below T-SQL script:

use TempDB
go
SELECT
SUM (version_store_reserved_page_count)*8/1024 /1024  as VersionStoreUsageGB
FROM sys.dm_db_file_space_usage

This gives the following result:

This tells us that there is no version store usage.

Now, on the primary replica, let's modify a few rows:

use AdventureWorks2019
go
begin transaction
update table_1
set Col1 = 100
where Col1 < 6

We have started a transaction on the primary replica that modifies 5 rows.

Let's head back to the secondary replica and see if there is any version store usage by re-running our version store query:

SELECT 
  DB_NAME(database_id) as 'Database Name',
  reserved_page_count,
  reserved_space_kb 
FROM sys.dm_tran_version_store_space_usage
where DB_NAME(database_id) = 'AdventureWorks2019'

The above query would yield a result similar to this now:

This tells us that 8 pages have been allocated in TempDB to hold the row versions.

So, clearly our update statement on the primary replica has caused some TempDB usage on the readable secondary replica.

Is this a problem?

No, it's Snapshot isolation at work here. With snapshot isolation, there will be additional space usage in TempDB as rows are inserted or modified. It is important to know that this is going to happen. This means that we as DBA's will need to make sure that our TempDB is properly configured to handle the extra load that Always On Readable secondaries comes with.

Depending on your workload and rate of change in your database, you should pay a close eye to your TempDB to monitor the effects.

The extra 14 bytes per row

In order to maintain optimistic concurrency on readable secondaries, Snapshot isolation is used. When a row is modified, the version of that row is copied to the version store in TempDB - We know this. On top of this, an extra 14 bytes of information is added to the modified row which contains a pointer to the versioned row in TempDB as well as the transaction sequence number of the transaction that modified the row.

Let's have a look...

At this point, readable secondaries are turned off. First, let's create another table while we do not have readable secondaries enabled.

use AdventureWorks2019
go
create table table_2 (Col1 int)
go
DECLARE @Counter int 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
    insert into table_2
values (@Counter)
    SET @Counter  = @Counter  + 1
END

And now, let's update a few rows.

update table_2
set Col1 = 100
where Col1 < 6

Now, let's examine a page in this table by running the below:

DBCC IND ('AdventureWorks2019','table_2',0)
GO

This command lists all the 8kb pages that makes up our table.

In my case, this is the result:

For the purpose of this test, we are interested in PageType 1 as this is a data page.

Using the PagePID for this page, we can go a bit deeper and have a look the contents of the actual page:

DBCC TRACEON (3604)
GO
DBCC PAGE (AdventureWorks2019, 1, 24944, 3)

Isolate an entry with a value of 100 as a result of our update statement

Of importance here is the record size of 11 bytes.

Now, let's turn on readable secondaries and repeat the test. We'll recreate the test with readable secondaries turned on using the below T-SQL script:

use AdventureWorks2019
go
drop table table_2
go
create table table_2
(Col1 int)
go
DECLARE @Counter int 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
    insert into table_2
values (@Counter)
    SET @Counter  = @Counter  + 1
END
go
update table_2
set Col1 = 100
where Col1 < 6

Again, we'll get a list of pages that makes up our table:

DBCC IND ('AdventureWorks2019','table_2',1)
GO

This yields..

And again, we'll have a look at the contents of the page with PageType 1

DBCC TRACEON (3604)
GO
DBCC PAGE (AdventureWorks2019, 1, 41320, 3)

Isolate an entry with a value of 100 as a result of our update statement and we'll see this

Straight off the bat, we can see that the record size is now 25 bytes, 14 bytes more than when readable secondaries was turned off. Also, we note that versioning information has been added to the row.

Is this a problem?

Again, no it's not. This is how snapshot isolation works.....But

14 bytes seems negligible because, well...It's only 14 bytes. It's important to keep in mind how volatile to change your system is and how heavy your workload is. Once you start multiplying that 14 bytes by each row modified or inserted, it's starts to add up.

An even more critical side effect of this is page splits, if your page does not have enough space to accommodate this extra 14 bytes, a page split will happen.

In conclusion, some points to note

Readable secondaries are a great feature by means of offloading reporting onto secondary replicas, as long as we are aware of the side effects. The effects shown above are a side effect of the fact that readable secondaries use Snapshot isolation and not a direct effect of readable secondaries themselves. The version store usage on the secondary replica will happen regardless of whether or not the row is going to be read on the secondary replica. The extra 14 bytes per row will happen regardless of whether or not the row is going to be read on the secondary replica. ANY inserted or modified row will be affected by the extra 14 bytes.

We as DBA's are spoiled with the many features that can make our lives easier. It's important to test everything and test thoroughly so that when we implement something, we know what  to expect.