Damir Matešić .blog

Accelerated Database Recovery (ADR)

By DM / Oct 20, 2019 / MS SQL
Facebook Twitter Google + LinkedIn Pinterest Email Share...

SQL Server 2019 introduces a new feature called Accelerated Database Recovery (ADR). 

ADR changes the way SQL Server maintains the durability of transactions.

IT dramatically reduces the time required for crash recovery, Availability Group failover, and transaction rollback.

Current Database Recovery Process

  • Recovery time is proportional to the size of the longest active transaction
  • Transaction log cannot be truncated

The primary benefits of ADR are:

  • Significantly improves database availability especially in the presence of long running transactions
  • Fast and consistent database recovery - With ADR, long running transactions do not impact the overall recovery time, enabling fast and consistent database recovery irrespective of the number of active transactions in the system or their sizes.
  • Instantaneous transaction rollback - With ADR, transaction rollback is instantaneous, irrespective of the time that the transaction has been active or the number of updates that has performed.
  • Aggressive log truncation - With ADR, the transaction log is aggressively truncated, even in the presence of active long running transactions, which prevents it from growing out of control.

In current versions of SQL Server, crash recovery time is determined by the largest transaction undo or redo phase. 

Database recovery in SQL Server consists of three phases:

  • Analysis phase - Forward scan of the transaction log from the beginning of the last successful checkpoint (or the oldest dirty page LSN) until the end, to determine the state of each transaction at the time SQL Server stopped.
  • Redo phase - Forward scan of the transaction log from the oldest uncommitted transaction until the end, to bring the database to the state it was at the time of the crash by redoing all committed operations.
  • Undo phase - For each transaction that was active as of the time of the crash, traverses the log backwards, undoing the operations that this transaction performed.

The crash recovery process looks like this:

 ADR main components:

  • Persisted Version Store (PVS) - Contains previous versions of data modified by transactions. In the case of an update, the previous version of each row is written to the PVS.
  • Logical revert – During rollback of a transaction, active transactions read records from the PVS.
  • sLog – In-memory log stream that contains activity that is not written to the PVS
  • Cleaner - Cleans up unneeded row versions from the PVS.

Because active transactions read from the PVS, they do not need to wait for the undo and redo phases to complete before accessing data.

Accelerated Database Recovery:

When ADR is enabled, a 14-byte pointer is added to each row in the database. When the row is modified this pointer allows logical revert to find the previous version in the PVS. As a result. the data file for a database with ADR enabled may be larger than the current process. But ADR allows the transaction log to be truncated more aggressively. This means the transaction log for an ADR database remain significantly smaller than a standard database. 

Let’s see ADR in action.

I prepared a test table filled with 2.3 million of rows. I opened the transaction and inserted all the rows in the same table. After that I perform a rollback. The rollback action finish in about 5 seconds on my laptop.

SELECT * INTO dbo.TestADR FROM WideWorldImporters.Sales.Orders;
GO
INSERT INTO dbo.TestADR SELECT * FROM dbo.TestADR;
GO 5
ALTER DATABASE [ADR] SET  ACCELERATED_DATABASE_RECOVERY = OFF;
GO
-- Data are prepared
BEGIN TRAN
INSERT INTO dbo.TestADR SELECT * FROM dbo.TestADR;
-- Wait for records to be inserted
ROLLBACK;
/*
SQL Server Execution Times:
CPU time = 2531 ms,  elapsed time = 4916 ms.
*/

Now, I am turning on ADR and repeat the action:

ALTER DATABASE [ADR] SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
BEGIN TRAN
INSERT INTO dbo.TestADR SELECT * FROM dbo.TestADR;
-- Wait for records to be inserted
ROLLBACK;
/*
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 0 ms.
*/

So, the rollback is instant!

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (1.71 kb)