The starting line is a realization that keeping old versions of rows can help log-related pain points. How does ADR use row versioning to solve these? Read on!<\/p>\n
Versions to the Rescue<\/h2>\n
Begin with a rollback, where one session makes a number of changes but then aborts. This is how it typically works:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image.gif\")
<\/p>\n
See how another session\u2019s SELECT is blocked until the rollback process completes? When an UPDATE rolls back, other queries have to wait until the modifications are undone before they can select data (with exceptions for existing isolation levels that use versioning).<\/p>\n
What if SQL Server stored old row versions?<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-1.gif\")
<\/p>\n
The rollback in this case is effectively instant (merely marking a particular transaction as aborted). The SELECT no longer has to wait for change reversal to finish and locks to release but can read the older, committed record.<\/p>\n
Recovery after a restart or a restore is a more complicated process. When a database is recovered, there\u2019s no guarantee about data on disk. Some committed transactions may need to be redone, and some in-flight transactions may need to be aborted and their changes rolled back. This occurs across multiple phases:<\/p>\n
- \n
- Analysis (figuring out changes that matter)<\/li>\n
- Redo (replaying changes)<\/li>\n
- Undo (reversing changes)<\/li>\n<\/ul>\n
Important side note here, since some misleading graphics are floating around: data modification only occurs for changes since the last checkpoint \u2013 earlier Redo activity is for locks. Here\u2019s roughly what Recovery looks like now, with gross omissions and oversimplifications.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-2.gif\")
<\/p>\nThe process has to go all the way back to the start of the oldest transaction, which, if you have junior developers, could be a very long time ago.<\/p>\n
Imagine it with row versions:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-3.gif\")
<\/p>\nNow Recovery only needs to start from the latest checkpoint, and the Undo phase is unnecessary, as queries can begin selecting data from the old versions (remember how versions helped with rollbacks). Even more exciting, since those log records before the checkpoint aren\u2019t needed, SQL Server can throw them out as soon as they\u2019re no longer required (read: after they\u2019re backed up).<\/p>\n
The potential benefits are now apparent: versioning can help with rollback, versioning can help with recovery, and versioning can even eliminate the risk of log file explosion during a large transaction.<\/p>\n
Three New Structures: PVS, ATM, and S-Log<\/h2>\n
The potential benefits of versioning are large \u2013 what does it take to make it a reality? Three new important structures emerge as solutions to important challenges, each of which is critical to understanding ADR. Other minor objects and a host of cleanup processes exist as well but are tied to these central additions.<\/p>\n
Persistent Version Store (PVS)<\/h3>\n
The first issue is with the original way versioning is implemented pre-2019: versions don\u2019t survive a restart. In database lingo, they\u2019re not durable. Versions live in tempdb, and when the server restarts, tempdb gets rebuilt. And that just won\u2019t do for recovery, since recovery is often from a restart that would have wiped tempdb clean, taking those critical versions with it.<\/p>\n
The solution to this is to make versions durable, or persistent, thus the name Persistent Version Store (PVS). Instead of being written to tempdb, they are instead stored in a table in the database, and their creation is logged to make them recoverable.<\/p>\n
Versions work mostly the same as before. Every row has transaction information, and if necessary, a pointer to the older version in the version store. I would visualize it like this:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-4.gif\")
<\/p>\nAborted Transaction Map (ATM)<\/h3>\n
Versioning sounds promising, but this leads to another missing piece. How does a query know which row to read in the presence of an aborted transaction? The solution is to track which transactions were aborted, not just which ones are ongoing. This way, every time a query looks at a row, it checks whether the row was modified by an aborted transaction. The list of aborted transactions is called the Aborted Transaction Map. It\u2019s implemented as a hash table, and I imagine it like below:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-5.gif\")
<\/p>\nSecondary Log (S-Log)<\/h3>\n
The solution is making good progress, but there\u2019s another problem (get used to this). Most transactions sent to SQL Server are composed of various smaller operations, and not everything can be versioned. For example, a large insert may add many rows which include versioning information, but will also require space allocations that get tracked in pages impossible to version. In the case of an operation that can\u2019t be versioned, there are two main options: 1) Make it a short, independent transaction and clean it up later if needed. 2) Log it anyways and follow the traditional Redo\/Undo recovery process.<\/p>\n
But following the traditional logging method, even for a subset of logged activity, runs the risk of undoing the progress. What happens to logs before a checkpoint? How will the database recover? In this case, Microsoft sets up a secondary structure in concert with the log file named the S-Log. The secret sauce for the S-Log is what happens during a checkpoint. SQL Server locates all S-Log records from before the checkpoint, and adds them all together after the checkpoint, making the old log records safe to truncate. Here\u2019s how I visualize it:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-6.gif\")
<\/p>\nOptimizing the New Structures<\/h2>\n
There are now sufficient pieces in place for functional Accelerated Database Recovery. Versions are made durable in the Persistent Version Store to survive restarts, aborted transactions are tracked in the Aborted Transaction Map, and the S-Log captures unversionable actions and guarantees they\u2019re accessible after the most recent checkpoint. These are the three main components: PVS, ATM, and S-Log.<\/p>\n
These structures all add overhead. Real-world performance matters, so how can they be optimized? Microsoft doesn\u2019t want a tagline of \u201cADR: It Just Runs Slower.\u201d<\/p>\n
PVS is probably the component with the most optimization. The first issue to be wary of is a case where repeated aborted transactions push the actual, committed row further and further down the version chain.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-4.png\")
<\/p>\nIn this situation, instead of aborted transactions pushing the last committed data further away, the updating query will check transaction status before making a new version. If the row on page belongs to an aborted transaction, the new row will overwrite it, while keeping the pointer to the older, committed row.<\/p>\n
Another optimization for PVS is in how it does versioning. Since creating a new version in the PVS pages takes a separate transaction and stores data on a separate page, it sometimes makes sense to store the version within the original row itself. This is done with a differential appended to the row, looking something like below (again, with criminal oversimplification).<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-7.gif\")
<\/p>\nMoreover, funneling all database versions into a single table sounds like a recipe for contention. Microsoft is aware of this, however, and say they partition accessors and preallocate space, in addition to making the PVS table append-only.<\/p>\n
The ATM has performance tweaks as well. A system with numerous rollbacks would experience a bloated, inefficient ATM. To mitigate this, sufficiently small transactions (measured by transaction log size) are rolled back immediately, instead of leaving transaction info in the map for later cleanup.<\/p>\n
Optimizations of the S-Log are focused on keeping it small \u2013 both by limiting what goes into it and by constant cleaning to minimize its memory footprint. Remember that the S-Log is for operations that aren\u2019t viable for versioning. Probably the most significant contributor to these would be allocation operations. In what should be a familiar strategy by now, these are instead committed immediately, with a background process tasked with undoing any mistakes. Deallocations can\u2019t be committed immediately, but are deferred instead of logged, again with a background process assigned for cleanup.<\/p>\n
The other big risk for S-Log explosion would come from locks. For various reasons, locks are logged in SQL Server. Under ADR, the number of these is reduced. Only the highest level locks are logged in the S-Log. To make sure recovery still works correctly during the Redo process, a new locking object is introduced to make sure no incorrect reads occur.<\/p>\n
Cleanup Processes<\/h2>\n
What happens to versions that are no longer needed? Will the Aborted Transaction Map continue to grow indefinitely? The size of these new structures has performance implications as well, so background cleanup tasks are required.<\/p>\n
You may have noticed the process called Logical Revert in Microsoft\u2019s explanation of ADR. Because it\u2019s inefficient to leave aborted rows hanging around in data pages, there\u2019s a cleanup process for this too. In the case where the data page contains an aborted row while the committed version is in the PVS (or an in-row version), Logical Revert will overwrite the aborted row with the committed, effectively moving the committed row back. I visualize it like this:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-8.gif\")
<\/p>\nOver time, the ATM will fill up, and a background task cleans it. For an aborted transaction to be removed from the map, all of its aborted rows left in pages need Logical Revert applied to them. Cleaning one transaction at a time would require a way to determine which pages a transaction touched. Log records would allow that, except in ADR, SQL Server is truncating log records early. Instead, what happens is a scan of the entire database \u2013 at least, the pages containing rows from aborted transactions. PFS pages now track which data pages contain aborted rows, allowing a faster sweep. Also, instead of worrying about constant additions to the ATM, a snapshot of it is taken at the beginning, and when the sweep completes, all transactions present in the snapshot are removed. What\u2019s that? Too many words? Fine, have an animation:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2019\/11\/word-image-9.gif\")
<\/p>\nPVS pages use their own hash map as well, but this stores the most recent transaction per page. The oldest transaction necessary for Snapshot Isolation and the oldest transaction in the ATM are both tracked. Any PVS page with a max transaction earlier than both of these is safe to clean up. Since the PVS is append-only, cleanup occurs by deallocating the page.<\/p>\n
The S-Log, as an in-memory structure, is also critical to keep lean. The in-memory records are needed for transactions that might rollback, while the on-disk logs are for recovery. As such, any S-Log for a transaction that can\u2019t roll back is eligible for removal. There are two cleanup processes here. The first occurs at checkpoints, where S-Log records for transactions older than the oldest active transaction are removed. The second is a background scan that looks for S-Logs related to inactive transactions and removes them.<\/p>\n
Summary<\/h2>\n
You can now review the key points:<\/p>\n
- \n
- Long rollbacks, long recovery, and bloated log files are problems<\/li>\n
- Versioning can solve these problems<\/li>\n
- To do so, it needs the PVS, ATM, and S-Log<\/li>\n
- These structures have optimizations to make them faster<\/li>\n
- These structures use numerous deferred cleanup processes<\/li>\n<\/ul>\n
I would also summarize as below. If you want to check yourself after reading this, try to recreate the grid from memory.<\/p>\n
\n\n
\n \n Major Structure<\/strong><\/p>\n<\/td>\n
\n PVS<\/strong><\/p>\n<\/td>\n
\n ATM<\/strong><\/p>\n<\/td>\n
\n S-Log<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n \n Purpose<\/p>\n<\/td>\n
\n Makes versions durable<\/p>\n<\/td>\n
\n Tracks Aborted Transactions<\/p>\n<\/td>\n
\n Allows logging for unversionable changes<\/p>\n<\/td>\n<\/tr>\n
\n \n Optimizations<\/p>\n<\/td>\n
\n In-row versions. Table design. Abort overwrites.<\/p>\n<\/td>\n
\n Short transaction rollbacks<\/p>\n<\/td>\n
\n Most unversionable changes moved to deferred cleanup.<\/p>\n<\/td>\n<\/tr>\n
\n \n Cleanup<\/p>\n<\/td>\n
\n Hash map transaction tracking, page deallocation.<\/p>\n<\/td>\n
\n Whole database sweep, PFS tracking, ATM snapshot<\/p>\n<\/td>\n
\n Background and checkpoint cleanup for unneeded S-Logs<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
By now, it should be obvious that ADR isn\u2019t a magic go-faster option, (nor is NOLOCK by the way, despite what certain developers seem to believe.) There\u2019s storage overhead, compute overhead, and a myriad of deferred cleanup processes.<\/p>\n
After the number of log-related incidents I\u2019ve suffered, I\u2019m ready to say these are tradeoffs I\u2019m willing to make. Moreover, I don\u2019t even get a say if I want to use some newer cloud offerings like Hyperscale. I believe there are two critical components to supporting a product: the ability to Google well and actually understanding how it works. Since Stack Exchange doesn\u2019t have answers on ADR failure modes yet, I\u2019ll do my best to be prepared, and I hope this serves as a useful primer for you too.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Accelerated Database Recovery is new with SQL Server 2019 and Azure SQL Database and used to decrease the time for rolling back large operations and database recovery. In this article, Forrest McDaniel explains how it works. …<\/p>\n","protected":false},"author":326180,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,143529],"tags":[5842],"coauthors":[101643],"class_list":["post-85708","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-performance-sql-server","tag-sql-monitor"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85708","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/326180"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=85708"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85708\/revisions"}],"predecessor-version":[{"id":85722,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85708\/revisions\/85722"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=85708"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=85708"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=85708"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=85708"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}