Notes on SQL

Random articles from a puzzled DBA

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.

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.

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

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.

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

Readable Secondary Performance Problems

Leave a Reply

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

You are commenting using your 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: