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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply