SQL Server 2019 and the Accelerated Database Recovery

Hi Guys,

First of all thanks for the many visits to this blog! These are really appreciated!
Today I want to tell you about another important feature introduced by SQL Server 2019 called Accelerated Database Recovery. 

Are you ready?

Let's start!

Introduction

A few weeks ago we already told about Database Recovery (Here).
We had analyzed what happens when the SQL Server engine crashes.
We did this by intentionally crashing SQL Server and then analyzing the situation through extended events.

We also looked at the various phases that SQL Server takes in order to restart.

In short, these three phases are:

1.Analysis:  During this phase the transaction log is read forward from the last checkpoint to determine then oldest dirty page.
Is also determined the status of all active transactions which will fall into one of two cases:

• Committed to the log but not already written to the database (this transations need to be redone)

• Uncommitted or already rollback (this transactions need to be undone)

2.Redo: During this phase the log is read forward starting from the oldest uncommitted transaction. Every transaction committed to the log  but not to the database in committed

3.Undo: During this phase the log is read backward from the end. Every transation still open or not committed to the log in rollbacked.

We also noticed how long it takes to complete the task!

Imagine now that you are the DBA of a well-known e-commerce site active 24 hours a day. If your SQL Server were to restart due to a crash or power failure, surely the time in which the data would remain offline during the restart would seem very very long!

The Accelerated Database recovery

With the new Accelerated Database recovery technique Microsoft has been looking for a way to make SQL Server restart faster.

To do this, some new concepts and components have been introduced:

1.The first new component is called Persisted Version Store (or PVS). The PVS contain the previous versions of data modified by transactions.For example, if you execute an update, previous version of each rows is written into the PVS.
To do this, a 14 byte pointer is added to each row of the database.

2.The Logical revert. During rollback of a transaction, active transactions read data directly from the PVS (and so not from the transaction log). In this way the rollback operation is more fast!

3.The sLog (where "s" stand for secondary) is a in-memory log stream that contain activities that are not written to the PVS.

4.The Cleaner is an asyncronous process that clean up unneeded row versions from the PVS.

Now looking at the at the illustration above:

We have again three phases, Analysis, Redo and Undo but with some differences.

• Analysis phase
  
  The process remains the same with the addition of reconstructing sLog and copying log records for non-versioned operations.
  
  
• Redo phase
  
  The Redo pahes is now splitted into two sub-phases (P)
  
  • Phase 1
    Redo from sLog from the oldest uncommitted transaction up to last checkpoint.
    Redo is a fast operation because it only needs to process few records from sLog.
    
  • Phase 2
    Redo from Transaction Log starts from last checkpoint up to the Log End (instead of oldest uncommitted transaction)
    
    
• Undo phase
  
  The Undo phase uses  sLog to undo non-versioned operations and Persisted Version Store (PVS) with Logical Revert to perform row level version-based Undo.
  In this way the Undo phase with ADR completes almost instantaneously. 
  

Well, all these new things promise really well!
But we want to try and for this reason we have organized a good road test!

The road test!

For our test, we first create two databases.

The first will be created normally and therefore without Accelerated Database Recovery

CREATE DATABASE NEW_DB_NO_ADR
      

In the second database, after creating it, we will activate the Accelerated Database recovery.

CREATE DATABASE NEW_DB_WITH_ADR 

ALTER DATABASE NEW_DB_WITH_ADR SET ACCELERATED_DATABASE_RECOVERY = ON

Now that we have our two databases we write a Query that can last a few minutes.

For example:

SELECT q2.* into dbo.table1 from sys.all_columns Q1 CROSS JOIN sys.all_objects Q2
       

We will execute it on both databases and during execution we will kill them and restart the instance.

Now let's take a look at the results

The database without the ADR activated is fully online in 215 seconds.

• For the analysis phase were needed: 11459 ms
• The Redo phase lasted 82942 ms
• The Undo phase lasted 118581 ms.

If we now look at the database with the ADR activated is fully online in just 12 seconds.  

WOW!

• For the analysis phase were needed: 11150 ms
• The Redo phase lasted 370 ms
• The Undo phase lasted 190 ms.

The duration of the redo and undo phases has actually dropped significantly that's very very good!

Now, we will not look at the many other informations coming from the extended events.
We will look instead to the size of then mdf and ldf files.

We can see as the ldf file has smaller dimension on the databases with the ADR activated.

See you soon! I hope you enjoyed this post!

Luca Biondi @ The Cutting Edge SQL Server Performance blog!

 

 

 

 

 

 

Next post: Write high performing query: Is your query SARGABLE?

Previous post: SQL Server, the Parametrization and the Parameter Sniffing