SQL Server 2022: Accelerated Database Recovery enhancements from SQL 2019 to SQL 2022

Hi Guys,

 

Welcome back! You Start to be really many to read this blog! Just two words: thank you very much!

Today we will talk again about the Accelerated Database Recovery (ADR) feature, we talked about the same topic already 4 years ago here SQL Server 2019 and the Accelerated Database Recovery.

Yeah, it’s been four years. In 2019 we described in detail what ADR is, now it’s time to talk about what improvements this feature has in SQL 2022.

I want to remind you that SQL 2022 is the latest version of the famous RDBMS produced by Microsoft.

 

Accelerated Data Recovery: a short summary!

As we have seen some time ago, the ADR has been introduced in SQL 2019 (15.x) with the obective of improving the database availability, especially in the presence of long running transactions. 

To achieve this, the database engine recovery process has been redesigned.

We do not have to scan the transaction log from and to the beginning of the oldest active transaction instead our log is read only from the last checkpoint and for this reason the recovery time is not influenced by long-running transaction. 

Yes this is a very cool feature introduced by the 2019 release of the SQL Server!

In addition, the fact that it is no longer necessary to process the log for the entire transaction, makes the required space in the log considerably reduced.

Without going down in major details (for now) we get this behavior by versioning all physical database modifications and only undoing logical operations, which are limited and can be undone almost instant. All transactions that were active at the time of a crash are marked as broken, and therefore any version generated by these transactions can be ignored by simultaneous user queries.

 

SQL 2022 and ADR improvements

Speaking of ADR the new SQL Server 2022 bring us many improvements.

While in SQL Server 2019 (15.x) the ADR cleanup process is single threaded within a SQL Server instance with the new SQL Server 2022 (16.x) this process uses became multi-threaded and now it is called multi-threaded version cleanup (MTVC).

Now multiple databases in the same SQL Server instance are cleaned in parallel.

Even if MTVC is enabled by default it uses one thread per SQL instance. In order to use more thread you need to set the ADR Cleaner Thread Count value with sp_configure.

However, even if you configure the ADR Cleaner Count to a higher value in a sql instance, the ADR cleaner will only assign one thread per database, the remaining threads remain inactive.

If the Multi-threaded version cleanup is the first improvement, the second is called User transaction cleanup.

User transaction cleanup allows user transactions to perform page cleaning that cannot be addressed by the regular cleaning process due to lock conficts, in this way the ADR cleanup process is more efficient. 

But the news didn’t end here.

Persisted version store (PVS) pages are tracked at the extent level to reduce the memory needed to manage versioneg pages. 

Then, we have improvement on the Accelerated Data Recovery cleaner. 

Accelerated Data Recovery cleaner has been improved. Some improvements have been implemented in the way SQL Server tracks and records aoorted versions of a page. This has lead to an optimized memory usage.

Finally, we have also a changes in the persisted version store. We can say that now clean up happens at  transaction-level. Now infact ADR can clean up versions belonging to committed transactions independent of whether there are aborted transactions in the system. 

PVS pages can be deallocated, even if the cleanup cannot complete a successful sweep to trim the aborted transaction map. This leads to a reduced PVS growth even if ADR cleanup is slow or fails.

To track these changes a new extended event called tx_mtvc2_sweep_stats has been addedon the ADR PVS multi-threaded version cleaner.

End!

Probably next time i will present a performance comparison between SQL Server 2017 (NO ADR), SQL Server 2019 (ADR) and SQL Server 2022 (improved ADR).

So if you are interested ...You just have to stay tuned!

That's all for now!
~Luke



































Comments

  1. AnonymousApril 10, 2023 at 1:26 PM

    Great…excellent post. Congratulation for share information…👏🏻

    ReplyDelete
  2. ThanmaiJuly 5, 2023 at 3:49 AM

    Thank you for sharing the valuable article.
    Best Supply Chain Optimization Software Services Company/a>

    ReplyDelete
  3. renukaApril 1, 2024 at 9:47 PM


    Very good article . Thanks for sharing.
    Snowflake Training
    Snowflake Training in Hyderabad
    Snowflake Online Training
    Snowflake Online Training Hyderabad
    Snowflake Training Online
    Snowflake Training in Ameerpet
    Snowflake Training Institute in Hyderabad

    ReplyDelete

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?    I
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo
Read more

How to solve EXECUTE Permission denied on object 'sp_send_dbmail'

Image
Hi guys, Welcome back ! Today a light five minute post to talk about this error: EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'. ..or in italian language: Autorizzazione execute negata per l'oggetto 'sp_send_dbmail' del database 'msdb' con schema 'dbo' The sp_send_dbmail You know that this stored procedure is used to send a mail through SQL server. You know also that this stored procedure is contained into the MSDB system Databases. But if you get the error above? What can you do? How to solve the execute permission denied on sp_send_dbmail You should consider that: Your user must be in the MSDB database.  Your user must be also member of the DatabaseMailUserRole .  You can solve the problem in this way: 1) In the Object Explorer of the SQL Server management Studio, locate the Security / Account menu. Locate your user and do a double click on it. 2) In the opene
Read more