The Read Committed Snapshot database option in SQL Server, known perhaps more accurately as Read Committed Snapshot Isolation or RCSI, can be enabled to help readers from blocking writers and writers from blocking readers. However, enabling it can cause two issues with the tempdb database which are often overlooked.
One can slow down queries, the other can cause queries to fail.
Overview of RCSI
Enabling the option changes the behaviour of the default SQL Server isolation level, read committed. By default, this isolation level means that if one session is updating a range of rows then another session cannot execute a select query against the same set of rows; the shared locks the select query needs are incompatible with the eXclusive or Update locks that the update query will be using; the session executing the select query ends up being blocked until the transaction involving the update query has committed. That’s a very high level overview of what happens so those wanting to know more or become an MCM should watch this video here.
Configuring a database to use the Read Committed Snapshot option aims to stop this happening by using Row Versioning. Essentially, before a row is locked in preparation for changes a copy of it is put in a special part of the tempdb database, known as the version store. When a select query then wants to read that row it uses the copy in the version store rather than waiting for the actual row to have its locks released. Again, I recommend watching the video linked to above to see just what happens as I’m just giving a high level overview of the feature before I discuss the primary points in this article.
One of the first things people mention when you suggest enabling the Read Committed Snapshot option is that it will put pressure on the tempdb database which stores the version store.
That’s correct it will, but a properly configured tempdb database should never be a bottleneck in your environment. You can read how super-tuning expert Thomas Kejser put the world’s biggest database workloads through SQL Server and never had to worry about tempdb performance here.
There are two considerations I recommend for configuring your tempdb database in order to ensure good performance:
- The number of tempdb data files – in the absence of your own monitoring for PFS, GAM or SGAM contention within tempdb I hear people at Microsoft these days recommending having 8 equally sized data files which I’d agree with. They don’t need to each be massive, their total size could still be 8MB(!), but having more increases the number of internal system objects within the database so reduces the changes a single system objects becoming contended. (The rule in SQL Server generally tends to be that if you have any kind of resource contention add more of that resource; whether it be IOPS, locks or PFS pages etc.). Having too many data files is much less likely to cause you problems than having too few, just be sensible about how many you have.
- The type of physical storage for the tempdb data files – as always, configure the storage you use for tempdb like you would for your database files. Fast, reliable and striped arrays, and while Thomas will tell you that using dedicated disks isn’t important for him, others will tell you its crucial. The only additional comment I have is that some people deploy NAND storage specifically for tempdb, in fact SQL Server 2012 had some changes to support this with failover clusters.
Crucially though, having a poor performing tempdb database won’t break a user’s queries, they may just run slower than they could do, especially if you’re using the read committed snapshot option.
How much space your tempdb database needs depends on how you use SQL Server, some people need very little space whiles others need lots. What’s relevant to this article is that the read committed snapshot option doesn’t just like tempdb space, it needs it.
We mentioned how rows get written to the version store in tempdb as they get locked in the source user database, but what happens if there’s no free space in tempdb when that process goes to happen? I did a test to find out, you can email me for my demo script if you want to try it for yourself.
In summary, when tempdb is full update queries will still execute successfully but select queries which expect to find the rows they need in the version store will fail when they can’t find them there. You’re likely to see this message:
“Msg 3958, Level 16, State 1, Line 1
Transaction aborted when accessing versioned row in table ‘dbo.t1’ in database ‘s1’. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning.”
So how do you stop that from happening?
The safety option I suggest is to place tempdb on a drive with lots of free space although its acceptable to configure tempdb to start with small initial data file sizes. However, make sure you configure the date file auto-grow settings so that when tempdb runs out of free space SQL Server will automatically allocate it some more. There are methodologies you can use for setting a good initial size for tempdb but for now enabling the autogrow option is a good failsafe. Then, after a couple of months see what size the data files have grown to and consider setting that to be their initial sizes.
General Recommendations, Test
Those are very general recommendations, along with setting the data file auto grow size to 500MB, but in the absence of specific testing or advice for your environment they might be something you want to try using in a test environment to help you learn more about tempdb behaviour in your environment.