Availability Groups Can Use Snapshot Isolation

7 Aug

Background
Whilst reading up on the finer details of High Availability Groups (HA Groups) I have discovered that it can, under certain circumstances, use Snapshot Isolation Level and therefore, row versioning. This has implications with your monitoring and maintenance tasks.

Detail
In a desire to understand HA Groups further, I am reading a particularly useful book. In this, it explains that if you have a HA Group that has readable secondaries, then the primary server will activate read-committed snapshot isolation. This is to avoid contention between the redo thread (applying the log changes that have been sent to the replica) and any read-only queries. Although you cannot apply any updates to a read-only secondary, a query will still obtain share locks, which would interfere with the log redo.

Demonstration
In a previous article I wrote about monitoring the version store, which is used by snapshot isolation. Although I’m currently using SQL Server 2012 (the article used 2017) the same monitoring applies.

Having activated Performance Monitor, I have selected:
Version Store unit Creation
Version Store Unit Count
Version Generation Rate and
Version Cleanup Rate

Although low, the resultant graph shows activity on all of these metrics. Read-committed snapshot isolation has not been specified on this system but has still been implemented due to having at least one readable secondary node in our HA group.

Showing Version Store usage

Conclusion
Although not necessarily an issue, having discovered that snapshot isolation is implemented, whenever you have a read-only secondary on a HA Group, there are now other areas that may need checking, when considering performance issues.

References
Read Committed Snapshot – Another Optimistic Flavour
SQLskills SQL101: Readable secondary performance problems
Book – Pro SQl Server Always on Availability Groups
Huge Redo Queue – Version Store Full

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

%d bloggers like this: