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
