بررسی جداول Heap و Clustered در SQL Server

جداول Heap، یکی از مسائلی که در ابتدای ایجاد یک جدول لازم است به آن توجه نماییم آن است که آیا جدول را با یک ایندکس Cluster ایجاد نماییم یا خیر. جدولی که دارای یک Cluster Index باشد را Cluster Table گویند بنابراین جداول فاقد چنین ساختاری به جداول Heap معروف است. داده‌ها در یک  Cluster Table دارای چینش فیزیکی مرتب شده‌ هستند که باعث می‌شود تا در کسری از زمان رکوردهای مورد نظر در کوئری با توجه به کلید ایندکس واکشی شوند. بنابراین بدیهی است یک جدول می‌تواند یا به‌ صورت Heap ایجاد گردد و یا حداکثر یک Cluster Index داشته باشد. پیشنهاد میکنیم برای درک بهتر مفاهیم آموزش جامع SQL Server را مطالعه کنید.

جداول Heap

اما با این وجود، واکشی داده از یک Cluster Table نیز می‌تواند طوری کند شود که اعتراض تمامی کاربران را به همراه داشته باشد. این امر زمانی رخ می‌دهد که ایندکس مورد نظر Fragment شده باشد. مسئله Fragmentation می‌تواند براثر عملیات DML رخ دهد. به‌عبارت‌دیگر، اگر در جدول دارای ایندکس کلاستر عملیات Insert, Delete و Update انجام شود ایندکس کلاستر دچار تکه‌تکه شدگی می‌شود زیرا داده‌های موجود در Page طوری تغییر نموده‌اند که لازم است SQL محل فیزیکی آن‌ها را تغییر دهد تا مرتب‌سازی داده‌ها رعایت گردد.

ویژگی‌های جداول Heap

• داده‌ها فاقد چینش مرتب شده می‌باشند.
• واکشی رکوردها به‌کندی صورت می‌گیرد (مگر آنکه روی جدول مورد نظر یک Non-Cluster Index وجود داشته باشد)
• از آنجایی‌که Page‌ها به یکدیگر هیچ‌گونه Link ندارند بنابراین لازم است SQL مراجعات زیادی به IAM Page‌ها داشته باشد و به ازای واکشی اطلاعات هر Page حداقل یک بار به IAM Page مراجعه کند.
• با توجه به عدم وجود Cluster Index، عملیات Insert با سرعت بیشتری انجام می‌گیرد.
• فضای کمتری را اشغال می‌کند زیرا وجود ایندکس‌ها خود باعث افزایش استفاده از دیسک می‌شود. دلیل این امر هم وجود اشاره‌گرها و تشکیل B-Tree است که SQL برای ایجاد ایندکس‌ها تشکیل می‌دهد.
• برای جداول Heap، مقدار Index_Id برابر صفر در نظر گرفته می‌شود.

use [DB_Name]
select object_id,index_id,type_desc
from sys.indexes
where   object_id = OBJECT_ID('HeapTableName');

نحوه واکشی داده‌های موجود در جداول Heap

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

ویژگی‌های Cluster Table

• داده‌ها بر اساس کلید کلاستر مرتب‌سازی می‌شوند.
• واکشی داده‌ها (کوئری‌های Select) بر اساس کلید کلاستر بسیار سریع است. اما دستورات Insert، Delete و Update کندتر است.
• Data Page‌ها به یکدیگر لینک هستند. این کار باعث می‌شود پیمایش داده‌ها سریع‌تر انجام گیرد.
• به فضای ذخیره سازی بیشتری نیاز است زیرا لازم است تا SQL Server درخت B-Tree را برای ایندکس مربوطه ایجاد و ذخیره نماید.
• برای Table Cluster، مقدار Index_Id برابر یک در نظر گرفته می‌شود.

use [DB_Name]
select object_id,index_id,type_desc
from sys.indexes
where   object_id = OBJECT_ID('ClusterTableName');

مسئله Forwarding Pointer

زمانی که در یک جدول Heap قصد دارید تا رکوردی را Update کنید SQL Server سعی می‌کند رکورد مورد نظر را در همان Page نگهداری کند و عملیات Update را انجام دهد. اما گاهی این کار امکان‌پذیر نیست. فرض کنید جدولی به‌صورت زیر موجود باشد:

create table dbo.ForwardingPointersDB
(
ID int not null,
Val varchar(8000) null
);
insert into dbo.ForwardingPointersDB(ID,Val) values(1,null),(2,replicate('2',7900)),(3,null);

می‌توانیم ببینیم تعداد Page‌های استفاده شده برای این جدول برابر یک است زیرا مجموع سایز سه رکورد کمتر از ۸۰۶۰ Byte می‌باشد. (حداکثر سایز یک Page برای ذخیره داده‌ها برابر ۸۰۶۰ بایت است.) پیشنهاد میکنیم برای درک بهتر مفاهیم دوره کوئری نویسی پیشرفته را مطالعه کنید.

select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent
,forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.ForwardingPointersDB'),0
,null,'DETAILED'
);

۹۸% Page مربوطه اشغال‌ شده است. حال اگر مقدار دو رکورد ۱ و ۳ را Update کنیم، از آنجایی‌ که Page مربوطه ظرفیت نگهداری رکوردها را ندارد مجبور می‌شود رکوردها را در Page مجزا درج کند. بنابراین مشاهده می‌شود تعداد Page‌های مربوط به جدول افزایش می‌یابد. در نگاه اول این موضوع یک مسئله ساده در نظر گرفته می‌شود اما خواهیم دید پس از انجام عملیات Update تعداد Forwarding Count از صفر به عدد ۲ تغییر می‌یابد.

دلیل این موضوع آن است که SQL Server با استفاده از Forwarding Pointer سربار ناشی از به‌روزرسانی Non-Cluster Index را کاهش می‌دهد و نیازی ندارد برای کلید ایندکس عملیات بروز رسانی آدرس Page جدید را انجام دهد و همانند قبل به همان Page1 به ازای کلید مربوطه اشاره می‌کند و از آنجا به Page‌های ۲ و ۳ منتقل می‌شود. اما این کار سربار ناشی از Logical Read را افزایش می‌دهد زیرا باعث می‌شود Pageهای ۲ و ۳ دو بار خوانده شوند. به‌عبارت‌دیگر یک بار از طریق Forwarding Pointer این اتفاق می‌افتد و دفعه بعد از طریق آدرس بروز شده‌ای که در IAM Page به ازای آن Pageها وجود دارد رخ می‌دهد. این امر زمانی بیشتر تأثیر منفی می‌گذارد که تعداد Forwarding Pointer‌ ها افزایش یابد.

سخن پایانی

از نسخه ۲۰۰۸ SQL Server قابلیت جدیدی را مایکروسافت اضافه نموده است که می‌توان مسئله Forwarding Pointer را حل نمود. داده‌ها فاقد چینش مرتب شده می‌باشند. کد دستوری زیر عملیات Rebuild کردن جداول Heap را برای رفع مشکل Forwarding Pointer نشان می‌دهد. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

ALTER TABLE [ForwardingPointersDB] REBULD