فهرست مطالب
در بیشتر تیم‌هایی که DBA تمام‌وقت ندارند، یک جاب شبانه وجود دارد که «همهٔ ایندکس‌ها را rebuild می‌کند». کسی دقیق یادش نیست چه کسی آن را ساخته، فقط می‌دانند اگر یک شب اجرا نشود، فردا کسی می‌گوید سیستم کند است. فرگمنتیشن ایندکس یکی از پرتکرارترین موضوع‌های نگه‌داری SQL Server است و در عین حال یکی از موضوع‌هایی که بیشترین باور نادرست دربارهٔ آن وجود دارد: آستانه‌های ۵ و ۳۰ درصدی که از یک مقالهٔ قدیمی نقل می‌شوند، rebuild هایی که بیشتر از خودِ فرگمنتیشن هزینه دارند، و کندی‌هایی که در واقع از آمار قدیمی می‌آیند نه از صفحه‌های نامرتب.
در این نوشته می‌بینیم فرگمنتیشن دقیقاً چیست، چطور آن را بدون فشار بی‌جا روی سرور اندازه بگیریم، تفاوت واقعی rebuild و reorganize چیست، و چطور یک برنامهٔ نگه‌داری معقول بسازیم که هم کار کند و هم پنجرهٔ شبانه را نخورد.
فرگمنتیشن ایندکس دقیقاً یعنی چه
داده‌های یک ایندکس در SQL Server در صفحه‌های ۸ کیلوبایتی نگه‌داری می‌شوند و این صفحه‌ها در یک فهرست پیوندی به ترتیب کلید به هم وصل‌اند. وقتی ردیف تازه‌ای باید وسط یک صفحهٔ پر قرار بگیرد، SQL Server صفحه را نصف می‌کند (page split) و نیمی از ردیف‌ها را به صفحهٔ تازه‌ای می‌برد که معمولاً جای دیگری از فایل است. نتیجه دو نوع فرگمنتیشن است که باید از هم جدا دیده شوند.
- فرگمنتیشن منطقی (خارجی): ترتیب منطقی صفحه‌ها با ترتیب فیزیکی‌شان در فایل یکی نیست. این همان عددی است که در ستون
avg_fragmentation_in_percentمی‌بینید. اثرش بیشتر روی read-ahead و اسکن‌های بزرگ از دیسک است. - چگالی پایین صفحه (فرگمنتیشن داخلی): صفحه‌ها نیمه‌پرند. این را در
avg_page_space_used_in_percentمی‌بینید. اثرش مستقیم‌تر است: صفحهٔ نیمه‌پر یعنی حافظهٔ buffer pool بیشتر و I/O بیشتر برای همان مقدار داده.
نکتهٔ مهمی که اغلب نادیده گرفته می‌شود این است که روی دیسک‌های SSD و در سیستمی که بیشتر داده‌اش در حافظه است، فرگمنتیشن منطقی اثر بسیار کمتری از دوران دیسک‌های چرخان دارد. اما چگالی پایین صفحه همچنان مهم است، چون هر صفحه چه روی دیسک باشد چه در حافظه، همان ۸ کیلوبایت جا می‌گیرد.
اندازه‌گیری فرگمنتیشن بدون آزار سرور
ابزار اصلی تابع مدیریتی sys.dm_db_index_physical_stats است. این تابع سه حالت دارد و انتخاب حالت مهم است، چون روی یک دیتابیس چندصد گیگابایتی حالت اشتباه می‌تواند خودش یک بار سنگین I/O بسازد.
| حالت | چه می‌خواند | چه زمانی |
|---|---|---|
| LIMITED | فقط صفحه‌های سطح بالاتر از برگ | بررسی روزانه و جاب نگه‌داری؛ سریع و سبک |
| SAMPLED | نمونهٔ ۱ درصدی از صفحه‌های برگ در جدول‌های بزرگ | وقتی چگالی صفحه را هم می‌خواهید |
| DETAILED | همهٔ صفحه‌ها | بررسی موردی یک ایندکس مشخص، خارج از ساعت کاری |
کوئری زیر ایندکس‌های دیتابیس جاری را که بیش از هزار صفحه دارند با درصد فرگمنتیشن فهرست می‌کند. فیلتر تعداد صفحه عمداً گذاشته شده است؛ ایندکسی که چند ده صفحه دارد اصلاً ارزش بررسی ندارد و عدد فرگمنتیشنش هم معنای چندانی ندارد.
SELECT
OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i
ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
AND ips.index_id > 0 -- heap ها را جدا بررسی کنید
ORDER BY ips.avg_fragmentation_in_percent DESC;اگر چگالی صفحه را هم می‌خواهید، حالت را به SAMPLED تغییر دهید و ستون avg_page_space_used_in_percent را اضافه کنید. در حالت LIMITED این ستون مقدار NULL برمی‌گرداند.
تفاوت واقعی rebuild و reorganize
این دو عملیات گاهی جای هم به کار می‌روند، ولی رفتارشان کاملاً متفاوت است و انتخاب درست به محدودیت‌های محیط شما بستگی دارد، نه فقط به یک عدد.
ALTER INDEX … REORGANIZE
صفحه‌های برگ را در جای خودشان مرتب و فشرده می‌کند. همیشه آنلاین است، قفل‌های کوتاه‌مدت می‌گیرد و هر لحظه می‌شود آن را متوقف کرد بدون اینکه کار انجام‌شده از دست برود. در عوض تک‌رشته‌ای است، آمار ایندکس را به‌روز نمی‌کند و روی فرگمنتیشن خیلی بالا ممکن است از rebuild کندتر باشد و لاگ بیشتری تولید کند.
ALTER INDEX … REBUILD
ایندکس را از نو می‌سازد. نتیجه کاملاً مرتب است و آمار ایندکس با نمونهٔ کامل (FULLSCAN) به‌روز می‌شود. در نسخهٔ Standard، rebuild آفلاین است و در طول کار جدول برای نوشتن (و در ایندکس clustered برای خواندن هم) قفل می‌شود. گزینهٔ ONLINE = ON فقط در Enterprise و Developer در دسترس است. rebuild فضای خالی کافی برای ساخت نسخهٔ تازهٔ ایندکس لازم دارد و در مدل بازیابی Full به همان اندازه لاگ تولید می‌کند.
-- فشرده‌سازی صفحه‌ها در جا، قابل توقف
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REORGANIZE;
-- ساخت دوباره؛ در Enterprise به‌صورت آنلاین
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders
REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 4);
-- SQL Server 2017 به بعد: rebuild آنلاین قابل ازسرگیری
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);نکتهٔ عملی: اگر reorganize را انتخاب کردید، آمار را جداگانه به‌روز کنید. بسیاری از بهبودهایی که بعد از rebuild دیده می‌شود در واقع از به‌روز شدن آمار است، نه از مرتب شدن صفحه‌ها. این همان دلیلی است که بعضی تیم‌ها فکر می‌کنند rebuild معجزه می‌کند.
آستانه‌های ۵ و ۳۰ درصد را با احتیاط به کار ببرید
قاعدهٔ مشهور «زیر ۵ درصد کاری نکنید، بین ۵ و ۳۰ reorganize، بالای ۳۰ rebuild» سال‌ها در مستندات مایکروسافت بود و هنوز در بسیاری از اسکریپت‌ها دیده می‌شود. خود مایکروسافت در مستندات جدیدتر تأکید می‌کند که این اعداد نقطهٔ شروع‌اند و باید با اندازه‌گیری روی بار کاری خودتان تنظیم شوند. چند دلیل برای این احتیاط:
- کوئری‌هایی که با seek چند ردیف می‌خوانند، تقریباً از فرگمنتیشن منطقی اثری نمی‌پذیرند. اگر بار اصلی سیستم شما OLTP با seek است، rebuild شبانه بیشتر هزینه است تا سود.
- ایندکسی که کلیدش تصادفی است (مثل
uniqueidentifierباNEWID()) چند ساعت بعد از rebuild دوباره فرگمنت می‌شود. rebuild هر شب این ایندکس فقط لاگ و I/O تولید می‌کند. برای این ایندکس‌ها تنظیم FILLFACTOR مؤثرتر است. - rebuild در مدل بازیابی Full حجم زیادی لاگ تولید می‌کند. اگر Log Shipping یا Always On دارید، این حجم باید به نسخه‌های دیگر هم برسد و ممکن است تأخیر همانندسازی بسازد.
یک روش منطقی‌تر: فرگمنتیشن را معیار اول نگیرید. اول ببینید کدام کوئری‌ها کند شده‌اند و آیا کندی‌شان به اسکن‌های بزرگ ربط دارد. اگر بله، ایندکس‌های درگیر را بسنجید. اگر نه، احتمالاً مشکل جای دیگری است.
FILLFACTOR: پیشگیری به‌جای درمان
FILLFACTOR تعیین می‌کند هنگام rebuild هر صفحهٔ برگ چند درصد پر شود. مقدار پیش‌فرض صفر است که همان ۱۰۰ درصد است. برای ایندکسی که درج‌هایش به انتهای کلید اضافه می‌شوند (مثل کلید IDENTITY یا تاریخ ثبت)، ۱۰۰ درست است، چون ردیف تازه همیشه به آخرین صفحه می‌رود و page split میانی رخ نمی‌دهد. برای ایندکسی که درج‌ها و به‌روزرسانی‌های افزایندهٔ طول در وسط کلید رخ می‌دهد، مقدار ۸۰ تا ۹۰ درصد جا برای ردیف‌های تازه باقی می‌گذارد و سرعت فرگمنت شدن را کم می‌کند.
ALTER INDEX IX_Customers_Email ON dbo.Customers
REBUILD WITH (FILLFACTOR = 90);هزینهٔ FILLFACTOR پایین را فراموش نکنید: صفحهٔ ۹۰ درصد پر یعنی ۱۰ درصد فضای بیشتر روی دیسک و در حافظه، و اسکن‌های ۱۰ درصد بزرگ‌تر. هرگز FILLFACTOR را در سطح کل سرور پایین نیاورید؛ فقط برای ایندکس‌هایی تنظیمش کنید که شواهد page split زیاد دارند. شمارندهٔ Page Splits/sec در sys.dm_os_performance_counters و ستون leaf_allocation_count در sys.dm_db_index_operational_stats سرنخ خوبی برای پیدا کردن این ایندکس‌ها هستند.
یک جاب نگه‌داری معقول
به‌جای «همه چیز را هر شب rebuild کن»، یک جاب انتخابی بسازید. اسکریپت زیر یک نسخهٔ ساده است که برای فهم منطق نوشته شده؛ در محیط واقعی بهتر است از اسکریپت‌های شناخته‌شده و آزموده‌ای مثل اسکریپت نگه‌داری Ola Hallengren استفاده کنید که همین منطق را با گزینه‌های کامل‌تر پیاده کرده‌اند.
DECLARE @sql nvarchar(max);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT
CASE
WHEN ips.avg_fragmentation_in_percent >= 30
THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON '
+ QUOTENAME(OBJECT_SCHEMA_NAME(ips.object_id)) + N'.'
+ QUOTENAME(OBJECT_NAME(ips.object_id)) + N' REBUILD;'
ELSE N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON '
+ QUOTENAME(OBJECT_SCHEMA_NAME(ips.object_id)) + N'.'
+ QUOTENAME(OBJECT_NAME(ips.object_id)) + N' REORGANIZE;'
END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i
ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
AND ips.index_id > 0
AND ips.avg_fragmentation_in_percent >= 10;
OPEN c;
FETCH NEXT FROM c INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @sql; -- اول فقط چاپ کنید و بررسی کنید
-- EXEC sys.sp_executesql @sql;
FETCH NEXT FROM c INTO @sql;
END
CLOSE c;
DEALLOCATE c;چند قاعده که این جاب را قابل اعتماد می‌کند:
- جاب را در پنجرهٔ کم‌بار اجرا کنید و با بکاپ‌گیری هم‌زمان نکنید؛ هر دو I/O سنگین دارند.
- اگر مدل بازیابی Full است، فاصلهٔ بکاپ لاگ را در ساعت اجرای جاب کوتاه‌تر کنید یا مطمئن شوید فضای کافی برای رشد لاگ دارید. جاب ایندکسی که لاگ را پر می‌کند مشکل بزرگ‌تری از فرگمنتیشن می‌سازد.
- بعد از reorganize، آمار جدول را با
UPDATE STATISTICSبه‌روز کنید. - مدت اجرای جاب را ثبت کنید. جابی که هر هفته طولانی‌تر می‌شود، روزی به ساعت کاری می‌رسد.
heap ها و ایندکس‌های columnstore
جدولی که ایندکس clustered ندارد (heap) فرگمنتیشن به معنای بالا ندارد، ولی مشکل خودش را دارد: forwarded record. وقتی ردیفی در heap بزرگ‌تر می‌شود و در صفحهٔ فعلی جا نمی‌گیرد، به صفحهٔ دیگری منتقل می‌شود و یک اشاره‌گر جای قبلی‌اش باقی می‌ماند. هر خواندن آن ردیف یک I/O اضافه دارد. ستون forwarded_record_count در حالت SAMPLED یا DETAILED این را نشان می‌دهد و ALTER TABLE … REBUILD آن را پاک می‌کند. راه‌حل بهتر معمولاً اضافه کردن یک ایندکس clustered مناسب است.
ایندکس‌های columnstore هم منطق دیگری دارند. معیار آنها ردیف‌های حذف‌شده و rowgroup های کوچک است که در sys.dm_db_column_store_row_group_physical_stats دیده می‌شود. اعمال همان آستانه‌های rowstore روی آنها نتیجهٔ درستی نمی‌دهد.
پایش مستمر با DBMug
مشکل بیشتر تیم‌ها نبودن اسکریپت نیست؛ این است که کسی نمی‌داند جاب نگه‌داری دیشب اجرا شد یا نه، چقدر طول کشید و آیا کوئری کندی که کاربر گزارش داده اصلاً به ایندکس ربط دارد. دی‌بی‌ماگ هر دقیقه SQL Server را با یک حساب فقط‌خواندنی می‌خواند و بدون نصب هیچ چیزی روی دیتابیس، ایرادهای کارایی مثل کوئری کند، ایندکس گمشده، ایندکس بی‌استفاده و آمار قدیمی را با شاهد و اسکریپت رفع نشان می‌دهد. تصمیم اجرای اسکریپت با تیم خودتان می‌ماند؛ دی‌بی‌ماگ کوئری یا ایندکس شما را خودسرانه تغییر نمی‌دهد.
اگر هنوز بکاپ و نگه‌داری را جداگانه مدیریت می‌کنید، دو نوشتهٔ آزمون بازیابی بکاپ در SQL Server و پر شدن لاگ ترنزکشن را هم ببینید؛ هر دو مستقیماً به rebuild ایندکس و حجم لاگ‌اش مربوط‌اند. برای نمای کلی‌تر از سرور هم چک‌لیست پایش سرور مفید است.
پرسش‌های پرتکرار
آیا روی SSD هم باید فرگمنتیشن را رفع کرد؟
فرگمنتیشن منطقی روی SSD اثر خیلی کمتری دارد، چون خواندن تصادفی تقریباً به سرعت خواندن ترتیبی است. اما چگالی پایین صفحه همچنان حافظه و I/O هدر می‌دهد. پس روی SSD بیشتر به avg_page_space_used_in_percent توجه کنید تا درصد فرگمنتیشن.
آیا shrink کردن دیتابیس فرگمنتیشن می‌سازد؟
بله، و شدید. DBCC SHRINKDATABASE و SHRINKFILE صفحه‌ها را از انتهای فایل به ابتدای آن جابه‌جا می‌کنند بدون توجه به ترتیب کلید، و فرگمنتیشن تقریباً کامل می‌سازند. ترکیب shrink شبانه و rebuild شبانه یکی از بدترین الگوهای نگه‌داری است: یکی فایل را کوچک می‌کند و دیگری دوباره بزرگش.
rebuild آمار را هم به‌روز می‌کند؟
rebuild آمار همان ایندکس را با نمونهٔ کامل به‌روز می‌کند، ولی آمار ستون‌هایی که ایندکس ندارند (آمار خودکار ستونی) دست نمی‌خورند. reorganize هیچ آماری را به‌روز نمی‌کند. بنابراین در کنار جاب ایندکس، به‌روزرسانی آمار را جداگانه برنامه‌ریزی کنید.
جمع‌بندی
فرگمنتیشن ایندکس واقعی است، ولی همیشه مقصر کندی نیست. آن را با حالت LIMITED و فقط روی ایندکس‌های بزرگ بسنجید، بین فرگمنتیشن منطقی و چگالی صفحه فرق بگذارید، و rebuild را جایی به کار ببرید که شواهد از اسکن‌های کند یا صفحه‌های نیمه‌پر دارید. reorganize برای محیط‌هایی که rebuild آنلاین ندارند ابزار سبک و قابل توقفی است، به شرط آنکه آمار را جداگانه به‌روز کنید. FILLFACTOR را فقط برای ایندکس‌هایی تنظیم کنید که page split مکرر دارند، و هرگز shrink را جزو نگه‌داری روزانه نکنید. در نهایت، هر جاب نگه‌داری را مثل کد production ببینید: مدتش را ثبت کنید، اثرش را بسنجید و اگر سودی ندارد کنارش بگذارید.