Accelerated Database Recovery with SQL Server 2019

Beginning with SQL Server 2019, Microsoft has redesigned the database recovery process (ie. crash recovery and rollback) to improve availability and performance. This new feature is called Accelerated Database Recovery (ADR).

Prior to SQL Server 2019, recovering a database (after a crash or restart) consisted of 3 phases that followed the ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) recovery model. The phases of this model are Analysis, Redo & Undo. The Analysis phase begins with the last successful database checkpoint and forward-scans the transaction log to determine the state of each transaction. The Redo phase begins with the oldest uncommitted transaction (that was active after the checkpoint) and rolls-forward, bringing the database state to the same point it was at immediately prior to the crash. The Undo phase then goes backward from the end of the transaction log to reverse all transactions that were active (uncommitted) at the time of the crash. With this process, the database recovery time is roughly the same as the longest running query that was active at the time of the crash.

With ADR, four new components have been incorporated into the 3 phases of the recovery process. These components are:

• Persisted Version Store (PVS)
  • PVS allows for row versioning in the database, instead of using the tempdb version store.
• sLog
  • An in-memory log that stores records for non-versioned operations (eg. lock acquisitions). The sLog is a secondary log stream that works in conjunction with the transaction log. It is truncated as transactions commit and allows for the transaction log to be aggressively truncated by only preserving required log records. Additionally, the sLog is persisted onto disk during checkpoint operations.
• Logical Revert
  • An asynchronous process that keeps track of all aborted transactions and immediately releases any locks. It also performs row-level version-based undo operations (rollbacks) using the PVS for any user transactions.
• Cleaner
  • An asynchronous process that periodically removes old page versions that are no longer needed.

By incorporating these components into the recovery process, physical changes to databases can be versioned. Active transactions (at the time of a crash) are marked as aborted and any versions that are a result of those transactions can be ignored.

In the new recovery process, the Analysis phase scans the transaction log from the last successful database checkpoint, reconstructs the sLog, and copies non-versioned log records. The Redo phase begins with processing the sLog, from the oldest uncommitted transaction until the last checkpoint (which only includes a few records). Then it processes the transaction log beginning with the last checkpoint. The Undo phase uses the sLog to revert non-versioned operations, and the PVS (with Logical Revert) to undo version-based row-level transactions. This allows the undo phase to complete (almost) instantaneously.

In addition to allowing faster database recovery, ADR also reduces the amount of log space that is required for recovery and allows the transaction log to be aggressively truncated during backups and checkpoints.

To enable ADR:

ALTER DATABASE [DB Name] SET ACCELERATED_DATABASE_RECOVERY = ON;

If the database contains more than one filegroup, ADR can be enabled, and a filegroup specified to contain the PVS, with:

ALTER DATABASE [DB Name] SET ACCELERATED_DATABASE_RECOVERY = ON (PERSISTENT_VERSION_STORE_FILEGROUP = [Filegroup Name]);

There are a few caveats/considerations to ADR…

• ADR is not supported for databases enrolled in database mirroring operations (ie. availability groups).
• The PVS information is added to the data file, which increases the file size (by approximately 10%), but at the benefit of reducing the log file size (by around 75%).