yb751 (4/16/2015)

---

I'd just change the Transaction Isolation Level.

Stairway to HA Level 6

---

Impact of Row Versioning

When you enable a database as a readable Secondary in an AlwaysOn Availability group, row versioning is automatically implemented and applies a 14 byte overhead on each row that is modified. In fact all isolation levels are transparently mapped to the snapshot isolation level to avoid redo thread blocking. Without this, report workloads could possibly interfere with the redo thread process.

The addition of the row version data on the Primary depends on the snapshot isolation or read-committed snapshot isolation (RCSI) level setting on the primary database. If row versioning is also explicitly implemented on the Primary database in the group there will be an overhead on the Primary replica too.

This row overhead can be viewed by querying the "max_record_size_in_bytes" column in the DMV "sys.dm_db_index_physical_stats". The table below describes the behaviour of versioning on a readable secondary database under different settings for disk based tables.

Readable Secondary? Snapshot isolation or RCSI Level Enabled? Primary Database Secondary Database

No No No row versions or 14-byte overhead No row versions or 14-byte overhead

No Yes Row versions and 14-byte overhead No row versions or 14-byte overhead

Yes No No row versions or 14-byte overhead Row versions and 14-byte overhead

Yes Yes Row versions and 14-byte overhead Row versions and 14-byte overhead

From the Books Online link: http://msdn.microsoft.com/en-GB/library/ff878253.aspx