During this week I have prepared a workshop about Locking & Blocking for one of my customers. One big part of this workshop was also the new Optimistic Concurrency model that SQL Server offers us since the inception of SQL Server 2005. Since SQL Server 2005 we have the READ COMMITTED SNAPSHOT ISOLATION level (RCSI) and the SNAPSHOT ISOLATION level (SI). When you are using these new isolation levels readers (SELECT statements) doesn’t acquire (S)hared Locks during their reading. Writers (UPDATE, DELETE statements) are versioning the old image of the records they are changing into the TempDb. They are creating a version chain where the actual version of the record (which is stored on a data page inside the database) points to older versions that are stored on pages in the TempDb. The following picture illustrates this concept.

To make this work, SQL Server has to add a 14 bytes long pointer to each record on the data page inside the database. This means that each record gets 14 bytes longer. As you might know, a record inside SQL Server can’t be longer than 8060 bytes when you are using fixed length data types. This means that enabling RCSI/SI could lead to records that are larger than these 8060 bytes. Let’s have a look on a very simple example.

USE master
GO

-- Create a new database
CREATE DATABASE VersionStoreRestrictions
GO

-- Enable RCSI
ALTER DATABASE VersionStoreRestrictions SET READ_COMMITTED_SNAPSHOT ON
GO

-- Use it
USE VersionStoreRestrictions
GO

-- Create a table where each record is 8047 bytes large
CREATE TABLE TableB
(
   Column1 CHAR(40),
   Column2 CHAR(8000)
)
GO

As you can see from the previous code I’m creating a table with two CHAR columns of a length of 8040 bytes. SQL Server also needs internally at least 7 bytes overhead for each record. In this case one record needs 8047 bytes on a data page. Because we have enabled RCSI on the database level, SQL Server has to add the additional 14 bytes for the Row Version Pointer, which expands each record in the table to 8061 bytes. This means that each record is 1 byte too long for SQL Server. Let’s now insert one record into the table:

-- Insert a initial row
INSERT INTO TableB VALUES (REPLICATE(‘A’, 40), REPLICATE(‘A’, 8000))
GO

When you now try to update the row (SQL Server now tries to version the old record into TempDb), the connection to the database is broken by a 208 error:

UPDATE TableB
SET Column1 = REPLICATE(‘B’, 40)
GO
 
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘TableB

The error message is not very meaningful, because the database context is wrong (SSMS shows that you are now in the master database). But when you fully qualify the table during the UPDATE statement, you are able to get back the actual error message:

UPDATE VersionStoreRestrictions.dbo.TableB
SET Column1 = REPLICATE(‘B’, 40)
GO
 
Msg 682, Level 22, State 214, Line 2
Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.

Wow, that’s an internal error, because the buffer that is used by SQL Server is only 8060 bytes large, and now we try to store in that buffer 8061 byte – Voila that’s a bug inside SQL Server! You can also reproduce this behavior on each version of SQL Server starting with SQL Server 2005 which means that this bug is almost 6 years old! Interesting enough the bug is already fixed in SQL Server Denali CTP1, where a page dump shows that SQL Server stores the expected 8061 bytes. I’ve also already filed a bug on Connect – so please feel free to vote for it!

When you are enabling RCSI/SI for existing databases, please keep this bug in mind, because it means that RCSI/SI doesn’t work in any possible scenario. When you have one table in your database that exceeds the 8046 bytes limit, then you are in real troubles! With this nasty bug you can also see how important it is to know the internals of SQL Server, and how SQL Server internally stores its data.

-Klaus