رکورد های Forwarded و تاثیر آنها بر Performance

این مقاله از سایت simpletalk انتخاب شده و در مورد هزینه تحمیلی رکوردهای Forwarded بر Performance بحث می کند.

 ساختار یک Heap

رکوردهای Forwarded فقط در Heap ها می توانند بوجود بیایند. جدول بدون clustered index یک heap نامیده می شود چون دیتا را بدون هیچ ترتیبی ذخیره می کند. وقتی رکوردی جدید در یک heap درج می شود، SQL Server نوعی Page به نام PFS Page را اسکن می کند و در بین data page های موجود که به heap تخصیص یافته اند شروع به جستجو می کند. اگر SQL Server یک data page با فضای خالی کافی برای رکورد درج شونده پیدا کند، در اینصورت رکورد در آنجا درج می شود.
از طرف دیگر data page ای با فضای کافی وجود نداشته باشد، در اینصورت حداکثر ۸ data page جدید (یعنی اگر mixed extent ای که دارای حداقل یک page خالی باشد پیدا نشود که در این حالت یک extent کامل که معادل ۸ page است تخصیص می یابد) توسط SQL Server Database Engine ایجاد می شود.

PFS-PAGE

PFS-Page ها وضعیت تخصیص و فضای استفاده شده توسط data page ها را ردیابی می کنند. PFS-Page هر data page را در یک دیتابیس با استفاده از یک بایت ماسک به ازای هر data page مانیتور می کند؛ بنابراین یک PFS-Page می تواند ۸۰۸۸ page را مدیریت کند. اگر data page تخصیص یافته یک heap باشد، در اینصورت SQL Server در ۲ بیت اول اطلاعات مربوط به extent ای که data page در آن قرار دارد را ذخیره می کند:
 مقدار         معنی
0x00          پیج خالی است.
0x01          پیج تا ۵۰% پر شده است
0x02          پیج از ۵۱% تا ۸۰%  پر شده است
0x03          پیج از ۵۱% تا ۸۰% پر شده است
0x04          پیج تا از ۸۱% تا ۹۵% پر شده است
0x05          پیج تا از ۹۶% تا ۱۰۰% پر شده است
 حالا با یک مثال کدی نشان خواهم داد که SQL Server چطور page های یک heap را مدیریت می کند. کد اول یک نمونه جدول دو ستونی ایجاد می کند. هر رکورد دارای یک دیتا سایز ۲۵۰۴ بایتی خواهد بود.

CREATE TABLE dbo.demo_table
(
Id INT NOT NULL IDENTITY (1, 1),
C1 CHAR(2500) NOT NULL
);

در تکه کد بعدی یک رکورد جدید درج شده و موقعیت این رکورد جدید با استفاده از یک تابع undocumented داخلی به نام sys.fn_physloccracker تعیین می شود:

INSERT INTO dbo.demo_table (C1) VALUES ('Uwe Ricken');
GO
-- Check the allocated page(s)
SELECT P.*, D.*
FROM dbo.demo_table AS D
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS P;

تصویر بالا جایگاه منطقی رکورد درج شده جدید را نشان می دهد. در تکه کد بعدی PFS برای فضای مصرفی page 126 چک می شود. با توجه به این واقعیت که این جدول بخشی از یک دیتابیس بسیار کوچک است، page تخصیص داده شده در ۶۴ MB اول است، بنابراین اولین PFS-Page مرور خواهد شد.

-- Check the degree of filling for the allocated page
DBCC TRACEON (3604);
DBCC PAGE (demo_db, 1, 1, 3);
GO

لطفاً توجه کنید که دستور اول فعال سازی یک Trace Flag است. TF 3604 فعال شده ات خروجی DBCC PAGE از error log به خود برنامه client ارسال شود. دستور DBCC PAGE محتویات PFS را به SSMS ارسال می کند.
این تصویر نتیجه DBCC PAGE را در SSMS نشان می دهد. گرچه فقط ۲۵۰۴ بایت توسط رکورد درج شده اختصاص یافته، SQL Server فضای مصرفی ۵۰% اعلام کرده است. نتیجه به ازای درج رکورد بعدی به شکل زیر خواهد بود:

 INSERT INTO dbo.demo_table (C1) VALUES ('Phil Factor');
GO

تصویر بالا فضای مصرفی صفحه را ۸۰% اعلام می کند یعنی کلاً ۶۴۴۸ بایت. نگاهی به header page ، data page اختصاص یافته (۱:۱۲۶) ۳۰۷۰ بایت خالی را نشان می دهد. مقایسه این مقدار با طول یک رکورد داده ای نشان می دهد که رکورد بعدی باید بتواند در page جای بگیرد.بعد از درج رکورد سوم در جدول نتیجه تخصیص یک data page جدید خواهد بود! با وجود اینکه فضای کافی در data page 1:126 وجود دارد، رکورد جدید در data page 1:142 ذخیره می شود. دلیل آن یک قاعده ریاضی بسیار ساده است. SQL Server، PFS Page را چک کرده و میزان پر بودن data page را ۸۰% بدست آورده است. این ۸۰% مقدار ۶۴۴۸ بایت (۸۰۶۰*۰.۸) را نشان می دهد.
 ۲۰% فضای آزاد در data page 126 به این معنی است که ۱۶۱۲ بایت باقیمانده است. بر اساس این محاسبه، SQL Server رکورد را در data page ذخیره نمی کند چون جا نمی شود. اگر SQL Server خود data page را برای میزان فضای آزاد بررسی می کرد بیش از ۳۰۰۰ بایت فضای آزاد را پیدا می کرد. SQL Server از PFS data page برای تخصیص فضای data page های heap ها استفاده می کند.

IAM-Page

SQL Server با استفاده از این نوع Page (Index Allocation Map) تشخیص می دهد که هر Page در فایل داده ای دیتابیس (.mdf یا .ndf) متعلق به کدام شیء از اشیای دیتابیس (جدول یا ایندکس) است. هر IAM Page تا ۴GB از فایل دیتابیس را پوشش می دهد.
IAM Page توسط SQL Server برای حرکت روی heap استفاده می شود. به دلیل ساختار داخلی یک heap که هیچ پیوندی بین data page های یک heap ارائه نمی کند، تنها راه مرور data page های یک heap استفاده از IAM Page است.

 SELECT P.index_id,
P.rows,
SIAU.type_desc,
SIAU.total_pages,
SIAU.used_pages,
SIAU.data_pages,
SIAU.first_page,
SIAU.first_iam_page
FROM sys.partitions AS P
INNER JOIN sys.system_internals_allocation_units AS SIAU
ON (P.hobt_id = SIAU.container_id)
WHERE P.object_id = OBJECT_ID('dbo.demo_table', 'U');

اسکریپت بالا ستون first_iam_page جدول demo-table را لیست می کند. SQL Server فقط IAM Page اول را برای مرور کل جدول نیاز دارد. هر IAM Page عنصری از اطلاعات را که موقعیت IAM Page بعدی را که دیتای جداول تاثیر یافته را مدیریت می کند به ما می دهد. چنین رفتاری برای مدیریت Forwarded rocord ها الزامی است.

یک Forwarded Record چیست؟

Forwarded Record یک data record از heap است که به دلیل اینکه update شده و حجمش به اندازه ای شده که دیگر در data page جا نمی شود، باید به page ای جدید منتقل شود. SQL Server یک data page جدید تخصیص داده و رکورد بزرگتر را به آن data page  ایجاده شده جدید انتقال می دهد. SQL Server محل جدید رکورد را در data page اولیه یادداشت می کند؛ علت چنین جهت دهی مجددی (redirection) کاملاً ساده است: آدرس این رکورد هنوز موقعیت فعلی است (page فعلی).
اگر قرار باشد SQL Server آدرس جدید را ذخیره کند در اینصورت نیاز خواهد داشت همه ایندکس های non-clustered را بروز کند. هزینه بروز رسانی ایندکس های non-clustered در مقایسه با هزینه ناشی از مراجعه به موقعیت جدید رکورد در Page جدید برای Storage engine بسیار بیشتر خواهد بود.

 محیط آزمایش

برای نمایش یک Forwarded Record نیاز دارم تا جدولی با ۲۰ رکورد را بسازم. این جدول دارای یک ایندکس non-clustered روی ستون c2 است. یکی از این بیست رکورد update می شود و اندازه بزرگی خواهد داشت. اندازه جدید به حدی بزرگ است که در data page اصلی جای نمی گیرد. رکورد باید به یک data page جدید منتقل شود. این عملکرد اساسی یک Forwarded Record است.

 /* Create the demo table for 20 records */
CREATE TABLE dbo.demo_table
(
Id INT NOT NULL IDENTITY (1, 1),
C1 VARCHAR(8000) NOT NULL,
C2 DATE NOT NULL,
);
GO
CREATE NONCLUSTERED INDEX ix_demo_table_C2 ON dbo.demo_table (C2);
GO
/* Now insert 20 records into the table */
INSERT INTO dbo.demo_table (C1, C2) VALUES
(REPLICATE('A', 1995), getdate()),
(REPLICATE('B', 1995), getdate()),
(REPLICATE('C', 1995), getdate()),
(REPLICATE('D', 1995), getdate());
GO 5
/* On what pages are the records stored? */
SELECT FPLC.*,
DT.*
FROM dbo.demo_table AS DT
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC;
GO

کد بالا جدولی با ۲۰ رکورد ایجاد کرده و اندازه هر یک ۲۰۱۵ بایت خواهد بود. بنابراین هر data page تا ۱۰۰% پر شده است.
۲۰ رکورد داده ای در ۴ پیج جای داده شده اند یعنی هر پیج ۴ رکورد  به اضافه یک data page برایIAM Page. (ردیف اول در شکل زیر که فیلد is_iam_page آن یک است.)
با اجرای یک دستور SELECT * یک ایندکس اسکن با هزینه ۵ logical read اجرا می شود:
 
وقتی SQL Server یک heap را اسکن می کند، ابتدا به IAM Page دست پیدا می کند و data page هایی که به جدول اختصاص یافته اند را تعیین می کند. وقتی اطلاعات page های تاثیر یافته خوانده شد، عملیات IO واقعی می تواند شروع شود.

تولید یک Forwarded Record

وقتی یک رکورد روی یک data page بروز شده و اندازه جدید رکورد به اندازه ای می رسد که نمی تواند توسط data page اصلی پوشش داده شود SQL Server یک data page دیگر ایجاد کرده و دیتای تغییر داده شده را به data page جدید انتقال می دهد. کد زیر رکوردی با ID=1 را بروز کرده و مقدار ستون C1 را افزایش می دهد.

UPDATE dbo.demo_table
SET C1 = REPLICATE('Z', 2500)
WHERE Id = 1;
GO

با افزایش مقدار C1 برای رکورد ۱ ، یک فرآیند داخلی شروع به یافتن فضایی بزرگتر برای ذخیره کردن دیتای تغییر یافته می کند. نگاهی به transaction log می تواند این تلاش را بیشتر محسوس کند.

 SELECT FD.[Current LSN],
FD.Operation,
FD.Context,
FD.AllocUnitName,
FD.[Page ID],
FD.[Slot ID],
FD.[RowLog Contents 0],
FD.[RowLog Contents 1]
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE FD.Context <> N'LCX_NULL'
ORDER BY
FD.[Current LSN];

(لطفاً به خاطر بسپارید که fn_dblog() نباید در یک سیستم Production نباید استفاده شود چون log clearing مادامی که fn_dblog() در حال اجراست غیر فعال می شود!)
 نگاهی به transaction log نشان می دهد که چه اتفاقی افتاده است. در مرحله اول (کادر قرمز)، یک شیء statistics ایجاد شده است. این بخش از transaction log خیلی کمکی به درک چگونگی عملکرد داخلی Forwarded Record نمی کند.

اما بخش دوم transaction log (کادر آبی) مراحل مستقلی که موتور دیتابیس هنگام ایجاد یک Forwarded Record دنبال می کند را نشان می دهد

•    PFS یک data page جدید ثبت می کند. به طور پیش فرض این data page خالی است، و این اطلاعات باید در   PFS data page ثبت شوند.
•    رکورد بعدی(خط ۱۰) بروز رسانی IAM data page را نشان می دهد. data page جدید بخشی از جدول dbo.demo-table است. IAM data page که کار مدیریت data page های تخصیص داده شده را دارد باید تغییرات جدید ساختاری را ثبت کند.
•    بعد از اینکه سیستم یک data page جدید در دیتابیس ثبت کرد، فرمت می شود (خط ۱۱).
•    وقتی data page جدید آماده شد، data record اصلی در data page جدید نوشته می شود.
•    به دلیل درجه جدید میزان پر بودن data page، باید   PFS data page بروز شود (خط ۱۳).
•    وقتی دیتای اصلی در data page جدید ذخیره شد(کپی) و  PFS data page بروز شد، رکورد کپی شده به مقدار جدید بروز می شود (خط ۱۴).
•    بعد از اینکه مقادیر داده ای جدید ذخیره شدند، رکورد موجود در page مبدا باید بروز شود. رکورد داده ای توسط یک “Forwarded Stub”جایگزین می شود. این یک اشاره گر به موقعیت جدید رکورد داده ای است (خط ۱۵).
•    یک “Forwarded Stub” ۹ بایت اندازه دارد و این باعث بروز شدن مجدد  PFS data page می شود.

 کشف یک Forwarded Record

یک Forwarded Record می تواند با استفاده از dmv  [sys].[dm_db_index_physical_stats] می تواند مشاهده شود.

SELECT DDIPS.index_type_desc,
DDIPS.page_count,
DDIPS.avg_page_space_used_in_percent,
DDIPS.record_count,
DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID('dbo.demo_table', 'U'),
۰,
NULL,
'DETAILED'
) AS DDIPS;

یک تغییر جالب تعداد رکوردها که در شکل بالا آمده است می باشد! گرچه هنوز ۲۰ رکورد در جدول وجود دارد اما شمارش بیست و یک رکورد را نشان می دهد، چون یک Forwarded Record به عنوان یک دیتا رکورد عادی شمارش می شود. اطلاعات مربوط به Forwarded Record ها در فیلد [forwarded_record_count] ذخیره می شود.

خواندن یک Forwarded Record

وقتی یک heap ، Forwarded Record  ذخیره می کند چه مشکلاتی می تواند رخ دهد؟ همانطور که قبلاً نمایش داده شد، SQL Server ، IAM – page ها را برای اینکه باید کدام data page ها باید خوانده شوند می خواند. وقتی IAM Page ها خوانده شدند، SQL Server شروع به اسکن data page ها می کند. کد زیر میزان IO را وقتی یک Forwarded Record باید خوانده شود را نشان می دهد.

 SET STATISTICS IO ON;
GO
SELECT * FROM dbo.demo_table;
GO
SET STATISTICS IO OFF;
GO

همان تعداد رکورد ۲ logical read بیشتر تولید کرد. این رفتار کاملاً عجیب به نظر می رسد اما دقیقاً چگونگی مدیریت Forwarded Record ها توسط SQL Server را نشان می دهد.

اولین IO

در مرحله اول (که به عنوان IO علامت نخورده) SQL Server ، IAM Page را اسکن کرده و page های ۱۱۹، ۱۲۱، ۱۲۶، ۱۲۷، ۱۴۲ و data page ای که حاوی Forwarded Record است درست مانند data page هایی که دیتای heap را نگه می دارند را پیدا می کند.
حالا SQL Server شروع به اسکن  data page اول (۱۱۹) می کند – این اولین IO است که باید شمرده شود.
وقتی SQL Server می خواهد اولین data page را بخواند، خود رکورد را پیدا نمی کند بلکه به یک link به data page جدیدی می رسد که رکورد در آنجا می تواند پیدا شود. نگاهی به page 119 مشکل را نشان می دهد:

DBCC TRACEON (3604);
DBCC PAGE (demo_db, 1, 119, 3);
GO

 وقتی SQL Server با page 119 برخورد می کند یک Forwarding Stub پیدا می کند که به محل جدید رکورد اشاره می کند.

IO دوم

وقتی SQL Server آدرس یک Forwarded Record را می داند، به این آدرس (۱۵۶) پرش می کند تا این رکورد را بخواند. لطفاً توجه کنید که فقط این رکورد خوانده می شود به جای کل page! حالا SQL Server باید ۲ logical read انجام دهد و به page 119 برمی گردد تا سایر رکوردها را بخواند

 IO سوم تا هفتم

به محض اینکه Forwarded Record خوانده شد، SQL Server به عقب برگشته و data page های زیر را می خواند (۱۲۱، ۱۲۶، ۱۲۷ و ۱۴۲) که ۴ logical read اضافه را مصرف می کند. اما این همه ماجرا نیست! SQL Server باید در آخر page 156 را دوباره بخواند چون IAM حاوی اطلاعاتی راجع به این page که به heap تخصیص یافته است می باشد.
نکته آخر اینکه با Rebuild ایندکس های Nonclustered آدرس های جدید رکوردهای Forwarded در ایندکس ها ثبت نمی شود بلکه همان آدرس فعلی در ایندکس باقی می ماند و فقط با Rebuild جدول Heap که باعث Rebuild ایندکس های Nonclustered هم می شود آدرس های جدید رکوردها در ایندکس ثبت می شود