مفهوم 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 می‌شود.

تأثیرات 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 > ۰ -- فقط ایندکس‌ها (۰ برای Heap است)
  AND page_count > ۱۰۰ -- فقط ایندکس‌هایی با تعداد صفحات قابل توجه
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:
  • ۰-۱۰%: تکه‌تکه شدن کم (نیازی به اقدام نیست).
  • ۱۰-۳۰%: تکه‌تکه شدن متوسط (Reorganize پیشنهاد می‌شود).
  • ۳۰%: تکه‌تکه شدن بالا (Rebuild پیشنهاد می‌شود).
• avg_page_space_used_in_percent:
  • ۷۵%: پر شدن مناسب صفحات.
  • <75%: فضای خالی زیاد (Internal Fragmentation).

رفع Fragmentation

Reorganize Index

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

ALTER INDEX IndexName ON TableName REORGANIZE;

• زمان استفاده: وقتی avg_fragmentation_in_percent بین ۱۰-۳۰% است.

Rebuild Index

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

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

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

تنظیم Fill Factor

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

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

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

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

• بررسی منظم: 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 برای مانیتورینگ پیشرفته.

نتیجه گیری