{"id":4794,"date":"2017-08-16T08:11:12","date_gmt":"2017-08-16T15:11:12","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4794"},"modified":"2025-03-08T14:46:40","modified_gmt":"2025-03-08T22:46:40","slug":"sqlskills-sql101-readable-secondary-performance-problems","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-readable-secondary-performance-problems\/","title":{"rendered":"SQLskills SQL101: Readable secondary performance problems"},"content":{"rendered":"

As\u00a0Kimberly blogged about earlier this year<\/a>, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n

Yesterday I blogged about log shipping performance issues and mentioned a performance problem that can be caused by using availability group readable secondaries, and then realized I hadn’t blogged about the problem, only described it in our Insider newsletter<\/a>. So here’s a post about it!<\/p>\n

Availability groups (AGs) are pretty cool, and one of the most useful features of them is the\u00a0ability to read directly from one of the secondary replicas. Before, with database mirroring, the\u00a0only way to access the mirror database was through the creation of a database snapshot, which\u00a0only gave a single, static view of the data. Readable secondaries are constantly updated from the\u00a0primary so are far more versatile as a reporting or non-production querying platform.<\/p>\n

But I bet you didn\u2019t know that using this feature can cause performance problems on your\u00a0primary replica?<\/p>\n

As with most things in life, you don\u2019t get anything for free. Readable secondaries are really useful, but there is a performance trade off you need to be aware of. All queries that are executed against a readable secondary are automatically run using snapshot isolation. This means they do not require share locks and so will not block any database changes being replayed from the primary replica (i.e. the constant redo of log records on the secondary replica that have been sent from the primary replica).<\/p>\n

To do this requires the use of the versioning system, where (simplistically) pre-change versions\u00a0of records are copied into the version store in tempdb and queries work out which version of the\u00a0record is the correct one for them to process, based on the query\u2019s starting time. All records that\u00a0change get a 14-byte tag added on the end of the record that allows a query to see if this is the\u00a0correct record, and if not to follow a pointer to the previous version of the record in the version\u00a0store. This has been the mechanism since snapshot isolation and read-committed snapshot\u00a0isolation were introduced in SQL Server 2005.<\/p>\n

Now consider this: all AG replicas are exact copies of the primary replica. So how can\u00a0versioning work on the readable secondary, adding 14-byte tags to some records? That must\u00a0break the \u2018exact copy\u2019 rule, right?<\/p>\n

Well, yes, it would\u2026 if the primary replica didn\u2019t also change.<\/p>\n

When a readable secondary is configured in an AG environment, all changing records on the\u00a0primary replica start getting empty 14-byte versioning tags added to them. This is so that the 14-bytes of extra space on the record is noted in the transaction log and replayed on the secondary\u00a0replicas, allowing the readable secondary to make use of the empty 14-byte space to store the\u00a0versioning tag it needs.<\/p>\n

This doesn\u2019t break the \u2018exact copy\u2019 rule because the 14-bytes isn\u2019t used for anything to do with\u00a0recovery, there just has to be 14-bytes there.<\/p>\n

So versioning tags start getting added to changing records on the primary (to be clear, it doesn\u2019t\u00a0turn on versioning on the primary) so table and index records start to get 14-bytes longer. And\u00a0what happens when records get longer on pages where there isn\u2019t enough space? Page splits in\u00a0your indexes (and forwarded records in heaps \u2013 but I\u2019ll concentrate on indexes here) leading to\u00a0low page densities (wasted disk space and buffer pool memory), logical fragmentation (poor scan performance), and a bunch of extra, expensive log record generation from the page\u00a0splits themselves.<\/p>\n

To counteract this, you\u2019ll need to implement (and\/or possibly lower existing) fill factors on your indexes and even potentially start doing index maintenance on indexes that may not have\u00a0required it previously. Quite an insidious problem that can be hard to figure out unless you know\u00a0what\u2019s going on under the covers!<\/p>\n

See the following blog posts for more info:<\/p>\n