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

مقدمه

PAUSE عملیات Resumable در حال اجرا را در نقطه‌ای قابل ادامه متوقف می‌کند. Metadata و پیشرفت حفظ می‌شود تا بعداً RESUME انجام گیرد؛ بنابراین با Cancel کردن Session یا ABORT تفاوت بنیادی دارد.

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

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

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

Syntax استاندارد

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

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

نام ایندکس باید به عملیات Resumable فعال اشاره کند.
جدول و Schema باید دقیقاً با عملیات ثبت‌شده منطبق باشند.
State مناسب برای Pause معمولاً RUNNING است.

نوع خروجی

خروجی جدولی ندارد و نتیجه در state_desc برابر PAUSED دیده می‌شود.

کاربرد واقعی

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

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

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

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

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

ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales PAUSE;

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

این فرمان فقط برای عملیات 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'RUNNING'
    )
        ALTER INDEX IX_BigSales_OrderDate ON dbo.BigSales PAUSE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

خطاهای رایج

اشتباه گرفتن PAUSE با ABORT.
رها کردن عملیات برای مدت طولانی.
فرمان روی ایندکس غیرResumable.
نبود پایش فضای Log.
اقدام بدون بررسی State.

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

زمان و درصد Pause را ثبت کنید.
هشدار برای عملیات‌های رهاشده بسازید.
زمان Resume بعدی را از قبل تعیین کنید.
فرمان را فقط روی ایندکس مشخص اجرا کنید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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