Category: SQL Server

Forcing Cleanup

There’s a stored procedure called sp_persistent_version_cleanup we can use to clean out PVS data.

There’s some helpful information about it in sp_helptext:

create procedure sys.sp_persistent_version_cleanup  
(  
 @dbname sysname = NULL,  -- name of the database.  
 @scanallpages BIT = NULL  -- whether to scan all pages in the database.  
)  
as  
begin  
 set @dbname = ISNULL(@dbname, DB_NAME())  
 set @scanallpages = ISNULL(@scanallpages, 0)  
  
 declare @returncode int  
  
 EXEC @returncode = sys.sp_persistent_version_cleanup_internal @dbname, @scanallpages  
  
 return @returncode  
end

We can pass in a database name, and if we want to scan all the pages during cleanup.

Unfortunately, those get passed to sp_persistent_version_cleanup_internal, which only throws an error with sp_helptext.

Locking?

While the procedure runs, it generates a wait called PVS_CLEANUP_LOCK.

This doesn’t seem to actually lock the PVS so that other transactions can’t put data in there, though.

While it runs (and boy does it run for a while), I can successfully run other modifications that use PVS, and roll them back instantly.

If we look at the locks it’s taking out using sp_WhoIsActive…

sp_WhoIsActive @get_locks = 1;

<Database name="StackOverflow">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
</Database>

It doesn’t seem to do anything terribly invasive.

Watching the session with XE also doesn’t reveal locking, but it may be cleverly hidden away from us.

In all, it took about 21 minutes to cleanup the 37MB of data I had in there.

I don’t think this is my fault, either. It’s not like I’m using a clown shoes VM here.

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.

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

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;

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.

Thinking Ahead

I love the idea of Accelerated Database Recovery, because it solves some really cool problems in SQL Server.

There was always this big Wompson & Wompson around rollback, in that it was single threaded. If you had a process get a parallel plan to do some modifications, the rollback could take much longer.

ADR doesn’t solve concurrency issues around multiple modification queries. They both still need the same locks, and other transactions aren’t reading from the Persistent Version Store (PVS from here on out).

But they could. Which would allow for some interesting stuff down the line.

Flashsomething

Oracle has a feature called Flashback that lets you view data as it existed in various points in time. You sort of have this with Temporal Tables now, but not database wide. It’s feasible to think that not only would the PVS let us look at data at previous points in time, but also to restore objects to that point in time.

Yep. Single objects.

AlwaysOptimistic

With PVS up and running, we’ve got row versioning in place.

That means SQL Server could feasibly join the rest of the civil database world by using optimistic locking by default.

It could totally be used in the way that RCSI and SI are used today to let readers and writers (and maybe even writers and writers!) get along peaceably.

Happy Halloween

You know those spool things that I hate? This could be used to make some of them disappear.

The way PVS works now, we have a record of rows modified, which means we’ve effectively spooled those rows out somewhere already.

With those rows recorded, we could skip using spools all together and just read the rows we need to modify from here.

I’m Excited!

This is a very cool step forward for SQL Server.

I mean, aside from the fact that it took 15 minutes to cleanup a 75MB version store.

But still! This is gonna help a lot of people, and has potential to go in a few new directions to really improve the product.

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.