SQL SERVER – Getting Started with Accelerated Database Recovery – Instant Rollback

There are many reasons to envy my job as SQL Server Performance Tuning Expert Consultant but the most important reason is that I get to work on quite often the latest version of SQL Server. Just the other day I had an early adopter customer who was using SQL Server 2019 for their environment and they wanted me to help them with the new feature of Accelerated Database Recovery.

There are lots of discussions we can do about this subject but let me simply say this particular feature resolves one of the ancient problems when a long-running transaction is killed and stopped unexpectedly the rollback takes forever. The same problem is now resolved in SQL Server 2019. Please note this feature only works in SQL Server 2019 and the latest version of SQL Server and trying for earlier version will give you an error.

Instant Rollback

In today’s blog post we will see how the instant rollback works. In our experiment, we will create a big transaction and roll it back with the default configuration and the new configuration of Accelerated Database Recovery (ADR).

First, let us create a database. As the default of the database is to have ADR off, the database will be created as of how it behaved all the time.

-- Create the Database
-- Create a Test Database
CREATE DATABASE AccDB;
GO
USE AccDB
GO

Next, we will enable statistics time on to measure the time of the transactions.

SET STATISTICS TIME ON
GO

Test 1: Default Configurations

Now let us open the transaction and run a huge transaction. Measure the time taken by the transactions.

-- Set up a test 1
-- The default for Accelerated Database Recovery is OFF
-- Begin Transaction
BEGIN TRANSACTION

DROP TABLE IF EXISTS HugeTable;

SELECT TOP 10000000 a1.* 
INTO HugeTable 
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO

The time taken by the above query is like around 28 seconds.

/*
SQL Server Execution Times:
CPU time = 1485000 ms, elapsed time = 28213 ms.
*/

Now execute the ROLLBACK statement and measure the time as well.

ROLLBACK
GO

In my case, rollback takes around 13 seconds.

/*
SQL Server Execution Times:
CPU time = 317000 ms, elapsed time = 13804 ms.
*/

Test 2: Accelerated Database Recovery

While doing this test first we will change the database configuration as following to enable ADR to enable.

-- Set up a test 2
-- Change Accelerated Database Recovery to ON
ALTER DATABASE AccDB 
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

Next, once again we will do the same test by running the long transactions. Measure the time taken by the transactions.

-- Begin Transaction
BEGIN TRANSACTION

DROP TABLE IF EXISTS HugeTable;

SELECT TOP 10000000 a1.* 
INTO HugeTable 
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO

The time taken by the above query is like around 28 seconds.

/*
 SQL Server Execution Times:
   CPU time = 1485000 ms,  elapsed time = 28213 ms.
*/

Now execute the ROLLBACK statement and measure the time as well.

ROLLBACK
GO

In my case, rollback takes around 0 seconds.

/*
SQL Server Execution Times:
<strong>CPU time = 0 ms, elapsed time = 0 ms.</strong>
*/

You will notice that the when ADR is enabled there is absolutely no time taken by the rollback process and it is actually absolutely instant.

Here is how you can clean up all the work which we have done so far.

-- Cleanup the Database
USE master
GO
ALTER DATABASE AccDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE AccDB
GO

Final Words

I am very much impressed by this feature but this feature should not be enabled by every single database. If you have a long-running transaction and often that creates a problem with growing logs and frequent long-running recovery scenarios.

Reference: Pinal Dave (https://blog.sqlauthority.com)