0

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:

enter image description here

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

1 Answer 1

0

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.

1
  • The problem here was that the DELETE statement was attempting to delete more than what the version store could hold as well as what the T-Log could hold. Was actually just trying to delete 100000 rows but from a table with a lot of BLOBS. A delete of 100 rows generated 7GB of version store...so.. Had to turn off readable secondary and kill the transaction. Commented Jul 20, 2023 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.