{"id":7871,"date":"2026-11-02T03:49:03","date_gmt":"2026-11-02T11:49:03","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=7871"},"modified":"2026-11-05T10:39:03","modified_gmt":"2026-11-05T18:39:03","slug":"your-server-wasnt-slow-it-was-waiting-we-fixed-it-with-constant-time-recovery","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/your-server-wasnt-slow-it-was-waiting-we-fixed-it-with-constant-time-recovery\/","title":{"rendered":"The paper that explains how Microsoft SQL learned to recover without undoing everything first"},"content":{"rendered":"

Under the Hood: The Research Behind Microsoft SQL, part 2<\/em><\/span><\/p>\n

Here\u2019s a scenario that used to be genuinely frightening: you kick off a bulk load of a few hundred million rows in a single transaction. Somewhere in the middle, the server goes down. Not because of your load, just an unlucky reboot.<\/span><\/p>\n

When SQL Server comes back, it has to undo everything that transaction did before anyone can touch those tables. Row by row (I think I\u2019ve heard probably Bob Ward say \u201crow by agonizing row\u201d to describe this). Working backwards through the log.<\/span><\/p>\n

That exact thing happened to an Azure SQL customer, and the team wrote it into the opening of their paper:<\/span><\/p>\n

\u201cUnexpected failure occurred while one of our customers was attempting to load hundreds of millions of rows in a single transaction. The database required 12 hours to recover while the corresponding tables were completely inaccessible.\u201d<\/span><\/p><\/blockquote>\n

Twelve hours. The paper is Constant Time Recovery in Azure SQL Database<\/a> (VLDB 2019), and it explains how recovery stopped depending on how much work your transaction did.<\/span><\/p>\n

<\/a>Why recovery was slow<\/span><\/h2>\n

Databases have used a recovery algorithm called ARIES since the early 90s (I did not know that before this paper, it stands for \u201cAlgorithms for Recovery and Isolation Exploiting Semantics\u201d). I\u2019m no expert, but I understand it runs three phases: analysis, redo, then undo. The undo phase is the problem. As the paper puts it:<\/span><\/p>\n

\u201cRecovering the database to a consistent state requires undoing all operations performed by uncommitted transactions which makes the cost of recovery proportional to the work performed by these transactions.\u201d<\/span><\/p><\/blockquote>\n

Cost proportional to work done, yikes. A transaction that touched 50 million rows generates 50 million units of undo. On a departmental server in 1995, transactions were small (so I\u2019m told \u2013 I was definitely not doing anything with a departmental server in 1995!). In a cloud service running millions of databases, where hardware fails and maintenance restarts happen constantly, that proportionality becomes an availability problem.<\/span><\/p>\n

<\/a>The idea: stop undoing, start ignoring<\/span><\/h2>\n

SQL Server already had a version store for snapshot isolation, where updating a row keeps the old version around in tempdb so readers see a consistent picture. This has been a topic that is always fuzzy for me, but the paper helped me understand. The innovation of Constant Time Recovery, or CTR, takes that and makes it durable (i.e., not in tempdb).<\/span><\/p>\n

The Persistent Version Store (PVS)<\/strong> keeps row versions in the database itself, so they survive a restart. Versions live in two places: small changes are stored in-row as a byte diff next to the current row, larger ones go to an off-row table. By the way, it\u2019s pretty crazy that the database just does this for you. The more I learn about the engine, the more I am impressed.<\/span><\/p>\n

Logical revert<\/strong> is the payoff. When a transaction aborts, the system doesn\u2019t walk the log undoing changes. It marks the transaction ID as aborted in an in-memory hash table called the Aborted Transaction Map<\/strong>. Any query that later encounters a row version written by that transaction skips it and reads the previous version instead. The abort is effectively instant. Cleanup happens lazily (I still smile that this is a technical term) in the background.<\/span><\/p>\n

\u201cThe database is fully available, releasing all locks, while row versions are lazily cleaned up in the background.\u201d<\/span><\/p><\/blockquote>\n

The SLog<\/strong> handles the leftovers. A few operations can\u2019t be versioned, like certain metadata changes and lock acquisitions. Those go into a small secondary log stream that can be redone and undone in constant time, because there are very few of them.<\/span><\/p>\n

The design choice I appreciate most is that they didn\u2019t rewrite the engine, they just got creative:<\/span><\/p>\n

\u201cCTR combines traditional ARIES with multi-version concurrency control to improve recovery time without having to fundamentally redesign the storage engine.\u201d<\/span><\/p><\/blockquote>\n

<\/a>The numbers<\/span><\/h2>\n

Recovery time, measured across production Azure SQL Database:<\/span><\/p>\n\n\n\n\n\n\n\n
Percentile<\/span><\/td>\nTraditional<\/span><\/td>\nCTR<\/span><\/td>\n<\/tr>\n<\/thead>\n
99.9th<\/span><\/td>\n68 s<\/span><\/td>\n60 s<\/span><\/td>\n<\/tr>\n
99.99th<\/span><\/td>\nover 10 min<\/span><\/td>\n1.3 min<\/span><\/td>\n<\/tr>\n
99.999th<\/span><\/td>\nover 1 hour<\/span><\/td>\n3 min<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The tail is where this matters. At the 99.999th percentile, over an hour became three minutes. Rollback also benefits from this, making it incredibly fast.<\/span><\/p>\n

\"Picture1<\/a><\/p>\n

Source: Figure 9 from Constant Time Recovery in Azure SQL Database (VLDB 2019).<\/em><\/span><\/p>\n

There\u2019s a second benefit that surprised me the first time I read it. Because aborted work no longer has to stay in the log for undo purposes, the log can be truncated aggressively even while a huge transaction is running:<\/span><\/p>\n\n\n\n\n\n\n\n
Operation<\/span><\/td>\nTraditional log<\/span><\/td>\nCTR log<\/span><\/td>\n<\/tr>\n<\/thead>\n
10M inserts<\/span><\/td>\n5,101 MB<\/span><\/td>\n99 MB<\/span><\/td>\n<\/tr>\n
50M inserts<\/span><\/td>\n25,873 MB<\/span><\/td>\n185 MB<\/span><\/td>\n<\/tr>\n
50M deletes<\/span><\/td>\n38,255 MB<\/span><\/td>\n147 MB<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

That 50 million row delete used 38 GB of log before. Now it uses 147 MB. If you\u2019ve ever had a maintenance job fill the transaction log and take an application down, you understand why that row matters. Crazy.<\/span><\/p>\n

The cost is real but bounded, at least at the time of the paper. On TPC-C, an update-heavy workload, throughput dropped 13.8% with in-row versioning enabled (28% without it). On TPC-E, a more mixed workload, the hit was 2.4%. The team took a single-digit to low-double-digit throughput cost in exchange for removing hour-long outages from the tail.<\/span><\/p>\n

CTR was enabled worldwide for over three million databases in Azure SQL Database in May 2019.<\/span><\/p>\n

<\/a>Where you see this today<\/span><\/h2>\n

CTR ships as Accelerated Database Recovery<\/a>, always on in Azure SQL Database (you can\u2019t turn it off) and available in SQL Server since 2019.<\/span><\/p>\n

Practically, it means:<\/span><\/p>\n