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.