THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Growing Rows with Snapshot Isolation

I just finished a wonderful week in Stockholm, teaching a class for Cornerstone Education. We had 19 SQL Server enthusiasts, all eager to find out everything they could about SQL Server Internals. One questions came up on Thursday that I wasn’t sure of the answer to. I jokingly told the student who asked it to consider it a homework exercise, but then I was so interested in the answer, I try to figure it out myself Thursday evening.  In this post, I’ll tell you what I did to try to answer the question.

I am writing this on an airplane, flying from Frankfurt to San Francisco.

When a database is enabled for either ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT, SQL Server adds 14 bytes of overhead to each row. These 14 bytes contain the XSN (transaction sequence number) at the time the row was modified, and a pointer to the previous ‘version of the row (if there is one) which is stored in a part of the tempdb database called the version store. In class, we were discussing the fact that rows involved in row versioning have the 14 bytes added the first time the row is modified after one of the snapshot isolation levels is enabled.  If your pages are very full, sometimes this can mean that adding the 14 extra bytes will cause one or more of the affected rows to not fit on the original page, so the page may need to be split.

The student asked if could could avoid the problem of adding the extra 14 bytes on the fly for each row if we rebuilt the table right after enabling one of the snapshot isolation levels. In another words, did rebuilding count as modifying the rows?

I tested it by creating a copy of one of the small tables in the pubs sample database that you can download from Microsoft, here.  You, of course, could run a similar test of your own with any table.  I first made sure that both snapshot isolation level settings were off. No one can be in the database when you set read READ_COMMITTEE_SNAPSHOT on or off, and it’s best to make sure there are no current transactions when you set ALLOW_SNAPSHOT_ISOLATION on or off.

ALTER DATABASE pubs SET read_committed_snapshot OFF;
ALTER DATABASE pubs SET allow_snapshot_isolation OFF;

Now make a copy of the authors table.

USE pubs;
IF exists (SELECT * FROM sys.tables WHERE name = 'SIauthors')
            DROP TABLE SIauthors;
SELECT * INTO SIauthors from authors;

Now I want to look at the new authors table to see what an actual row looks like on the data page. DBCC IND will show me which pages belong to the table.

DBCC IND (pubs, SIauthors, -1);

Sometimes I get two rows for data pages (PageType = 1) back, but only one of them has data.


DBCC PAGE, with traceflag 3604 on, will show me the rows on the data page.

DBCC PAGE(pubs, 1,245, 1)

Before enabling snapshot isolation, the rows end with the the bytes for the name of the city. In the output below, for the first row on my page, the city is MENLO PARK. 


So let’s look at how things change if we enable one of the snapshot isolation levels and rebuild the table.

-- Make sure no other connections are in the pubs database before running this ALTER. (The connection where you run the command can be in the database.  After changing the database, verify the setting.

ALTER DATABASE pubs SET read_committed_snapshot ON;

SELECT is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'pubs';

Now rebuild the table and find out the new page number(s).


DBCC IND (pubs, SIauthors, -1);

Because the table has been rebuilt, there will be new page numbers. Use DBCC PAGE to look at the new page, and you’ll see the 14 extra bytes at the end of the rows.


So I concluded that rebuilding the table would add the extra 14 bytes to each row and that’s what I told the class on Friday. However, as I started preparing to write this post, I decided to do a few more tests, one of which was building a clustered index on the table and then rebuilding the clustered index instead of the table itself. (Note that rebuilding a table is a new feature in SQL Server 2008.) However, you should know that the clustered index includes the table, so we’re still rebuilding the table when we rebuild the clustered index. 

I found that an ALTER INDEX REBUILD, although it moved the table to new pages, did not add the 14 bytes.  Performing an ALTER TABLE REBUILD, if the table had a clustered index, also did not add the extra bytes. It only seemed to happen when the table is a heap.

Obviously, more research is required. But I’ll leave that as a homework exercise for you!

Have fun!


Published Saturday, November 19, 2011 5:43 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Adithya said:

Interesting. Thanks for the post Kalen.

December 3, 2011 1:19 PM

king said:



January 16, 2018 11:34 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:51 PM

chenyingying said:


May 6, 2018 11:08 PM

linying123 said:


May 10, 2018 8:11 PM

shenyuhang said:


June 1, 2018 7:06 PM

dongdong8 said:



June 29, 2018 2:51 AM

linying123 said:



July 16, 2018 8:35 PM

dongdong8 said:



July 23, 2018 11:32 PM

shenyuhang said:

July 23, 2018 11:41 PM

obat diabetes said:

August 1, 2018 11:20 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:50 PM

obat sinusitis said:

August 10, 2018 7:23 PM

yaoxuemei said:


August 15, 2018 2:12 AM

qqq said:

August 16, 2018 12:19 AM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 9:00 PM

chenjinyan said:



August 22, 2018 11:10 PM

shenyuhang said:


August 23, 2018 10:22 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:59 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:22 AM

obat benjolan di tubuh said:

August 30, 2018 7:41 PM

obat wasir said:

September 4, 2018 8:28 PM

chenlixiang said:



September 18, 2018 10:00 PM

obat limfoma said:


September 23, 2018 6:51 PM

xiaojun said:

20180928 junda

September 27, 2018 10:58 PM

kakakaoo said:

October 8, 2018 2:24 AM

chenjinyan said:


October 9, 2018 6:55 PM

chenjinyan said:


October 9, 2018 6:55 PM

chenqiuying said:


October 10, 2018 6:28 PM

kakakaoo said:


November 8, 2018 2:07 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement