Estimate database growth caused by Accelerated Database Recovery

Question

Accelerated Database Recovery (ADR) is on by default in Azure and an option in SQL Server 2019. It has some really interesting potential.

I have experimented by turning ADR on in a static test database, and if nothing happens there is no growth. It seems like growth is only going to occur on an active database. So unless you have a test system that allows you to full reproduce your production workload, you are going to be doing your growth experiments in production.

Add to this, turning ADR on or off essentially requires a down time as there is an "exclusive lock" requirement to change the state, and you have a recipe for a career changing event.

If you have a 10GB, database with 50GB available, probably not a big concern. But if you have a 3.5TB database and only 4TB of space available, this could be a big problem.

From the Manage accelerated database recovery documentation:

PVS is considered large if it's significantly larger than baseline or if it is close to 50% of the size of the database.

###Question

How can I accurately estimate the growth of using ADR, prior to implementing?

Answer 1

Indeed the only way to be sure is to run a representative workload against a test copy of the database.

If you don't have a realistic test environment, you could consider testing this in the cloud. For instance, in Azure it is relatively quick and simple to copy a backup and restore it. You can use the Database Experimentation Assistant, which you can use directly or through the included RML Utilities to capture and replay a production trace.

Otherwise, you may be able to gain an acceptable level of confidence by running just the parts that you expect to generate significant PVS (persistent version store) usage.

PVS also takes over the usual user version store usage from tempdb for things like row-versioning isolation levels, triggers, and MARS. You may expect a corresponding reduction in usage of tempdb for these purposes.

Using ADR you should expect an increase in log volume, but not necessarily the number of log records. The size of the log at any given time should be smaller, possibly very much smaller, than before ADR was enabled due to aggressive log truncation.

In ADR, versions are needed for recovery purposes, so they have to be preserved until the committed row version is brought back to the source page. That is performed by background cleaner threads, so you can expect PVS to be larger than the tempdb version store if you currently have RCSI and/or SI enabled for this database.

There are a lot of moving parts to ADR, and much depends on the detailed characteristics of your workload. Unless you are able to perform realistic testing beforehand, you would be well-advised to ensure a decent margin of database space is available for PVS growth before enabling it.

The exclusive database lock required to enable ADR is the same as that required to enable RCSI, and should be planned accordingly. Enabling RCSI has a difficult-to-assess impact on tempdb as well. Every significant change to database configuration requires an assessment of the benefits and risks, and a robust implementation and back-out plan.

If you are interested in the deeper details of ADR (internally named Constant Time Recovery), see this Microsoft Research Paper (pdf).