راهنمای جامع دستورات نگهداری ایندکس در SQL Server

مقدمه

ایندکس در SQL Server ساختاری برای کاهش هزینه دسترسی به داده است، اما خود این ساختار نیز در اثر درج، حذف، Update، Split صفحات و تغییر الگوی بار کاری نیازمند پایش می‌شود. نگهداری درست به معنی اجرای شبانه یک فرمان ثابت نیست؛ هدف، حفظ تعادل میان سرعت خواندن، هزینه نوشتن، فضای ذخیره‌سازی، دسترس‌پذیری و ظرفیت عملیاتی است.

این مقاله مجموعه دستورات REBUILD، REORGANIZE، DISABLE، RESUME، PAUSE، ABORT، ALTER INDEX ALL و DROP INDEX را در یک نقشه تصمیم واحد قرار می‌دهد. هر بخش به مقاله تخصصی خود لینک دارد تا Syntax، ده مثال مستقل، خروجی نمونه، خطاها و نکات Performance را جداگانه مطالعه کنید.

Fragmentation فقط یکی از سیگنال‌هاست. ایندکس کوچک با پراکندگی بالا ممکن است هیچ اثر قابل اندازه‌گیری نداشته باشد، در حالی که Statistics قدیمی، Plan نامناسب، Lookup زیاد یا طراحی کلید اشتباه می‌تواند علت اصلی کندی باشد. پیش از هر تغییر، مسئله را با Actual Plan، Query Store، Wait Statistics و Logical Read تأیید کنید.

دسترسی سریع

معماری و معیارهای تصمیم

ایندکس Rowstore به شکل B-Tree از سطح Root، صفحات میانی و سطح Leaf تشکیل می‌شود. Page Split می‌تواند ترتیب منطقی و چگالی صفحات را تغییر دهد. avg_fragmentation_in_percent ترتیب منطقی صفحات و avg_page_space_used_in_percent چگالی را توصیف می‌کنند، اما تفسیر آن‌ها بدون page_count و نوع ذخیره‌ساز ناقص است.

REBUILD ساختار را از نو می‌سازد و معمولاً پرهزینه‌تر است. REORGANIZE صفحات Leaf را تدریجی مرتب می‌کند. DISABLE یک وضعیت موقت و پرریسک است، در حالی که DROP تعریف را حذف می‌کند. عملیات Resumable اجازه می‌دهد Rebuild بزرگ میان پنجره‌های زمانی Pause و Resume شود یا با ABORT پایان قطعی یابد.

در Recovery Model کامل، عملیات نگهداری می‌تواند Log زیادی ایجاد کند و نرخ ارسال به Replicaها یا مدت Backup Log را تغییر دهد. ONLINE نیز به معنای حذف کامل قفل نیست و فازهای کوتاه Schema Lock دارد. این واقعیت‌ها باید در Runbook و SLA ثبت شوند.

جدول مقایسه دستورات

دستور	کاربرد اصلی	نوع خروجی یا نکته مهم	لینک آموزش کامل
ALTER INDEX ... REBUILD	ساخت دوباره ایندکس	کاهش شدید Fragmentation؛ هزینه بیشتر	آموزش کامل
ALTER INDEX ... REORGANIZE	مرتب‌سازی تدریجی Leaf	آنلاین و سبک‌تر؛ آمار جدا بررسی شود	آموزش کامل
ALTER INDEX ... DISABLE	غیرفعال‌سازی موقت	Metadata می‌ماند؛ Clustered بسیار پرریسک	آموزش کامل
ALTER INDEX ... RESUME	ادامه عملیات Resumable	حفظ پیشرفت قبلی	آموزش کامل
ALTER INDEX ... PAUSE	توقف موقت عملیات	قابل ادامه؛ فضا و Metadata باقی می‌ماند	آموزش کامل
ALTER INDEX ... ABORT	لغو نهایی عملیات	پیشرفت قبلی قابل ادامه نیست	آموزش کامل
ALTER INDEX ALL	عملیات گروهی روی جدول	ساده ولی کم‌دقت و بالقوه سنگین	آموزش کامل
DROP INDEX	حذف تعریف ایندکس	دائمی؛ نیازمند تحلیل وابستگی	آموزش کامل

آموزش کامل ALTER INDEX REBUILD در SQL Server

ALTER INDEX ... REBUILD برای ساخت دوباره ایندکس استفاده می‌شود. کاهش شدید Fragmentation؛ هزینه بیشتر. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی ALTER INDEX ... REBUILD با مثال‌های عملی

آموزش ALTER INDEX REORGANIZE در SQL Server

ALTER INDEX ... REORGANIZE برای مرتب‌سازی تدریجی Leaf استفاده می‌شود. آنلاین و سبک‌تر؛ آمار جدا بررسی شود. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی ALTER INDEX ... REORGANIZE با مثال‌های عملی

آموزش ALTER INDEX DISABLE در SQL Server

ALTER INDEX ... DISABLE برای غیرفعال‌سازی موقت استفاده می‌شود. Metadata می‌ماند؛ Clustered بسیار پرریسک. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی ALTER INDEX ... DISABLE با مثال‌های عملی

آموزش ALTER INDEX RESUME در SQL Server

ALTER INDEX ... RESUME برای ادامه عملیات Resumable استفاده می‌شود. حفظ پیشرفت قبلی. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی ALTER INDEX ... RESUME با مثال‌های عملی

آموزش ALTER INDEX PAUSE در SQL Server

ALTER INDEX ... PAUSE برای توقف موقت عملیات استفاده می‌شود. قابل ادامه؛ فضا و Metadata باقی می‌ماند. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی ALTER INDEX ... PAUSE با مثال‌های عملی

آموزش ALTER INDEX ABORT در SQL Server

ALTER INDEX ... ABORT برای لغو نهایی عملیات استفاده می‌شود. پیشرفت قبلی قابل ادامه نیست. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی ALTER INDEX ... ABORT با مثال‌های عملی

آموزش ALTER INDEX ALL در SQL Server

ALTER INDEX ALL برای عملیات گروهی روی جدول استفاده می‌شود. ساده ولی کم‌دقت و بالقوه سنگین. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی ALTER INDEX ALL با مثال‌های عملی

آموزش کامل DROP INDEX در SQL Server

DROP INDEX برای حذف تعریف ایندکس استفاده می‌شود. دائمی؛ نیازمند تحلیل وابستگی. انتخاب آن باید با State ایندکس، اندازه، پنجره نگهداری و نتیجه مورد انتظار هماهنگ باشد.

مطالعه آموزش تخصصی DROP INDEX با مثال‌های عملی

شش مثال کاربردی مستقل

مثال 1: شناسایی نامزدهای نگهداری

Fragmentation و تعداد صفحات همه ایندکس‌های کاربری خوانده می‌شود.

SELECT
        SchemaName = SCHEMA_NAME(o.schema_id),
        TableName = o.name,
        IndexName = i.name,
        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
    JOIN sys.objects AS o ON o.object_id = i.object_id
    WHERE o.type = 'U' AND i.name IS NOT NULL AND ips.page_count >= 1000
    ORDER BY ips.avg_fragmentation_in_percent DESC;

شاخص	مقدار نمونه	تفسیر
IX_Sales_Date	38.20	نامزد Rebuild
IX_Order_Customer	17.40	نامزد Reorganize

اندازه، بار کاری و Query Store باید نتیجه DMV را تکمیل کنند.

مثال 2: بازسازی هدفمند

یک ایندکس پرتراکنش با محدودیت موازی‌سازی بازسازی می‌شود.

ALTER INDEX IX_Sales_OrderDate ON dbo.Sales
    REBUILD WITH (SORT_IN_TEMPDB = ON, MAXDOP = 2);

شاخص	مقدار نمونه	تفسیر
Action	REBUILD	خروجی نمایشی
MAXDOP	2	خروجی نمایشی

فضای tempdb و Log پیش از اجرا کنترل شود.

مثال 3: مرتب‌سازی تدریجی

ایندکس با پراکندگی میانی بدون Rebuild کامل مرتب می‌شود.

ALTER INDEX IX_Order_Customer ON dbo.Orders
    REORGANIZE WITH (LOB_COMPACTION = ON);

    UPDATE STATISTICS dbo.Orders IX_Order_Customer
    WITH SAMPLE 50 PERCENT;

شاخص	مقدار نمونه	تفسیر
Action	REORGANIZE	خروجی نمایشی
Statistics	Updated separately	خروجی نمایشی

Reorganize و Update Statistics دو تصمیم جدا هستند.

مثال 4: سیاست انتخابی خودکار

بر اساس Page Count و Fragmentation یک تصمیم نمونه ساخته می‌شود.

DECLARE @Frag decimal(6,2) = 24.8,
            @Pages bigint = 42000;

    SELECT Decision =
        CASE
            WHEN @Pages < 1000 OR @Frag < 10 THEN N'SKIP'
            WHEN @Frag < 30 THEN N'REORGANIZE'
            ELSE N'REBUILD'
        END;

شاخص	مقدار نمونه	تفسیر
Pages	42000	خروجی نمایشی
Fragmentation	24.80	خروجی نمایشی
Decision	REORGANIZE	خروجی نمایشی

آستانه‌ها باید با داده تاریخی هر سامانه تنظیم شوند.

مثال 5: پایش عملیات Resumable

وضعیت عملیات قابل توقف از Catalog View گزارش می‌شود.

SELECT
        TableName = OBJECT_NAME(object_id),
        IndexName = name,
        state_desc,
        percent_complete,
        total_execution_time,
        last_pause_time
    FROM sys.index_resumable_operations
    ORDER BY total_execution_time DESC;

شاخص	مقدار نمونه	تفسیر
IX_BigSales_Date	PAUSED	63.70

برای عملیات PAUSED زمان Resume یا تصمیم ABORT مشخص کنید.

مثال 6: تحلیل پیش از حذف ایندکس

میزان خواندن و نوشتن ایندکس پیش از DROP گزارش می‌شود.

SELECT
        i.name,
        Reads = COALESCE(s.user_seeks, 0) + COALESCE(s.user_scans, 0),
        Writes = COALESCE(s.user_updates, 0)
    FROM sys.indexes AS i
    LEFT JOIN sys.dm_db_index_usage_stats AS s
      ON s.database_id = DB_ID()
     AND s.object_id = i.object_id
     AND s.index_id = i.index_id
    WHERE i.object_id = OBJECT_ID(N'dbo.Sales');

شاخص	مقدار نمونه	تفسیر
IX_Sales_Date	15420	8320
IX_Sales_Legacy	0	8120

Restart سرور Usage Stats را ریست می‌کند؛ Query Store و چرخه کاری کامل نیز لازم‌اند.

الگوی تصمیم‌گیری حرفه‌ای

یک Job حرفه‌ای ابتدا Catalog و DMVها را Snapshot می‌کند، ایندکس‌های کوچک و Unsupported را کنار می‌گذارد، عملیات را بر اساس نوع و State دسته‌بندی می‌کند و برای هر فرمان زمان، نتیجه و خطا را ثبت می‌نماید. Dynamic SQL فقط از نام‌های تأییدشده Catalog ساخته و با QUOTENAME محافظت می‌شود.

پس از پایان، Query Store، رشد Log، Replica Lag و شاخص‌های زمان پاسخ مقایسه می‌شوند. اگر Rebuild مکرر سودی نشان ندهد، باید آستانه، طراحی ایندکس یا برنامه زمانی عوض شود. نگهداری خوب یک حلقه بازخورد است، نه فهرستی ثابت از فرمان‌ها.

تعریف خط مبنا و معیار موفقیت
انتخاب دامنه بر اساس اندازه و اثر
محافظت از قفل، Log و tempdb
ثبت کامل نتیجه و خطا
مقایسه قبل و بعد و اصلاح سیاست

سؤالات متداول

سؤال متداول 1: نگهداری ایندکس دقیقاً چه کاری انجام می‌دهد؟

این فرمان بخشی از چرخه مدیریت فیزیکی یا چرخه عمر ایندکس است. اثر دقیق آن به نوع فرمان، نوع ایندکس و State فعلی بستگی دارد؛ بنابراین پیش از اجرا Catalog Viewها و مستند نسخه نصب‌شده را بررسی کنید.

سؤال متداول 2: آیا نگهداری ایندکس برای افراد مبتدی مناسب است؟

یادگیری Syntax ساده است، اما اجرای تولیدی نیازمند شناخت قفل، Log، tempdb، Availability و برنامه بازگشت است. ابتدا روی پایگاه آزمایشی و نسخه پشتیبان تمرین کنید.

سؤال متداول 3: هزینه اجرای نگهداری ایندکس چگونه برآورد می‌شود؟

اندازه ایندکس، page_count، نرخ تغییر داده، سرعت ذخیره‌ساز، مدت پنجره و رشد لاگ را اندازه بگیرید. برای برآورد دقیق‌تر می‌توان از خدمات مشاوره و تحلیل کارایی SQL Server استفاده کرد.

سؤال متداول 4: آیا اجرای نگهداری ایندکس می‌تواند سرعت سامانه را بیشتر کند؟

ممکن است، اما تضمینی نیست. بهبود تنها وقتی رخ می‌دهد که مشکل واقعی با ساختار ایندکس مرتبط باشد؛ Query Store و معیارهای قبل و بعد باید اثر را ثابت کنند.

سؤال متداول 5: تفاوت نگهداری ایندکس با فرمان‌های نزدیک چیست؟

REBUILD ساختار را دوباره می‌سازد، REORGANIZE مرتب‌سازی تدریجی است، DISABLE استفاده را متوقف می‌کند، DROP حذف دائمی است و PAUSE، RESUME و ABORT چرخه عملیات Resumable را مدیریت می‌کنند.

سؤال متداول 6: برای اجرای سازمانی نگهداری ایندکس چه خدماتی لازم است؟

طراحی Job، پایش، گزارش خطا، آزمون بازیابی و تنظیم آستانه‌ها بخش‌های اصلی هستند. تیم آموزش یا مشاوره پایگاه داده می‌تواند اسکریپت را با SLA و معماری همان سازمان هماهنگ کند.

سؤال متداول 7: خطای رایج در نگهداری ایندکس چیست؟

اجرای فرمان با نام یا State نامعتبر، کمبود فضا، محدودیت Edition، قفل Schema و فراموش کردن وابستگی‌ها از خطاهای رایج‌اند. ERROR_NUMBER و ERROR_MESSAGE را ثبت و خطا را دوباره THROW کنید.

سؤال متداول 8: نگهداری ایندکس چه اثری بر Performance دارد؟

اثر می‌تواند هم مثبت و هم منفی باشد. CPU، I/O، Waitها، رشد Log و زمان Queryهای مهم را در بازه‌ای قابل مقایسه ثبت کنید و به یک درصد Fragmentation اکتفا نکنید.

سؤال متداول 9: Best Practice اجرای نگهداری ایندکس چیست؟

دستور را هدفمند، تکرارپذیر، قابل ثبت و دارای Guard Clause بنویسید. نام اشیا در Dynamic SQL باید از Catalog اعتبارسنجی و با QUOTENAME محافظت شود.

سؤال متداول 10: نگهداری ایندکس در کدام نسخه‌های SQL Server کار می‌کند؟

Syntax پایه بسیاری از فرمان‌ها قدیمی است، ولی گزینه‌هایی مانند IF EXISTS، ONLINE، WAIT_AT_LOW_PRIORITY و RESUMABLE در نسخه‌ها و Editionهای متفاوت عرضه شده‌اند. سازگاری دقیق را با نسخه سرور و مستندات همان نسخه کنترل کنید.

سؤالات مصاحبه

سؤال مصاحبه 1: چه زمانی نگهداری ایندکس را در Production اجرا می‌کنید؟

پاسخ خوب باید به اندازه ایندکس، بار کاری، SLA، ظرفیت Log، قفل‌ها و معیار قبل و بعد اشاره کند، نه فقط یک آستانه ثابت.

سؤال مصاحبه 2: چگونه ریسک Dynamic SQL را کاهش می‌دهید؟

نام‌ها از sys.indexes و sys.objects اعتبارسنجی می‌شوند، QUOTENAME اعمال می‌شود و مقادیر داده‌ای با sp_executesql پارامتری می‌شوند.

سؤال مصاحبه 3: چگونه موفقیت عملیات را ثابت می‌کنید؟

با ثبت زمان، State، Fragmentation، page_count، رشد Log و معیارهای Query Store در قبل و بعد، نتیجه قابل دفاع می‌شود.

سؤال مصاحبه 4: تفاوت نگهداری ایندکس و Update Statistics چیست؟

ساختار فیزیکی و توزیع آماری دو مسئله مرتبط ولی جدا هستند. Rebuild معمولاً آمار ایندکس را تازه می‌کند، Reorganize نیاز به تصمیم جدا برای Statistics دارد.

سؤال مصاحبه 5: در Availability Group چه چیزی مهم است؟

حجم Log تولیدشده، نرخ ارسال و Redo در Replicaها، فضای دیسک و مدت پنجره باید هم‌زمان پایش شوند.

خدمات برنامه‌نویسی و پایگاه داده

برنامه‌نویسی اصفهان؛ قبول سفارشات برنامه‌نویسی و پایگاه داده: 09131253620. خدمات انجام پروژه‌های برنامه‌نویسی، آموزش برنامه‌نویسی، آموزش پایگاه داده SQL Server، طراحی Job نگهداری و تحلیل Performance ارائه می‌شود.

جمع‌بندی و مسیر مطالعه

دستور مناسب از پاسخ به چهار سؤال به دست می‌آید: مشکل واقعی چیست، دامنه آن کدام ایندکس است، هزینه تغییر چقدر است و موفقیت چگونه سنجیده می‌شود. REBUILD و REORGANIZE درمان عمومی هر کندی نیستند؛ DISABLE و DROP نیازمند کنترل وابستگی‌اند و فرمان‌های Resumable باید چرخه State روشن داشته باشند.

راهنمای جامع دستورات نگهداری ایندکس در SQL Server با مثال عملی و نکات Performance