Constant Time Recovery: how Azure SQL stopped undoing work row by row
Microsoft explains Constant Time Recovery, the design that stopped Azure SQL recovery time from scaling with the amount of work an interrupted transaction had done. A persistent version store plus logical revert let the engine mark an aborted transaction and clean up lazily instead of walking the log row by row. The work ships as Accelerated Database Recovery: always on in Azure SQL Database, opt-in on SQL Server.
Key points
- Recovery and rollback time no longer scale with transaction size; 99.999th percentile fell from over an hour to three minutes.
- The persistent version store keeps row versions in the database: in-row byte diffs for small changes, an off-row table for larger ones.
- Aborts are recorded instantly in an in-memory Aborted Transaction Map, with old versions cleaned up lazily in the background.
- Log usage for a 50 million row delete dropped from 38 GB to 147 MB, and from 5,101 MB to 99 MB for 10 million inserts.
- ADR is always on and cannot be disabled in Azure SQL Database; on SQL Server it exists but is off by default.
- SQL Server 2022 added multi-threaded cleanup, and SQL Server 2025 extends ADR to tempdb.
ARIES, the textbook recovery algorithm, ends with an undo phase whose cost is proportional to the work the uncommitted transaction performed: touch 50 million rows and you owe 50 million units of rollback. The VLDB 2019 paper behind Constant Time Recovery makes the snapshot version store durable, keeping row versions in the database itself, as an in-row byte diff for small changes and in an off-row table for larger ones.
An abort therefore no longer replays the log. The transaction ID is flagged in an in-memory Aborted Transaction Map, queries skip those versions, and cleanup happens lazily in the background; the few operations that cannot be versioned go to a small side log. In production Azure SQL Database, 99.999th percentile recovery fell from over an hour to three minutes, and a 50 million row delete produced 147 MB of log instead of 38 GB. The price was up to 13.8% lower throughput on update-heavy TPC-C.
Real-world use
- An import job in a .NET service bulk-loading millions of rows cannot afford to leave tables locked for hours after an unlucky reboot mid-load.
- An operator cancelling a runaway UPDATE wants the system back immediately, not after a rollback as long as the update itself.
- A team running large nightly deletes needs to stop the transaction log from growing and taking the application down.
- A multi-tenant SaaS database that must meet a recovery SLA after failover needs predictable startup times.
Why it matters
Your worst case stops being tied to data volume: large transactions become cancellable, and both failover and maintenance outages become predictable instead of open-ended.