Accelerated Database Recovery: What It Could Mean For SQL Server

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.

SQL Server Query Plan
Avoidable

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.