AlwaysOn Readable Secondaries: That Extra 14-bytes In Your Record You Didn’t Know About

– “Progress comes at a price.” Edwin Sarmiento-

I’ve responded to customers’ inquiries about the benefits of implementing SQL Server 2012 AlwaysOn Availability Groups since Microsoft released a public CTP of “Denali.” I’ve delivered presentations, written articles, prepared proof-of-concept designs and even recorded a video on this topic. While it may be a new feature in SQL Server 2012, the concepts are the same as in the older versions of SQL Server. In a few words, I describe AlwaysOn Availability Groups as a “database mirroring configuration sitting on top of a Windows Failover Cluster infrastructure.” Why do I say this? It’s because I want SQL Server DBAs to leverage what they already know on features like database mirroring and failover clustering and apply them when dealing with AlwaysOn Availability Groups.

With the drive to maximize IT investments in organizations, I also get asked a lot about the concept of the readable secondaries. Now, I’d be honest. I try to stay away from anything that pertains to licensing but I sure do answer any technical question that may arise during the discussion. One of this is the concept of redirecting read-only workloads on the secondary replica. Sure, this is a great idea because you can offload your reporting applications from the primary replica to the secondary replica.  You can, then, provision faster disk subsystems and more memory on the secondary replica to accommodate the read-only workload that you can now run against it. This concept is really nothing new. It’s the concept behind a mirrored database that you take a snapshot from so you can run reports against it. Because of the maintenance overhead of creating database snapshots against mirrored databases, not too many customers implement it. But those who do understand what they’re up against. They know that the tempdb database on the mirrored server will now be experiencing more load because of the versioning applied to the mirrored database when a snapshot is taken. Now, Readable Secondaries are basically that – a mirrored database that is constantly getting row versions shipped from the principal database. Since you cannot make any changes – whether schema or data –  to the mirrored database (or Readable Secondaries for AlwaysOn Availability Groups), neither can the version store in the database engine. Can you guess where those changes will be made?

Answer: The principal database (or the primary replica in the case of AlwaysOn Availability Groups.)

With that in mind, all records that are being modified in the primary database will automatically get an additional 14-bytes as a side effect of turning on Readable Secondaries. Now, you might be thinking, “14 bytes isn’t all that bad, is it?” Think again. A 100 byte row will grow by 14% once you make some modifications on it. Think about what that will do to your pages – index page splits that may cause index fragmentation or the allocation of a new data page if there isn’t enough space on the data page where the record is being stored. This also affects the amount of transaction log records that get sent to the secondary replicas and, therefore, affect the failover time which affects the overall recovery point objective and recovery time objective (RPO/RTO.)

To prove the point, let’s look at a record from a database that is configured in an AlwaysOn Availability Group. Using the undocumented DBCC commands – DBCC IND and DBCC PAGE, we can take a look at a particular record inside a database.

DBCC IND ('membership','tblCommittees',1)
GO
DBCC TRACEON (3604)
GO
DBCC PAGE (membership, 1, 203, 3)

Example of using DBCC IND with DBCC PAGE

Example of using DBCC IND with DBCC PAGE

I’m using a sample database called membership with a table named tblCommittees for this example. Using the DBCC IND command, I was able to retrieve the Page ID value for the record that I want to look at, in this case, page 203. Currently, the row containing the CommitteeName column value Membership has a length of 63 bytes. The membership database is joined in an AlwaysOn Availability Group but not configured with readable secondaries. Even if I modify the value of the CommitteeName column from Membership to Membershop, the size of the row remains the same.

DBCC_PAGE2

After turning on readable secondaries on my AlwaysOn Availability Group, I updated the same column again, reverting it back to Membership. This time, the record size has increased by an additional 14 bytes, bringing it up to 77 bytes.

DBCC_PAGE3

This is the version store kicking in, appending 14 bytes in the row to contain the row versioning information. You’ll see the output of the DBCC PAGE command displaying the version information for the updated record (the size of the column remains the same because I’ve only modified one character in the column value. )

This is just one of those things to keep in mind when implementing AlwaysOn Availability Groups readable secondaries. make sure that you properly test your environment with your existing workload, including administrative workloads such as batch processes and index maintenance.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

3 thoughts on “AlwaysOn Readable Secondaries: That Extra 14-bytes In Your Record You Didn’t Know About

  1. Nice article Edwin. Looking forward to your presentation at the Charlotte SQL Sentry User Group meeting tomorrow night!

  2. Hello Edwin,
    First of all, congratulations on one more great blog post… You really use to write good ones.

    After read this article, I have a question about when will the extra 14 bytes be added to the record.
    You told the record became 14 bigger after you´ve modified the name of the column back to Membership.

    The question is, if I enable a readble secondary to my AlwaysOn AG solution, when will the 14 bytes be actually added to the record? Will it happen instantly or only after some DDL operation?

    Once again,

    Great article…

    Edvaldo Castro
    http://edvaldocastro.com

    • Edvaldo,

      Even after enabling readable secondary on your Availability Group, the extra 14-bytes will not be added unless you read those records on the secondary. For example, if you are running DDL statements on tblEmployees but only read the tblSales on the secondary replica, the records on tblEmployees will not have the 14-extra bytes. So you need to be aware of what is being read on the secondary replica and what the workload is on the primary replica that will cause the extra 14-bytes added to the rows. Keep in mind that because your secondary replicas cannot be changed, that extra 14-bytes will be added on the primary replica and will have to be sent to the secondary as part of the Availability Group replication. That extra 14-bytes will also be included in your backups, read in your replication, cause index fragmentation, etc.