بهینه‌سازی Fragmentation ایندکس‌های کلاستر: راهنمای جامع تنظیم Fill Factor و کاهش Page Splits در SQL Server

Fragmentation ایندکس، یک چالش عملکردی رایج در SQL Server است که می‌تواند منجر به کاهش سرعت کوئری‌ها و افزایش سربار I/O شود. در این مقاله به بررسی عمیق Fragmentation ایندکس‌های کلاستر، نقش حیاتی Fill Factor در مدیریت آن و چگونگی کاهش Page Splits برای حفظ عملکرد بهینه پایگاه داده می‌پردازیم. با درک صحیح این مفاهیم، می‌توانید استراتژی‌های موثری برای بهبود پایداری و سرعت سیستم‌های SQL Server خود پیاده‌سازی کنید.

به یاد داشته باشید که اندازه صفحات در SQL Server 8KB است و این یک حقیقت مهم است که اغلب در هنگام صحبت در مورد Fragmentation نادیده گرفته می‌شود. هر صفحه 8192 بایت داده را ذخیره می‌کند و SQL Server همیشه به صورت داخلی بلوک‌های 8K را تخصیص می‌دهد. این موضوع حتی زمانی که شما هیچ ایندکسی ایجاد نمی‌کنید نیز صادق است. به عنوان مثال، اگر یک جدول هیپ (heap) دارید، همچنان صفحات 8K را در پشت صحنه دریافت می‌کنید.

در قسمت اول این سری، ما یک بررسی کلی از ایندکس‌های کلاستر و نحوه عملکرد آن‌ها ارائه دادیم. در این بخش، بر روی Page Splits و Fill Factor تمرکز خواهیم کرد. وقتی Fragmentation ایندکس به 5 درصد یا بالاتر می‌رسد، ممکن است نیاز به بازسازی یا سازماندهی مجدد ایندکس (rebuild or reorganize) داشته باشید. در حالی که این دو گزینه می‌توانند Fragmentation را کاهش دهند، اما باعث افزایش I/O برای بازسازی ایندکس نیز می‌شوند و بازسازی ایندکس به خصوص برای جداول بزرگ می‌تواند زمان‌بر باشد.

Fragmented Index – صفحات و داده‌ها

Fragmentation زمانی اتفاق می‌افتد که ترتیب منطقی صفحات یک ایندکس با ترتیب فیزیکی آن‌ها روی دیسک مطابقت نداشته باشد، یا زمانی که فضای خالی زیادی در صفحات وجود داشته باشد. این امر باعث می‌شود که SQL Server برای خواندن داده‌ها، صفحات بیشتری را جستجو کند و در نتیجه عملکرد کاهش یابد.

برای اندازه‌گیری حجم حافظه کش شده توسط SQL Server در بافر پول برای پایگاه داده جاری (با فرض اینکه دیتابیس در بافر پول بارگذاری شده)، می‌توانید از کوئری زیر استفاده کنید:

SELECT page_count * page_size FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID()

این کوئری تعداد صفحات کش شده برای پایگاه داده فعلی را در 8192 (اندازه صفحه) ضرب می‌کند تا کل بایت‌های کش شده را برگرداند. برای مشاهده این مقدار به مگابایت، کوئری زیر را اجرا کنید:

SELECT page_count * page_size / 1024 / 1024 FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID()

برای مشاهده جزئیات بیشتر در مورد Fragmentation ایندکس کلاستر خود، از دستور `DBCC SHOW_STATISTICS` استفاده کنید. این دستور اطلاعاتی در مورد توزیع داده‌ها و تراکم صفحات ایندکس را نشان می‌دهد.

DBCC SHOW_STATISTICS (N'TableName', N'IndexName')

این دستور یک جدول به نام `Density` و یک جدول `All density` را برمی‌گرداند. همچنین در خروجی این دستور، ستون‌های `Pages`, `Rows`, `Rows Sampled`, `Steps`, `Density`, `Average Length` وجود دارند. اگرچه مستقیماً Fragmentation را نشان نمی‌دهد، اما اطلاعاتی در مورد تراکم داده‌ها ارائه می‌دهد که می‌تواند به درک چگونگی پر شدن صفحات کمک کند.

Page Splits و Fill Factor

Page Split (تقسیم صفحه) زمانی اتفاق می‌افتد که SQL Server نیاز به درج یا به‌روزرسانی داده در یک صفحه دارد، اما آن صفحه پر است و فضای کافی برای رکورد جدید ندارد. در نتیجه، SQL Server صفحه را به دو قسمت تقسیم می‌کند و نیمی از داده‌ها را به یک صفحه جدید منتقل می‌کند. این فرآیند باعث Fragmentation منطقی و فیزیکی می‌شود، زیرا صفحات دیگر به ترتیب نیستند و ممکن است روی دیسک از هم دور باشند. همچنین Page Splits عملیات I/O اضافی ایجاد کرده و منجر به کاهش عملکرد می‌شود.

Fill Factor (ضریب پر شدن) یک تنظیم مهم برای ایندکس‌ها است که مشخص می‌کند هنگام ایجاد یا بازسازی ایندکس، چه مقدار از هر صفحه داده باید پر شود. به عنوان مثال، اگر Fill Factor را 70 تنظیم کنید، 70 درصد از هر صفحه ایندکس با داده پر می‌شود و 30 درصد باقی‌مانده به عنوان فضای خالی برای درج‌ها و به‌روزرسانی‌های آینده رزرو می‌شود.

وقتی ایندکسی با `FILLFACTOR = 70` ایجاد یا بازسازی می‌کنید، به SQL Server می‌گویید که فقط 70 درصد از فضای هر صفحه را پر کند و 30 درصد باقی‌مانده را خالی بگذارد. این کار باعث کاهش Page Splits می‌شود، زیرا فضای خالی برای درج داده‌های جدید در همان صفحه وجود دارد.

برای تنظیم Fill Factor هنگام ایجاد یک ایندکس کلاستر، می‌توانید از عبارت زیر استفاده کنید:

CREATE CLUSTERED INDEX IX_Clustered_Test ON dbo.TestTable (ID) WITH (FILLFACTOR = 70);

اگر ایندکس از قبل وجود دارد، می‌توانید آن را با Fill Factor مورد نظر بازسازی کنید:

ALTER INDEX [IndexName] ON [TableName] REBUILD WITH (FILLFACTOR = 70);

نکته مهم: Fill Factor فقط هنگام ایجاد یا بازسازی ایندکس اعمال می‌شود. در طول عملیات عادی درج و به‌روزرسانی، SQL Server فضای خالی موجود در صفحات را استفاده می‌کند تا زمانی که آن صفحه پر شود. Fill Factor روی عملیات `REORGANIZE` تأثیری ندارد. `REORGANIZE` فقط Fragmentation منطقی را از بین می‌برد، در حالی که `REBUILD` Fragmentation فیزیکی و منطقی را برطرف کرده و می‌تواند Fill Factor را مجدداً اعمال کند.

مثال عملی: بررسی تأثیر Fill Factor
بیایید با یک مثال عملی تأثیر Fill Factor را بررسی کنیم. ابتدا یک جدول آزمایشی ایجاد می‌کنیم:

ALTER TABLE [dbo].[TestTable] SET ( SYSTEM_VERSIONING = OFF );
DROP TABLE IF EXISTS [dbo].[TestTable];

CREATE TABLE [dbo].[TestTable](
    [ID] [int] NOT NULL,
    [SomeString] [nvarchar](250) NULL,
    [SomeInt] [int] NULL
);

سپس یک ایندکس کلاستر با Fill Factor پیش‌فرض (0 یا 100) ایجاد می‌کنیم. Fill Factor = 0 همان Fill Factor = 100 است.

ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];

حالا 100000 رکورد را به صورت ترتیبی درج می‌کنیم:

DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT INTO dbo.TestTable (ID, SomeString, SomeInt) VALUES (@i, REPLICATE('A', 200), @i);
    SET @i = @i + 1;
END;

برای مشاهده جزئیات ایندکس شامل Fragmentation و درصد فضای استفاده شده از صفحات، از `sys.dm_db_index_physical_stats` استفاده می‌کنیم. قبل از آن، بافر پول را پاک می‌کنیم تا اطمینان حاصل شود که آمار از دیسک خوانده می‌شود:

TRUNCATE TABLE dbo.TestTable; -- برای شروع تمیز
DBCC DROPCLEANBUFFERS; -- پاک کردن بافر پول

-- درج مجدد داده‌ها بدون Fill Factor
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT INTO dbo.TestTable (ID, SomeString, SomeInt) VALUES (@i, REPLICATE('A', 200), @i);
    SET @i = @i + 1;
END;

SELECT 
    object_name(p.object_id) AS TableName,
    i.name AS IndexName,
    p.index_id,
    p.index_type_desc,
    alloc_unit_type_desc,
    page_count,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
JOIN sys.partitions AS p ON ps.object_id = p.object_id AND ps.index_id = p.index_id
JOIN sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.object_id = OBJECT_ID(N'dbo.TestTable');

در این حالت (بدون Fill Factor یا Fill Factor = 100)، `avg_fragmentation_in_percent` باید نزدیک به 0 باشد و `avg_page_space_used_in_percent` باید نزدیک به 100 درصد باشد، زیرا درج‌ها ترتیبی بوده‌اند و نیازی به Page Split نبوده است. تعداد صفحات استفاده شده نیز نمایش داده می‌شود.

حالا، جدول را پاک کرده، ایندکس کلاستر را بازسازی می‌کنیم، این بار با `FILLFACTOR = 70`. سپس همان 100000 رکورد را درج می‌کنیم.

TRUNCATE TABLE dbo.TestTable;
DBCC DROPCLEANBUFFERS;

ALTER INDEX [PK_TestTable] ON [dbo].[TestTable] REBUILD WITH (FILLFACTOR = 70);

DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
    INSERT INTO dbo.TestTable (ID, SomeString, SomeInt) VALUES (@i, REPLICATE('A', 200), @i);
    SET @i = @i + 1;
END;

SELECT 
    avg_fragmentation_in_percent, 
    page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.TestTable'), 1, NULL, 'DETAILED');

با `FILLFACTOR = 70` و درج ترتیبی، `avg_fragmentation_in_percent` همچنان نزدیک به 0 خواهد بود. اما `avg_page_space_used_in_percent` باید تقریباً 70 درصد باشد و `page_count` (تعداد صفحات) افزایش یافته باشد، زیرا صفحات عمداً کمتر پر شده‌اند. این نشان می‌دهد که Fill Factor به درستی اعمال شده است.

چه زمانی از Fill Factor کم استفاده کنیم؟
استفاده از Fill Factor کمتر از 100 زمانی مفید است که شما:
جداول با نرخ بالای درج (INSERT) و به‌روزرسانی (UPDATE) دارید: به خصوص جداولی که رکوردهای جدید در وسط ایندکس درج می‌شوند (نه فقط در انتهای ایندکس).
با Fragmentation مکرر مواجه هستید: Fill Factor پایین می‌تواند تعداد Page Splits را به طور قابل توجهی کاهش دهد.
عملیات بازسازی ایندکس را نمی‌توانید به دفعات زیاد انجام دهید: با فضای خالی بیشتر، مدت زمان بین نیاز به بازسازی ایندکس افزایش می‌یابد.

چه زمانی از Fill Factor کم استفاده نکنیم؟
جداول عمدتاً ثابت (Static) یا فقط خواندنی (Read-Only) که به‌روزرسانی یا درج کمی دارند: در این جداول، نیازی به فضای خالی برای درج‌های آینده نیست و Fill Factor 100 بهترین استفاده از فضا را فراهم می‌کند.
جداول با درج ترتیبی (Sequential Inserts) در انتهای ایندکس: اگر تمام درج‌ها در انتهای ایندکس انجام می‌شوند، Page Splits اتفاق نمی‌افتد و Fill Factor پایین فقط باعث هدر رفتن فضا می‌شود.
محدودیت فضای ذخیره‌سازی دارید: Fill Factor پایین به معنای استفاده از صفحات بیشتر و در نتیجه فضای دیسک بیشتر است.
افزایش I/O برای خواندن: با Fill Factor پایین، داده‌ها در صفحات بیشتری پراکنده می‌شوند، بنابراین SQL Server برای خواندن همان مقدار داده نیاز به خواندن صفحات بیشتری دارد که می‌تواند I/O را افزایش دهد.

نتیجه‌گیری

Fill Factor یک ابزار قدرتمند برای مدیریت Fragmentation ایندکس‌ها و کاهش Page Splits در SQL Server است. با این حال، انتخاب Fill Factor مناسب نیازمند درک عمیق الگوی دسترسی به داده‌ها در جداول شماست. Fill Factor پایین‌تر می‌تواند عمر مفید یک ایندکس را بین بازسازی‌ها افزایش دهد، اما هزینه آن افزایش فضای دیسک و احتمالاً افزایش I/O برای عملیات خواندن است. با تحلیل دقیق نیازهای سیستم خود و آزمون و خطا، می‌توانید بهترین استراتژی Fill Factor را برای بهینه‌سازی عملکرد پایگاه داده SQL Server خود پیدا کنید.