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

مقدمه

RESUME یک عملیات ساخت یا بازسازی Resumable که در حالت PAUSED قرار دارد را از نقطه ذخیره‌شده ادامه می‌دهد. این قابلیت برای تقسیم نگهداری ایندکس‌های بسیار بزرگ میان چند پنجره زمانی و کنترل فشار منابع طراحی شده است.

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

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

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

Syntax استاندارد

ALTER INDEX index_name
    ON [schema_name].[table_name]
    RESUME
    WITH (MAXDOP = 2, MAX_DURATION = 60 MINUTES);

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

عملیات اولیه باید با RESUMABLE = ON آغاز شده باشد.
MAXDOP هنگام ادامه قابل تنظیم است.
MAX_DURATION زمان اجرای دوره جدید را محدود می‌کند.
WAIT_AT_LOW_PRIORITY رفتار انتظار قفل را کنترل می‌کند.

نوع خروجی

Result Set ندارد. State و percent_complete از sys.index_resumable_operations خوانده می‌شود.

کاربرد واقعی

برای ادامه بازسازی ایندکس‌های بزرگ پس از Pause خودکار یا دستی در محیط‌های ۲۴ ساعته کاربرد دارد.

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

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

مثال 1: ادامه عملیات پایه

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

ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales RESUME;

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

این فرمان فقط برای عملیات 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 RESUME;

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

کنترل 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' RESUME;';
        EXEC sys.sp_executesql @Sql;
    END;

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

نام‌های دریافت‌شده از ورودی بیرونی را بدون 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 RESUME;

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

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

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

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

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 RESUME;

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

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

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

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

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

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

شاخص	خروجی نمونه	توضیح
RequestedAction	RESUME	نتیجه نمایشی
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 RESUME;

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

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

مثال 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

Resume از کار انجام‌شده استفاده می‌کند و هزینه شروع مجدد کامل را حذف می‌سازد، ولی فضای لازم برای عملیات و اثر روی Log و هم‌زمانی همچنان باید پایش شود.

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

خطاهای رایج

اجرای RESUME برای Rebuild عادی.
استفاده از نام ایندکس اشتباه.
ندیدن State پیش از فرمان.
فرض آزاد شدن تمام فضا در حالت Pause.
نادیده گرفتن محدودیت نسخه و Edition.

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

State را پیش از ادامه کنترل کنید.
MAX_DURATION را با پنجره باقی‌مانده هماهنگ کنید.
درصد پیشرفت را ثبت کنید.
خطا را با THROW به Job برگردانید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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