Index Maintenance در SQL Server، زمان‌بندی هوشمند Rebuild و Reorganize

پindex maintenance، sql server rebuild reorganize، fragmentation sql server، performance tuning، dba best practices، sql maintenance job، ola hallengren، query store plan، sql performance optimization، lanDa sql server

فهرست مطالب

در محیط‌های سازمانی با حجم بالای تراکنش، عملکرد Queryها به‌شدت وابسته به سلامت و وضعیت ایندکس‌هاست. از نگاه یک DBA ، نگهداری ایندکس‌ها (Index Maintenance) نه یک وظیفه جانبی، بلکه بخش حیاتی از Database Lifecycle است.
با گذشت زمان و انجام عملیات Insert، Update و Delete، ساختار ایندکس‌ها دچار Fragmentation می‌شود و اگر این مسئله نادیده گرفته شود، منجر به افت شدید Performance خواهد شد.

مفهوم Fragmentation در SQL Server

Fragmentation یعنی عدم پیوستگی فیزیکی صفحات داده در دیسک.
وقتی داده‌ها در صفحات غیرپیوسته ذخیره شوند، SQL Server برای خواندن داده‌ها باید پرش‌های بیشتری انجام دهد، که باعث افزایش I/O و کاهش سرعت Query می‌شود.

Fragmentation دو نوع دارد:

Internal Fragmentation
فضای خالی داخل صفحات داده زیاد است (به دلیل حذف یا به‌روزرسانی‌های مکرر).
External Fragmentation
صفحات ایندکس در سطح دیسک پیوسته نیستند؛ ساختار Logical ایندکس از حالت مرتب خارج شده است.

شناسایی Fragmentation

DBAهای حرفه‌ای هرگز Maintenance را کورکورانه انجام نمی‌دهند.
قبل از هر اقدامی، باید Fragmentation اندازه‌گیری شود.
کوئری زیر یکی از ابزارهای اصلی در محیط Production است:

SELECT 
    dbschemas.[name] AS 'Schema',
    dbtables.[name] AS 'Table',
    dbindexes.[name] AS 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

تصمیم‌گیری بین Rebuild و Reorganize

SQL Server دو روش برای اصلاح Fragmentation دارد:

نوع عملیات	توضیح	سطح قفل	مزایا	معایب
Reorganize	بازچینی صفحات ایندکس درجا (Online)	Shared	کم‌هزینه و بدون Downtime	برای Fragmentation کم مؤثر است
Rebuild	بازسازی کامل ایندکس از نو	Exclusive	بازسازی کامل آمار و ساختار	پرهزینه، نیازمند فضای TempDB بالا

توصیه استاندارد مایکروسافت و جامعه DBA به‌صورت زیر است:

اگر Fragmentation < 10% → تقریبا اقدامی لازم نیست
اگر ۱۰% ≤ Fragmentation ≤ ۳۰% → Reorganize
اگر Fragmentation > 30% → Rebuild

نکات کلیدی در زمان‌بندی Index Maintenance

زمان‌بندی خارج از Peak Hours
اجرای هم‌زمان Rebuild روی چند جدول در ساعت کاری می‌تواند باعث Lock و Deadlock شود.
استفاده از Online Rebuild (Enterprise Edition)
در SQL Server Enterprise می‌توانید از گزینه WITH (ONLINE = ON) استفاده کنید تا در حین بازسازی، کاربران دسترسی خود را از دست ندهند.
مدیریت TempDB
Rebuild ایندکس‌ها از TempDB فضای زیادی مصرف می‌کند.
همیشه ظرفیت TempDB را حداقل ۲ برابر بزرگ‌ترین ایندکس در دیتابیس تنظیم کنید.
به‌روزرسانی Statistics هم‌زمان
بعد از Rebuild، آمار خودکار ایندکس‌ها (Statistics) به‌روز می‌شود؛ اما پس از Reorganize باید دستی انجام شود:
```
UPDATE STATISTICS [TableName];
```
ثبت و مانیتورینگ نتایج
لاگ‌کردن درصد Fragmentation قبل و بعد از عملیات برای تحلیل Performance ضروری است.

طراحی Job خودکار در SQL Server Agent

DBAهای حرفه‌ای معمولاً از Job خودکار برای مدیریت ایندکس‌ها استفاده می‌کنند.
یک رویکرد توصیه‌شده، ساخت Job با مراحل زیر است:

اجرای کوئری بررسی Fragmentation
اجرای Reorganize برای ایندکس‌های بین ۱۰ تا ۳۰٪
اجرای Rebuild برای ایندکس‌های بالای ۳۰٪
ارسال گزارش نهایی به ایمیل DBA

نمونه ساختار ساده‌ی Job:

EXECUTE dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @UpdateStatistics = 'ALL';

(این نمونه بر پایه‌ی ابزار محبوب Ola Hallengren’s Maintenance Solution است.)

ارتباط Index Maintenance با Performance Query Store

اگر از Query Store استفاده می‌کنید، بعد از Rebuild ایندکس‌ها ممکن است Execution Planها تغییر کنند.
برای جلوگیری از ناپایداری Plan، حتماً Query Store را مانیتور کنید و در صورت نیاز از Plan Forcing استفاده نمایید.

چالش‌های واقعی DBAها

در بانک‌های اطلاعاتی بزرگ با ۵۰۰+ گیگابایت داده، Rebuild کامل ممکن است ساعت‌ها طول بکشد.
در محیط‌های Always On Availability Group، عملیات Rebuild باید روی Secondary Replica با دقت انجام شود.
در محیط‌های Cloud (مانند Azure SQL DB)، محدودیت منابع TempDB ممکن است Rebuild را مختل کند.

نتیجه‌گیری

Index Maintenance همان قلب تپنده‌ی عملکرد SQL Server است. اگر این فرآیند را با دقت و زمان‌بندی درست انجام دهید، می‌توانید عملکرد Queryها را تا چند برابر بهبود دهید، بدون آن‌که سخت‌افزار گران‌قیمت‌تری بخرید.
به‌عنوان یک DBA ارشد، هدف شما باید ایجاد تعادل بین در دسترس‌بودن (Availability) و کارایی (Performance) باشد و این هنر در طراحی دقیق سیاست نگهداری ایندکس‌ها متجلی می‌شود.

سؤالات متداول (FAQ)

۱. آیا اجرای مکرر Rebuild به دیتابیس آسیب می‌زند؟
بله، اجرای بیش‌ از حد باعث مصرف زیاد I/O و TempDB می‌شود. برنامه‌ریزی هوشمند ضروری است.

۲. آیا می‌توان ایندکس‌ها را در محیط Always On به‌روزرسانی کرد؟
بله، ولی باید عملیات روی Replica ثانویه انجام و سپس با Primary هماهنگ شود.

۳. آیا Fragmentation در SSD هم اهمیت دارد؟
بله، چون Fragmentation علاوه بر I/O، روی نحوه‌ی خواندن Pageها توسط Engine تأثیر می‌گذارد.

۴. آیا ایندکس‌های فیلترشده نیاز به نگهداری جداگانه دارند؟
بله، آن‌ها نیز Fragmentation دارند و باید طبق همان سیاست‌ها نگهداری شوند.

تماس و مشاوره با لاندا

آیا دیتابیس شما کند شده، اما سخت‌افزار پاسخگو نیست؟
در لاندا، ما با ابزارهای پیشرفته‌ی Monitoring و تحلیل Fragmentation، سیاست‌های Index Maintenance را برای محیط Production شما طراحی و خودکارسازی می‌کنیم.

همین امروز با تیم SQL Performance لاندا تماس ✆ بگیرید تا ظرف چند ساعت، طرح بهینه‌سازی Indexهای SQL Server خود را دریافت کنید.

روزبه امیرعصامی

۱۴۰۴/۰۸/۰۹