RCSI/SI doesn’t work with rows larger than 8046 bytes

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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.

image

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.

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:

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:

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:

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

2 Comments

SQLpassion

Copyright © 2017 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Go to Top