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

مقدمه

DISABLE ساختار فیزیکی ایندکس را غیرقابل استفاده می‌کند اما Metadata و تعریف آن را نگه می‌دارد. Disable کردن Nonclustered Index ممکن است برای بارگذاری انبوه موقت مفید باشد، ولی Disable کردن Clustered Index دسترسی به داده جدول را متوقف و ایندکس‌های وابسته را نیز تحت تأثیر قرار می‌دهد.

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

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

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

Syntax استاندارد

ALTER INDEX index_name
    ON [schema_name].[table_name]
    DISABLE;

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

index_name باید یک ایندکس موجود باشد.
نام Schema و جدول محدوده شیء را مشخص می‌کنند.
ALL می‌تواند همه ایندکس‌ها را هدف بگیرد ولی ریسک بسیار بیشتری دارد.

نوع خروجی

دستور Result Set ندارد. ستون is_disabled در sys.indexes وضعیت نهایی را نشان می‌دهد.

کاربرد واقعی

در Stage Tableها و سناریوهای بارگذاری کنترل‌شده استفاده می‌شود؛ برای بهبود عمومی کارایی یک راهکار دائمی نیست.

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

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

مثال 1: غیرفعال کردن Nonclustered Index

روی جدول آزمایشی، ایندکس غیربانکی Disable و وضعیت آن مشاهده می‌شود.

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 DISABLE;
    SELECT name, is_disabled FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IndexLab');

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

تعریف ایندکس باقی می‌ماند اما Optimizer نمی‌تواند از ساختار غیرفعال استفاده کند.

مثال 2: فعال‌سازی دوباره با Rebuild

پس از Disable، دستور Rebuild داده‌های ایندکس را دوباره می‌سازد و آن را قابل استفاده می‌کند.

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 DISABLE;
    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	نتیجه نمایشی

برای فعال‌سازی دوباره از ENABLE استفاده نمی‌شود؛ راه درست REBUILD است.

مثال 3: بررسی وابستگی پیش از Disable

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

SELECT
        i.name,
        i.type_desc,
        i.is_primary_key,
        i.is_unique_constraint,
        i.is_disabled
    FROM sys.indexes AS i
    WHERE i.object_id = OBJECT_ID(N'dbo.Sales')
      AND i.name = N'IX_Sales_OrderDate';

شاخص	خروجی نمونه	توضیح
type_desc	NONCLUSTERED	نتیجه نمایشی
is_primary_key	0	نتیجه نمایشی
is_disabled	0	نتیجه نمایشی

ایندکس پشتیبان Constraint و Clustered Index به ارزیابی دقیق‌تری نیاز دارد.

مثال 4: محافظت از Clustered Index

اسکریپت اجازه نمی‌دهد Clustered Index ناخواسته Disable شود، زیرا دسترسی به داده جدول را مختل می‌کند.

DECLARE @IndexName sysname = N'CX_Sales';

    IF EXISTS
    (
        SELECT 1
        FROM sys.indexes
        WHERE object_id = OBJECT_ID(N'dbo.Sales')
          AND name = @IndexName
          AND type_desc = N'CLUSTERED'
    )
        THROW 51010, N'Disable کردن Clustered Index مجاز نیست.', 1;

شاخص	خروجی نمونه	توضیح
نتیجه	خطای کنترل‌شده 51010	نتیجه نمایشی

Guard Clause از تبدیل اشتباه یک عملیات نگهداری به عدم دسترسی کامل جدول جلوگیری می‌کند.

مثال 5: Disable شرطی با نام امن

نام Schema، جدول و ایندکس اعتبارسنجی و با QUOTENAME وارد Dynamic SQL می‌شود.

DECLARE @Schema sysname = N'dbo',
            @Table sysname = N'Sales',
            @Index sysname = N'IX_Sales_OrderDate';

    IF EXISTS
    (
        SELECT 1
        FROM sys.indexes
        WHERE object_id = OBJECT_ID(QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table))
          AND name = @Index
          AND type_desc = N'NONCLUSTERED'
    )
    BEGIN
        DECLARE @Sql nvarchar(max) =
            N'ALTER INDEX ' + QUOTENAME(@Index) + N' ON '
            + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N' DISABLE;';
        EXEC sys.sp_executesql @Sql;
    END;

شاخص	خروجی نمونه	توضیح
Index	IX_Sales_OrderDate	نتیجه نمایشی
Action	DISABLE	نتیجه نمایشی

پارامترسازی نام شیء ممکن نیست؛ اعتبارسنجی Catalog و QUOTENAME هر دو لازم‌اند.

مثال 6: رفتار هنگام NULL بودن نام

ورودی خالی پیش از ساخت دستور رد می‌شود تا Dynamic SQL ناقص تولید نشود.

DECLARE @IndexName sysname = NULL;

    IF NULLIF(LTRIM(RTRIM(@IndexName)), N'') IS NULL
        THROW 51011, N'نام ایندکس الزامی است.', 1;

شاخص	خروجی نمونه	توضیح
نتیجه	خطای کنترل‌شده 51011	نتیجه نمایشی

ورودی NULL نباید به انتخاب ALL یا عملیات روی شیء دیگری تعبیر شود.

مثال 7: ثبت دلیل غیرفعال‌سازی

Disable همراه با شماره تغییر و دلیل کسب‌وکاری در جدول لاگ ثبت می‌شود.

BEGIN TRANSACTION;

    ALTER INDEX IX_Stage_ImportKey ON dbo.StageSales DISABLE;

    INSERT dbo.IndexMaintenanceLog
        (IndexName, StartedAt, FinishedAt, ResultText)
    VALUES
        (N'IX_Stage_ImportKey', SYSDATETIME(), SYSDATETIME(),
         N'Disabled for bulk load; Change CHG-2048');

    COMMIT;

شاخص	خروجی نمونه	توضیح
ResultText	Disabled for bulk load; Change CHG-2048	نتیجه نمایشی

ثبت دلیل و Change ID از باقی ماندن طولانی ایندکس غیرفعال جلوگیری می‌کند.

مثال 8: سناریوی بارگذاری انبوه

ایندکس ثانویه پیش از Bulk Load غیرفعال و پس از پایان در همان کنترل خطا بازسازی می‌شود.

BEGIN TRY
        ALTER INDEX IX_Stage_ImportKey ON dbo.StageSales DISABLE;

        BULK INSERT dbo.StageSales
        FROM 'D:\Data\sales.csv'
        WITH (FORMAT = 'CSV', FIRSTROW = 2);

        ALTER INDEX IX_Stage_ImportKey ON dbo.StageSales REBUILD;
    END TRY
    BEGIN CATCH
        IF EXISTS
        (
            SELECT 1 FROM sys.indexes
            WHERE object_id = OBJECT_ID(N'dbo.StageSales')
              AND name = N'IX_Stage_ImportKey'
              AND is_disabled = 1
        )
            ALTER INDEX IX_Stage_ImportKey ON dbo.StageSales REBUILD;
        THROW;
    END CATCH;

شاخص	خروجی نمونه	توضیح
مرحله نهایی	Index rebuilt	نتیجه نمایشی

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

مثال 9: اصلاح اشتباه استفاده از DISABLE برای حذف

اگر هدف حذف دائمی است، ابتدا وابستگی بررسی و سپس DROP INDEX استفاده می‌شود؛ DISABLE فقط موقت است.

SELECT
        i.name,
        i.is_disabled,
        usage_reads = COALESCE(s.user_seeks, 0) + COALESCE(s.user_scans, 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')
      AND i.name = N'IX_Sales_OrderDate';

شاخص	خروجی نمونه	توضیح
is_disabled	0	نتیجه نمایشی
usage_reads	15420	نتیجه نمایشی

Usage Stats پس از Restart ریست می‌شود و به‌تنهایی مجوز Disable یا Drop نیست.

مثال 10: گزارش همه ایندکس‌های غیرفعال

یک گزارش مدیریتی ایندکس‌های Disable شده و نوع آن‌ها را فهرست می‌کند.

SELECT
        SchemaName = SCHEMA_NAME(o.schema_id),
        TableName = o.name,
        IndexName = i.name,
        i.type_desc
    FROM sys.indexes AS i
    JOIN sys.objects AS o ON o.object_id = i.object_id
    WHERE i.is_disabled = 1
      AND o.type = 'U'
    ORDER BY SchemaName, TableName, IndexName;

شاخص	خروجی نمونه	توضیح
Schema	Table	Index
dbo	StageSales	IX_Stage_ImportKey

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

ملاحظات Performance

Disable کردن ایندکس ثانویه هزینه نگهداری آن در Bulk Load را حذف می‌کند، اما Queryها ممکن است Scan سنگین‌تری بگیرند. هزینه Rebuild نهایی و فضای لاگ باید در محاسبه کل فرایند دیده شود.

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

خطاهای رایج

اشتباه گرفتن DISABLE با DROP.
Disable کردن Clustered Index بدون Change Plan.
فراموش کردن Rebuild پس از بارگذاری.
نادیده گرفتن Constraintها.
ساخت Dynamic SQL بدون اعتبارسنجی نام‌ها.

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

دلیل و زمان Disable را ثبت کنید.
مسیر Rebuild را در CATCH قرار دهید.
Clustered و Constraint-backed Index را محافظت کنید.
گزارش روزانه ایندکس‌های Disable داشته باشید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

سؤال مصاحبه 1: چه زمانی ALTER INDEX ... DISABLE را در 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 ... DISABLE زمانی ارزشمند است که مسئله، دامنه و هزینه آن اندازه‌گیری شده باشد. نمونه‌های این مقاله الگوی ساخت فرمان امن، کنترل State، ثبت نتیجه و ارزیابی کارایی را نشان دادند. پیش از اجرای Production، اسکریپت را روی نسخه‌ای مشابه آزمایش و معیار موفقیت را از قبل تعریف کنید.

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

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