How does the read committed snapshot isolation level relate (link) the row in tempdb to the original row?

by

The question:

I understand that the read committed snapshot isolation level holds the row versions in the tempdb.

I enabled this feature on a test database and I was expecting to see a new column (guid) get automatically added to the source data table (which I thought SQL will use to link to the record in tempdb). But there is no such column I can visibly see.

How does the read committed snapshot isolation level relate (link) the row in tempdb to the original row?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

SQL Server adds this 14 byte overhead as internal information. You need to use special command to see the overhead, as noted by MBuschi.

Note that the overhead is not added until you modify a row, after you turned on RCSI.

These 14 bytes is a combo of two pieces of information:

  1. XSN (Transaction Sequence Number), which identifies the transaction that performed the modification. This is 6 bytes.
  2. RID, a pointer to the versioned row. This is 8 bytes.

Documentation

Method 2

You can’t see the metadata added to each row as new column. After all, if it had worked as you wrote, it would have broken anything (views, procedures, apps).

You can see the row size difference using

DBCC TRACEON(3604)
DBCC PAGE('mytable',PageNo, PageType, 3)

You can check record size in the output before and after changing isolation level.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment