Over the years Microsoft developed several processes that work together to ensure the durability of data. Data durability, in this case, means ensuring that in case of a system failure, all completed transactions are retained and all incomplete transactions are rolled back. This ensures that committed data is not lost during system failure. These processes have been in use for quite a long time and play a critical role in SQL Server.
In October of 2018, Microsoft announced Accelerated Database Recovery (ADR) and it was one of the most interesting topics of that year’s PASS Summit. ADR is a redesign of the SQL engine’s recovery process. It represents a big change from the long-established model and represents a big boost to recovery time.
Due to the changes in logging, only certain operations require log space. This leads to efficient log usage and aggressive truncation. This means reduced log growth and better manageability in addition to fast recovery. Systems that have very long running transactions, high log growth and long recovery will benefit greatly from ADR.
Before looking into the updated recovery process, its worth taking a quick look at how the current three-step recovery process works.
When SQL Server starts up, the following steps are performed before a database is available:
SQL Server reads the database transaction log from the latest checkpoint to the end of the log. This establishes potential dirty pages (pages that had been modified in memory but not persisted to disk).
SQL Server then begins replaying the log from the oldest uncommitted transaction to the end of the of the log, redoing all transactions up to the point of the crash. This brings the database to the point of the crash.
The final step of recovery, SQL Server traverses the log backwards, from the end to the oldest uncommitted transaction, undoing any transactions that were still active during the crash.
While this process has served SQL Server well for years, it is not without its drawbacks. Because transactions are replayed, the recovery time is relative to the largest transaction. If the database is subject to large, long running transactions, recovery time can be equally as long.
At its core, ADR still uses the three-step recovery process. The new mechanisms ADR introduces substantially improve crash recovery and startup, as well as transaction rollback and transaction log management.
The new mechanisms and process are described below:
In ADR, SQL Server stores row versions. In isolation levels that leverage versioning, these are stored in tempdb. In ADR, these versions are stored in the database itself. Although this can increase database size, this is an enhancement that allows versioning in readable secondaries.
This is the mechanism for handling the undo section of recovery from the PVS.
Slog is an in-memory secondary log stream. Slog only contains non-versioned operations such as lock acquisitions, metadata cache invalidation, etc. The number of transactions stored in slog is very small compared to the normal transaction log. It is persisted to disk during checkpoint process. The slog is key to ADR.
The cleaner process is responsible for removing unneeded row versions.
So what does this mean in practice? In short, the new process is very similar but a bit busier than traditional recovery. Here is the new ADR Process:
This is identical to standard recovery, except the slog is rebuilt during the process.
Redo from the oldest uncommitted transaction only has to redo the slog. Because these are considerably fewer in number, this process is very fast. Traditional redo then resumes from the latest checkpoint.
The undo process is also greatly improved. Because slog is in memory, undoing those are very fast. Because rows are stored in the PVS, Logical Revert can quickly undo any traditional transactions.
Microsoft had a great application example during Pass Summit. Check out the video here:
Lastly, if your organization is looking to learn more about SQL database recovery options, please reach out to us. We’d love to help you out.
SQL Server Architect
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
|cookielawinfo-checbox-analytics||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".|
|cookielawinfo-checbox-functional||11 months||The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".|
|cookielawinfo-checbox-others||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.|
|cookielawinfo-checkbox-necessary||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".|
|cookielawinfo-checkbox-performance||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".|
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.