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.
Introduction A couple of years a go I wrote an article about monitoring the version store. With SQL Server 2017 a new DMV has been added to make this easier.
Optimistic locking requires the use of row versioning, where a copy of the data about to be modified is stored in TempDB. The advantage of this approach is that the number of locks are reduced and the opportunities for blocking are also reduced. The downside is that the resources required for data modifications are increased, …