Accelerated Database Recovery in SQL Server 2019

2020-01-08 · mssql, sql2019, recovery

I've lost count of the times I killed a session and then sat watching it in ROLLBACK for two hours, or waited on a database stuck in In Recovery after a failover. Undo takes as long as the original transaction did, and that's the weak point in the SQL Server recovery model. Accelerated Database Recovery (ADR), new in SQL Server 2019, redesigns this. Rollback becomes near-instant regardless of transaction size, crash recovery no longer depends on the longest running transaction, and the transaction log can be truncated aggressively even while a long transaction is open.

It's a per-database setting, off by default in SQL Server 2019 (it is on and cannot be disabled in Azure SQL Database). Below: how it works, how I enable it, and what to watch afterwards.

How ADR changes recovery

Traditional recovery runs three phases: analysis, redo and undo, with undo replaying log records backwards for every uncommitted transaction. ADR adds four components:

Persistent Version Store (PVS): row versions are stored in the user database itself rather than in tempdb. Rollback simply marks the transaction aborted and the old row version becomes visible.
Logical revert: aborted rows are cleaned up lazily in the background; a new transaction touching such a row reverts it on the spot.
sLog: a small secondary in-memory log for operations that cannot be versioned (metadata, allocation). It is what makes redo fast.
Cleaner: a background task that removes versions no longer needed.

The effects: KILL and ROLLBACK return almost immediately; after a crash, redo starts from the last checkpoint rather than from the oldest active transaction; and log_reuse_wait_desc stops showing ACTIVE_TRANSACTION for hours.

Before you switch it on

ADR works on any edition of SQL Server 2019, and the compatibility level can stay where it is (150 is not required). Plan for roughly 10 to 30 percent extra space in the database for the version store, depending on update volume. Try it in a test environment first, because ADR changes I/O patterns for update-heavy workloads.

Pick a filegroup

SELECT name, is_accelerated_database_recovery_on FROM sys.databases;

The version store lives in the PRIMARY filegroup by default. On a busy database I prefer to give it its own filegroup on separate storage so its growth does not compete with the data files:

ALTER DATABASE ShopDB ADD FILEGROUP ADR_PVS;
ALTER DATABASE ShopDB ADD FILE (
    NAME = N'ShopDB_PVS',
    FILENAME = N'F:\SQLData\ShopDB_PVS.ndf',
    SIZE = 4096MB, FILEGROWTH = 1024MB
) TO FILEGROUP ADR_PVS;

Enable it

The database must have no active users for the moment the setting changes; use single-user mode during a short window:

ALTER DATABASE ShopDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ShopDB SET ACCELERATED_DATABASE_RECOVERY = ON (PERSISTENT_VERSION_STORE_FILEGROUP = ADR_PVS);
ALTER DATABASE ShopDB SET MULTI_USER;

Without the filegroup clause, the PVS goes into PRIMARY. Note that changing the filegroup later requires turning ADR off and on again, which is why choosing it up front matters.

See the instant rollback

In one window, start a large transaction:

BEGIN TRAN;
UPDATE dbo.OrderLines SET Discount = Discount + 0 WHERE OrderId < 5000000;
-- several minutes on a big table; do not commit

In another, kill it and time the rollback:

KILL 57;
KILL 57 WITH STATUSONLY;

Before ADR, WITH STATUSONLY would show a percentage crawling up for as long as the update took. With ADR, the session is gone within a second.

Keep an eye on the version store

SELECT DB_NAME(database_id) AS db,
       persistent_version_store_size_kb / 1024 AS pvs_mb,
       current_aborted_transaction_count,
       oldest_active_transaction_id,
       pvs_off_row_page_skipped_low_water_mark,
       aborted_version_cleaner_start_time,
       aborted_version_cleaner_end_time
FROM sys.dm_tran_persistent_version_store_stats;

persistent_version_store_size_kb should rise and fall. If it only rises, look at oldest_active_transaction_id and find that session; a long-open transaction (or an open snapshot, or a lagging secondary replica) prevents cleanup exactly as it does for the tempdb version store.

You can trigger cleanup manually when investigating:

EXEC sys.sp_persistent_version_cleanup 'ShopDB';

Confirming the setup

Check the setting and filegroup:

SELECT name, is_accelerated_database_recovery_on FROM sys.databases WHERE name = 'ShopDB';
SELECT fg.name, df.name, df.size / 128 AS size_mb
FROM sys.filegroups fg JOIN sys.database_files df ON fg.data_space_id = df.data_space_id
WHERE fg.name = 'ADR_PVS';

Then check the log after a busy period with an open transaction: SELECT name, log_reuse_wait_desc FROM sys.databases; should no longer be pinned at ACTIVE_TRANSACTION, and DBCC SQLPERF(LOGSPACE) should show the log being reused.

Notes

Version store keeps growing. The cleaner cannot remove versions newer than the oldest active transaction. Find it with DBCC OPENTRAN('ShopDB') or sys.dm_tran_active_transactions. Also check that the PVS filegroup has room to grow; if it fills, updates fail with a 1105 "Could not allocate space for object" error naming the PVS filegroup.

Higher write I/O on update-heavy tables. Row versions are written to the user database, so an update that modified one page may now touch two. On systems that were I/O bound on the data drive this is measurable; this is the main reason to test first and to give the PVS its own storage.

The ALTER DATABASE is refused. Some databases cannot use ADR: master, tempdb, databases with memory-optimised filegroups in certain configurations, and databases with database snapshots. Drop the snapshots and retry.

Disabling requires the same quiet window. ALTER DATABASE ShopDB SET ACCELERATED_DATABASE_RECOVERY = OFF; waits for the version store to be cleaned and needs exclusive access; do it in single-user mode.

Watch sys.dm_tran_persistent_version_store_stats for a couple of weeks after enabling. After that, I'm just glad I never have to explain to a manager why the killed query is still running.