SQL Server Accelerated Database Recovery

Mit Version 2019 hat Microsoft das Feature Accelerated Database Recovery bzw. ADR eingeführt, das die Wiederherstellung der Datenbanken nach einem Absturz einer SQL Server Instanz beschleunigen soll. In einem offiziellen Video von Microsoft fällt die Formulierung, dass es Transaktionen gibt, die Stunden benötigen - an einer anderen Stelle wird erwähnt, dass ein Rollback in einer Kundenumgebung nahezu 0 Sekunden benötigt hat. In dem selben Video wird auch erwähnt, dass das neue Verfahren “way faster” sei.

Dieser Beitrag zeigt wie ADR eingerichtet wird und gibt einige Metriken wieder, die im Verlauf mehrerer Testreihen bei uns gesammelt wurden. Es gibt Ihnen eine Idee, ob dieses neue Feature in Ihrem Umfeld sinnvoll eingesetzt werden kann.

Um Missverständnisse zu vermeiden: Es geht hier nicht um das Thema Restore von Datenbanken, sondern darum, dass im Fall eines Instanz-Absturzes während Transaktionen in der Datenbank aktiv sind, bei einem Neustart der Instanz die noch nicht abgeschlossenen Transaktionen zurückgerollt werden.

Setup

Wie in anderen unserer Beiträge wird auch hier eine der Microsoft Testdatenbanken verwendet, die unter learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure heruntergeladen werden können, nämlich die AdventureWorks2019. Nach dem Download wird diese in einer SQL Server 2022 Instanz in vier Varianten eingerichtet:

KonfigurationOhne ADR mit Kompatibilitätslevel 150 (MSSQL 2019)mit ADR mit Kompatibilitätslevel 150 (MSSQL 2019)ohne ADR mit Kompatibilitätslevel 160 (MSSQL 2022)mit ADR mit Kompatibilitätslevel 160 (MSSQL 2022)

ADR verwendet eine Komponente mit der Bezeichnung Persisted Version Store oder kurz PVS die, sofern nichts entsprechend anderes bei der Konfiguration angegeben wird, in der Primary Filegruppe liegt. Alternativ (und wie später zu sehen ist) performanter, kann eine separate Filegruppe erstellt werden, die ausschließlich für PVS vorgesehen ist. Wir haben unsere Tests mit beiden Optionen durchgeführt. Falls sie diese Tests nicht nachvollziehen möchten, können Sie die Statements zum Anlegen der jeweiligen Filegruppe einfach weglassen.

Setup der vier Datenbanken

Der folgende SQL Block legt die vier Datenbanken unter Verwendung des heruntergeladenen Backups an, das im Verzeichnis c:\Backups liegt.

USE [master]
RESTORE DATABASE [AW2019_150] FROM  DISK = N'c:\Backups\AdventureWorks2019.bak' WITH  FILE = 1
,  MOVE N'AdventureWorks2019' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\DATA\AW2019_150.mdf'
,  MOVE N'AdventureWorks2019_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\LOGS\AW2019_150_log.ldf'
GO

RESTORE DATABASE [AW2019_150_AR] FROM  DISK = N'c:\Backups\AdventureWorks2019.bak' WITH  FILE = 1
,  MOVE N'AdventureWorks2019' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\DATA\AW2019_150_AR.mdf'
,  MOVE N'AdventureWorks2019_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\LOGS\AW2019_150_AR_log.ldf'
GO

RESTORE DATABASE [AW2019_160] FROM  DISK = N'c:\Backups\AdventureWorks2019.bak' WITH  FILE = 1
,  MOVE N'AdventureWorks2019' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\DATA\AW2019_160.mdf'
,  MOVE N'AdventureWorks2019_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\LOGS\AW2019_160_log.ldf'
GO

RESTORE DATABASE [AW2019_160_AR] FROM  DISK = N'c:\Backups\AdventureWorks2019.bak' WITH  FILE = 1
,  MOVE N'AdventureWorks2019' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\DATA\AW2019_160_AR.mdf'
,  MOVE N'AdventureWorks2019_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\LOGS\AW2019_160_AR_log.ldf'
GO

Die folgenden Befehle bringen die eingerichteten Datenbanken, die sich im Simple Recovery Modus befinden in den Full Recovery Modus.

Use [master]
GO
ALTER DATABASE [AW2019_150] SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [AW2019_150_AR] SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [AW2019_160] SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [AW2019_160_AR] SET RECOVERY FULL WITH NO_WAIT

Tests mit Kompatibilitätslevel 150 und 160

Microsoft spricht von einer deutlichen Verbesserung bei der Verwendung von ADR in MSSQL 2019 (Kompatibilitätslevel 150) zu der in MSSQL 2022 (Kompatibilitätslevel 160), sodass mit dem folgenden Codeblock zwei der Datenbanken auf Level 160 gebracht werden.

ALTER DATABASE [AW2019_160] SET COMPATIBILITY_LEVEL = 160
ALTER DATABASE [AW2019_160_AR] SET COMPATIBILITY_LEVEL = 160

Verschiedene Testreihen

Es wurden verschiedene Testreihen durchgeführt. Eine dieser Testreihen benutzte eine separate Filegruppe für PVS, die mit dem folgenden Befehl eingerichtet wird:

ALTER DATABASE [AW2019_150_AR] ADD FILEGROUP [VS_AW2019_150_AR];
GO
ALTER DATABASE [AW2019_150_AR] ADD FILE ( NAME = N'VS_AW2019_150_AR'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\DATA\VS_AW2019_150_AR.ndf'
, SIZE = 65536KB , FILEGROWTH = 65536KB )
TO FILEGROUP [VS_AW2019_150_AR];
GO

ALTER DATABASE [AW2019_160_AR] ADD FILEGROUP [VS_AW2019_160_AR];
GO
ALTER DATABASE [AW2019_160_AR] ADD FILE ( NAME = N'VS_AW2019_160_AR'
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MADAFA_DEV\MSSQL\DATA\VS_AW2019_160_AR.ndf'
, SIZE = 65536KB , FILEGROWTH = 65536KB )
TO FILEGROUP [VS_AW2019_160_AR];

Trigger Deaktivierung

Zur Durchführung länger andauernder Transaktionen wurde die größte in der DB vorhandene Tabelle (Sales.SalesOrderDetail, 121317 Zeilen) ausgewählt, in der Datenänderungen mit mehreren parallelen Sessions durchgeführt wurden. Diese Tabelle hatte einen Trigger zugeordnet, der im Zusammenhang mit den vielen parallelen Sessions zu Exceptions führte, sodass dieser Trigger jeweils mit dem folgenden Codeblock deaktiviert wurde:

alter table AW2019_150.Sales.SalesOrderDetail disable trigger iduSalesOrderDetail
alter table AW2019_150_AR.Sales.SalesOrderDetail disable trigger iduSalesOrderDetail
alter table AW2019_160.Sales.SalesOrderDetail disable trigger iduSalesOrderDetail
alter table AW2019_160_AR.Sales.SalesOrderDetail disable trigger iduSalesOrderDetail

ADR Aktivierung

Mit dem folgenden Codeblock wird das Accelerated Database Recovery Feature aktiviert. Es handelt sich hierbei um eine Aktion, die im laufenden Betrieb durchgeführt werden kann. In verschiedenen Tests hat sich ergeben, dass die Aktivierung u.U. extrem lange dauern kann. Schneller ging es, wenn man zuvor einen Checkpoint erstellte, so dass die folgenden Zeilen verwendet wurden:

USE [AW2019_150_AR]
GO
checkpoint
ALTER DATABASE [AW2019_150_AR] SET ACCELERATED_DATABASE_RECOVERY = ON

USE [AW2019_160_AR]
GO
checkpoint
ALTER DATABASE [AW2019_160_AR] SET ACCELERATED_DATABASE_RECOVERY = ON

Deaktivierung der TLog-Backups

Während der Durchführung der Testreihen wurden die Backups der TLogs deaktiviert, weil diese das Wachstum der TLogs beeinflussen: Nach dem TLog-Backup können Transaktionsinformationen wieder überschrieben werden. Das ist zwar im Allgemeinen ein positiver Effekt, hier aber wegen der Auswirkung auf das Wachstum und die Performance nicht gewünscht.

Überblick über die Konfiguration

Der folgende Block fasst die durchgeführten Einstellungen zusammen:

select name
     , compatibility_level
	 , recovery_model
	 , is_accelerated_database_recovery_on as is_ADR_on
from sys.databases where name like 'AW2019%' order by name

name	      compatibility_level	recovery_model	is_ADR_on
AW2019_150	   150	                     1              0
AW2019_150_AR	   150	                     1              1
AW2019_160	   160	                     1              0
AW2019_160_AR	   160	                     1              1

Transaktionen

Zur Durchführung der Tests wurde das SQLQueryStress-Tool von Erik Ejlskov Jensen verwendet, das in Git bereitgestellt ist. Hier wurden die im folgenden Code-Block wiedergegebenen Statements zehn mal in zehn parallelen Sessions ausgeführt:

update AW2019_150.Sales.SalesOrderDetail
  set CarrierTrackingNumber = 'x' + CarrierTrackingNumber
    , OrderQty = OrderQty + 1
	, UnitPrice = UnitPrice + 1
	, UnitPriceDiscount = UnitPriceDiscount + 1
update AW2019_150.Sales.SalesOrderDetail
  set CarrierTrackingNumber = substring( CarrierTrackingNumber, 2, 100)
    , OrderQty = OrderQty - 1
	, UnitPrice = UnitPrice - 1
	, UnitPriceDiscount = UnitPriceDiscount - 1
update AW2019_150_AR.Sales.SalesOrderDetail
  set CarrierTrackingNumber = 'x' + CarrierTrackingNumber
    , OrderQty = OrderQty + 1
	, UnitPrice = UnitPrice + 1
	, UnitPriceDiscount = UnitPriceDiscount + 1
update AW2019_150_AR.Sales.SalesOrderDetail
  set CarrierTrackingNumber = substring( CarrierTrackingNumber, 2, 100)
    , OrderQty = OrderQty - 1
	, UnitPrice = UnitPrice - 1
	, UnitPriceDiscount = UnitPriceDiscount - 1
update AW2019_160.Sales.SalesOrderDetail
  set CarrierTrackingNumber = 'x' + CarrierTrackingNumber
    , OrderQty = OrderQty + 1
	, UnitPrice = UnitPrice + 1
	, UnitPriceDiscount = UnitPriceDiscount + 1
update AW2019_160.Sales.SalesOrderDetail
  set CarrierTrackingNumber = substring( CarrierTrackingNumber, 2, 100)
    , OrderQty = OrderQty - 1
	, UnitPrice = UnitPrice - 1
	, UnitPriceDiscount = UnitPriceDiscount - 1
update AW2019_160_AR.Sales.SalesOrderDetail
  set CarrierTrackingNumber = 'x' + CarrierTrackingNumber
    , OrderQty = OrderQty + 1
	, UnitPrice = UnitPrice + 1
	, UnitPriceDiscount = UnitPriceDiscount + 1
update AW2019_160_AR.Sales.SalesOrderDetail
  set CarrierTrackingNumber = substring( CarrierTrackingNumber, 2, 100)
    , OrderQty = OrderQty - 1
	, UnitPrice = UnitPrice - 1
	, UnitPriceDiscount = UnitPriceDiscount - 1

Dateigrößen

Die u.a. Tabelle stellt die Größen der Datenbankdateien mit den unterschiedlichen Konfigurationen dar. Die Ermittlung der Größen erfolgte mit dem folgenden Skript:

create table #TempTable ( ID integer identity
                        , FileName varchar(128)
						, FileSize integer constraint PK_TempTableID primary key(ID)
						);

Insert into #TempTable(FileName, FileSize )
exec SP_MSforeachdb @command1 = 
            'use [?];
                select substring(physical_name, 69,100) as FileName
				     , size as FileSize from sys.database_files'
select FileName,FileSize from #TempTable where FileName like '%AW2019%' order by ID

Die folgende Tabelle zeigt die Dateigrößen ohne separate Recovery-Dateigruppen

Größe (inital) [8 KB Seiten]256009216256009216256009216256009216

Hier die Dateigrößen mit separaten Dateigruppen für PVS:

Größe (inital) [8KB Seiten]25600921625600921681922560092162560092168192

Ein Vergleich der beiden Tabellen miteinander zeigt, dass das Dateiwachstum bei Verwendung der separaten Filegruppe für PVS erwartungsgemäß etwas geringer ist.

Timing

Die oben verwendeten Update-Statements wurden jeweils paarweise (+'x' und +1 bzw. substring und -1) in vier separaten 10 * 10 Stresstest Sessions ausgeführt. Die folgende Tabelle enthält die Ergebnisse für die vier Datenbanken, die durchschnittlichen logical Reads pro Iteration lagen in allen Testläufen bei 4955,9800:

CPU-Sek./Iteration1,47342,14391,58522,2920

Hier fällt auf, dass Advanced Recovery mit einem bemerkenswerten hohen Overhead von mehr als 45% verbunden ist. Dies liegt allerdings auch an der hier hohen von dem Test verwendeten Tranasktionslast. Hierzu findet man bzgl. der Einschränkungen bei der Verwendung von Accelerade Database die folgende Formulierung:

"ADR wird nicht für Datenbankumgebungen mit einer hohen Anzahl von Aktualisierungen/Löschungen wie z. B. OLTP mit hohem Volumen empfohlen, ohne dass dem PVS-Bereinigungsprozess ein Zeitraum der Ruhe/Wiederherstellung zur Verfügung steht, in dem der Speicherplatz freimachen kann. In der Regel lassen Geschäftsbetriebszyklen diese Zeit zu, aber in einigen Szenarien sollten Sie den PVS-Bereinigungsprozess manuell initiieren, um die Bedingungen der Anwendungsaktivität zu nutzen."

Hierzu wird eine Stored Procedure unter dem Namen sys.sp_persistent_version_cleanup bereitgestellt.

Rollback nach großen Transaktionen

Um das Rollback-Verhalten der verschiedenen Konfigurationen zu testen, wurden die bereits zuvor verwendeten Updates paarweise pro Datenbank in drei Schritten ausgeführt:

-- Schritt 1: Starten einer Transaktion
begin transaction

-- Schritt 2: 50-maliges Ausführen der beiden update-Statements je Datenbank
update .Sales.SalesOrderDetail
  set CarrierTrackingNumber = 'x' + CarrierTrackingNumber
    , OrderQty = OrderQty + 1
	, UnitPrice = UnitPrice + 1
	, UnitPriceDiscount = UnitPriceDiscount + 1
update .Sales.SalesOrderDetail
  set CarrierTrackingNumber = substring( CarrierTrackingNumber, 2, 100)
    , OrderQty = OrderQty - 1
	, UnitPrice = UnitPrice - 1
	, UnitPriceDiscount = UnitPriceDiscount - 1
go 50

-- Schritt 3: Rollback der o.a. Transaktion
rollback

Hier ergaben sich folgende mittlere Ausführungszeiten bei jeweils drei Testläufen:

Transaktion [Sek.]39774077

Wie man aus der vorstehenden Tabelle ablesen kann, erkauft man sich die extreme Performanceverbesserung beim Rollback durch einen fast 100 prozentigen Overhead für die Transaktionsverarbeitung. Dies kann i.A. zunächst einmal als sehr negativ betrachtet werden, die verzögerungsfreie Rollback-Funktionalität bietet jedoch im Hochverfügbarkeits-Umfeld mit Always-On Verfügbarkeitsgruppen einen nützlichen Performancevorteil, da die Wartezeit im Fall eines Failovers extrem verkürzt wird.

Recovery nach großen Transaktionen

Nach den Tests wurde das Recovery-Verhalten für große Transaktionen betrachtet. Hierzu wurden die acht Update Statements innerhalb einer einzigen Transaktion paarweise / DB-weise 10 mal ausgeführt, ohne die Updates jedoch abschließend per Commit abzuschließen. Anschließend wurde eine zweite Session geöffnet, in der die Instanz mit 'shutdown with nowait' heruntergefahren wurde, also ohne auf die Beendigung der zuvor begonnenen Transaktionen zu warten.

Nach dem Neustart der Datenbank wurde das Fehlerprotokoll der Instanz bezüglich der Recovery-Zeiten ausgelesen.

Zur Vereinfachung der Suche nach den gewünschten Informationen wurde das folgende Script verwendet:

drop table if exists #ErrLogs 
  
CREATE TABLE #ErrLogs                                
 (                                  
  ID INT IDENTITY PRIMARY KEY NOT NULL,  
  LogDate  varchar(100),    
  Processinfo varchar(200),  
 Text  varchar(Max)   
 )  
  
 insert into  #ErrLogs exec master.sys.xp_readerrorlog

select * from #ErrLogs where Text like 'Recovery completed%AW2019%'

Das select-Statement lieferte in den jeweiligen Text-Spalten die folgenden Zeit-Informationen:

AW2019_150661113973167145

Die Aussage von Microsoft “ADR ist in SQL Server 2019 (15.x) neu und in SQL Server 2022 (16.x) verbessert.”, die man unter Verbesserte Wiederherstellung von Datenbanken findet, konnte mit diesem Beispiel nicht nachvollzogen werden: In beiden AW20??_AR-Datenbanken lag die Recovery-Zeit bei 27 Sekunden.

Bei der Gesamtzeit des Recovery von Datenbanken im Allgemeinen kann jedoch bei Verwendung von ADR eine deutliche Performancesteigerung verzeichnet werden. Diese resultiert trotz eines erhöhten Aufwands für Analyse und Redo in einer um 40% verbesserten Recovery-Zeit. Diese wird durch das enorm beschleunigte Undo-Handling erreicht.

Fazit

Accelerated Recovery kann bei speziellen Kundenanforderungen eine wichtige Rolle spielen. Wie bei vielen Neuerungen muss aber immer die genaue Situation betrachtet werden, unter der ein solches neues Feature eingesetzt wird. Hierzu nochmals der Hinweis auf die Bewertung, ob eine Workload für ADR geeignet ist.

Wenn Sie mehr über dieses Thema erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Verfügung. Vereinbaren Sie gerne ein unverbindliches Beratungsgespräch über unser Kontaktformular.

Wir helfen Ihnen gerne weiter!

SQL Server Accelerated Database Recovery - Setup und Metriken