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

مقدمه

ABORT عملیات Resumable را به‌طور نهایی لغو و وضعیت ذخیره‌شده آن را پاک می‌کند. برخلاف PAUSE، پس از ABORT امکان ادامه از درصد قبلی وجود ندارد و شروع دوباره هزینه کامل خواهد داشت.

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

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

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

Syntax استاندارد

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

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

نام ایندکس و جدول باید با عملیات Resumable موجود منطبق باشد.
عملیات می‌تواند بر اساس State و سیاست عملیاتی برای لغو انتخاب شود.
ABORT گزینه بازگشت به پیشرفت قبلی ندارد.

نوع خروجی

Result Set ندارد؛ پس از موفقیت ردیف عملیات از sys.index_resumable_operations حذف می‌شود.

کاربرد واقعی

برای عملیات خراب، اشتباه، بی‌اولویت یا فاقد ظرفیت ادامه استفاده می‌شود و یک اقدام نهایی است.

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

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

مثال 1: لغو عملیات پایه

یک عملیات Resumable موجود با فرمان ABORT مدیریت می‌شود.

ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales ABORT;

شاخص	خروجی نمونه	توضیح
فرمان	ABORT	نتیجه نمایشی
نتیجه	در صورت وجود عملیات معتبر اجرا می‌شود	نتیجه نمایشی

این فرمان فقط برای عملیات Resumable و ایندکس مشخص معنا دارد.

مثال 2: مشاهده وضعیت پیش از اقدام

Catalog View وضعیت، درصد پیشرفت و زمان باقی‌مانده عملیات را نشان می‌دهد.

SELECT
        object_name = OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id),
        index_name = name,
        state_desc,
        percent_complete,
        total_execution_time,
        last_pause_time
    FROM sys.index_resumable_operations
    WHERE object_id = OBJECT_ID(N'dbo.BigSales');

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

پیش از لغو، state_desc را بررسی کنید تا پیام خطای قابل پیش‌بینی رخ ندهد.

مثال 3: اجرای شرطی بر اساس State

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

IF EXISTS
    (
        SELECT 1
        FROM sys.index_resumable_operations
        WHERE object_id = OBJECT_ID(N'dbo.BigSales')
          AND index_id = INDEXPROPERTY(OBJECT_ID(N'dbo.BigSales'), N'IX_BigSales_OrderDate', 'IndexID')
          AND state_desc = N'PAUSED'
    )
        ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales ABORT;

شاخص	خروجی نمونه	توضیح
شرط	سازگار	نتیجه نمایشی
Action	ABORT	نتیجه نمایشی

کنترل State باعث می‌شود Job قابل تکرار و کم‌خطاتر باشد.

مثال 4: ساخت فرمان امن برای چند پایگاه

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

DECLARE @Schema sysname = N'dbo',
            @Table sysname = N'BigSales',
            @Index sysname = N'IX_BigSales_OrderDate';

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

شاخص	خروجی نمونه	توضیح
Dynamic action	ABORT	نتیجه نمایشی

نام‌های دریافت‌شده از ورودی بیرونی را بدون Catalog Check و QUOTENAME اجرا نکنید.

مثال 5: مدیریت NULL و نبود عملیات

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

DECLARE @OperationId bigint =
    (
        SELECT TOP (1) index_id
        FROM sys.index_resumable_operations
        WHERE object_id = OBJECT_ID(N'dbo.BigSales')
    );

    IF @OperationId IS NULL
        SELECT N'عملیات Resumable پیدا نشد' AS ResultText;
    ELSE
        ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales ABORT;

شاخص	خروجی نمونه	توضیح
ResultText	عملیات Resumable پیدا نشد	نتیجه نمایشی

نبود عملیات وضعیت عادی است و می‌تواند بدون Failed کردن کل Job گزارش شود.

مثال 6: ثبت اقدام در جدول لاگ

اقدام ABORT همراه با درصد پیشرفت پیش از اجرا ثبت می‌شود.

DECLARE @Percent decimal(6,2);
    SELECT @Percent = percent_complete
    FROM sys.index_resumable_operations
    WHERE object_id = OBJECT_ID(N'dbo.BigSales')
      AND name = N'IX_BigSales_OrderDate';

    ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales ABORT;

    INSERT dbo.IndexMaintenanceLog
        (IndexName, StartedAt, FinishedAt, ResultText)
    VALUES
        (N'IX_BigSales_OrderDate', SYSDATETIME(), SYSDATETIME(),
         CONCAT(N'ABORT at ', COALESCE(CONVERT(nvarchar(20), @Percent), N'unknown'), N' percent'));

شاخص	خروجی نمونه	توضیح
ResultText	ABORT at 48.60 percent	نتیجه نمایشی

در رخدادهای عملیاتی، دانستن درصد پیشرفت هنگام اقدام برای تحلیل ظرفیت مفید است.

مثال 7: اجرای کنترل‌شده در TRY/CATCH

خطای ناشی از State ناسازگار ثبت و دوباره پرتاب می‌شود.

BEGIN TRY
        ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales ABORT;
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            N'ABORT' AS RequestedAction;
        THROW;
    END CATCH;

شاخص	خروجی نمونه	توضیح
RequestedAction	ABORT	نتیجه نمایشی
ErrorNumber	وابسته به وضعیت	نتیجه نمایشی

CATCH نباید خطا را پنهان کند؛ ثبت و THROW مجدد رفتار Job را شفاف نگه می‌دارد.

مثال 8: گزارش عملیات‌های طولانی

عملیات‌هایی با زمان اجرای زیاد یا پیشرفت پایین برای تصمیم اپراتور جدا می‌شوند.

SELECT
        table_name = OBJECT_NAME(object_id),
        name,
        state_desc,
        percent_complete,
        total_execution_time
    FROM sys.index_resumable_operations
    WHERE total_execution_time >= 30
       OR percent_complete < 50
    ORDER BY total_execution_time DESC;

شاخص	خروجی نمونه	توضیح
Index	State	Percent
IX_BigSales_OrderDate	PAUSED	48.60

این گزارش ورودی تصمیم لغو است و خودش تغییری در عملیات ایجاد نمی‌کند.

مثال 9: اصلاح فرمان اشتباه ALTER INDEX ALL

عملیات Resumable با نام ایندکس مشخص مدیریت می‌شود و ALL جایگزین امنی برای آن نیست.

DECLARE @IndexName sysname = N'IX_BigSales_OrderDate';

    IF @IndexName = N'ALL'
        THROW 51020, N'برای عملیات Resumable نام ایندکس مشخص لازم است.', 1;

    ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales ABORT;

شاخص	خروجی نمونه	توضیح
IndexName	IX_BigSales_OrderDate	نتیجه نمایشی
Action	ABORT	نتیجه نمایشی

صریح بودن نام ایندکس احتمال اقدام روی عملیات دیگر را حذف می‌کند.

مثال 10: داشبورد عملیاتی برای تصمیم کارایی

وضعیت Resumable همراه با مدت اجرا و زمان آخرین Pause برای داشبورد نگهداری استخراج می‌شود.

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

شاخص	خروجی نمونه	توضیح
State	Percent	Pages
PAUSED	48.60	1250000

تصمیم لغو باید بر ظرفیت لاگ، پنجره باقی‌مانده و اولویت بار کاری متکی باشد.

ملاحظات Performance

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

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

خطاهای رایج

استفاده وقتی Pause کافی است.
نداشتن ثبت درصد پیشرفت.
لغو عملیات درست به دلیل فشار کوتاه.
اجرای نام پویا بدون کنترل.
فرض امکان RESUME پس از ABORT.

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

تأیید اپراتور برای عملیات بزرگ بگیرید.
State و درصد را در لاگ نگه دارید.
علت ABORT را ثبت کنید.
پس از لغو، برنامه شروع دوباره یا حذف نیاز را مشخص کنید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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