ADR is a complete redesign of the SQL database engine recovery process. The primary benefits of ADR are:

• 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.

https://docs.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts?view=sql-server-ver15

Enabling ADR

• After you restore and change the compatibility of your database to SQL 2019, ADR will not be enabled by default
• Since ADR leverages the new Persisted version store (PVS) instead of tempdb to store row versions, the space/IO overhead is now transferred to the database
• To isolate this overhead, PVS can be stored in a separate filegroup:

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON (PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG])

Recovery

To test the benefits of ADR, tests were catered for two separate scenarios – undo and redo. Server level ‘recovery interval’ and database level target_recovery_times were both extended to 10+ minutes to reduce automatic checkpoints from interfering with tests.

Undo Performance

Undo was tested using the following method

1. Run a manual checkpoint
2. Begin transaction and update 100k rows
3. Issue manual checkpoint to flush uncommitted records to disk
4. Immediately kill SQL Server instance via task manager to emulate a disaster
5. Start SQL Instance and track recovery – SQL will need to perform an undo as transaction was not committed.

Drastic improvement in undo operation with ADR! 

 Redo Performance

Redo was tested using the following method:

1. Run a manual checkpoint
2. Insert 1 million rows and commit
3. Immediately kill SQL Server instance via task manager to emulate a disaster
4. Start SQL Instance and track recovery – SQL will need to perform redo since the transaction was committed, but not flushed to disk

As expected, ADR does not benefit much in the redo as committed data that is not yet flushed to disk will need to be redone.

Rollback

Rollback was tested by inserting 2 million rows into an empty table and then issuing a rollback. Elapsed time was captured for the actual rollback operation.

ADR excels with instantaneous rollbacks every time!

Log Truncation

ADR allows the transaction log to be aggressively truncated even in the presence of active, long running transactions. Since changes are versioned, SQL Server does not need the log records to be present to rollback. Instead, it just marks the updated rows as aborted.

Testing method:

1. Open transaction and insert 1 million rows
2. Try to truncate the log while transaction is still open

SQL 2017

Starting Tlog status:

After inserting with an open tran and taking a log backup + checkpoint:

SQL 2019 + ADR

Starting Tlog status:

After inserting with an open tran:

After taking a log backup + checkpoint:

After commit + log backup + checkpoint:

Log truncation with Index Rebuilds

Resumable index rebuilds in SQL 2017 provided the ability to pause and resume index rebuilds, along with the ability to truncate the log. Traditional index rebuilds can now benefit from the new aggressive log truncation. Normal index rebuilds do not need to run to completion for the transaction log to be truncated. This is especially helpful for large online index operations.

I ran an online index rebuild on the ADR enabled database. As soon as the log reached around 1GB used, I issued a transaction log backup.

Transaction log usage for in-flight online index rebuild:

Transaction log usage for in-flight online index rebuild after issuing log backup:

I was able to run backups throughout the index rebuild operation, which kept the tlog usage to a minimum.

Version Store

ADR utilizes the new Persisted version store (PVS) to version records. If ADR is enabled, tempdb will no longer be used to version records even if RCSI / Snapshot isolation is enabled on the database.

Testing method:

1. Enable RCSI
2. For SQL 2019 database, a separate FG (PVS_FG) was created for the PVS
3. Begin transaction and delete 1 million records from a table

SQL 2017

After delete

Tempdb:

SQL 2019 + ADR

After delete

Tempdb:

PVS:

select db_name(database_id), persistent_version_store_size_kb
from sys.dm_tran_persistent_version_store_stats

PVS Filegroup:

After aborting the delete transaction, the PVS was not cleaned up immediately.

Cleanup status can be viewed in the sys.dm_tran_persistent_version_store_stats DMV.

After the end time was populated in the DMV, the PVS size dropped.

Space

Note that if ADR is enabled, extra space needs to be allocated to the PVS filegroup for the overhead of versioning. If the PVS filegroup runs out of space, it can cause an outage to the entire database until the transaction is aborted and PVS clean-up is completed. Below is the error generated for a transaction once PVS is full.

Msg 1101, Level 17, State 12, Line 11

Could not allocate a new page for database ‘DB_2019’ because of insufficient disk space in filegroup ‘PVS_FG’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.