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
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
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 …
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.
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!