SQL Server 2019 Accelerated Database Recovery – Instantaneous rollback and aggressive log truncation

In my previous article about Accelerated Database Recovery (ADR), I wrote mostly about the new Persistent Volume Store (PVS), how important it was important in the new SQL database engine recovery process and the potential impact it may have on the application workload. This time let’s talk a little bit more about ADR feature benefits we may get with instantaneous rollback and aggressive log truncation. These two capabilities will address some DBA pains especially when rollback or crash recovery kick in with open long running transactions.

First, let’s set the context by running the following long running transaction without ADR enabled:

BEGIN TRAN;
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO
ROLLBACK TRAN;

The above query generates 10GB of log records (roughly 90% of the total transaction log space size) as shown below:

SELECT 
	DB_NAME(database_id) AS database_name,
	total_log_size_in_bytes / 1024 / 1024 / 1024 AS total_GB,
	used_log_space_in_bytes / 1024 / 1024 / 1024 AS used_GB,
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage

Before cancelling my previous query to trigger a rollback operation, let’s run the following concurrent update:

BEGIN TRAN;

DECLARE @begin_date DATETIME = GETDATE();

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1
Where TransactionID = 1;

DECLARE @end_date DATETIME = GETDATE();

SELECT DATEDIFF(SECOND, @begin_date, @end_date);

As expected, the second query is blocked during the rollback process of the first one because they compete on the same resource:

SELECT 
	spid,
	blocked,
	lastwaittype,
	waitresource,
	cmd,
	program_name
FROM sys.sysprocesses
WHERE spid IN (64, 52)

In my case, the second query was blocked during 135s. Regarding your scenario, it could be less or more. I experienced this annoying issue myself at some customer shops and I’m pretty sure it is the case of many of SQL Server DBAs.

Let’s now perform the same test after enabling ADR. Executing the query below (used in my first test) gave interesting results.

BEGIN TRAN;
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

First, rolling back the transaction was pretty instantaneous and the concurrent query executed faster without being blocked by ROLLBACK process. This is where the logical revert comes into play. As stated to the Microsoft documentation, when rollback is triggered all locks are released immediately. Unlike the usual recovery process, ADR uses the additional PVS is to cancel operations for identified aborted transactions by restoring the latest committed version of concerned rows. The sys.dm_tran_aborted_transactions DMV provides a picture of aborted transactions:

SELECT *
FROM sys.dm_tran_aborted_transactions;
GO

For a sake of curiosity, I tried to dig further into the transaction log file to compare rollback operations between usual recovery process and ADR-based recovery process. I used a simpler scenario including a simple dbo.test_adr table with one id column and that consists in insert 2 rows and updating them afterwards. To get log record data, the sys.fn_db_log function is your friend.

CREATE TABLE dbo.test_adr (
	id INT
);

CHECKPOINT;

BEGIN TRAN;

INSERT INTO dbo.test_adr ( id ) VALUES (1), (2);

UPDATE dbo.test_adr SET id = id + 1;

ROLLBACK TRAN;

SELECT 
	[Current LSN]
	,Operation
	,Context
	,[Transaction ID]
	,[Lock Information]
	,[Description]
       ,[AllocUnitName]
FROM sys.fn_dblog(NULL, NULL);

Without ADR, we retrieve usual log records for rollback operations including compensation records and the transaction’s end rollback mark. In such case, remind the locks are released only at the end of the rollback operation (LOP_ABORT_XACT). 

With ADR, the story is a little bit different:

Let’s precise it is only a speculation stuff from my own here and I just tried to correlate information from the Microsoft documentation. So, don’t take my word for it. When a transaction is roll backed, it is marked as aborted and tracked by the logical revert operation. The good news is that locks are immediately released afterwards. My guess is that the LOP_FORGET_XACT record corresponds to the moment when the transaction is marked as aborted and since this moment no blocking issues related to the ROLLBACK can occur. At the same time the logical revert is an asynchronous process and comes into play by providing instantaneous transaction rollback and undo for all versioned operations by using the PVS. 

Second, reverting to this first test scenario …

BEGIN TRAN;
UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

UPDATE dbo.bigTransactionHistory
SET Quantity = Quantity + 1;
GO

… I noticed the transaction log file space was used differently and even less compared to my first previous test without ADR enabled. I performed the same test several times and I got results in the same order of magnitude.  

I got some clues by adding some perfmon counters:

• SQL Server databases:PVS in-row diff generated/sec
• SQL Server databases:PVS off-row records generated/sec
• SQL Server databases:Percent Log Used
• SQL Server Buffer Manager:Checkpoints/sec

My two update operations use different storage strategies for storing row versions. Indeed, in the first shot, row versions fit in the data page whereas in the second shot SQL Server must go through the off-row storage to store additional versions. In addition, we are also seeing interesting behavior of the ADR sLog component with the aggressive log truncation at the moment of the different checkpoint operations. Due to the changes in logging, only certain operations require log space and because the sLog content on every checkpoint operation it makes possible aggressive log truncation.

In my case, it led to keep under control the space used by my long running transaction even if it is in open state.

In this blog post we’ve seen how ADR may address database unavailability paint point through instantaneous rollback and aggressive log truncation. Good to know that we may get benefit from such features in SQL Server 2019!

See you!

By David Barbarin