مفهوم Fragmentation (تکه‌تکه شدن) در SQL Server

• علت:
  • عملیات‌های درج، حذف یا به‌روزرسانی که باعث تغییر در ساختار ایندکس یا جدول می‌شوند.
  • تخصیص غیرپیوسته صفحات جدید به جدول یا ایندکس.
• تأثیر:
  • SQL Server برای خواندن داده‌ها نیاز به پرش بین صفحات غیرمرتبط در دیسک دارد، که باعث افزایش زمان اجرای کوئری‌ها می‌شود.

دلایل ایجاد Fragmentation

• عملیات DML (Data Manipulation Language):
  • Insert: افزودن رکوردهای جدید می‌تواند باعث تخصیص صفحات جدید شود، به‌خصوص اگر ایندکس‌ها به ترتیب خاصی مرتب نباشند.
  • Update: تغییر اندازه رکوردها (مثلاً افزایش طول داده در یک ستون) می‌تواند باعث جابه‌جایی داده‌ها و ایجاد فضای خالی شود.
  • Delete: حذف رکوردها فضای خالی در صفحات ایجاد می‌کند.
• عدم نگهداری منظم ایندکس‌ها: اگر ایندکس‌ها به طور دوره‌ای بازسازی (Rebuild) یا سازمان‌دهی (Reorganize) نشوند، تکه‌تکه شدن افزایش می‌یابد.
• رشد سریع جدول: جداولی که به سرعت رشد می‌کنند (مثلاً در برنامه‌های پرتراکنش) بیشتر در معرض Fragmentation هستند.
• Fill Factor نامناسب: تنظیم نادرست Fill Factor (درصد پر شدن صفحات ایندکس) می‌تواند باعث Internal Fragmentation شود. برای مثال Fill Factor پایین باعث فضای خالی زیاد و Fill Factor بالا باعث Page Splits (تقسیم صفحات) می‌شود.
• Page Splits: وقتی یک صفحه پر می‌شود و رکورد جدیدی باید به آن اضافه شود، SQL Server صفحه را به دو صفحه تقسیم می‌کند، که این باعث پراکندگی داده‌ها و External Fragmentation می‌شود.

قش Page Split در افزایش Fragmentation

یکی از مهم‌ترین عواملی که باعث افزایش Fragmentation می‌شود، رخداد Page Split است.

هر Page در SQL Server دارای ظرفیت ثابتی برابر با ۸ کیلوبایت است. زمانی که صفحه کاملاً پر شده باشد و رکورد جدیدی باید در همان موقعیت منطقی درج شود، SQL Server مجبور است صفحه را به دو بخش تقسیم کند. بخشی از رکوردها به صفحه جدید منتقل می‌شوند و سپس لینک‌های ساختار B-Tree نیز به‌روزرسانی می‌شوند.

این فرآیند اگرچه برای حفظ ترتیب منطقی ایندکس ضروری است، اما هزینه قابل توجهی دارد. Page Split علاوه بر ایجاد عملیات نوشتن بیشتر روی دیسک، باعث افزایش Log Generation، افزایش مصرف CPU، کاهش Page Density و در نهایت افزایش Fragmentation خواهد شد.

به همین دلیل در جداولی که به طور مداوم داده‌های جدید در میان کلیدهای موجود درج می‌شوند، انتخاب صحیح کلید خوشه‌ای و تنظیم مناسب Fill Factor اهمیت بسیار زیادی دارد.

تأثیرات Fragmentation

• کاهش سرعت کوئری‌ها: به دلیل نیاز به خواندن صفحات پراکنده یا تعداد بیشتری از صفحات.
• افزایش I/O: پراکندگی داده‌ها باعث افزایش عملیات ورودی/خروجی دیسک می‌شود.
• مصرف بیشتر منابع: CPU و حافظه بیشتری برای پردازش کوئری‌ها نیاز است.
• افزایش فضای ذخیره‌سازی: Internal Fragmentation باعث هدر رفتن فضای دیسک می‌شود.
• تأخیر در عملیات‌های تراکنشی: در سیستم‌های پرتراکنش، Fragmentation می‌تواند تأخیر قابل توجهی ایجاد کند.

شناسایی Fragmentation

SELECT 
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent,
    page_count
FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabaseName'), NULL, NULL, NULL, 'DETAILED')
WHERE index_id > 0 -- فقط ایندکس‌ها (0 برای Heap است)
  AND page_count > 100 -- فقط ایندکس‌هایی با تعداد صفحات قابل توجه
ORDER BY avg_fragmentation_in_percent DESC;

• avg_fragmentation_in_percent: درصد تکه‌تکه شدن خارجی (External Fragmentation). مقادیر بالاتر نشان‌دهنده پراکندگی بیشتر است.
• avg_page_space_used_in_percent: درصد پر شدن صفحات (مربوط به Internal Fragmentation). مقادیر پایین‌تر نشان‌دهنده فضای خالی بیشتر است.
• page_count: تعداد صفحات در ایندکس یا جدول.

• avg_fragmentation_in_percent:
  • 0-10%: تکه‌تکه شدن کم (نیازی به اقدام نیست).
  • 10-30%: تکه‌تکه شدن متوسط (Reorganize پیشنهاد می‌شود).
  • 30%: تکه‌تکه شدن بالا (Rebuild پیشنهاد می‌شود).
• avg_page_space_used_in_percent:
  • 75%: پر شدن مناسب صفحات.
  • <75%: فضای خالی زیاد (Internal Fragmentation).

رفع Fragmentation

Reorganize Index

• توضیح: این عملیات ایندکس را به صورت آنلاین (Online) سازمان‌دهی می‌کند و صفحات را مرتب می‌کند بدون اینکه ایندکس را از نو بسازد. این روش برای رفع External Fragmentation و بهبود ترتیب صفحات مناسب است.
• مزایا:
  • عملیات سبک‌تر و کم‌هزینه‌تر از Rebuild.
  • به صورت آنلاین انجام می‌شود و تأثیر کمتری روی دسترسی به جدول دارد.
• معایب:
  • Internal Fragmentation را به طور کامل برطرف نمی‌کند.
  • برای Fragmentation شدید کافی نیست.
• دستور:

ALTER INDEX IndexName ON TableName REORGANIZE;

• زمان استفاده: وقتی avg_fragmentation_in_percent بین 10-30% است.

Rebuild Index

• مزایا:
  • تمام انواع تکه تکه شدن را برطرف می‌کند.
  • Fill Factor را بهینه می‌کند.
• معایب:
  • منابع بیشتری (CPU، I/O) مصرف می‌کند.
  • در حالت آفلاین (در نسخه‌های استاندارد) می‌تواند باعث قفل شدن جدول شود. در نسخه‌های Enterprise، می‌توان از گزینه ONLINE استفاده کرد.
• دستور:

ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);

• زمان استفاده: وقتی avg_fragmentation_in_percent بیش از 30% است یا Internal Fragmentation شدید است.

تنظیم Fill Factor

• Fill Factor تعیین می‌کند که صفحات ایندکس تا چه درصدی پر شوند. تنظیم مناسب Fill Factor می‌تواند از Fragmentation در آینده جلوگیری کند.
• مقادیر پیشنهادی:
  • برای جداول با تغییرات کم: Fill Factor بین 90-100%.
  • برای جداول با تغییرات زیاد (تراکنش‌های مکرر): Fill Factor بین 70-80%.
• دستور:

ALTER INDEX IndexName ON TableName REBUILD WITH (FILLFACTOR = 80);

آیا Fill Factor پایین همیشه انتخاب مناسبی است؟

یکی از رایج‌ترین اشتباهات DBAها این است که تصور می‌کنند کاهش Fill Factor همیشه باعث کاهش Fragmentation خواهد شد.

واقعیت این است که Fill Factor تنها فضای خالی بیشتری در صفحات ایجاد می‌کند تا احتمال وقوع Page Split کاهش یابد. اگر جدول به ندرت تغییر کند، این فضای خالی عملاً بلااستفاده خواهد ماند و تنها باعث افزایش حجم ایندکس و مصرف بیشتر Buffer Pool می‌شود.

از سوی دیگر، اگر جدول دارای نرخ بالای Insert یا Update باشد، کاهش منطقی Fill Factor می‌تواند تعداد Page Splitها را کاهش دهد و عملکرد کلی سیستم را بهبود بخشد.

بنابراین هیچ مقدار ثابتی برای Fill Factor وجود ندارد و مقدار مناسب باید بر اساس الگوی واقعی تغییرات داده‌ها، نوع بار کاری و نتایج مانیتورینگ انتخاب شود.

نگهداری خودکار

نکات مهم و بهترین روش‌ها

• بررسی منظم: Fragmentation را به صورت دوره‌ای (مثلاً هفتگی یا ماهانه) بررسی کنید، به‌خصوص برای جداول پرتراکنش.
• انتخاب روش مناسب: از Reorganize برای Fragmentation متوسط و از Rebuild برای Fragmentation شدید استفاده کنید.
• استفاده از ONLINE Option: در محیط‌های عملیاتی با دسترسی بالا، از گزینه ONLINE = ON در Rebuild استفاده کنید تا تأثیر روی کاربران کاهش یابد.
• توجه به Heap Tables: جداول بدون Clustered Index (Heap) نیز می‌توانند دچار Fragmentation شوند. برای رفع آن، می‌توانید Clustered Index اضافه کنید یا جدول را بازسازی کنید.
• مانیتورینگ عملکرد: بعد از رفع Fragmentation، عملکرد کوئری‌ها را با ابزارهایی مثل SQL Server Profiler یا Extended Events بررسی کنید.
• جلوگیری از Page Splits :Fill Factor را بهینه تنظیم کنید و از ستون‌های با طول متغیر (مثل VARCHAR) با دقت استفاده کنید.

تفاوت Fragmentation در Heap و Clustered Index

• Heap Tables: جداولی که Clustered Index ندارند، بیشتر در معرض Internal Fragmentation هستند، زیرا داده‌ها به صورت غیرمرتب ذخیره می‌شوند. برای رفع Fragmentation در Heap، می‌توانید جدول را بازسازی کنید یا Clustered Index اضافه کنید.
• Clustered Index: این ایندکس‌ها ترتیب منطقی داده‌ها را تعیین می‌کنند و بیشتر در معرض External Fragmentation هستند، به‌خصوص اگر درج‌ها و حذف‌ها مکرر باشند.

ابزارهای کمکی

• SQL Server Management Studio (SSMS): گزارش‌های استاندارد برای بررسی تکه تکه شدن ارائه می‌دهد.
• Maintenance Plans: برای خودکارسازی Reorganize و Rebuild.
• اسکریپت‌های T-SQL: برای بررسی و رفع تکه تکه شدن به صورت سفارشی.
• سوم‌-شخص ابزارها: ابزارهایی مثل Redgate SQL Monitor یا SolarWinds Database Performance Analyzer برای مانیتورینگ پیشرفته.

نتیجه گیری