AlwaysOn Readable secondaries - Version Store

Question

The environment is: SQL 2019 3 Node AG Synchronous between Node 1 & Node 2 Asynchronous to Node 3

Following this article:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver15#Read-OnlyStats

Specifically this point:

I don't have Snapshot isolation OR RCSI enabled on the database.

This clearly states that on the Primary - There will be no row versions.

However on my system - A delete was running on the primary and I could clearly see version store records being created on the primary. In fact, there was more version store usage on the primary than the secondary.

This is what I used to gather this:

    SELECT
SUM (user_object_reserved_page_count)*8/1024.0/1024.0 as user_obj_GB,
SUM (internal_object_reserved_page_count)*8/1024.0/1024.0 as internal_obj_GB,
SUM (version_store_reserved_page_count)*8/1024.0/1024.0  as version_store_GB,
SUM (unallocated_extent_page_count)*8/1024.0/1024.0 as freespace_GB,
SUM (mixed_extent_page_count)*8/1024.0/1024.0 as mixedextent_GB
FROM sys.dm_db_file_space_usage

And this:

SELECT  
    transaction_sequence_num,  
    version_sequence_num,  
    database_id rowset_id,  
    status,  
    min_length_in_bytes,  
    record_length_first_part_in_bytes,  
    record_image_first_part,  
    record_length_second_part_in_bytes,  
    record_image_second_part  
  FROM sys.dm_tran_version_store;

I can understand why their would be version store usage on the secondary replica but why is there version store usage on the primary replica? Am I missing something?

Also, the synchronous replica shows no version store usage but the asynchronous replica does. Why is this?

Any advice?

Thanks

Answer 1

I don't have Snapshot isolation OR RCSI enabled on the database.

and

I can understand why their would be version store usage on the secondary replica but why is there version store usage on the primary replica? Am I missing something?

Readable secondary replicas remap the default isolation of READ COMMITTED to SNAPSHOT, thus versions will be generated. Since versioning requires a 14-byte tag to be added to the row, which may cause page splits and other items, the versioning record space must be created on the primary. This means the primary needs to make space for the versioning tag for the records, which requires the use of the version store on the primary.

Also, the synchronous replica shows no version store usage but the asynchronous replica does. Why is this?

It has to do with which databases are marked at readable or not, and if readable, do they have any user workload on them. If it's not readable or if there is no user workload, there won't be any version store data because there's nothing to keep in the version store. No query, no need for versions.