So assuming that I already have Read Committed Snapshot Isolation enabled, what are the downsides of Accelerated Database Recovery?
The opposite of the upsides
You have no choice, Persistent Version Store (PVS) must be part of enablement of ADR, as such your version store is now part of the database instead of in tempdb.
If you don't have a large version store, this might be fine, if you have a large version store, your database size will grow.
Since these are all items that need to be saved, this can have an effect on logging and technologies that consume the log, such as replication, CDC, Availability Groups, Log Shipping, etc., which can cause secondary issues.
Since ADR uses PVS the versions need to be cleaned, just like tempdb, you can't cleanup versions that are needed. This can cause the cleaner to get behind, PVS to bloat, and eventually the cleaner either can't keep up well or causes other issues.
There have been enhancements to the cleaner over time, with SQL Server 2022 doing a better job than 2019. Azure SQL DB is a different story as it's usually the latest code bits (in some form or fashion) but is not immune to these issues.
ADR is nice for most workloads; however, I've seen terrible performance and results from workloads with high numbers of aborted transactions.
This ends up manifesting as PVS bloat and not so accelerated recovery times. Additionally, this can cause the need for manual runs of PVS cleanup, if it isn't already blocked. This is also true of long running queries on things such as secondary readable replicas and AGs.
Note, ADR is required for Optimized Locking, which is currently only available for Azure SQL Database and Fabric but can be expected on SQL Server at some stage.
