آشنایی با ویژگی Accelerated Database Recovery در SQL Server 2019

مقدمه

Accelerated Database Recovery یا به اختصار ADR یک ویژگی جدید است که در SQL Server 2019 ارائه شده است.
این ویژگی در دسترس بودن Database را به صورت قابل ملاحظه‌ای بهبود می‌بخشد، بویژه در سیستم‌هایی که Long Running Transactions یا تراکنش¬هایی که مدت زمان اجرای آن‌ها طولانی است (تراکنش‌هایی که مدت زمان زیادی است در حال اجرا هستند)، وجود دارند.
مایکروسافت پروسه SQL Server Recovery را به طور کامل طراحی مجدد نموده است

مزیت‌های اصلی ADR عبارتند از

۱- انجام Database Recovery به صورت سریع و Consistent
با ADR تراکنش‌هایی که مدت زمان اجرای آن‌ها طولانی است تاثیری بر مدت زمان Recovery ندارند و این امکان به وجود می‌آید که Database Recovery به صورت سریع و Consistent صرف نظر از تعداد و اندازه تراکنش‌های فعال (Active Transaction) که در سیستم وجود دارند، انجام شود.
۲- عمل Rollback یک Transaction به صورت بسیار سریع انجام می‌شود.
عمل Rollback صرف نظر از مدت زمانی که از اجرای Transaction سپری شده و تعداد رکوردهایی که تحت تاثیر Update قرار گرفته‌اند انجام می‌شود.
۳- عمل Truncation لاگ فایل حتی با وجود Long Running Transaction سریعا انجام می‌شود و این ویژگی از رشد لاگ فایل به صورت غیر قابل کنترل جلوگیری می‌نماید.

چه کسانی باید از ویژگی ADR استفاده نمایند

۱- کسانی که در سیستم‌هایشان Long Running Transaction دارند.
۲- کسانی که Active Transaction Log در Database‌های آن ها به صورت قابل ملاحظه‌ایی رشد می‌کند.
کسانی که در دسترسی نبودن Database را در طی پروسه Recovery SQL Server Long Running تجربه نموده‌اند، که این اتفاق مجددا برای آن‌ها تکرار خواهد شد.
در ادامه سعی خواهیم نمود هر یک از مزیت‌هایی ADR را با انجام یک مثال بررسی نماییم:
ابتدا تاثیر ADR را بر عملیات Rollback نمودن یک Transaction بررسی می‌نماییم، برای این کار یک Database با نام FastRecovery ایجاد می¬کنیم

Use master
GO
If DB_ID (N'FastRecovery') Is Not Null
 Begin
  Alter Database FastRecovery Set Single_User With Rollback Immediate
  Drop Database FastRecovery
 End
Create DataBase FastRecovery
On Primary
(Name = N'C', FileName = N'D:\Databases\Data\FastRecovery.mdf', Size = 4096 mb, FileGrowth = 256 mb, Maxsize = Unlimited )
 Log On
(Name = N'ADRdbLog', FileName = N'D:\Databases\Data\FastRecovery.ldf', Size = 4096 mb, FileGrowth = 256 mb, Maxsize = Unlimited )
GO

اسکریپت زیر جدول Users را ایجاد نموده و سه میلیون رکورد را به آن Insert می‌نماید.

Use FastRecovery
GO
Drop Table If Exists Users
Create Table Users
 (ID Int Not Null Primary Key, FullName Varchar(50) Not Null, Reputation Int Not Null, Index IXNCReputation (Reputation))
; With Tbl (r) as (Select 1 Union All Select r + 1 From Tbl Where r < 3000000)
 Insert Into Users
 Select r, CONCAT ('User', r), 0 From Tbl Option (maxrecursion 0)

نکته: ایندکس گذاری بر روی ستون‌هایی که بروز رسانی می‌شوند کار صحیحی نمی‌باشد، در این جا ما برای اینکه مدت زمان اجرای تراکنش افزایش یابد روی ستون Reputation یک NonClustered ایندکس ایجاد نموده‌ایم. (محیط تست است.)
دستور زیر نشان می‌دهد که ویژگی ADR برای Database مذکور غیر فعال است:

Select name, is_accelerated_database_recovery_on from sys.databases Where database_id = DB_ID()

آنچه در این تست برای ما اهمیت دارد این است که نشان دهیم مدت زمان Rollback بعد از فعال نمودن ویژگی ADR چقدر کاهش خواهد یافت. در ادامه با استفاده از یک Transaction ستون Reputation را به ازای همه رکوردهای جدول Users بروز رسانی می‌نماییم:

Begin Transaction
  Update Users Set Reputation = 1000000

سپس عمل Rollback را انجام می‌دهیم:

Rollback Transaction

مدت زمان عمل Rollback به ازای تراکنش فوق بسته به سخت افزار چند ده ثانیه ممکن است طول بکشد که مدت زمان قابل توجهی است.

در ادامه ویژگی ADR را به ازای دیتابیس FastRecovery فعال می‌نماییم.
برای فعال نمودن این ویژگی به دو نکته باید توجه داشت:
۱- ویژگی ADR به ازای Database می باشد و اصطلاحا Per Database است.
۲- هنگام فعال نمودن این ویژگی فقط یک Seeeion باید برای Database فعال باشد.
با استفاده از دستور زیر ویژگی ADR را به ازای دیتابیس FastRecovery فعال می‌نماییم:

Alter Database FastRecovery Set Accelerated_Database_Recovery = ON

 همان گونه که در تصویر زیر مشاهده می‌شود ویژگی ADR به ازای دیتابیس مذکور فعال شده است.

Select name, is_accelerated_database_recovery_on from sys.databases Where database_id = DB_ID()

مجددا دستور زیر را اجرا می‌نماییم:

Begin Transaction
  Update Users Set Reputation = 1000000

سپس عمل Rollback را انجام می‌دهیم:

Rollback Transaction

عملیات Rollback نمودن Transaction در کسری از ثانیه انجام می‌شود.
همان طور که گفته شده با استفاده از ADR عملیات Database Recovery با سرعت بسیار بالاتری انجام خواهد شد و نیز از رشد Log File به میزان قابل ملاحظه‌ایی جلوگیری می‌شود، با انجام مثال زیر هر دو این ویژگی ها را بررسی می‌نماییم:
ابتدا دو Database ایجاد می‌کنیم که ویژگی ADR بر روی یکی از آن‌ها فعال است

Use Master
GO
CREATE DATABASE OldStyle;
GO
CREATE DATABASE NewStyle;
GO
ALTER  DATABASE NewStyle SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

سپس کوئری‌های زیر را در یک زمان معین، تقریبا هم زمان با هم در Sessionهای متفاوت اجرا می‌نماییم.
کوئری‌ها به گونه ایی طراحی شده‌اند که اجرای آن‌ها چندین دقیقه طول خواهد کشید.
من در پنجره شماره یک در حدود ساعت ۱۰:۵۴ دقیقه کوئری زیر را اجرا نمودم:

WAITFOR TIME '10:55:00';
USE OldStyle;
GO
DROP TABLE IF EXISTS dbo.fl1, dbo.fl2, dbo.fl3;
SELECT s2.* INTO dbo.fl1 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl2 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl3 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;

سریعا در پنجره شماره دو کوئری زیر را نیز اجرا نمودم:

WAITFOR TIME '10:55:00';
USE NewStyle;
GO
DROP TABLE IF EXISTS dbo.fl1, dbo.fl2, dbo.fl3;
SELECT s2.* INTO dbo.fl1 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl2 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;
SELECT s2.* INTO dbo.fl3 FROM sys.all_columns AS s1 CROSS JOIN sys.all_objects AS s2;

و بعد در پنجره شماره سه با اجرای کوئری زیر بعد از گذشت دو دقیقه و نیم SQL Server را Shutdown نمودم:

WAITFOR DELAY '00:02:30';
SHUTDOWN WITH NOWAIT;

اکنون سرویس SQL Server را Start نموده و منتظر می‌شویم تا همه Databaseها Recovery شوند.
بعد از Recover شدن Database‌ها جهت مشاهده مدت زمان Recovery هر یک از Database‌ها به سراغ SQL Server Log می‌رویم:
همان طور که در تصویر بالا نشان داده شده است مدت زمان Recovery برای دیتابیس NewStyle در حدود ۱۲ ثانیه می‌باشد که تقریبا ۱۰ ثانیه مرحله Analysis است و ۱ ثانیه نیز صرف Redo شده است و Undo نیز در کسری از ثانیه انجام شده است.
تصویر زیر مدت زمان Recovery برای دیتابیس OldStyle را نمایش می‌دهد:مدت زمان Recovery برای دیتابیس OldStyle در حدود ۱۹۸ ثانیه می‌باشد که تقریبا ۱۷ ثانیه مرحله Analysis است و ۱۷۹ ثانیه نیز صرف Redo شده است و Undo نیز در یک ثانیه انجام شده است.
همچنین تصویر زیر میزان رشد Data File و نیز Log File را به ازای هر دو دیتابیس استفاده شده در تست فوق نمایش می‌دهد:میزان رشد لاگ فایل به ازای دیتابیس NewStyle نسبت به دیتابیس OldStyle بسیار کمتر است اما همان گونه که مشاهده می‌نمایید Data File مربوط به دیتابیس NewStyle نسبت به OldStyle رشد بیشتری داشته است. البته این رشد Data File نسبت به میزان رشد Log File برای دیتابیس OldStyle آن چنان محسوس نمی‌باشد.

مباحث Internals

بررسی پروسه SQL Server Recovery:
پروسه SQL Server Recovery شامل سه مرحله می‌باشد:

۱- Analysis: در این قسمت، Log File از آخرین Checkpoint به جلو خوانده می‌شود و قدیمی ترین Dirty Page و نیز وضعیت همه تراکنش ها در هنگامی که سرویس SQL Server متوقف شده است تعیین می گردد.
به طور کلی Transactionها به دو دسته تقسیم می‌شوند:

• تراکنش‌هایی که در لاگ فایل نوشته شده‌اند و Commit شده‌اند اما در Data File نوشته نشده‌اند و بایدRoll Forward شوند.
• تراکنش‌های UnCommitted یا تراکنش‌های Commit نشده که باید Rollback شوند.

۲- Redo: در این قسمت لاگ فایل از آخرین تراکنش Commit نشده (Oldest UnCommitted Transaction) به جلو خوانده می‌شود و تمامی تراکنش‌هایی که در لاگ فایل Commit شده‌اند اما در Data File نوشته نشده‌اند، در Data File نیز نوشته می‌شوند.
۳- Undo: در این قسمت از انتهای لاگ فایل به سمت عقب خوانده می‌شود و همه تراکنش‌هایی که هنوز باز هستند و یا Commit نشده‌اند Rollback می‌گردند.
تصویر زیر مراحل SQL Server Recovery را نمایش می دهد:قبل از اینکه Database در دسترس قرار گیرد همه مراحل فوق باید کامل شوند.
البته در Enterprise Edition امکانی با نام Fast Recovery وجود دارد، در Fast Recovery بعد از کامل شدن مرحله Redo دیتابیس می‌تواند در دسترس قرار گیرد و امکان دسترسی به داده‌هایی که توسط مرحله Undo حفاظت نمی‌شوند وجود دارد.
مشکلی که در Process شرح داده شده (مراحل Recovery) وجود دارد این است که مدت زمان Recovery به Longest-Running Transaction وابسته است که باید Applied یا Rollback شده باشد و بعد از آن دیتابیس در دسترس قرار می‌گیرد و این موضوع نیازمند این است که بخش قابل توجهی از لاگ فایل خوانده شود (از UnCommitted Transaction به بعد) و بدیهی است که باعث Delay در Online شدن Database خواهد شد.
همان طور که گفته شد Accelerated Database Recovery یک ویژگی جدید در SQL Server 2019 هست که با از بین بردن برخی از مشکلات پروسه Recovery باعث می شود Databaseها بسیار سریع تر Online شوند و دردسترس قرار گیرند.

SQL Server 2019 با استفاده از Componentها و مفاهیم زیر در ویژگی ADR عمل Database Recovery را تسریع می‌بخشد:
۱- یکی از مهم ترین اجزا Persisted Version Store (PVS) می‌باشد، این قابلیت شبیه به Version Store است که در Read Committed Snapshot Isolation استفاده می‌شود با این تفاوت که داده‌ها به جای System Database Tempdb در User Database ذخیره می‌شوند. هنگامی که یک ردیف تغییر می‌کند، نسخه قبلی آن ردیف به یکی از دو روش In-Row (Up to 200 bytes) یا Off-Row (Using 14-byte pointer) ایجاد و نگه داشته می‌شود.
۲- مفهوم جدید دیگر Logical Revert که یک Row-Level Undo با استفاده از PVS است. هنگامی که یک تراکنش Abort می‌شود ورژن قبلی رکوردهای تحت ثاثیر تراکنش از طریق Pointer در دسترس می‌باشند این موضوع باعث می‌شود که همه Lock‌ها سریعا آزاد شوند بجای اینکه منتظر کامل شدن تغییرات باشند. اگر یک تراکنش Rollback شود Logical Revert اجازه می‌دهد تراکنش دوم با استفاده از PVS به یک رکورد کاملا یکسان سریعا دسترسی یابد، می‌دانیم که در حالت عادی تراکنش دوم Block می‌شود و منتظر می‌ماند تا عملیات Rollback تراکنش اول به پایان برسد.
۳- یکی دیگر از اجزای مهم و جدید In-Memory Log Stream یا اصطلاحا sLog است، که یک Secondary Log Stream می‌باشد. sLog، لاگ مربوط به عملیات‌های low-volumn و non-versioned را ذخیره می‌کند. sLog کمک می‌کند که عملیات Undo و Redo سریع‌تر انجام شوند زیرا آن‌ها فقط بخش مربوط به عملیات‌های non-versiond را پردازش می‌کنند. فقط قسمت مربوط به Physical Transaction Log در هنگام عملیات‌های Redo و Undo باید خوانده شوند یعنی از آخرین Checkpoint به بعد. (لطفا به تصویر بعدی توجه نمایید).
۴- Cleaner Proccess: این پروسه هر یک دقیقه یک بار انجام می‌شود که Asynchronuos می‌باشد و Page Version‌هایی که مورد نیاز نیستند را پاک می‌نماید. این پروسه را می‌توان به صورت دستی با استفاده از پروسیجر sys.sp_persistent_version_cleanup فراخوانی نمود.
تصویر زیر Process Recovery جدید را نمایش می‌دهد:زمانی که ما ویژگی ADR را به ازای یک دیتابیس فعال می‌نماییم به صورت عمومی یک نسخه از داده‌هایی که تحت تاثیر یک Transaction قرار می‌گیرند درون آن Database ذخیره می‌شوند. برای نشان دادن این موضوع به مثالی که برای عملیات Rollback بررسی شد برمی‌گردیم:
برای این کار ابتدا ویژگی ADR را به ازای دیتابیس FastRecovery غیر فعال می‌نماییم:

Alter Database FastRecovery Set Accelerated_Database_recovery =  off

 کد زیر جدول Users را بروز رسانی می‌کند:

Use FastRecovery
GO
Begin Transaction
Update [dbo].[Users] set Reputation = 3000000

با استفاده از DMV زیر Version Record Countرا می‌توان مشاهده نمود:

Select * from sys.dm_db_index_physical_stats (db_id(), Object_id (N'Users'), 1, 0, N'Detailed')

 جدول Users صفر است. این موضوع بدان معنی است که هیچ نسخه ایی از رکوردهایی که تحت تاثیر تراکنش قرار گرفته اند درون دیتابیس FastRecovery ذخیره نشده است.

Commit Transaction
Alter Database FastRecovery Set Accelerated_Database_recovery =  ON

 مجددا رکوردهای جدول Users را بروزرسانی می‌نماییم:

Begin Transaction
Update [dbo].[Users] set Reputation = 4000000

با استفاده از DMV زیر می توان تعداد Version Record را مشاهده نمود:

Select * from sys.dm_db_index_physical_stats (db_id(), Object_id (N'Users'), 1, 0, N'Detailed')

توجه نمایید که این رکوردها در دیتا فایل ذخیره می‌شوند و این یک OverHead برای دیتا فایل محسوب می‌گردد و حجم دیتا فایل را افزایش می‌دهد.