An Overview of Traditional Recovery
As with all relational database systems, SQL Server guarantees the durability of data by implementing crash recovery. Durability in the acronym ACID which refers to the characteristics of transactions in relational databases means that we can be assured that if the database fails suddenly, our data is safe.
SQL Server implements this capability using the transaction log. Changes made by all Data Manipulation Operations in SQL Server are captured in the transaction log before being applied to data files (through the checkpoint process) in case it’s needed to roll back or roll forward.
The three-phase crash recovery process in SQL Server is as follows:
-
Analysis – SQL Server reads the transaction log from the latest checkpoint to the end of the transaction log
-
Redo – SQL Server replays the log from the oldest uncommitted transaction to the end of the log
-
Undo – SQL Server reads the log from the end of the log to the oldest uncommitted transaction and reverts all transactions that were active during the crash
Experienced DBAs would have at some point or the other in their careers had the disheartening experience of waiting helplessly for crash recovery to be completed on a very large database. Transaction ROLLBACK uses a similar mechanism as the crash recovery process. Microsoft has enhanced the recovery process significantly in SQL Server 2019.
Accelerated Database Recovery
Accelerated Database Recovery is a new feature based on versioning that significantly increases the rate of recovery in the case of a ROLLBACK or recovery from a crash.
In SQL Server 2019, three new mechanisms within the SQL Server engine modify the way in which recovery is handled and effectively reduce the time required to perform a rollback/rollforward.
-
Persistent Version Store (PVS) – Captures row versions within the database in question. The Persistent Version Store can be defined in a separate file group for performance or size reasons
-
Logical Revert – Uses the row versions stored in PVS to perform rollback when a rollback is invoked for a particular transaction or when the undo phase of crash recovery is invoked.
-
sLog – This possibly stands for secondary log. It’s an in-memory log stream used to capture operations that cannot be versioned. When ADR is enabled in the database, the sLog is always rebuilt during the analysis phase of crash recovery. During the redo phase, the sLog is used rather than the actual transaction log making the process faster since it sits in memory and contains fewer transactions. The traditional recovery process handles transactions from the last checkpoint. The sLog is also used during the undo phase.
-
Cleaner – Removes unnecessary row versions from the PVS. Microsoft also provides a stored procedure to manually force a cleanup of unnecessary row versions.
-- LISTING 1: INVOKE THE BACKGROUND CLEANER USE TSQLV4_ADR GO EXECUTE sys.sp_persistent_version_cleanup; USE master GO EXECUTE master.sys.sp_persistent_version_cleanup 'TSQLV4_ADR';
Accelerated Database Recovery is Turned OFF by Default
The fact that ADR is turned off in SQL Server 2019 by default might seem surprising to some DBAs given that it appears to be such a great feature. ADR uses versioning in the user database in which it is enabled. This can impact the database size significantly. In addition, you may need to plan for the database growth as well as the possible location of the PVS in order to ensure good performance if ADR is enabled. So it makes sense to deliberately enable this functionality.
The Experiment: Preparatory Phase
We set up an experiment to explore the new feature and see the impact of ADR on the size of the transaction log as well as on the speed of ROLLBACK. In our experiment, we create two identical databases using a single backup set and then we enable ADR only on one of these databases. Listing 2 shows the preparatory stages for the task.
[expand title =”Code”]
-- LISTING 2: PREPARE THE DATABASES AND CONFIGURE ADR
-- 2a. Backup a sample database and restore as two identical databases
BACKUP DATABASE TSQLV4 TO DISK='TSQLV4.BAK' WITH COMPRESSION;
-- Restore Database TSQLV4_NOADR (ADR will not be enabled)
RESTORE DATABASE TSQLV4_NOADR FROM DISK='TSQLV4.BAK' WITH
MOVE 'TSQLV4' TO 'C:\MSSQL\DATA\TSQLV4_NOADR.MDF',
MOVE 'TSQLV4_log' TO 'E:\MSSQL\LOG\TSQLV4_NOADR_LOG.LDF';
-- Restore Database TSQLV4_ADR (ADR will be enabled)
RESTORE DATABASE TSQLV4_ADR FROM DISK='TSQLV4.BAK' WITH
MOVE 'TSQLV4' TO 'C:\MSSQL\DATA\TSQLV4_ADR.MDF',
MOVE 'TSQLV4_log' TO 'E:\MSSQL\LOG\TSQLV4_ADR_LOG.LDF';
-- 2b. Enable ADR in TSQLV4_ADR
USE [master]
GO
-- First create a separate filegroup and add a file to the filegroup
ALTER DATABASE [TSQLV4_ADR] ADD FILEGROUP [ADR_FG];
ALTER DATABASE [TSQLV4_ADR] ADD FILE ( NAME = N'TSQLV4_ADR01', FILENAME = N'C:\MSSQL\Data\TSQLV4_ADR01.ndf' ,
SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [ADR_FG]
GO
-- Enable ADR
ALTER DATABASE TSQLV4_ADR SET ACCELERATED_DATABASE_RECOVERY = ON (PERSISTENT_VERSION_STORE_FILEGROUP = ADR_FG);
GO
-- 2c. Check if all ADR is enabled as planned
SELECT name
, compatibility_level
, snapshot_isolation_state_desc
, recovery_model_desc
, target_recovery_time_in_seconds
, is_accelerated_database_recovery_on FROM SYS.DATABASES
WHERE name LIKE 'TSQLV4_%';
-- 2d. Check sizes of all files in the databases
SELECT DB_NAME(database_id) AS database_name
, name AS file_name
, physical_name
, (size * 8)/1024 AS [size (MB)]
, type_desc
FROM SYS.master_files
WHERE DB_NAME(database_id) LIKE 'TSQLV4_%';
-- 2e. Check size of log used
CREATE TABLE ##LogSpaceUsage (database_name VARCHAR(50)
, database_id INT, total_log_size_in_bytes BIGINT
, used_log_space_in_bytes BIGINT
, used_log_space_in_percent BIGINT
, log_space_in_bytes_since_last_backup BIGINT)
INSERT INTO ##LogSpaceUsage
EXEC sp_MSforeachdb @command1='
IF ''?'' LIKE ("TSQLV4_%")
SELECT DB_NAME(database_id), * FROM ?.SYS.dm_db_log_space_usage;'
SELECT * FROM ##LogSpaceUsage;
DROP TABLE ##LogSpaceUsage;
[/expand]
Fig. 1 shows the output of the SQL statement in Listing 2 section 2c. We also captured the size of the database files and the transaction log file usage. (see Fig. 3).
Fig. 1 Confirm ADR is Configured
Fig. 2 Review Database Data File Sizes
Fig. 3 Check the Size of Log Used for Both Databases
The Experiment: Execution Phase
Once we have captured the details we need to proceed, we then execute the SQL code from Listings 3 and 4 in stages. The two listings are equivalent, but we are executing them on two identical databases separately. First, we do an INSERT (Listing 3, 3a), then we perform a DELETE (Listing 3, 3b) which we will subsequently roll back. Notice that in both the INSERT and the DELETE, we have encapsulated the operations in transactions. Also, take note that the INSERT is executed 50 times. At each stage of execution, i.e. between 3a, 3b, and 3c, we capture the transaction log usage with the help of the code in Listing 2,2e. This is same for the sections 4a, 4b, and 4c.
-- LISTING 3: EXECUTE DML IN TSQLV4_NOADR DATABASE -- 3a. Execute INSERT Statement in TSQLV4_NOADR Database USE TSQLV4_NOADR GO BEGIN TRAN SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT * INTO [Sales].[OrderDetails_noadr] FROM [Sales].[OrderDetails]; GO INSERT INTO [Sales].[OrderDetails_noadr] SELECT * FROM [Sales].[OrderDetails]; GO 50 COMMIT; -- 3b. Execute DELETE in TSQLV4_NOADR Database USE TSQLV4_NOADR GO BEGIN TRAN SET STATISTICS IO ON; SET STATISTICS TIME ON; DELETE FROM [Sales].[OrderDetails_noadr] GO -- 3c. Perform Rollback and Capture Time ROLLBACK;
Fig. 4 and 5 show us that the SELECT INTO operation took 6 milliseconds more in the TSQLV4_ADR database where we enabled Accelerated Database Recovery. We also see in Fig. 6 that we have greater transaction log usage in the TSQLV4_ADR database. I was particularly surprised at this, so I repeated the experiment several times to ensure I was getting this result consistently.
Fig. 4 Insert Execution Time for TSQLV4_NOADR
Fig. 5 Insert Execution Time for TSQLV4_ADR
Fig. 6 Transaction Log Usage After Inserts
-- LISTING 4: EXECUTE DML IN TSQLV4_ADR DATABASE -- 4a. Execute INSERT Statement in TSQLV4_ADR Database USE TSQLV4_ADR GO BEGIN TRAN SET STATISTICS IO ON; SET STATISTICS TIME ON; SELECT * INTO [Sales].[OrderDetails_adr] FROM [Sales].[OrderDetails]; GO INSERT INTO [Sales].[OrderDetails_adr] SELECT * FROM [Sales].[OrderDetails]; GO 50 COMMIT; -- 4b. Execute DELETE in TSQLV4_ADR Database USE TSQLV4_ADR GO BEGIN TRAN SET STATISTICS IO ON; SET STATISTICS TIME ON; DELETE FROM [Sales].[OrderDetails_adr] GO -- 4c. Perform Rollback and Capture Time ROLLBACK;
Fig. 7 and 8 show us that the DELETE operation took considerably more time to be completed in the TSQLV4_ADR database where we enabled Accelerated Database Recovery even though the same number of rows was deleted in both databases. This time around, however, we have greater transaction log usage in the TSQLV4_NOADR database.
Fig. 7 Delete Execution Time for TSQLV4_NOADR
Fig. 8 Delete Execution Time for TSQLV4_ADR
Fig. 9 Transaction Log Usage After Deletes
By now it was becoming obvious that DML operations take longer in databases with ADR enabled. This partly explains why the feature is off in the first place. Deeply thinking about it, it makes sense since SQL Server must store the row versions in the PVS while an insert, update, or delete operation is running. Whatever amount of time the DML takes, we find that issuing a ROLLBACK with ADR turned ON takes less than 1 millisecond (see Figs. 10 – 13). In some cases, the quick rollback can compensate for the overhead of the DML itself, but not in all cases!
Fig. 10 Execution Time for ROLLBACK (After DELETE) on TSQLV4_NOADR
Fig. 11 Execution Time for ROLLBACK (After DELETE) on TSQLV4_ADR
Fig. 12 Execution Time for ROLLBACK (After INSERT) on TSQLV4_NOADR
Fig. 13 Execution Time for ROLLBACK (After DELETE) on TSQLV4_ADR
Conclusion
Accelerated Database Recovery is one of the great features released in SQL Server 2019. However, as with all extremely nice things in life, someone has to pay for it. ADR can have a negative performance impact in certain scenarios, so it’s important to evaluate your scenario carefully before implementing ADR in your production database. Microsoft specifically recommends Accelerated Database Recovery for databases supporting workloads with very long-running transactions, excessive transaction log growth, or frequent outages related to a long-running recovery.
