{"id":635,"date":"2024-07-09T08:00:00","date_gmt":"2024-07-09T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=635"},"modified":"2024-07-06T17:40:16","modified_gmt":"2024-07-06T22:40:16","slug":"understanding-row-versioning-sql-server-always-on-availability-groups","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=635","title":{"rendered":"SQL Server AG Readonly Secondaries and their Associated Overhead"},"content":{"rendered":"

Introduction<\/h2>\n

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.<\/p>\n

Key Concepts<\/h3>\n

Row Versioning:<\/strong><\/p>\n

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.<\/p>\n

14-Byte Overhead:<\/strong><\/p>\n

Each modified, inserted, or deleted row incurs an additional 14 bytes of storage to accommodate versioning information.<\/p>\n

Read-Access Configuration:<\/strong><\/p>\n

Configuring read-access on secondary replicas offloads read operations from the primary replica, which can improve performance and availability.<\/p>\n

Considerations<\/h3>\n

Storage Impact:<\/strong><\/p>\n

The 14-byte overhead per row can accumulate, especially in systems with high transaction volumes. Monitoring storage usage and planning accordingly is crucial.<\/p>\n

Performance:<\/strong><\/p>\n

While offloading read operations to secondary replicas can enhance performance, the additional overhead on the primary replica should be factored into performance tuning.<\/p>\n

Maintenance:<\/strong><\/p>\n

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.<\/p>\n

How Row Versioning Works<\/h3>\n

Transaction Isolation:<\/strong><\/p>\n

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.<\/p>\n

Version Store in TempDB:<\/strong><\/p>\n

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.<\/p>\n

Tracking Changes:<\/strong><\/p>\n

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.<\/p>\n

Read Operations on Secondary Replicas:<\/strong><\/p>\n

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.<\/p>\n

Detailed Behavior Based on Settings<\/h3>\n

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:<\/p>\n\n\n\n\n\n\n\n\n
Readable Secondary Replica?<\/th>\nSnapshot Isolation or RCSI Level Enabled?<\/th>\nPrimary Database<\/th>\nSecondary Database<\/th>\n<\/tr>\n<\/thead>\n
No<\/td>\nNo<\/td>\nNo row versions or 14-byte overhead<\/td>\nNo row versions or 14-byte overhead<\/td>\n<\/tr>\n
No<\/td>\nYes<\/td>\nRow versions and 14-byte overhead<\/td>\nNo row versions, but 14-byte overhead<\/td>\n<\/tr>\n
Yes<\/td>\nNo<\/td>\nNo row versions, but 14-byte overhead<\/td>\nRow versions and 14-byte overhead<\/td>\n<\/tr>\n
Yes<\/td>\nYes<\/td>\nRow versions and 14-byte overhead<\/td>\nRow versions and 14-byte overhead<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Conclusion<\/h3>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[36,12,5],"tags":[191,38,194,193,192,131],"class_list":["post-635","post","type-post","status-publish","format-standard","hentry","category-availability-groups","category-internals","category-performance","tag-always-on-availability-groups","tag-availability-groups","tag-data-consistency","tag-read-only-access","tag-row-versioning","tag-sql-server"],"yoast_head":"\nSQL Server AG Readonly Secondaries and their Associated Overhead<\/title>\n<meta name=\"description\" content=\"Learn about the 14-byte overhead and row versioning in SQL Server Always On Availability Groups. Understand how it impacts primary and secondary replicas, storage, and performance, ensuring data consistency and scalability.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqltabletalk.com\/?p=635\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server AG Readonly Secondaries and their Associated Overhead\" \/>\n<meta property=\"og:description\" content=\"Learn about the 14-byte overhead and row versioning in SQL Server Always On Availability Groups. Understand how it impacts primary and secondary replicas, storage, and performance, ensuring data consistency and scalability.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=635\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2024-07-09T13:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Yvonne Vanslageren\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Yvonne Vanslageren\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=635#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=635\"},\"author\":{\"name\":\"Yvonne Vanslageren\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082\"},\"headline\":\"SQL Server AG Readonly Secondaries and their Associated Overhead\",\"datePublished\":\"2024-07-09T13:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=635\"},\"wordCount\":612,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"Always On Availability Groups\",\"availability groups\",\"Data Consistency\",\"Read-Only Access\",\"Row Versioning\",\"SQL Server\"],\"articleSection\":[\"Availability Groups\",\"Internals\",\"Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=635#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=635\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=635\",\"name\":\"SQL Server AG Readonly Secondaries and their Associated Overhead\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2024-07-09T13:00:00+00:00\",\"description\":\"Learn about the 14-byte overhead and row versioning in SQL Server Always On Availability Groups. Understand how it impacts primary and secondary replicas, storage, and performance, ensuring data consistency and scalability.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=635#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=635\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=635#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server AG Readonly Secondaries and their Associated Overhead\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082\",\"name\":\"Yvonne Vanslageren\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g\",\"caption\":\"Yvonne Vanslageren\"},\"url\":\"https:\/\/www.sqltabletalk.com\/?author=2\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server AG Readonly Secondaries and their Associated Overhead","description":"Learn about the 14-byte overhead and row versioning in SQL Server Always On Availability Groups. Understand how it impacts primary and secondary replicas, storage, and performance, ensuring data consistency and scalability.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqltabletalk.com\/?p=635","og_locale":"en_US","og_type":"article","og_title":"SQL Server AG Readonly Secondaries and their Associated Overhead","og_description":"Learn about the 14-byte overhead and row versioning in SQL Server Always On Availability Groups. Understand how it impacts primary and secondary replicas, storage, and performance, ensuring data consistency and scalability.","og_url":"https:\/\/www.sqltabletalk.com\/?p=635","og_site_name":"SQL Table Talk","article_published_time":"2024-07-09T13:00:00+00:00","author":"Yvonne Vanslageren","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Yvonne Vanslageren","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=635#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=635"},"author":{"name":"Yvonne Vanslageren","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082"},"headline":"SQL Server AG Readonly Secondaries and their Associated Overhead","datePublished":"2024-07-09T13:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=635"},"wordCount":612,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["Always On Availability Groups","availability groups","Data Consistency","Read-Only Access","Row Versioning","SQL Server"],"articleSection":["Availability Groups","Internals","Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=635#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=635","url":"https:\/\/www.sqltabletalk.com\/?p=635","name":"SQL Server AG Readonly Secondaries and their Associated Overhead","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2024-07-09T13:00:00+00:00","description":"Learn about the 14-byte overhead and row versioning in SQL Server Always On Availability Groups. Understand how it impacts primary and secondary replicas, storage, and performance, ensuring data consistency and scalability.","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=635#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=635"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=635#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server AG Readonly Secondaries and their Associated Overhead"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"]},{"@type":"Person","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082","name":"Yvonne Vanslageren","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g","caption":"Yvonne Vanslageren"},"url":"https:\/\/www.sqltabletalk.com\/?author=2"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/635","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=635"}],"version-history":[{"count":3,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/635\/revisions"}],"predecessor-version":[{"id":660,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/635\/revisions\/660"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=635"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=635"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=635"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}