Maybe you’ve read about the Read Committed Snapshot (RCSI) or Snapshot Isolation levels. Or maybe you were trying to figure out the difference between the two snapshot isolation levels. Perhaps you’ve looked into read-only secondary replicas in your Availability Group, or even had some problems with AG secondaries. Along the way, people and articles have mentioned this “version store” thing.
What the heck is the version store?
If I don’t know what something is, I’m going to Google it. Turns out, the results for “version store” were not what I expected:
Yikes. That’s not a great start. Let’s see if I can help. Let’s talk about what it is, what it does, what it is, what it isn’t.
What it is
When using an optimistic locking isolation level, SQL Server introduces row versions, kept in the version store which allow read queries to not block or be blocked. When a row is updated or deleted, the “stale” version gets stored in the version store. The version store isn’t part of a database itself, but rather is in tempdb. This means that every database on the instance shares a single version store. This also means that all your snapshot-enabled databases will need to write to the tempdb version store–a new shared bottleneck for your separate databases. The overhead of this isn’t huge, but it is extra I/O on tempdb.
What it does
Before we talk about how the version store works, there’s one other ingredient we’ll need: the row timestamp/version itself. I already mentioned that the “stale” version of the row goes into the version store, but how do we tell which version is which? If I update a row twice, and send two stale versions into the version store, I need to know which row is newer. SQL Server does this by storing a 14-byte timestamp as the version identifier on the row. This timestamp tells SQL Server when the row was written. This completely solves the “Which version is newer? Which version do I want?” problem.
Now that we have the version timestamp on the row, every time we update the row, SQL Server quietly copies the old version of the row into the version store. Every update in the database generates an entry in the version store. Now, when you query under RCSI or Snapshot isolation, SQL Server can notice “whoops, the current version is too new! I better read an older one from the version store!” then it can go read the right one from the version store. (For a more accurate & detailed description of how this works, check out the series from Paul White (blog|twitter) that I referenced earlier.)
But wait, if all these updates are writing to the version store, isn’t tempdb going to be huge? In order to prevent you from needing a 500 Petabyte tempdb, SQL Server will clean up row versions that it doesn’t need. SQL Server will keep track of the oldest open transaction or query, and clean up anything in the version store older than that point in time using a background process. This means that a long running transaction could both grow your transaction log and grow your tempdb. It doesn’t even have to be a transaction. It could just be a very long running SELECT statement that will prevent the version store from being cleaned up, as even read-only queries (ie without a transaction) still need to access the version store to ensure consistent reads.
What it isn’t
It’s not magical, that’s for sure. It’s also not guaranteed to be fast. When the version store gets really, _ really _ large, performance can take a hit. I’ve recently witnessed a server have its version store grow to 200+GB, and at the same time watched read queries that use that version store slow to a crawl. That one read-only analytical query running in the background (using RCSI… readers don’t cause blocking!), caused the version store growth to impact everything else running on the server. It wasn’t causing blocking, but the overhead of reading the massive version store still caused impact.
The post What the heck is the SQL Server Version Store? appeared first on Andy M Mallon - AM².
Top comments (0)