بررسی Rebuild و Reorganizing ایندکس­ها:

بررسی Rebuild و Reorganizing ایندکس­ها:

داده­های ذخیره شده در جداول پایگاه داده با درج رکوردهای جدید و همچنین بروزرسانی یا حذف رکوردهای موجود در حال تغییر هستند. این منجر به وضعیتی می­شود که Data Page ها پر نیستند و یا نظم و ترتیب منطقی و فیزیکی Page ها بهم می­خورد و یا هر دو مورد اتفاق می­افتد. باید در نظر داشت که Disk based Index ها در معرض Fragmentation هستند. دو شکل از Fragmentation در B-Tree ها رخ می­دهد:

• Internal Fragmentation (تکه تکه شدن داخلی)
• External Fragmentation (تکه تکه شدن خارجی)

Internal Fragmentation : تکه تکه شدن داخلی به Pageهایی اشاره دارد که فضای خالی زیادی دارند. این مسئله با تراکم صفحه یا Page Density رابطه مستقیمی دارد. اگر Pageها فضای خالی زیادی داشته باشند، SQL Server برای برگرداندن تمام رکوردهای مورد نیاز برای یک Query نیاز به خواندن Pageهای بیشتری از آنچه لازم است دارد. همچنین وقتی Page Density کم باشد، Pageهای بیشتری برای برای ذخیره همان مقدار داده مورد نیاز است. به بیان ساده این یعنی هم برای خواندن و نوشتن داده­ها I/O بیشتری لازم است و هم برای ذخیره سازی داده­ها حافظه­ی بیشتری مورد نیاز است. باید دقت داشت که Page Density در طول زمان به دلیل Page Split کاهش می­یابد.

تذکر: برای جلوگیری از کاهش بی­مورد تراکم صفحه، مایکروسافت توصیه نمی­کند که Fill Factor را روی مقادیری غیر از 100 یا صفر تنظیم کنید، جز در موارد خاص برای ایندکس­هایی که تعداد زیادی Page Split را تجربه می­کنند.

توضیح: Fill Factor پارامتری است که کنترل می­کند که چه مقدار فضای خالی در هر Data Page باقی بماند.

External Fragmentation : تکه تکه شدن خارجی زمانی بوجود می­آید که ایندکس­ها دارای Pageهایی باشند که در آنها ترتیب منطقی درون ایندکس­ براساس مقادیر کلید شاخص، با ترتیب فیزیکی صفحات ایندکس مطابقت نداشته باشد. به بیان ساده تر تکه تکه شدن خارجی به خارج شدن صفحات ایندکس از نظم فیزیکی اشاره دارد.  تکه تکه شدن خارجی می­تواند به دلیل Page Split ایجاد شود. این مسئله می­تواند به شدت Performance را کاهش بدهد، زیرا داده­ها را نمی­توان بطور متوالی از دیسک خواند.

حال که با موضوع Fragmentation  داخلی و خارجی و تاثیر آنها بر کاهش Performance آشنا شدیم، این سوال مطرح می­شود که چطور می­توانیم این دو پدیده را از بین ببریم؟ ما می­توانیم با Reorganize (سازماندهی مجدد)  کردن و یا با Rebuild (بازسازی) کردن ایندکس تکه تکه شدن­های داخلی و خارجی ایندکس­ها را از بین ببیریم و یا باعث کاهش Fragmentation و افزایش Page Density بشویم.

Reorganize Index:

هنگامیکه یک ایندکس را Reorganize (سازماندهی مجدد) می­کنید، SQL Server داده­ها را در سطح Leaf Level ایندکس سازماندهی مجدد می­کند. سازماندهی مجدد یک ایندکس یک عملیات  همیشه آنلاین است. این فرآیند طولانی مدت نیست و قفل­های Object-Level بلند مدت نگهداری نمی­شوند، به همین دلیل برای Maintenanceهای روزانه می­تواند گزینه­ی مناسبی باشد.

Rebuild Index:

هنگامیکه یک ایندکس را Rebuild (بازسازی) می­کنید باعث Drop شدن و Re-Create شدن (ایجاد مجدد) ایندکس می­شود. بسته به نوع ایندکس و نسخه­ی Database Engine عملیات بازسازی ایندکس را می­توان بصورت آفلاین و یا آنلاین انجام داد. بازسازی ایندکس آفلاین معمولا زمان کمتری نسبت به بازسازی آنلاین نیاز دارد، اما قفل­های سطح آبجکت (Object-Level) را برای طول مدت زمان عملیات بازسازی نگه می­دارد و دسترسی Queryها به جداول و Viewها را مسدود می­کند. بازسازی ایندکس آنلاین تا پایان عملیات نیازی به قفل­های Object-Level بلند مدت ندارد. هنگامیکه تکه تکه شدن به شدت اتفاق می­افتد (معمولا بیشتر در جداول ایندکس شده با داده­های بزرگ) عموما نیاز داریم که کل ایندکس­ها را در چنین جداولی بازسازی کنیم.

مقایسه Reorganize Index و Rebuild Index:

• سازماندهی مجدد نسبت به بازسازی به منابع کمتری نیاز دارد.
• سازماندهی مجدد همیشه یک عملیات آنلاین است در حالیکه در بازسازی بسته به نوع ایندکس و نسخه­ی Database Engine عملیات بازسازی ایندکس را می­توان بصورت آفلاین و یا آنلاین انجام داد. در عملیات آنلاین قفل­های Object-Level بلند مدت نگهداری نمی­شوند.
• بازسازی جداول سیستمی و statistics را تحت تاثیر قرار می­دهد و ایندکس­های غیر فعال را فعال می­کند، در حالیکه سازماندهی مجدد یک عملیات پاکسازی خالص (Pure Cleanup Operation) است که تمام وضعیت سیستم را هانطور که هست باقی می­گذارد.
• سازماندهی مجدد Defragmentation را  فقط در سطح B-Tree  Leaf Level انجام می­دهد، در حالیکه بازسازی ایندکس کل B-Tree را تغییر می­دهد و ایندکس را دوباره ایجاد می­کند.
• در بازسازی کمی concurrency را از دست می­دهیم، اما سازماندهی مجدد برای concurrency بهتر است.
• سازماندهی مجدد از Extra Work Space کمی برای انجام عملیات Defrag استفاده می­کند در حالیکه بازسازی دوباره به اندازه­ی ایندکس از Extra Work Space استفاده می­کند.

Best Practices (بهترین روش) و توصیه­ها:

براساس اسناد مایکروسافت به دلیل اینکه سازماندهی مجدد یک ایندکس نسبت به بازسازی ایندکس به منابع کمتری نیاز دارد توصیه شده است که روش نگهداری ترجیحی شما باشد، مگر اینکه دلیل خاصی برای استفاده از بازسازی ایندکس وجود داشته باشد.همچنین مایکروسافت پیشنهاد می­کند که وقتی ایندکس­ها بیش از 30%، Fragmentation دارد، Rebuild کنیم و زمانیکه Fragmentation بین 5%  تا 30%  است، Reorganize نمائیم.همچنین در سند دیگری اشاره شده است که عموما  سطوح Fragmentation 10% یا کمتر نباید مشکلی در Performance ایجاد کند، بنابراین نیازی به انجام کاری ندارید و می توانید آنرا نادیده بگیرید. شاید به همین دلیل است که در برخی از اسناد توصیه شده است که  Fragmentation بین 10% تا 30% را Reorganize نمائید (نه 5% تا 30%).

در صورتی پایگاه داده شما یک پایگاه داده ایستا (مانند بایگانی) است و شما فقط از روی آن می­خوانید، بازسازی ایندکس مهم نیست زیرا داده­ها تغییر نمی کنند، اما اگر پایگاه داده شما دارای مقادیر زیادی درج/بروزرسلنی و حذف باشد، بازسازی ممکن است نقش مهمی همراه با بروزرسانی Statistics داشته باشد.

نکته: SQL Server بطور خودکار Statistics را پس از بازسازی ایندکس بروز می­کند. این بروز رسانی Statistics معادل بروز رسانی Statistics با Full Scan است و Column Statistics را به روز نمی­کند و ما باید Column Statistics را پس از Rebuild ایندکس بروز کنیم.