2020-07-10

SQL Server 2019 great new feature - Accelerated Database Recovery (ADR)

This month blog post I would like to introduce a great new feature for SQL Server 2019, Accelerated Database Recovery (ADR), and let's see how fast a rollback can be by enabling ADR to speed it up. This experiment uses the infamous WideWorldImporters sample database.
Firstly let's measure the update and rollback time for the following update 236667 rows involved:
USE WideWorldImporters;
SELECT COUNT(*) FROM Warehouse.StockItemTransactions;
BEGIN TRAN
DECLARE @updateStartTime datetime = GETDATE();
UPDATE Warehouse.StockItemTransactions SET SupplierID = 1;
DECLARE @updateEndTime datetime = GETDATE();
ROLLBACK
DECLARE @rollbackEndTime datetime = GETDATE();
SELECT DATEDIFF(millisecond, @updateStartTime, @updateEndTime) updateTime, DATEDIFF(millisecond, @updateEndTime, @rollbackEndTime) rollbackTime;

Without ADR, it takes more than 12 seconds to rollback the update statement.










Enable ADR on the WideWorldImporters database by executing below alter database statement:
USE master
GO
ALTER DATABASE WideWorldImporters SET ACCELERATED_DATABASE_RECOVERY = ON;

You can verify ADR really enabled by checking the server error log:


Run the update statement again, you will see the rollback is instantaneous!


Now we SQL Server database administrators have a great reason to convince our boss to pay for a software upgrade :D

No comments:

Post a Comment