اIndex Fragmentation در SQL Server

Index Fragmentation قابل پیش‌بینی در SQL Server: درک و مدیریت کارایی

Index Fragmentation یکی از چالش‌های رایج در مدیریت پایگاه داده است که می‌تواند به طور قابل توجهی بر کارایی SQL Server تأثیر بگذارد. برخلاف تصور عمومی که Index Fragmentation همیشه غیرقابل پیش‌بینی است، در واقع می‌توان بسیاری از الگوهای آن را پیش‌بینی کرد. این مقاله به بررسی چگونگی پیش‌بینی و درک Index Fragmentation، با تمرکز بر دو عامل اصلی: Page Splits (Page Splits) و عملیات حذف (Deletes) می‌پردازد. با درک این مکانیسم‌ها، می‌توانید استراتژی‌های موثرتری برای حفظ عملکرد بهینه پایگاه داده خود توسعه دهید.

**Page Splits و Index Fragmentation**

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

برای محاسبه میزان فضای خالی اولیه در یک صفحه، می‌توانیم از فرمول زیر استفاده کنیم:

این فرمول، درصد فضای خالی تنظیم شده برای ایندکس را به یک کسر اعشاری تبدیل می‌کند. برای مثال، اگر `FILL FACTOR` ایندکس 90 باشد، آنگاه 10 درصد فضای آزاد خواهید داشت، و این فرمول به شما 0.10 را می‌دهد. این مقدار در پارامترهای `CREATE INDEX` یا `ALTER INDEX` به عنوان `FILL FACTOR` مشخص می‌شود. مقدار 0 (یا 100) به این معنی است که هیچ فضای خالی در صفحات برگ (leaf pages) باقی نمی‌ماند.

فرض کنید ایندکسی با `FILL FACTOR` 90 ایجاد شده است. این بدان معناست که 10 درصد فضای هر صفحه، خالی نگه داشته می‌شود. با هر درج ردیف جدید در یک صفحه، این فضای خالی کاهش می‌یابد. درصد فضای آزاد استفاده شده پس از `N` درج در یک صفحه، با فرمول زیر محاسبه می‌شود:

در این فرمول:
* `N` تعداد ردیف‌های درج شده است.
* `RowsPerInitialPage` تعداد ردیف‌هایی است که صفحه در ابتدا می‌تواند نگه دارد.
* `PageFreeSpacePercentage` کسر فضای آزاد اولیه است (مثلاً 0.10 برای `FILL FACTOR` 90).

درصد فضای آزاد باقی‌مانده پس از `N` درج در یک صفحه را می‌توان این‌گونه محاسبه کرد:

برای درک بهتر، می‌توانیم تعداد درج‌هایی را که قبل از تقسیم صفحه رخ می‌دهند، پیش‌بینی کنیم. این عدد با فرمول زیر بدست می‌آید:

اگر یک صفحه 100 ردیف را در خود جای دهد و `FILL FACTOR` 90 باشد (10% فضای آزاد)، آنگاه تقریباً 10 درج قبل از تقسیم صفحه رخ خواهد داد.

حال به سراغ محاسبه واقعی Index Fragmentation می‌رویم. ابتدا نیاز به محاسبه تعداد کل صفحات ایندکس داریم. این شامل تمام صفحات در هر سطح ایندکس (غیر از صفحات برگ) است.

سپس تعداد صفحاتی که داده‌های واقعی را نگهداری می‌کنند:

و تعداد صفحات خالی (به دلیل `FILL FACTOR`):

هنگامی که یک تقسیم صفحه رخ می‌دهد، یک صفحه جدید در جایی در فایل داده ایجاد می‌شود. اگر این صفحه جدید به ترتیب منطقی در کنار صفحه قبلی قرار نگیرد، تجزیه ایجاد می‌شود. تعداد صفحات جدید مورد نیاز به دلیل Page Splits، با فرمول زیر محاسبه می‌شود:

با داشتن این اطلاعات، می‌توانیم درصد Index Fragmentation ناشی از Page Splits را به دست آوریم:

با استفاده از این فرمول، می‌توانید میزان Index Fragmentation را بر اساس تعداد درج‌ها و تنظیمات `FILL FACTOR` پیش‌بینی کنید. این به شما کمک می‌کند تا تصمیم بگیرید چه زمانی بازسازی یا سازماندهی مجدد ایندکس‌ها ضروری است.

**حذف (Deletes) و Index Fragmentation**

عملیات حذف نیز می‌تواند به Index Fragmentation کمک کند، اما به روشی متفاوت از Page Splits. هنگامی که ردیف‌ها از یک صفحه حذف می‌شوند، فضای خالی ایجاد می‌شود. اگر SQL Server تصمیم بگیرد این فضای خالی را بازیابی کند و صفحات را به عقب منتقل کند (Compact کند)، می‌تواند به کاهش تجزیه کمک کند. اما اگر ردیف‌ها حذف شوند و صفحات ایندکس خالی بمانند و بازیابی نشوند، این صفحات به عنوان “فضای هدر رفته” باقی می‌مانند و ایندکس را از نظر منطقی تجزیه می‌کنند، حتی اگر به طور فیزیکی مرتب باشند.

برای محاسبه درصد Index Fragmentation ناشی از حذف ردیف‌ها، می‌توانیم مراحل زیر را دنبال کنیم. فرض کنید تعداد کل ردیف‌ها به شرح زیر است:

تعداد ردیف‌ها در هر صفحه را می‌توان این‌گونه تخمین زد:

تعداد کل صفحات ایندکس:

پس از حذف تعدادی ردیف، تعداد صفحات باقی‌مانده در ایندکس (در صورتی که فضای آزاد شده بازیابی نشود) به این صورت خواهد بود:

سپس، درصد Index Fragmentation ناشی از حذف‌ها را می‌توان با فرمول زیر محاسبه کرد:

این فرمول به شما کمک می‌کند تا میزان “فضای هدر رفته” را به عنوان درصدی از کل ایندکس ارزیابی کنید. اگر صفحات خالی زیادی وجود داشته باشد، این نشان‌دهنده نیاز به بازسازی یا سازماندهی مجدد ایندکس است.

**ترکیب عوامل Index Fragmentation**

Index Fragmentation اغلب نتیجه ترکیبی از Page Splits و عملیات حذف است. در نهایت، درصد کلی Index Fragmentation (Fragmentation Percentage) را می‌توان با ترکیب تأثیرات هر دو عامل محاسبه کرد. این یک فرمول ساده برای جمع‌بندی نیست، بلکه نیازمند در نظر گرفتن هر دو مکانیسم به طور همزمان و نحوه تأثیرگذاری آن‌ها بر چینش فیزیکی و منطقی صفحات ایندکس است. به طور کلی، ما با استفاده از ابزارهای داخلی SQL Server مانند `DMVs` (`sys.dm_db_index_physical_stats`) و یا ابزارهای مانیتورینگ می‌توانیم این تجزیه را مشاهده کنیم و با استفاده از فرمول‌های بالا، دلیل اصلی و میزان سهم هر عامل را پیش‌بینی کنیم.

این فرمول تقریبی برای درک کلی است، اما در واقعیت، تعامل پیچیده‌تری بین این دو عامل وجود دارد. مهم این است که به هر دو جنبه توجه شود.

**نتیجه‌گیری**

Index Fragmentation قابل پیش‌بینی است، نه یک پدیده تصادفی. با درک دقیق مکانیسم‌های Page Splits و حذف‌ها و استفاده از فرمول‌های ارائه شده، متخصصان پایگاه داده و مهندسان `SQL Server` می‌توانند بهتر Index Fragmentation‌ها را پیش‌بینی کنند. این دانش به شما امکان می‌دهد تا استراتژی‌های فعال‌تری برای مدیریت ایندکس‌ها اتخاذ کنید، مانند تنظیم مناسب `FILL FACTOR`، زمان‌بندی بهینه عملیات بازسازی و سازماندهی مجدد ایندکس‌ها، و در نهایت حفظ عملکرد بالا و پایدار سیستم‌های `SQL Server`. با نظارت و تحلیل مداوم، می‌توانید اطمینان حاصل کنید که ایندکس‌های شما همیشه در بهترین حالت خود کار می‌کنند.

**مثال کد SQL برای بررسی وضعیت Fragmentation:**

برای بررسی وضعیت فعلی Index Fragmentation‌ها در `SQL Server`، می‌توانید از `sys.dm_db_index_physical_stats` استفاده کنید.

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
JOIN
    sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE
    ips.avg_fragmentation_in_percent > 10 -- نمایش ایندکس‌هایی با بیش از 10% تجزیه
ORDER BY
    ips.avg_fragmentation_in_percent DESC;

این `کوئری SQL` به شما کمک می‌کند تا ایندکس‌هایی که بیشترین تجزیه را دارند شناسایی کنید و بر اساس آن تصمیمات مدیریتی مناسب را اتخاذ نمایید.