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)
2 Comments. Leave new
Is this an enterprise edition only feature?
How do you check if ADR is enabled or not?