Accelerated Database Recovery enhancements in SQL Server 2022
Part of the SQL Server 2022 blog series.
We are excited to share that there are several Accelerated Database Recovery (ADR) enhancements in SQL Server 2022 that further improve the overall availability and scalability of the database, primarily around persistent version store (PVS) cleanup and management.
Overview of Accelerated Database Recovery (ADR)
ADR improves database availability, especially in the presence of long running transactions, by redesigning the SQL database engine recovery process. ADR is introduced in SQL Server 2019 (15.x) and improved in SQL Server 2022 (16.x).
ADR is also available for databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse SQL. ADR is enabled by default in SQL Database and SQL Managed Instance and cannot be disabled.
The primary benefits of Accelerated Database Recovery (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.
ADR completely redesigns the database engine recovery process.
- Make it constant time and instant by avoiding having to scan the log from and to the beginning of the oldest active transaction. With ADR, the transaction log is only processed from the last successful checkpoint (or oldest dirty page log sequence number (LSN)). As a result, recovery time is not impacted by long running transactions.
- Minimize the required transaction log space since there is no longer a need to process the log for the whole transaction. As a result, the transaction log can be truncated aggressively as checkpoints and backups occur.
At a high level, ADR achieves fast database recovery by versioning all physical database modifications and only undoing logical operations, which are limited and can be undone almost instantly. Any transactions that were active at the time of a crash are marked as aborted and, therefore, any versions generated by these transactions can be ignored by concurrent user queries.
Note: For more details about ADR, please visit this page: and this video for a high-level overview of ADR and its components.
SQL Server 2022
Learn about the new features on security, platform, management, and more.
New ADR improvements in SQL Server 2022
Multi-threaded version cleanup
In SQL Server 2019 (15.x), the ADR cleanup process is single threaded within a SQL Server instance. Beginning with SQL Server 2022 (16.x), this process uses multi-threaded version cleanup (MTVC), that allows multiple databases under the same SQL Server instance to be cleaned in parallel.
MTVC is enabled by default in SQL Server 2022 and uses one thread per SQL instance. To adjust the number of threads for version cleanup, set ADR Cleaner Thread Count with sp_configure.
USE master;
GO
-- Enable show advanced option to see ADR Cleaner Thread Count
EXEC sp_configure 'show advanced option', '1';
-- List all advanced options
RECONFIGURE;
EXEC sp_configure;
-- The following example sets the ADR Cleaner Thread Count to 4
EXEC sp_configure 'ADR Cleaner Thread Count', '4';
RECONFIGURE WITH OVERRIDE;
-- Run RECONFIGURE to verify the number of threads allocated to ADR Version Cleaner.
RECONFIGURE;
EXEC sp_configure;
In the above example, if you configure the ADR Cleaner Count to be four on a sql instance with two databases, the ADR cleaner will allocate only one thread per database, leaving the remaining two threads idle.
Note: The maximum number of ADR Cleaner threads is capped at the number of cores used by the SQL Server instance. For example, if you are running SQL Server on an eight core machine, the maximum number of ADR cleaner threads that the engine can use will be eight, even if the value in the sp_configure is set to a greater value.
User transaction cleanup
This improvement allows user transactions to run cleanup on pages that could not be addressed by the regular cleanup process due to lock conflicts. This helps ensure that the ADR cleanup process works more efficiently.
Reducing memory footprint for PVS page tracker
This improvement tracks persisted version store (PVS) pages at the extent level, in order to reduce the memory footprint needed to maintain versioned pages.
Accelerated Data Recovery cleaner improvements
ADR cleaner has improved version cleanup efficiencies to improve how SQL Server tracks and records aborted versions of a page leading to improvements in memory and capacity.
Transaction-level persisted version store
This improvement allows ADR to clean up versions belonging to committed transactions independent of whether there are aborted transactions in the system. With this improvement PVS pages can be deallocated, even if the cleanup cannot complete a successful sweep to trim the aborted transaction map.
The result of this improvement is reduced PVS growth even if ADR cleanup is slow or fails.
New extended event
A new extended event, tx_mtvc2_sweep_stats, has been added for telemetry on the ADR PVS multi-threaded version cleaner.
Summary
In this blog post, we covered all the exciting ADR improvements that we are including with SQL Server 2022 that further improve the overall availability and scalability of your databases.
Stay tuned as we are currently working on further improvements of the multi-threaded version cleaner that will enable parallelizing version cleanup within databases.
Learn more
For more information, and to get started with SQL Server 2022, check out the following references:
- Manage accelerated database recovery
- Troubleshoot accelerated database recovery
- SQL Server Accelerated Database Recovery
Read What’s new in SQL Server 2022 for all the new features on security, platform, management, and more.
