ADR new features in SQL 2022 – multithreaded version store

Accelerated database recovery is one of those features that was introduced in SQL 2019 and you have probably been enjoying its benefits without ever knowing how much it contributes towards fast recovery times of SQL server after a restart. SQL2022 has seen some improvements with regard to accelerated database recovery. One of the key improvements is with regard to multi-threaded version cleanup in ADR.

Before we start talking about the new feature. It might be better to understand how the old feature used to work. Accelerated database recovery as its name suggests is able to improve the recovery time of a database after an unexpected shutdown. This is achieved by creating a version store of all physical changes that have not yet been committed. In the event of a failure since the version store contains uncommitted data they are rolled back by a simple logical operation. Rolling back the logical operation is a lot faster than trying to undo the physical changes which are stored in the physical data file as it was previously. There is a more detailed explanation of how ADR works in the link below.

In the new version of SQL Server 2022 accelerated database recovery now allows you to create multiple threads for version cleanup. What this means for you is previously only a single thread was available and that meant only one database could be recovered at a time after an unexpected shutdown. While the operation was fast it was still a limitation for database servers that contained maybe tens or hundreds of databases. By allowing you to create multithreaded version cleanup we are able to parallelly process recovery for multiple databases at the same time.

In order to enable multithreaded version cleanup in accelerated degrees recovery there is a setting available in the SQL server configuration options sp_configure. The option is listed below.

EXEC sp_configure 'ADR Cleaner Thread Count', '4';

RECONFIGURE WITH OVERRIDE

Reference