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.
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;
WHERE name = 'pubs';
Now rebuild the table and find out the new page number(s).
ALTER TABLE SIauthors REBUILD;
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!