Introduction
In SQL Server Always On Availability Groups, configuring read-access for one or more secondary replicas introduces additional overhead to the primary databases. This overhead, specifically a 14-byte addition to modified, inserted, or deleted data rows, is essential for the row versioning feature that ensures data consistency across replicas. Row versioning allows secondary replicas to perform read operations without being affected by ongoing changes on the primary replica, ensuring a consistent view of the data. Understanding this overhead and its impact on storage and performance is crucial for optimizing SQL Server environments.
Key Concepts
Row Versioning:
SQL Server uses row versioning to keep track of changes to data rows. This allows secondary replicas to read consistent data without being affected by ongoing changes on the primary replica.
14-Byte Overhead:
Each modified, inserted, or deleted row incurs an additional 14 bytes of storage to accommodate versioning information.
Read-Access Configuration:
Configuring read-access on secondary replicas offloads read operations from the primary replica, which can improve performance and availability.
Considerations
Storage Impact:
The 14-byte overhead per row can accumulate, especially in systems with high transaction volumes. Monitoring storage usage and planning accordingly is crucial.
Performance:
While offloading read operations to secondary replicas can enhance performance, the additional overhead on the primary replica should be factored into performance tuning.
Maintenance:
Regular maintenance tasks such as index maintenance and statistics updates should be adapted to account for the additional overhead and the presence of read-access secondary replicas.
How Row Versioning Works
Transaction Isolation:
SQL Server maintains snapshot isolation levels for read-access on secondary replicas using row versioning. This ensures that readers see a consistent view of the data, unaffected by ongoing transactions on the primary replica.
Version Store in TempDB:
When a row is modified on the primary replica, SQL Server creates a version of the row and stores it in the version store located in the tempdb database. This versioned row includes the original data along with a 14-byte overhead containing metadata such as the transaction sequence number.
Tracking Changes:
Each row modification on the primary replica results in a new version being created. This allows SQL Server to maintain a history of changes, enabling secondary replicas to access a consistent snapshot of the data at the time the read operation started.
Read Operations on Secondary Replicas:
Secondary replicas can be configured to allow read-only access. When a read query is executed on a secondary replica, SQL Server uses the versioned rows to provide a consistent view of the data as it existed at the start of the read transaction. This read consistency is maintained by using the row versions stored in tempdb to resolve conflicts caused by ongoing changes on the primary replica.
Detailed Behavior Based on Settings
The addition of the 14-byte overhead depends on the snapshot isolation or read-committed snapshot isolation (RCSI) level setting on the primary database. The table below outlines the behavior of versioning on a readable secondary database under different settings for disk-based tables:
| Readable Secondary Replica? | 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, but 14-byte overhead |
| Yes | No | No row versions, but 14-byte overhead | Row versions and 14-byte overhead |
| Yes | Yes | Row versions and 14-byte overhead | Row versions and 14-byte overhead |
Conclusion
By understanding and managing these aspects, SQL Server Always On Availability Groups can be effectively leveraged for high availability and read scalability. Proper configuration and regular maintenance ensure that both performance and data consistency are maintained across the primary and secondary replicas.