Accelerated Database Recovery: How Big Is My Version Store?

Size Matters

When Query Store rolled out, there were a lot of questions about controlling the size, and placement of it.

To date, there’s still not a way to change where Query Store data ends up, but you can manage the size pretty well.

Of course, there’s a lot more going on there — query text and plan XML heavily inflate the size of things — so there’s naturally more concern.

What About Version Stores?

When talking about traditional row versioning in SQL Server, via Read Committed Snapshot Isolation, or Snapshot Isolation, there’s always a warning to keep an eye on the size of your version store.

Rightfully so, too. They put their data in tempdb, not locally the way the Persistent Version Store does. That means tempdb size can quickly get out of hand with multiple databases storing their Version Store data in there.

Storing the data locally, there’s far less chance of system-wide impact. I wouldn’t say it’s ZERO, depending on where you put your data, but it’s close to it.

Let’s Make Some Indexes

Classics tho

I wanted to make this realistic — and after years of looking at your tables, I know you get rid of indexes at the same rate I get rid of SQL Server books.

So I went ahead and created 20 of them, and made sure they all had a column in common — in this case the Age column in the Users table.

Now We Need To Modify Them

This’ll run for a bit, obviously.

While it runs, we can use this query to look at how big the version store is.

Not bad.

The only thing is that it stays the same size after we roll that back.

I mean, the ROLLBACK is instant, but cleanup isn’t.

In the next post, we’ll look at forcing cleanup.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *