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