SQL Server 2019 introduces a new feature called Accelerated Database Recovery (ADR). ADR changes the way SQL Server maintains the durability of transactions and dramatically reduces the time required for crash recovery, Availability Group failover, and transaction rollback.
When a SQL Server database is created, at least two files are provisioned. A data file, which contains data in 8-kb units called pages, and a transaction log file, which tracks all changes to database data and schema.
Any time a change is made to a database, it is made in a transaction. When a transaction begins, the data pages changed are read into memory from disk, if they are not already there. Accessing data in memory is exponentially faster than reading it from disk, so SQL Server attempts to maintain as much data in memory as possible.
In order to maintain the durability of changes to the database, transaction log records associated with a change are written to disk in the transaction log file when the transaction completes, or commits. The data pages changed can remain in memory before being written to disk. In addition to log records being flushed to disk on commit, log records are flushed to disk on a regular basis by the checkpoint process.
When SQL Server recovers from a crash, it runs through three phases, analysis, redo, and undo. In the analysis phase, the transaction log is scanned for transactions which committed and were written to the log but not written to disk. It also scans for transactions which began but did not commit prior to the crash. The committed transactions are written to disk during the redo phase, while the uncommitted transactions are rolled back during the undo phase.
In current versions of SQL Server, crash recovery time is determined by the largest transaction undo or redo phase. The crash recovery process looks like this:
Accelerated Database Recovery (ADR) introduces several new concepts to speed up the recovery process.
- Persisted Version Store (PVS) - Contains previous versions of data modifed by transactons. 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 uneeded row versions from the PVS.
Because active transactions read from the PVS, they do not to wait for the undo and redo phases to complete before accessing data. With ADR the creash recovery process looks like this:
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 with remain significantly smaller than a standard database.