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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
/* Grow data & log files out to give us some room to work */ USE [master] GO ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150 GO ALTER DATABASE [StackOverflow2013] MODIFY FILE ( NAME = N'StackOverflow2013_1', SIZE = 15360000KB ) GO ALTER DATABASE [StackOverflow2013] MODIFY FILE ( NAME = N'StackOverflow2013_2', SIZE = 15360000KB ) GO ALTER DATABASE [StackOverflow2013] MODIFY FILE ( NAME = N'StackOverflow2013_3', SIZE = 15360000KB ) GO ALTER DATABASE [StackOverflow2013] MODIFY FILE ( NAME = N'StackOverflow2013_4', SIZE = 15360000KB ) GO ALTER DATABASE [StackOverflow2013] MODIFY FILE ( NAME = N'StackOverflow2013_log', SIZE = 10240000KB ) GO /* Enable RCSI - not technically required, but more on that in another post. */ ALTER DATABASE [StackOverflow2013] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT GO USE StackOverflow2013; GO /* Rebuild the table to get the RCSI version time stamps: */ ALTER INDEX ALL ON dbo.Users REBUILD; /* Set up an index to make our transaction slower: */ CREATE INDEX IX_Reputation_LastAccessDate ON dbo.Users(Reputation, LastAccessDate); SELECT * FROM sys.databases; /* Run a transaction that does a lot of work: */ BEGIN TRAN UPDATE dbo.Users SET Reputation = 1000000, LastAccessDate = GETDATE(); GO ROLLBACK ALTER DATABASE StackOverflow2013 SET ACCELERATED_DATABASE_RECOVERY = ON; SELECT * FROM sys.databases; /* Run a transaction that does a lot of work: */ BEGIN TRAN UPDATE dbo.Users SET Reputation = 1000000, LastAccessDate = GETDATE(); GO /* How much space is being used? */ SELECT TOP 100 * FROM sys.dm_tran_persistent_version_store_stats SELECT TOP 100 * FROM sys.dm_tran_top_version_generators SELECT TOP 100 * FROM sys.dm_tran_version_store SELECT TOP 100 * FROM sys.dm_tran_version_store_space_usage /* Clustered index */ SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 1, 0, 'DETAILED') /* Nonclustered index */ SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 2, 0, 'DETAILED') EXEC sp_persistent_version_store; ROLLBACK; GO EXEC sp_persistent_version_cleanup; |
You can learn about the internals in the Azure SQL DB documentation on that same feature.
9 Comments. Leave new
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?
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.
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
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.
Any idea if this will work with cluster-less availability groups? From reading the link I get the idea that the answer is no.
No idea.
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!
Are there any disadvantages to the ADR feature?
If your answer is no,
Why is this feature not active by default?
Mehdi – for general questions, head to a Q&A site like https://dba.stackexchange.com or https://www.brentozar.com/go/sqlhelp.