معماری و ساختار Page در SQL Server

مفهوم Page در SQL Server

در این مطلب می‌ خواهیم با مفهوم Page در SQL Server آشنا شویم که درون یک Page چه اطلاعاتی قرار می ‌گیرد و از چه بخش ‌های تشکیل شده است. و چطور می ‌توان به اطلاعات یک Page دسترسی داشت. اما پیش از پرداختن به مفهوم Page قصد دارم شما را اجزای تشکیل دهنده یک دیتابیس را معرفی کرده و سپس در ادامه به جزئیات مفهوم Page پرداخته می ‌شود.

اجزاء تشکیل دهنده یک دیتابیس

زمانی که شما در SQL Server یک دیتابیس ایجاد می‌ کنید به طور پیش‌فرض یک فایل با پسوند «MDF» برای ذخیره داده ایجاد شده و یک فایل با پسوند «LDF» برای ثبت تراکنش ‌ها ایجاد شده است. اگر به مسیری که در ادامه آمده است در سیستم خود مراجعه کرده باشید خواهید دید که دو تا فایل به شکل زیر ایجاد شده است.
مثال: با استفاده از کوئری زیر می ‌توانید در SQL Server دیتابیس ایجاد کنید:

CREATE DATABASE PageAnatomy;
GO

توضیحات: یکی از دستورات پرکاربرد که برای نمایش فایل‌ های موجود یک دیتابیس مورد استفاده قرار می‌ گیرد، دستور «SP_HELPFILE» می ‌باشد. این دستور یکسری اطلاعات در رابطه با دیتابیس که ایجاد کردید یا از قبل وجود داشته، در اختیارتان قرار می ‌دهد. در خروجی این دستور یک بخشی وجود دارد بنام «usage» که پسوند فایل را مشخص می ‌کند.

EXEC SP_HELPFILE;
GO

تا الان یک دیتابیس با نام «PageAnatomy» ایجاد کردید، تصور کنید در این دیتابیس یک جدولی ایجاد کردید که اطلاعات مشتریان را ذخیره می ‌کند، این اطلاعات در نهایت در یک فایل بنام MDF ذخیره می ‌شود. که در ادامه مسیر دسترسی آن و همچنین تصویری از مسیری که فایل MDF و LDF در آن ذخیره می ‌شود، آمده است.
مسیر پیش‌فرض ایجاد دیتافایل و لاگ‌ فایل در :SQL Server

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLSERVER2017\MSSQL\DATA

آشنایی با مفهوم Page

بیش از این آموختیم که چطور یک دیتابیس ایجاد کنیم و اجزای تشکیل دهنده یک دیتابیس چیست که شامل دو فایل بنام MDF و LDF بود. در فایل LDF تمامی تغییراتی که در جدول انجام می ‌شود.مثل حذف یک فیلد یا رکورد یا به روز‌رسانی اطلاعات مشتری و موارد دیگر که باعث شود بیتی از اطلاعات تغییرکند در لاگ فایل (LDF) ثبت و ذخیره می ‌شود. اما در فایل MDF، جداولی که ایجاد یا رکوردی درج می ‌کنید. این دسته از Action ها در صفحه هایی به نام Page، که اجزای تشکیل دهنده فایل MDF می باشند ذخیره و نگه داری می شود. در معـماری SQL Server اطلاعـات درج شده در Page های ۸ KB و به صـورت Row Base ذخیره می شود. هر چقدر زمان در طراحی جداول و انتخاب Data Type مناسب وقت بگذارید. زمانی که SQL Server اطلاعات را درون جداول ذخیره می‌ کند، اطلاعات بیشتری را درون این Page ها قرار می ‌دهد. این رخ داد باعث بهبود Performance خواهد شد. چرا که زمانی که شما درخواستی را به سمت SQL Server ارسال می ‌کنید که بخشی از اطلاعات یک جدول را به شما نمایش دهد، به سراغ آن یک Page مراجعه کرده و می ‌تواند اطلاعات لازم را بخواند و نمایش دهد، اما هر چقدر تعداد Pageها بیشتر باشد SQL Server محبور است Pageهای زیادی را از دیسک وارد فضای Buffer pool که بخشی از حافظه Ram است، قرار دهد. و همین موضوع می ‌تواند باعث شود I/O اضافی به به دیسک زده و Pageها لازم را از فایل MDF که در هارد دیسک شما وجود دارد فراخوانی کرده و اطلاعات را به درستی نمایش دهد.

بررسی داخلی یک Page

ظرفیت هر page(8KB) برابر است با (۸۱۹۲) بایت می‌باشد. که ۹۶ بایت آن مربوط به Page header می‌ باشد، که یکسری اطلاعات سیستمی مربوط به Data Page هار در خود ذخیره می‌کند. و ۳۶ بایت آن هم برای جابجایی ردیف‌ها (Offsets Row) مورد استفاده قرار می‌گیرد و از حجم کل یک Page تنها فضای برای ذخیره اطلاعات برابر است با ۸۰۶۰ بایت است.در SQL Server انواع مختلف از Pageها را داریم که در ادامه همین مطلب به صورت جدول آمده است و توضیحات لازم برای هر یک از آنها آمده است.اما به طوری کلی وقتی از Pageها صحبت می‌ کنیم، یک ساختار طبقه بندی شده دارد. منظور این ساختار این است که که محتوایات یک Page به سه بخش اصلی تقسیم شده است که در ادامه آمده است.

•  Page Header: اطلاعات سیستمی در رابطه با Pageها را ذخیره می‌ کند.
•  Data Rows: این بخش دقیقا جای است که اطلاعات و رکوردی که ثبت می‌ کنید، در این بخش ذخیره می‌ شود.
•  Row Offsets: آدرس هر ردیف را ذخیره می‌ کند و دو بایت از کل فضای Page را به ازای هر آدرس ردیف استفاده می ‌کند.

در SQL Server انواع مختلفی از Pageها را داریم که در تصویر زیر فهرست آن‌ ها آمده است. و دانستن نوع (Type) آن ‌ها می ‌تواند کمک زیادی در رخ‌ داد مشکل ‌دار شدن Page تشخیص درست ‌تری داد که کدام Page دچار مشکل است.

نحوه ذخیره داده‌ ها درون یک Page

برای درک هر چه بهتر که داده‌ های که در SQL Server به چه صورت ذخیره می ‌شوند، یک سناریو را در نظر بگیرید که یک دیتابیس دارید با نام «PageAnatomy» و داخل این دیتابیس یک جدول دارید که اطلاعات مشتریان را ذخیره می ‌کند. در ادامه کوئری مربوط به ساخت دیتابیس و همچنین ایجاد جدول مشتری و درج یک رکورد اطلاعات مشتری در جدول مشتری (Customer) آمده است.

USE master;
GO
IF DB_ID('PageAnatomy') > 0
BEGIN
  ALTER DATABASE PageAnatomy SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DROP DATABASE PageAnatomy
END
GO
CREATE DATABASE PageAnatomy
ON PRIMARY
(
  NAME = PageAnatomy,
  FILENAME = 'C:\TEMP\PageAnatomy_DATA.MDF',
  SIZE = 100MB,
  MAXSIZE = 120MB,
  FILEGROWTH = 64536KB
)
LOG ON
(
  NAME = PageAnatomy_log,
  FILENAME = 'C:\TEMP\PageAnatomy_Tran.LDF',
  SIZE = 100MB,
  MAXSIZE = 120MB,
  FILEGROWTH = 64536KB
);
GO
USE PageAnatomy;
GO
CREATE TABLE Customer
(
  ID INT IDENTITY PRIMARY KEY,
  Customer_Name VARCHAR (1000)
);
GO
INSERT INTO dbo.Customer (Customer_Name) VALUES ('Recored 1');
GO

در ادامه توسط پروسیجر سیستمی فایل موجود دیتابیسی که ساختم را می ‌توانیم بدست بیاریم. و در ادامه توسط دستور سیستمی داخل Page را بررسی خواهیم کرد که داده‌ ها که درج می ‌کنیم در Page چطور ذخیره می‌ شوند.

EXEC SP_HELPFILE;
GO

مشاهده محتویات Page

برای اینکه محتوایات یک Page را مشاهده کنید از کوئری‌های که در ادامه آمده است می‌توانید استفاده کنید.اگر خروجی دستور را ببینید مشاهده می‌ کنید که ما در جدول Customer یک رکورد فقط ثبت کرده بودیم مقدار دیتاتایپ آن را از نوع fixed length 1000بایت قرار داده بودیم و طبق مواردی که بیش از این بیان شد در مورد فضای در دسترس Page که برابر بود با ۸۰۶۰ بایت بود. که با این احتساب یک رکوردی که ما درج کردیم یک Page را به خود اختصاص داده است. در صورتی که در زمان طراحی جداول به مورد توجه نداشته باشید این مورد باعث می ‌شود که اگر تعداد رکورد‌های که قرار است بیش‌تر از ظرفیت یک Page باشد رکورد‌های که در یک Page جا نشده ‌اند در یک Page جدیدی نوشته و ذخیره خواهد شد.
ما در SQL Server داده‌ ها را به دو دسته می‌ توانیم تقسیم کنیم. یک بخشی که کاربر اطلاعات (User Data) را وارد می‌ کند، و بخش دیگری که اصطلاحاً مِتادیتا(Meta Data) نیز گفته می ‌شود که خود SQL Sever از آن استفاده می ‌کند. چون زمانی که شما یک دستوری سمت دیتابیس ارسال می ‌کنید یک اطلاعاتی را از جدول واکشی کند و به شما نمایش دهد، SQL Server تمامی Page ها در زمان واکشی اطلاعات جست و جو نمی ‌کند. SQL Server درخواست شما جهت واکشی اطلاعات به IAM (index allocation maps) مراجعه کرده و آدرس Page را از این بخش دریافت می‌ کند و Page مورد نیاز فراخوانی کرده و اطلاعات را به شما نمایش می ‌دهد. اگر به تصویری که در ادامه آمده است با دقت نگاه کنید این مورد به خوبی قابل درک خواهد بود.
توسط دستور زیر می ‌توانید شماره Pageمربوط به اطلاعات جدول Customer را بدست آورید.

DBCC IND('PageAnatomy','Customer',1);
GO

برای اینکه محتویات یک Page را بدست بیارید می ‌توانید از کوئری زیر استفاده کنید. ابتدا فلگی به شماره DBCC 3604 را ON کنیم تا بتوانیم محتوا یک Page را نمایش دهیم. بعد از فعال کردن فلگ مربوطه از دستور DBCC PAGE استفاده می ‌کنیم برای نمایش جزئیات یک Page. که در ادامه کوئری مربوط به مواردی که گفته شده آمده است.

DBCC TRACEON (3604);
GO
DBCC PAGE ('PageAnatomy',1,8096,3);
GO

نتیجه گیری

در این نوشتار سعی کرده‌ایم اطلاعات لازم در خصوص Page ارائه دهیم برای اینکه دانش خودتان را در این زمینه بالا ببرید در مجموعه نیک آموز یک دوره با عنوان دوره آموزشی «Performance & Tuning» تهیه شده است. می‌ توانید با گذراندن این دوره دید خیلی خوبی بر روی کارایی SQL Server داشته باشید و با تسلط بر مفاهیم مروبط به Pageها باعث می ‌شود در بروز مشکلات آن‌ ها را رفع و رجوع کنید.