Inside the Storage Engine: When do versioning tags get added?


Ok – so we did more partying than we thought so blog posts have been a little sparse this month, but here’s one to end off the year.


There’s a popular impression that turning on snapshot isolation and then rebuilding indexes will cause all rows in the table to get the extra 14-byte versioning tags. Fact or fiction? Let’s find out.


First I’ll create a test database, containing a small table with a clustered index and few rows:



CREATE DATABASE SItest;
GO


USE SItest;
GO


CREATE TABLE SmallTable (c1 INT, c2 INT);
CREATE CLUSTERED INDEX SmallTableCI ON SmallTable (c1);
GO


INSERT INTO SmallTable VALUES (1, 1);
INSERT INTO SmallTable VALUES (2,2);
GO


Next I’ll turn on READ_COMMITTED_SNAPSHOT and rebuild the index to see if statement level versioning does the trick:



ALTER DATABASE SItest SET READ_COMMITTED_SNAPSHOT ON;
GO


ALTER INDEX SmallTableCI ON SmallTable REBUILD;
GO


Now let’s look at the data page holding the two rows to see if there’s any versioning info (the output is snipped short a little for brevity):



DBCC IND (SItest, SmallTable, 1);
GO


DBCC TRACEON (3604); — remember this makes the output go to the console
DBCC PAGE (SItest, 1, 153, 3);
GO


PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
——- ———– —— ———– ———– ———–
1       154         NULL   NULL        2073058421  1
1       153         1      154         2073058421  1


(2 row(s) affected)


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


PAGE: (1:153)


<SNIP SNIP SNIP>


Slot 0 Offset 0x60 Length 15


Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C060


00000000:   10000c00 01000000 01000000 0300f9 ……………
UNIQUIFIER = [NULL]


Slot 0 Column 1 Offset 0x4 Length 4


c1 = 1


Slot 0 Column 2 Offset 0x8 Length 4


c2 = 1


Slot 1 Offset 0x6f Length 15


Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C06F


00000000:   10000c00 02000000 02000000 0300f9 ……………
UNIQUIFIER = [NULL]


Slot 1 Column 1 Offset 0x4 Length 4


c1 = 2


Slot 1 Column 2 Offset 0x8 Length 4


c2 = 2


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Nope – both the rows look normal. Now for completeness let’s try transaction level versioning and a rebuild:



ALTER DATABASE SItest SET ALLOW_SNAPSHOT_ISOLATION ON;
GO


ALTER INDEX SmallTableCI ON SmallTable REBUILD;
GO


DBCC IND (SItest, SmallTable, 1);
GO


DBCC PAGE (SItest, 1, 143, 3); — page changed when we rebuilt the index
GO


PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
——- ———– —— ———– ———– ———–
1       152         NULL   NULL        2073058421  1
1       143         1      152         2073058421  1


(2 row(s) affected)


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


PAGE: (1:143)


<SNIP SNIP SNIP>


Slot 0 Offset 0x60 Length 15


Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C060


00000000:   10000c00 01000000 01000000 0300f9 ……………
UNIQUIFIER = [NULL]


Slot 0 Column 1 Offset 0x4 Length 4


c1 = 1


Slot 0 Column 2 Offset 0x8 Length 4


c2 = 1


Slot 1 Offset 0x6f Length 15


Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C06F


00000000:   10000c00 02000000 02000000 0300f9 ……………
UNIQUIFIER = [NULL]


Slot 1 Column 1 Offset 0x4 Length 4


c1 = 2


Slot 1 Column 2 Offset 0x8 Length 4


c2 = 2


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Nope – still nothing. Now I’ll force a versioning operation and we should see the tags. Any update to the table should cause versioned records to be created. In this case, I’ll start an explicit transaction and do some updates and we should be able to see the original values using another query window. First the updates:



BEGIN TRAN;
GO


UPDATE SmallTable SET c1 = 4;
GO


And in another window:



SELECT * FROM smalltable;
GO


c1          c2
———– ———–
1           1
2           2


(2 row(s) affected)


Cool – so the original values are still there. Let’s see the versioning info on the data page (output snipped again):



DBCC PAGE (SItest, 1, 143, 3);
GO


PAGE: (1:143)


<SNIP SNIP SNIP>


Slot 0 Offset 0x7e Length 29


Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO


Memory Dump @0x61CEC07E


00000000:   5c000c00 01000000 01000000 0300f9e0 \……………
00000010:   00000001 00000057 03000000 00       …….W…..


Version Infomation =
 Transaction Timestamp: 855
 Version Pointer: (file 1 page 224 slot 0)


UNIQUIFIER = [NULL]


Slot 0 Column 1 Offset 0x4 Length 4


c1 = 1


Slot 0 Column 2 Offset 0x8 Length 4


c2 = 1


Slot 1 Offset 0xb8 Length 29


Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO


Memory Dump @0x61CEC0B8


00000000:   5c000c00 02000000 02000000 0300f9e0 \……………
00000010:   00000001 00010057 03000000 00       …….W…..


Version Infomation =
 Transaction Timestamp: 855
 Version Pointer: (file 1 page 224 slot 1)


UNIQUIFIER = [NULL]


Slot 1 Column 1 Offset 0x4 Length 4


c1 = 2


Slot 1 Column 2 Offset 0x8 Length 4


c2 = 2


Slot 2 Offset 0x9b Length 29


Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFO


Memory Dump @0x61CEC09B


00000000:   50000c00 04000000 01000000 0300f800 P……………
00000010:   00000000 00000057 03000000 00       …….W…..


Version Information =
 Transaction Timestamp: 855
 Version Pointer: Null


UNIQUIFIER = [NULL]


Slot 2 Column 1 Offset 0x4 Length 4


c1 = 4


Slot 2 Column 2 Offset 0x8 Length 4


c2 = 1


Slot 3 Offset 0xd5 Length 37


Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO


Memory Dump @0x61CEC0D5


00000000:   70000c00 04000000 02000000 0300f801 p……………
00000010:   00170001 00000000 00000000 00000057 ……………W
00000020:   03000000 00                         …..


Version Information =
 Transaction Timestamp: 855
 Version Pointer: Null



Slot 3 Column 0 Offset 0x13 Length 4


UNIQUIFIER = 1


Slot 3 Column 1 Offset 0x4 Length 4


c1 = 4


Slot 3 Column 2 Offset 0x8 Length 4


c2 = 2


DBCC execution completed. If DBCC printed error messages, contact your system administrator.


I’ve marked the versioning parts in bold. Notice that the old records have been turned into ghost records too. The second record is now tagged as having variable-length columns too. This is because I updated both records to have the same clustering key value and so the second record now needs a uniquifier – which is stored as a variable-length column.


So, the original statement is a myth – the only time that rows get versioning info added to them is when it’s needed to support a versioning operation.

4 thoughts on “Inside the Storage Engine: When do versioning tags get added?

  1. If I rebuild index offline in another session, instead of select statement, rebuild index will get blocked by update statement.

    In Online index rebuild, online index final will have version records these version records.

    Please is it correct

    1. If I understand what you’re asking, an offline index rebuild will be blocked by any operation, and will block anything else, as it requires a Sch-M lock, which is not compatible with any other lock. If an update happens at the same time as an online index rebuild, the new index will have the updated value in it.

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.