| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |
|---|---|---|---|---|---|---|---|---|---|---|
Server configuration: ADR cleaner thread count |
Explains the SQL Server instance configuration setting for ADR cleaner thread count. |
MikeRayMSFT |
mikeray |
randolphwest, dfurman |
02/03/2025 |
sql |
configuration |
conceptual |
|
[!INCLUDE sqlserver2022-and-later]
This configuration setting is used for accelerated database recovery (ADR). The cleaner is an asynchronous process that wakes up periodically and cleans page versions that aren't needed.
By default, this configuration setting is set to 1. This means that the cleaner uses a single thread to clean persistent version store (PVS) in all databases on the database engine instance.
If the cleaner performance is insufficient and you observe that PVS size is reduced too slowly or remains large, you can increase this configuration to make the cleaner multi-threaded.
Important
PVS cleanup might be slow or blocked due to workload activity. Before increasing this configuration value, review Monitor and troubleshoot accelerated database recovery. If PVS cleanup is slow or blocked for one of the reasons mentioned in that article, follow the recommendations in the article instead of increasing the ADR Cleaner Thread Count configuration value.
Increasing the ADR Cleaner Thread Count configuration value to a large value isn't recommended. First start with a small increase, and then gradually increase the value incrementally until cleaner performance improves sufficiently. For example, you might increase the value to 2, and then to 4.
Database engine instances with many databases that experience large PVS growth might require higher values of this setting.
Regardless of configuration, the cleaner does not use more threads than the number of logical CPUs.
The following example sets the number of PVS cleaner threads to 2.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'ADR Cleaner Thread Count', 2;
RECONFIGURE;
GO