SQL Server with Mr. Denny

May 28 2013   4:27PM GMT

Extra Bytes Per Row With AlwaysOn Availability Groups

Denny Cherry Denny Cherry Profile: Denny Cherry

One of things to keep in mind with SQL Server 2012 AlwaysOn Availability Groups is that when the availability group has readable secondary replicas any rows that are changed will have an additional 14 bytes added to each row.  These 14 bytes are used by the readable secondary to handle the read committed snapshot isolation level (RCSI) so that the readable secondary replicas work correctly.

To look at what’s going on lets create a sample new table in a table and take a peak at the data.  To setup this test we create a new database and setup that database for use with AlwaysOn Availability Groups.  The availability group is setup with no readable secondary replicas.

create table MyTest (c1 int identity(1,1),
c2 int,
c3 varchar(100))
GO
insert into MyTest
(c2, c3)
values
(1, ‘test’)
GO 400

This creates a new table in the database with 400 rows in it.  Looking at the output from DBCC IND we can see that this table takes up 2 data pages with a root page (this will become important later on).

Looking at the data for the first data page we can see the following information.

Slot 0 Offset 0×60 Length 23

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x000000003CACA060

0000000000000000:   30000c00 01000000 01000000 03000001 00170074  0………………t
0000000000000014:   657374                                        est

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 1

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

When we update this row changing the value of c2 to equal 2 nothing really changes which we can see from DBCC PAGE again.

Slot 0 Offset 0×60 Length 23

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x000000003EECA060

0000000000000000:   30000c00 01000000 02000000 03000001 00170074  0………………t
0000000000000014:   657374                                        est

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 2

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Next I’ve changed the settings for the availability group to support readable secondary replicas.  Once that change has been made we change the value of c2 for the same row to equal the value of 3.  Again we can look at this with DBCC PAGE.

Slot 0 Offset 0x1d4e Length 37

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 37
Memory Dump @0x000000003AEEBD4E

0000000000000000:   70000c00 01000000 03000000 03000001 00170074  p………………t
0000000000000014:   65737400 00000000 0000000f 06000000 00        est…………..

Version Information =
Transaction Timestamp: 1551
Version Pointer: Null

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 3

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Looking at these two outputs from DBCC PAGE we can see a couple of differences.  First we see an additional value in the “Record Attributes” field which adds in VERSIONING_INFO to the value.  We also see that the record size has changed from 32 to 37.  Additionally we see that the Version Information has been added.

Looking at the DBCC PAGE output on one of the replicas for the same page as before we see some different information.

Slot 0 Offset 0x1d4e Length 37

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 37
Memory Dump @0x000000003893BD4E

0000000000000000:   70000c00 01000000 03000000 03000001 00170074  p………………t
0000000000000014:   65737440 01000001 0000000f 06000000 00        est@………….

Version Information =
Transaction Timestamp: 1551
Version Pointer: (file 1 page 320 currentSlotId 0)

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 3

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Specifically at this point we see that the Version Pointer now has a value in it. This tells us that the SQL Server has put a copy of the original page into the tempdb database so that we can read it.

A question comes up as to what happens to the page when this row information is added.  Specifically does the page split because of this additional 14 bytes of new data per row.  The answer to this question is “it depends”.  In my testing that I did when I updated a single row the page didn’t split, mostly this would be because there was some free space in the database page.  When I updated rows 2-40 and looked at DBCC IND I saw that in fact the page had split.

Looking at the values within the page there are now 159 rows in the page which was the original database page when there were 322 rows within the database page.  The remainder of the rows were copied into a new database page.

Now that we’ve identified that SQL Server is going to be page splitting older database pages, potentially like crazy what can we do about it?  The answer to that question is to just deal with it and to decrease the fill factor as needed so that page splits happen as little as possible.

To make matters worse when we rebuild the index on the table and look at the output from DBCC PAGE again we can see that the additional flag has been removed from row 1 (seen below).  This tells us that no only will this problem come up the first time that data is modified, it’ll come up every time that index rebuilds are done when the data is changed for the first time after the rebuild.

Slot 0 Offset 0×60 Length 23

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x000000003E0CA060

0000000000000000:   30000c00 01000000 03000000 03000001 00170074  0………………t
0000000000000014:   657374                                        est

Slot 0 Column 1 Offset 0×4 Length 4 Length (physical) 4

c1 = 1

Slot 0 Column 2 Offset 0×8 Length 4 Length (physical) 4

c2 = 3

Slot 0 Column 3 Offset 0×13 Length 4 Length (physical) 4

c3 = test

Slot 0 Offset 0×0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

Changing the data again, this time changing the first 40 rows (id values 1-40) the new flag comes into place as expected.  If we reorganize the index instead of doing a rebuild this time the flags are left in place.

This tells us that the better option for doing index maintenance on databases which are being protected by AlwaysOn Availability Groups is going to be to use reorganize commands instead of rebuild commands.  This way the 14 byte pointer isn’t removed from the rows so that when they are modified the additional 14 bytes of data doesn’t need to be added.

If you’ve got rows which are changed all the time then this will be a way to handle it.  If the rows never change after the data is reorged then it may or may not be something worth worrying about.

Hopefully this helps answer the questions of what these extra bytes are for and how we can deal with them.

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: