[Video] Demoing SQL Server 2019’s new Accelerated Database Recovery

Wouldn’t it be nice if rollbacks just finished instantly? Wouldn’t you love for startup times to be near-zero even when SQL Server crashed just as someone in the middle of a transaction? How much would you pay for all this? (Well, I’m a little afraid to ask that, since we don’t know yet whether this is an Enterprise-only feature, but dang, I sure hope not.)

Here’s how Accelerated Database Recovery works in SQL Server 2019:

In the video, I’m using the Stack Overflow 2013 (50GB) database with this script:

You can learn about the internals in the Azure SQL DB documentation on that same feature.

Previous Post
New Objects, Columns, and Messages in SQL Server 2019 CTP 2.3
Next Post
Can deleting rows make a table…bigger?

7 Comments. Leave new

  • Thomas Franz
    March 4, 2019 9:21 am

    If all of my updates etc. goes to separate pages in this new version store, would I not end with heavily fragmented indexes?

    Or is there an asynchron job, that put all the data back to the originial pages?

    Reply
    • Thomas – go ahead and hit up the more-info link at the end of the post. Microsoft does a great job of explaining how it works, and most importantly, who will benefit from the feature. The people who will benefit from it frankly won’t give a rip about fragmentation in order to achieve that goal.

      Reply
  • Javier Villegas
    March 4, 2019 12:33 pm

    Brent, do you see any scenario where you will not enable ADR for all the user databases?. If the feature works as described then I guess it should be enabled by default

    Reply
    • Javier – ooo, great question. First, anytime there’s a version 1.0 feature, you probably don’t want to enable it unless you need it. Usually better to let other folks work the bugs out.

      Setting that aside, ADR is going to mean additional writes to the data pages over time. SQL Server has to go back and clean out those old versions of rows. There’s a purge-history stored procedure, and it needs a lock in order to run. There’s going to be a cost for that.

      Reply
  • Any idea if this will work with cluster-less availability groups? From reading the link I get the idea that the answer is no.

    Reply
  • Andrew Peterson
    March 5, 2019 8:43 am

    Very intriguing. Seems like the key is the Persisted Version Store (PVS). And this sounds like someone thought, “…why don’t we take the code bits for the snapshot isolation/read committed snapshot, and wrap it up with the trans log.” And it’s actually brilliant!

    Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}