SQL Server’s Accelerated Database Recovery: How Big Is My Version Store?

Posted on by Erik Darling

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

Books about SQL Server
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.

CREATE INDEX [IX_Id_Age_AccountId] ON [dbo].[Users] ([Id], [Age], [AccountId]);
CREATE INDEX [IX_CreationDate_Age_AccountId] ON [dbo].[Users] ([CreationDate], [Age], [AccountId]);
CREATE INDEX [IX_DisplayName_Age_AccountId] ON [dbo].[Users] ([DisplayName], [Age], [AccountId]);
CREATE INDEX [IX_DownVotes_Age_AccountId] ON [dbo].[Users] ([DownVotes], [Age], [AccountId]);
CREATE INDEX [IX_EmailHash_Age_AccountId] ON [dbo].[Users] ([EmailHash], [Age], [AccountId]);
CREATE INDEX [IX_LastAccessDate_Age_AccountId] ON [dbo].[Users] ([LastAccessDate], [Age], [AccountId]);
CREATE INDEX [IX_Location_Age_AccountId] ON [dbo].[Users] ([Location], [Age], [AccountId]);
CREATE INDEX [IX_Reputation_Age_AccountId] ON [dbo].[Users] ([Reputation], [Age], [AccountId]);
CREATE INDEX [IX_UpVotes_Age_AccountId] ON [dbo].[Users] ([UpVotes], [Age], [AccountId]);
CREATE INDEX [IX_Views_Age_AccountId] ON [dbo].[Users] ([Views], [Age], [AccountId]);
CREATE INDEX [IX_WebsiteUrl_Age_AccountId] ON [dbo].[Users] ([WebsiteUrl], [Age], [AccountId]);
CREATE INDEX [IX_Age_CreationDate_AccountId] ON [dbo].[Users] ([Age], [CreationDate], [AccountId]);
CREATE INDEX [IX_Age_DisplayName_AccountId] ON [dbo].[Users] ([Age], [DisplayName], [AccountId]);
CREATE INDEX [IX_Age_DownVotes_AccountId] ON [dbo].[Users] ([Age], [DownVotes], [AccountId]);
CREATE INDEX [IX_Age_EmailHash_AccountId] ON [dbo].[Users] ([Age], [EmailHash], [AccountId]);
CREATE INDEX [IX_Age_Id_AccountId] ON [dbo].[Users] ([Age], [Id], [AccountId]);
CREATE INDEX [IX_Age_LastAccessDate_AccountId] ON [dbo].[Users] ([Age], [LastAccessDate], [AccountId]);
CREATE INDEX [IX_Age_Location_AccountId] ON [dbo].[Users] ([Age], [Location], [AccountId]);
CREATE INDEX [IX_Age_Reputation_AccountId] ON [dbo].[Users] ([Age], [Reputation], [AccountId]);
CREATE INDEX [IX_Age_UpVotes_AccountId] ON [dbo].[Users] ([Age], [UpVotes], [AccountId]);

Now We Need To Modify Them

BEGIN TRAN
UPDATE u
SET u.Age = 100
FROM dbo.Users AS u
WHERE u.Age IS NULL

ROLLBACK

This’ll run for a bit, obviously.

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

SELECT DB_NAME(database_id) AS database_name,
       (persistent_version_store_size_kb / 1024.) AS persistent_version_store_size_mb
FROM sys.dm_tran_persistent_version_store_stats
WHERE persistent_version_store_size_kb > 0;
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!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts