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

مقدمه

دستور REBUILD ساختار فیزیکی ایندکس را از نو ایجاد می‌کند، صفحات را مرتب می‌سازد و Fragmentation منطقی را تا حد زیادی کاهش می‌دهد. این عملیات برای ایندکس‌های بزرگ می‌تواند CPU، I/O، فضای موقت و Transaction Log قابل توجهی مصرف کند؛ بنابراین انتخاب ONLINE، MAXDOP، SORT_IN_TEMPDB، Fill Factor و حالت Resumable باید متناسب با محیط باشد.

در این راهنما فرمان ALTER INDEX ... REBUILD از Syntax پایه تا کنترل خطا، پایش، سناریوی سازمانی و تصمیم کارایی بررسی می‌شود. همه مثال‌ها برای Microsoft SQL Server نوشته شده‌اند و پیش از استفاده در تولید باید با نام اشیا، نسخه و Edition محیط شما تطبیق داده شوند.

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

تعریف، نحو و اجزای فرمان

Syntax استاندارد

ALTER INDEX index_name
    ON [schema_name].[table_name]
    REBUILD
    WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 2);

پارامترها و پیش‌نیازها

index_name نام دقیق ایندکس هدف است.
ONLINE میزان دسترس‌پذیری هم‌زمان را کنترل می‌کند و پشتیبانی آن به نسخه و Edition وابسته است.
SORT_IN_TEMPDB فضای مرتب‌سازی را به tempdb منتقل می‌کند.
MAXDOP سقف پردازنده‌های موازی عملیات را تعیین می‌کند.
FILLFACTOR درصد پرشدن صفحات سطح برگ هنگام ساخت را مشخص می‌کند.

نوع خروجی

این دستور Result Set کاربردی برنمی‌گرداند؛ موفقیت یا خطا از وضعیت اجرای Batch دریافت می‌شود. وضعیت عملیات Resumable از sys.index_resumable_operations قابل مشاهده است.

کاربرد واقعی

این فرمان در بازسازی ایندکس‌های پرتراکنش، آماده‌سازی پس از بارگذاری انبوه، اصلاح ساختار Disable شده و نگهداری Partitionهای تاریخی کاربرد دارد.

در محیط عملیاتی، فرمان باید در یک Change کنترل‌شده اجرا شود. ظرفیت فایل داده و لاگ، نقش سرور، Replicaها، قفل‌های جاری و بازه اوج مصرف پیش از اقدام ثبت شوند. اجرای موفق فنی بدون ارزیابی اثر بر کاربران، معیار کافی برای پایان کار نیست.

مثال‌های عملی

مثال 1: بازسازی پایه یک ایندکس

یک جدول آزمایشی و ایندکس تاریخ ایجاد می‌شود و همان ایندکس از نو ساخته می‌شود.

DROP TABLE IF EXISTS #IndexLab;
    CREATE TABLE #IndexLab
    (
        RowID int IDENTITY(1,1) NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(12,2) NOT NULL
    );

    INSERT INTO #IndexLab (OrderDate, CustomerID, Amount)
    VALUES ('2026-01-01', 101, 125000),
           ('2026-01-02', 102, 98000),
           ('2026-01-03', 101, 143500);

    CREATE INDEX IX_IndexLab_OrderDate
    ON #IndexLab (OrderDate);

    ALTER INDEX IX_IndexLab_OrderDate ON #IndexLab REBUILD;
    SELECT name, is_disabled FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IndexLab');

شاخص	خروجی نمونه	توضیح
IX_IndexLab_OrderDate	0	نتیجه نمایشی

REBUILD ساختار B-Tree را دوباره ایجاد می‌کند و ایندکس پس از پایان فعال می‌ماند.

مثال 2: بازسازی با Fill Factor

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

DROP TABLE IF EXISTS #IndexLab;
    CREATE TABLE #IndexLab
    (
        RowID int IDENTITY(1,1) NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(12,2) NOT NULL
    );

    INSERT INTO #IndexLab (OrderDate, CustomerID, Amount)
    VALUES ('2026-01-01', 101, 125000),
           ('2026-01-02', 102, 98000),
           ('2026-01-03', 101, 143500);

    CREATE INDEX IX_IndexLab_OrderDate
    ON #IndexLab (OrderDate);

    ALTER INDEX IX_IndexLab_OrderDate ON #IndexLab
    REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);
    SELECT name, fill_factor FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IndexLab');

شاخص	خروجی نمونه	توضیح
IX_IndexLab_OrderDate	90	نتیجه نمایشی

Fill Factor کمتر از صد هزینه فضا را بالا می‌برد؛ مقدار آن باید با الگوی واقعی درج سنجیده شود.

مثال 3: بازسازی آنلاین با انتظار کم‌اولویت

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

ALTER INDEX IX_Sales_OrderDate ON dbo.Sales
    REBUILD WITH
    (
        ONLINE = ON
        (
            WAIT_AT_LOW_PRIORITY
            (
                MAX_DURATION = 5 MINUTES,
                ABORT_AFTER_WAIT = SELF
            )
        ),
        MAXDOP = 2
    );

شاخص	خروجی نمونه	توضیح
وضعیت مورد انتظار	عملیات کامل یا پس از انتظار لغو می‌شود	نتیجه نمایشی

ONLINE به معنی بدون قفل بودن کامل نیست؛ شروع و پایان هنوز می‌تواند به قفل‌های کوتاه Schema نیاز داشته باشد.

مثال 4: بازسازی یک Partition

در جدول پارتیشن‌بندی‌شده فقط بخش هدف بازسازی می‌شود تا حجم کار کاهش یابد.

ALTER INDEX IX_FactSales_OrderDate
    ON dbo.FactSales
    REBUILD PARTITION = 12
    WITH (SORT_IN_TEMPDB = ON, MAXDOP = 2);

شاخص	خروجی نمونه	توضیح
Partition	12	نتیجه نمایشی
عملیات	REBUILD	نتیجه نمایشی

پارتیشن درست را از نگاشت Partition Function و بازه تاریخ تعیین کنید؛ شماره ثابت را بدون بررسی وارد تولید نکنید.

مثال 5: انتخاب شرطی بر پایه Fragmentation

DMV خوانده می‌شود و فقط ایندکس نسبتاً بزرگ با پراکندگی شدید بازسازی می‌شود.

DECLARE @Fragmentation decimal(6,2);

    SELECT @Fragmentation = ips.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats
    (
        DB_ID(), OBJECT_ID(N'dbo.Sales'), 2, NULL, 'LIMITED'
    ) AS ips;

    IF @Fragmentation >= 30
        ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REBUILD;

    SELECT COALESCE(@Fragmentation, 0) AS FragmentationBefore;

شاخص	خروجی نمونه	توضیح
FragmentationBefore	41.25	نتیجه نمایشی
تصمیم	REBUILD	نتیجه نمایشی

آستانه سی درصد قانون قطعی نیست و باید در کنار page_count و اثر واقعی Query سنجیده شود.

مثال 6: مدیریت مقدار NULL در پایش

اگر ایندکس یا داده کافی وجود نداشته باشد، DMV ممکن است ردیفی برنگرداند؛ تصمیم باید امن باقی بماند.

DECLARE @Frag decimal(6,2) = NULL;

    SELECT @Frag = avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats
    (
        DB_ID(), OBJECT_ID(N'dbo.Sales'), 2, NULL, 'LIMITED'
    );

    IF COALESCE(@Frag, 0) >= 30
        ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REBUILD;
    ELSE
        SELECT N'عملیاتی لازم نیست' AS MaintenanceDecision;

شاخص	خروجی نمونه	توضیح
MaintenanceDecision	عملیاتی لازم نیست	نتیجه نمایشی

COALESCE از اجرای ناخواسته جلوگیری می‌کند، اما نبود ردیف باید جداگانه در لاگ پایش ثبت شود.

مثال 7: بازسازی Resumable

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

ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales
    REBUILD WITH
    (
        ONLINE = ON,
        RESUMABLE = ON,
        MAX_DURATION = 60 MINUTES,
        MAXDOP = 2
    );

    SELECT state_desc, percent_complete
    FROM sys.index_resumable_operations
    WHERE object_id = OBJECT_ID(N'dbo.BigSales');

شاخص	خروجی نمونه	توضیح
state_desc	PAUSED یا RUNNING	نتیجه نمایشی
percent_complete	نمونه: 64.80	نتیجه نمایشی

در SQL Server 2017 و جدیدتر می‌توان Rebuild آنلاین را Resumable کرد؛ پشتیبانی Edition و گزینه‌ها را بررسی کنید.

مثال 8: ثبت نتیجه در جدول نگهداری

زمان، نام ایندکس و وضعیت عملیات در یک جدول لاگ ثبت می‌شود.

DECLARE @StartedAt datetime2(0) = SYSDATETIME();

    BEGIN TRY
        ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REBUILD;
        INSERT dbo.IndexMaintenanceLog
            (IndexName, StartedAt, FinishedAt, ResultText)
        VALUES
            (N'IX_Sales_OrderDate', @StartedAt, SYSDATETIME(), N'Succeeded');
    END TRY
    BEGIN CATCH
        INSERT dbo.IndexMaintenanceLog
            (IndexName, StartedAt, FinishedAt, ResultText)
        VALUES
            (N'IX_Sales_OrderDate', @StartedAt, SYSDATETIME(), ERROR_MESSAGE());
        THROW;
    END CATCH;

شاخص	خروجی نمونه	توضیح
ResultText	Succeeded	نتیجه نمایشی

لاگ عملیاتی برای تحلیل مدت، خطا و ظرفیت پنجره نگهداری ضروری است.

مثال 9: اصلاح روش اشتباه بازسازی روزانه

به جای اجرای کورکورانه، اندازه و Fragmentation بررسی و سپس دستور پویا با QUOTENAME ساخته می‌شود.

DECLARE @SchemaName sysname = N'dbo',
            @TableName sysname = N'Sales',
            @IndexName sysname = N'IX_Sales_OrderDate',
            @PageCount bigint = 25000,
            @Frag decimal(6,2) = 38.4;

    IF @PageCount >= 1000 AND @Frag >= 30
    BEGIN
        DECLARE @Sql nvarchar(max) =
            N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON '
            + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
            + N' REBUILD;';
        EXEC sys.sp_executesql @Sql;
    END;

شاخص	خروجی نمونه	توضیح
PageCount	25000	نتیجه نمایشی
Frag	38.40	نتیجه نمایشی
تصمیم	اجرا	نتیجه نمایشی

QUOTENAME تزریق نام شیء و خطاهای ناشی از نام‌های خاص را کاهش می‌دهد.

مثال 10: مقایسه منطقی پیش و پس از عملیات

Fragmentation و تعداد صفحات قبل و بعد ثبت می‌شود تا سود واقعی عملیات دیده شود.

SELECT
        phase = N'Before',
        avg_fragmentation_in_percent,
        page_count
    FROM sys.dm_db_index_physical_stats
        (DB_ID(), OBJECT_ID(N'dbo.Sales'), 2, NULL, 'SAMPLED');

    ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REBUILD;

    SELECT
        phase = N'After',
        avg_fragmentation_in_percent,
        page_count
    FROM sys.dm_db_index_physical_stats
        (DB_ID(), OBJECT_ID(N'dbo.Sales'), 2, NULL, 'SAMPLED');

شاخص	خروجی نمونه	توضیح
مرحله	Fragmentation	نتیجه نمایشی
Before	37.90	نتیجه نمایشی
After	0.15	نتیجه نمایشی

برای مقایسه معتبر، حالت نمونه‌برداری و شناسه ایندکس در هر دو اندازه‌گیری یکسان باشد.

ملاحظات Performance

Rebuild آمار همان ایندکس را با Scan کامل ساختار تازه می‌کند، اما می‌تواند لاگ زیادی تولید کند و روی Always On یا Replication فشار بگذارد. اندازه ایندکس، page_count، ظرفیت tempdb، نرخ رشد لاگ و قفل Schema باید پیش از اجرا سنجیده شوند.

برای اندازه‌گیری معتبر، زمان Queryهای منتخب، Logical Read، Waitها و رشد Transaction Log را در یک بازه کاری مشابه مقایسه کنید. Cache گرم یا سرد، اجرای هم‌زمان Jobها و تغییر حجم داده می‌تواند نتیجه را منحرف کند؛ بنابراین یک نمونه منفرد مبنای تصمیم بلندمدت نیست.

خطاهای رایج

اجرای روزانه REBUILD برای همه ایندکس‌ها بدون اندازه‌گیری.
فرض اینکه ONLINE هیچ قفلی نمی‌گیرد.
تنظیم FILLFACTOR پایین برای ایندکس‌های افزایشی بدون نیاز.
نادیده گرفتن فضای لاگ و مسیر بازیابی.
استفاده از درصد Fragmentation برای ایندکس‌های بسیار کوچک.

بهترین روش‌ها

آستانه را با بار واقعی و Query Store تنظیم کنید.
برای ایندکس بزرگ حالت RESUMABLE را ارزیابی کنید.
MAXDOP و پنجره اجرا را محدود و ثبت کنید.
پس از عملیات رشد لاگ و زمان Queryهای کلیدی را مقایسه کنید.

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

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

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

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

سؤال متداول 2: آیا ALTER INDEX ... REBUILD برای افراد مبتدی مناسب است؟

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

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

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

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

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

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

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

سؤال متداول 6: برای اجرای سازمانی ALTER INDEX ... REBUILD چه خدماتی لازم است؟

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

سؤال متداول 7: خطای رایج در ALTER INDEX ... REBUILD چیست؟

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

سؤال متداول 8: ALTER INDEX ... REBUILD چه اثری بر Performance دارد؟

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

سؤال متداول 9: Best Practice اجرای ALTER INDEX ... REBUILD چیست؟

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

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

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

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

سؤال مصاحبه 1: چه زمانی ALTER INDEX ... REBUILD را در 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ها، فضای دیسک و مدت پنجره باید هم‌زمان پایش شوند.

چک‌لیست نهایی

نسخه، Edition و پشتیبانی Syntax کنترل شده است.
نام Schema، جدول و ایندکس از Catalog تأیید شده است.
فضای داده، tempdb و Transaction Log بررسی شده است.
معیارهای پیش از اجرا ثبت شده‌اند.
TRY/CATCH، لاگ و مسیر بازگشت آماده است.
نتیجه و اثر کارایی پس از اجرا مقایسه می‌شود.

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

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

جمع‌بندی

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

مشاهده فهرست کامل دستورات نگهداری ایندکس در SQL Server

آموزش کامل ALTER INDEX REBUILD در SQL Server با مثال عملی و نکات Performance