Infrastructure at your Service

Microsoft Team

Availability groups : readable secondary replicas and impact on the storage layer

Do read-only capabilities of an availability group have a performance impact for databases? The quick response is yes but let’s see why in this blog post.

First of all, when we want to use our secondary replicas as read-only, we enable and we use implicitly the SNAPSHOT isolation level regardless either the transaction isolation level or the hint used in your query. So as you know, the use of the SNAPSHOT isolation level has an impact on the storage layer because 14 bytes are added on each row in a data page as a pointer to the versioned pages linkage.

Let’s demonstrate it on my lab environment that includes a pretty basic availability group with two replicas and a database (db1) as well. My database contains one table (dbo.t1) with one column id (INT IDENTITY) and no index as well.

I inserted first two records with no read-only secondary replicas. Let’s take a look at the concerned data page:

select 
	object_name(object_id) as table_name,
	index_id,
	allocation_unit_type_desc as alloc_type_desc,
	allocated_page_page_id,
	page_type_desc as page_tye
from sys.dm_db_database_page_allocations(db_id(), object_id('dbo.t1'), 0, null, 'detailed');
go

blog 73- 1 - data page location

The page id 126 is our data page. Let’s dump this page in order to see the records we inserted previously:

dbcc traceon(3604);
go
dbcc page (db1, 1, 126, 3);
go

blog 73- 2 - data record 1

blog 73- 3 - data record 2

No extraordinary things here … Let’s switch the secondary replica WIN20162\SQL14 to read-only mode …

USE [master]
GO

ALTER AVAILABILITY GROUP [AG2014]
MODIFY REPLICA ON N'WIN20162\SQL14' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO

… And let’s insert one additional record into the dbo.t1 table from the primary replica.

insert dbo.t1 default values;
go

Will we get the same kind of records in the data page? Let’s have a look …

blog 73- 3 - data record 3

The story has changed here. For this row, we may notice additional information related to the versioning information. Remember I’m still on the primary replica and I’m not using SI unlike the secondary and I get anyway a version pointer (null in this case). If we move on the read-only secondary replica we get the same result.

blog 73- 4 - data record 4

How about the previous rows? Well, only newly inserted rows are concerned but if UPDATE or DELETE / INSERT operations are done against my previous rows, the versioning information will be part of them. For small databases, it probably doesn’t matter but let’s image with large databases and billions of rows…

But that’s not all … it means that for tables / indexes on full pages, a simple UPDATE could result in page splitting with the overhead of 14 additional bytes. Moreover, we cannot insert as many rows as the snapshot isolation disabled causing a more or less increase of the storage size for the same data. Of course, enabling SI has also an impact on the tempdb database but this is another story!

See you

By David Barbarin

 

 

2 Comments

  • Vimal Prajapati says:

    I know I’m sooo late here but Thanks David for this interesting information.

    I would like to ask a question regarding this scenario. Suppose I am having a readable secondary replica and now if I run a select query on primary replica will it execute in snapshot isolation level as we already have the row versioning enabled? Or the data will be return from table taking the shared lock (S) on same?

    Thank you !

    • David Barbarin says:

      Hi Vimal,

      Thanks for reading.

      If RCSI is not enabled on the primary and RO is enabled on the secondary, the only impact is on the storage with the 14 bytes versioning overhead. This overhead comes from REDO thread on the secondary replica as it processes the UPDATE/DELETE operation from the primary but RCSI is not activated automatically by the SQL Server engine on the primary in any case.

      Regards

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Microsoft Team
Microsoft Team