RESUMABLE = ON در SQL Server؛ آموزش کامل و مثال عملی

آموزش RESUMABLE = ON در SQL Server با مثال‌های عملی

توسط admin | گروه SQL Server | 1405/04/31

نظرات 0

آموزش کامل RESUMABLE = ON در SQL Server

گزینه RESUMABLE = ON ساخت یا بازسازی آنلاین ایندکس را قابل توقف، ادامه و لغو می‌کند. وضعیت و درصد پیشرفت در نمای sys.index_resumable_operations باقی می‌ماند و پس از وقفه یا Failover می‌توان کار را از نقطه ذخیره‌شده ادامه داد. در این راهنما، RESUMABLE = ON از تعریف پایه تا سناریوی سازمانی، خطا، Performance و روش اعتبارسنجی پوشش داده می‌شود.

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

این مقاله بخشی از راهنمای جامع گزینه‌های مهم CREATE INDEX و ALTER INDEX در SQL Server است. برای دیدن رابطه این گزینه با Online، Resumable، Compression، Fill Factor، tempdb و تنظیمات Lock به مقاله مادر مراجعه کنید.

فهرست دسترسی سریع

  1. بازگشت به راهنمای جامع گزینه‌های مهم ایندکس
  2. تعریف و Syntax دقیق
  3. پارامترها و پیش‌نیازها
  4. ده مثال عملی
  5. خطاهای رایج و ملاحظات کارایی
  6. FAQ، مصاحبه و چک‌لیست نهایی

RESUMABLE = ON چیست؟

گزینه RESUMABLE = ON ساخت یا بازسازی آنلاین ایندکس را قابل توقف، ادامه و لغو می‌کند. وضعیت و درصد پیشرفت در نمای sys.index_resumable_operations باقی می‌ماند و پس از وقفه یا Failover می‌توان کار را از نقطه ذخیره‌شده ادامه داد.

کاربرد حرفه‌ای RESUMABLE = ON زمانی شکل می‌گیرد که DBA بتواند قبل از تغییر، خط مبنا ثبت کند و پس از آن همان معیارها را دوباره بسنجد. مدت اجرای DDL، CPU، خواندن و نوشتن، رشد Transaction Log، فضای tempdb، Blocking و Wait Typeها مجموعه حداقلی این ارزیابی هستند.

قاعده عملی: RESUMABLE = ON را فقط وقتی تغییر دهید که مسئله، معیار موفقیت، شرط توقف و مسیر بازگشت مشخص باشد.

Syntax استاندارد

شکل پایه دستور در ادامه آمده است. نام Schema، جدول، ایندکس، ستون‌ها و مقادیر باید با محیط مقصد جایگزین شوند و پشتیبانی نسخه پیش از اجرا کنترل گردد.

ALTER INDEX IX_Name ON dbo.TableName
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

پارامترها و حالت‌ها

مقدار یا مؤلفهمعنا و کاربرد
RESUMABLE = ONفعال‌کردن قابلیت توقف و ادامه
MAX_DURATIONمدت اجرا تا توقف خودکار
PAUSE / RESUME / ABORTمدیریت چرخه عمر عملیات

نوع خروجی و محل مشاهده نتیجه

RESUMABLE = ON یک تابع Scalar نیست و Return Type ندارد؛ نتیجه آن تغییر رفتار عملیات یا ویژگی ایندکس است. برای بررسی نتیجه از sys.indexes، sys.partitions، sys.index_resumable_operations، DMVهای قفل و انتظار یا Extended Events متناسب با موضوع استفاده کنید.

خروجی DDL موفق فقط نشان می‌دهد Syntax پذیرفته شده است. این خروجی ثابت نمی‌کند که تقسیم نگهداری طولانی ایندکس به پنجره‌های زمانی کنترل‌شده و جلوگیری از شروع دوباره کامل کار واقعاً رخ داده؛ اثبات منفعت به اندازه‌گیری قبل و بعد در بار نماینده نیاز دارد.

نحوه کار در موتور SQL Server

هنگام اجرای CREATE INDEX یا ALTER INDEX، موتور ابتدا داده ورودی را می‌خواند، کلیدها را مرتب می‌کند، صفحات B-tree را می‌سازد و تغییر را در Transaction Log ثبت می‌کند. RESUMABLE = ON یکی از تصمیم‌های همین مسیر را تغییر می‌دهد و ممکن است بر زمان قفل، مسیر I/O، تعداد Worker، چگالی صفحه یا Granularity قفل اثر بگذارد.

Optimizer برای Queryهای عادی از ساختار نهایی استفاده می‌کند، اما گزینه‌های اجرایی عملیات لزوماً در Plan Queryهای کاربر نمایش داده نمی‌شوند. بنابراین تاریخچه Job، متن فرمان، زمان آغاز و پایان، پیام خطا و Snapshot از DMVها باید در سامانه مانیتورینگ نگه‌داری شود.

سازگاری: بازسازی آنلاین قابل ادامه از SQL Server 2017 و ساخت قابل ادامه در نسخه‌های جدیدتر پشتیبانی می‌شود؛ دامنه دقیق CREATE، REBUILD و قیود را برای نسخه مقصد بررسی کنید. پیش‌نیازهای Edition، نوع ایندکس، ستون‌های LOB، Partition و محیط Azure نیز ممکن است دامنه استفاده را تغییر دهند.

مثال‌های عملی و قابل اجرا

مثال شماره 1: آغاز Rebuild قابل ادامه

در این سناریو RESUMABLE = ON مستقیماً هنگام CREATE INDEX اعمال می‌شود تا رفتار پایه آن قابل مشاهده باشد.

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (256)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt
ON dbo.Demo_RESUMABLE (CreatedAt, CustomerId)
INCLUDE (Status)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

SELECT COUNT_BIG(*) AS RowCount FROM dbo.Demo_RESUMABLE;
DROP TABLE dbo.Demo_RESUMABLE;
RowCountنتیجه
256ایندکس ایجاد شد

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

مثال شماره 2: ساخت قابل ادامه با پنجره یک‌ساعته

این مثال نشان می‌دهد چگونه RESUMABLE = ON روی یک ایندکس موجود و در دستور ALTER INDEX REBUILD استفاده می‌شود.

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (384)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt
ON dbo.Demo_RESUMABLE (CreatedAt, CustomerId);

ALTER INDEX IX_Demo_RESUMABLE_CreatedAt ON dbo.Demo_RESUMABLE
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

SELECT name, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Demo_RESUMABLE') AND name = N'IX_Demo_RESUMABLE_CreatedAt';
DROP TABLE dbo.Demo_RESUMABLE;
IndexNameType
IX_Demo_RESUMABLE_CreatedAtNONCLUSTERED

Rebuild لاگ‌بر است؛ فضای لاگ، TempDB، CPU و زمان قفل‌های ابتدا و انتها باید پایش شوند.

مثال شماره 3: مشاهده درصد پیشرفت ذخیره‌شده

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

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (128)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt
ON dbo.Demo_RESUMABLE (CreatedAt)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

SELECT i.name, i.type_desc,
       N'این گزینه اجرایی است و مقدار پایدار جداگانه‌ای در sys.indexes ندارد' AS OptionState
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.Demo_RESUMABLE')
  AND i.name = N'IX_Demo_RESUMABLE_CreatedAt';
DROP TABLE dbo.Demo_RESUMABLE;
ObjectOptionValue
IX_Demo_RESUMABLE_CreatedAt125000

بعضی گزینه‌ها اجرایی و موقت‌اند و در sys.indexes ستون پایدار ندارند؛ موفقیت دستور را با Extended Events و مانیتورینگ نیز ثبت کنید.

مثال شماره 4: محدودکردن CPU و زمان اجرا

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

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (640)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt
ON dbo.Demo_RESUMABLE (CustomerId, CreatedAt)
INCLUDE (Status, Payload)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 30 MINUTES, MAXDOP = 2);

SELECT name, type_desc, fill_factor
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Demo_RESUMABLE') AND name = N'IX_Demo_RESUMABLE_CreatedAt';
DROP TABLE dbo.Demo_RESUMABLE;
IndexNameترکیب
IX_Demo_RESUMABLE_CreatedAtONLINE = ON, RESUMABLE = ON, MAX_DURATION = 30 MINUTES, MAXDOP = 2

ترکیب گزینه‌ها باید با Syntax نسخه مقصد سازگار باشد و هر مقدار بر اساس ظرفیت واقعی سرور انتخاب شود.

مثال شماره 5: توقف خودکار در پنجره نگهداری

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

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (1000)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt
ON dbo.Demo_RESUMABLE (Status, CreatedAt)
INCLUDE (CustomerId)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

SELECT Status, COUNT_BIG(*) AS OrderCount,
       MIN(CreatedAt) AS FirstEvent, MAX(CreatedAt) AS LastEvent
FROM dbo.Demo_RESUMABLE
WHERE CreatedAt >= DATEADD(day, -1, SYSUTCDATETIME())
GROUP BY Status
ORDER BY Status;
DROP TABLE dbo.Demo_RESUMABLE;
StatusOrderCountبازه
A750یک روز اخیر
C250یک روز اخیر

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

مثال شماره 6: ادامه عملیات با MAXDOP جدید

دو ایندکس با RESUMABLE = ON و حالت مقابل ساخته می‌شوند تا تفاوت تنظیمات و خروجی کاتالوگ مقایسه شود.

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (300)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt_A
ON dbo.Demo_RESUMABLE (CreatedAt)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt_B
ON dbo.Demo_RESUMABLE (CustomerId)
WITH (RESUMABLE = OFF);

SELECT name, fill_factor, is_padded, allow_row_locks, allow_page_locks
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Demo_RESUMABLE')
  AND name IN (N'IX_Demo_RESUMABLE_CreatedAt_A', N'IX_Demo_RESUMABLE_CreatedAt_B')
ORDER BY name;
DROP TABLE dbo.Demo_RESUMABLE;
Indexحالتتفسیر
IX_Demo_RESUMABLE_CreatedAt_ARESUMABLE = ONآزمایش A
IX_Demo_RESUMABLE_CreatedAt_BRESUMABLE = OFFآزمایش B

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

مثال شماره 7: یافتن عملیات‌های فراموش‌شده در حالت PAUSED

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

DECLARE @MinimumPages bigint = 1000;

SELECT QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + N'.' +
       QUOTENAME(OBJECT_NAME(i.object_id)) AS TableName,
       QUOTENAME(i.name) AS IndexName,
       N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + N'.' +
       QUOTENAME(OBJECT_NAME(i.object_id)) +
       N' REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);' AS MaintenanceCommand
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS ps
  ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE i.index_id > 0 AND i.is_disabled = 0 AND ps.used_page_count >= @MinimumPages
ORDER BY ps.used_page_count DESC;
TableNameIndexNameMaintenanceCommand
dbo.BigTableIX_BigTable_DateALTER INDEX ... REBUILD WITH (...)

ساخت Dynamic SQL به معنی مجوز اجرای خودکار نیست؛ نام‌ها با QUOTENAME ایمن شده‌اند ولی پنجره نگهداری همچنان لازم است.

مثال شماره 8: طراحی Runbook برای ABORT ایمن

هدف این مثال پایش زنده یا موجودی تنظیمات مرتبط است؛ برای برخی DMVها مجوز VIEW SERVER STATE لازم خواهد بود.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       name AS IndexName, state_desc, percent_complete,
       last_pause_time, page_count
FROM sys.index_resumable_operations
ORDER BY percent_complete DESC;
شاخص پایشخروجی نمونهمعنا
وضعیتRUNNING یا مقدار کاتالوگوابسته به زمان اجرای Query

DMVها از زمان راه‌اندازی یا پاک‌شدن آمار داده می‌دهند؛ خط مبنا و بازه نمونه‌برداری را همراه خروجی ذخیره کنید.

مثال شماره 9: اصلاح Rebuild بدون ONLINE

خط رایج به‌صورت Comment نشان داده شده و نسخه درست واقعاً اجرا می‌شود؛ بنابراین کل بلوک قابل اجرا باقی می‌ماند.

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (64)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

-- نادرست: WITH (RESUMABLE = ON)
CREATE INDEX IX_Demo_RESUMABLE_CreatedAt
ON dbo.Demo_RESUMABLE (CreatedAt)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

SELECT N'فرمان اصلاح‌شده با موفقیت اجرا شد' AS ResultMessage;
DROP TABLE dbo.Demo_RESUMABLE;
ResultMessageوضعیت
فرمان اصلاح‌شده با موفقیت اجرا شدSuccess

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

مثال شماره 10: ثبت اندازه و پیشرفت عملیات قابل ادامه

مدت Rebuild، تعداد صفحه، چگالی و Fragmentation جمع‌آوری می‌شود تا تصمیم بر اساس اندازه‌گیری باشد.

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_RESUMABLE;
CREATE TABLE dbo.Demo_RESUMABLE
(
    EventId bigint IDENTITY(1,1) NOT NULL,
    CustomerId int NOT NULL,
    CreatedAt datetime2(3) NOT NULL,
    Status char(1) NOT NULL,
    Payload nvarchar(100) NOT NULL,
    CONSTRAINT PK_Demo_RESUMABLE PRIMARY KEY CLUSTERED (EventId)
);

INSERT INTO dbo.Demo_RESUMABLE (CustomerId, CreatedAt, Status, Payload)
SELECT TOP (2048)
       1 + ABS(CHECKSUM(NEWID())) % 100,
       DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 4 = 0 THEN 'C' ELSE 'A' END,
       N'داده نمونه برای آزمایش گزینه ایندکس'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_RESUMABLE_CreatedAt
ON dbo.Demo_RESUMABLE (CreatedAt, CustomerId)
WITH (RESUMABLE = OFF);

DECLARE @StartedAt datetime2(3) = SYSDATETIME();
ALTER INDEX IX_Demo_RESUMABLE_CreatedAt ON dbo.Demo_RESUMABLE
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

SELECT DATEDIFF(millisecond, @StartedAt, SYSDATETIME()) AS DurationMs,
       ips.page_count, ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(), OBJECT_ID(N'dbo.Demo_RESUMABLE'),
    INDEXPROPERTY(OBJECT_ID(N'dbo.Demo_RESUMABLE'), N'IX_Demo_RESUMABLE_CreatedAt', 'IndexId'),
    NULL, 'SAMPLED'
) AS ips;
DROP TABLE dbo.Demo_RESUMABLE;
DurationMsPageCountPageDensityFragmentation
18409691.350.00

یک بار سریع کافی نیست؛ چند تکرار و صدک‌های مدت، CPU و I/O تصویر قابل اتکاتری می‌سازند.

خطاهای رایج

  • حذف جدول یا تغییر ساختاری ناسازگار در حالی که عملیات در حالت PAUSED است.
  • فراموش‌کردن الزام ONLINE = ON برای عملیات Resumable.
  • ترکیب SORT_IN_TEMPDB = ON با عملیاتی که به‌صورت Resumable اجرا می‌شود.

پیام خطا را با حذف تصادفی گزینه‌ها پنهان نکنید. ابتدا Syntax همان نسخه، وضعیت ایندکس، مجوز ALTER، فضای فایل‌ها، Sessionهای Blocker و قابلیت Online یا Resumable را بررسی کنید؛ سپس نسخه اصلاح‌شده را در محیط آزمایش اجرا کنید.

ملاحظات Performance

  • عملیات متوقف‌شده همچنان فضا و وضعیت خود را نگه می‌دارد.
  • MAX_DURATION پنجره نگهداری را قابل پیش‌بینی می‌کند.
  • در RESUME می‌توان MAXDOP و تنظیمات انتظار قفل را متناسب با بار جدید تغییر داد.

یک تصمیم کارایی خوب هم throughput و هم پایداری را می‌سنجد. ممکن است عملیات کوتاه‌تر شود اما CPU کاربران بالا برود، یا اندازه ایندکس کم شود ولی زمان Rebuild و لاگ افزایش یابد. نتیجه باید با چند معیار و در چند اجرای قابل مقایسه گزارش شود.

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

  • برای عملیات متوقف‌شده هشدار و Runbook مشخص تعریف کنید.
  • در هر بازه، فضای لاگ و percent_complete را ثبت کنید.
  • برای پایان دائمی از ABORT استفاده کنید؛ PAUSE به معنی لغو نیست.

فرمان نهایی را در Source Control نگه دارید و قبل از تولید، Pre-check نسخه، فضای آزاد، وجود جدول و ایندکس، وضعیت AG یا Replication و طول تراکنش‌های فعال را اجرا کنید. پس از تغییر نیز Query Store، Waitها و خطاهای برنامه را برای یک بازه کافی زیر نظر بگیرید.

کاربرد واقعی در پروژه سازمانی

در یک سامانه سفارش‌گیری، RESUMABLE = ON نباید جدا از SLA انتخاب شود. تیم پایگاه داده اندازه ایندکس، نرخ Insert و Update، ساعات اوج، ظرفیت tempdb و لاگ و مدت قابل تحمل Blocking را جمع‌آوری می‌کند؛ سپس دو پیکربندی را روی نسخه بازیابی‌شده تولید مقایسه و تنها تغییر برنده را با Rollback Script منتشر می‌کند.

برای سامانه‌های حساس، Canary روی یک جدول یا پارتیشن کم‌ریسک، ثبت Telemetry و توقف خودکار هنگام عبور از آستانه CPU یا Log Used از اجرای کور بهتر است. هدف نگهداری ایندکس زیباتر نیست؛ هدف کاهش هزینه Query بدون لطمه به سرویس است.

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

RESUMABLE = ON دقیقاً چه کاری در SQL Server انجام می‌دهد؟

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

چگونه مقدار مناسب برای RESUMABLE = ON را انتخاب کنیم؟

ابتدا خط مبنای مدت اجرا، CPU، I/O، قفل، Page Count و Waitها را ثبت کنید؛ سپس فقط یک متغیر را در محیط آزمایش تغییر دهید و نتیجه چند اجرای هم‌شرایط را مقایسه کنید.

آیا فعال‌سازی RESUMABLE = ON برای هر سامانه تجاری سودمند است؟

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

هزینه پیاده‌سازی حرفه‌ای RESUMABLE = ON به چه عواملی وابسته است؟

تعداد پایگاه‌ها، حجم ایندکس‌ها، نیاز به تست بار، طراحی Rollback، مانیتورینگ و پنجره نگهداری بر زمان کار اثر می‌گذارند. مشاوره خوب باید خروجی قابل سنجش و Runbook تحویل دهد.

RESUMABLE = ON چه تفاوتی با تنظیمات نزدیک خود دارد؟

این گزینه یک مسئله مشخص را هدف می‌گیرد و جایگزین عمومی برای طراحی ایندکس، تنظیم Query یا ظرفیت‌سنجی نیست. جدول مقایسه مقاله مادر کمک می‌کند آن را با Online، Resumable، Compression، Fill Factor و گزینه‌های Lock اشتباه نگیرید.

برای سفارش بررسی و اجرای RESUMABLE = ON چه اطلاعاتی لازم است؟

نسخه و Edition، DDL جدول و ایندکس، اندازه و رشد، آمار انتظار، Queryهای مهم، SLA و بازه مجاز تغییر لازم است. برای آموزش، مشاوره و انجام پروژه SQL Server می‌توان از شماره 09131253620 هماهنگ کرد.

رایج‌ترین خطا هنگام استفاده از RESUMABLE = ON چیست؟

رایج‌ترین خطا کپی‌کردن یک مقدار ثابت از محیط دیگر بدون بررسی پیش‌نیاز و محدودیت Syntax است. پیام خطا، Execution Plan، کاتالوگ و وضعیت عملیات را نگه دارید تا علت دقیق قابل بازتولید باشد.

RESUMABLE = ON چه اثری بر Performance دارد؟

اثر می‌تواند روی CPU، I/O، لاگ، tempdb، حافظه، Blocking یا اندازه ایندکس ظاهر شود. یک شاخص منفرد کافی نیست و بهبود throughput نباید با افزایش خطر قفل یا زمان بازیابی معاوضه پنهان شود.

Best Practice استفاده از RESUMABLE = ON چیست؟

تغییر را نسخه‌بندی کنید، پیش‌بررسی و شرط توقف بنویسید، معیار موفقیت داشته باشید، ابتدا روی داده نماینده آزمایش کنید و پس از اجرا نیز خروجی DMVها و Query Store را با خط مبنا مقایسه کنید.

RESUMABLE = ON با کدام نسخه‌های SQL Server سازگار است؟

بازسازی آنلاین قابل ادامه از SQL Server 2017 و ساخت قابل ادامه در نسخه‌های جدیدتر پشتیبانی می‌شود؛ دامنه دقیق CREATE، REBUILD و قیود را برای نسخه مقصد بررسی کنید.

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

در مصاحبه چگونه RESUMABLE = ON را در یک جمله تعریف می‌کنید؟

تعریف باید مسئله هدف، محدوده اثر و مهم‌ترین هزینه جانبی را هم‌زمان بیان کند؛ پاسخ صرفاً حفظ Syntax امتیاز کامل ندارد.

برای اثبات نیاز به RESUMABLE = ON کدام شواهد را جمع می‌کنید؟

DMVهای مرتبط، Wait Statistics، اندازه و چگالی صفحه، نرخ تراکنش، مدت عملیات، فضای لاگ و tempdb و محدودیت SLA باید در یک بازه نماینده ثبت شوند.

اگر اجرای RESUMABLE = ON باعث افت سرویس شد چه می‌کنید؟

ابتدا شرط توقف یا Pause تعریف‌شده در Runbook اجرا می‌شود، سپس وضعیت تراکنش و Rollback پایش و تغییر با نسخه قبلی مقایسه می‌گردد؛ تصمیم بداهه وسط رخداد مناسب نیست.

چرا تست روی جدول کوچک کافی نیست؟

رقابت قفل، فشار حافظه، موازی‌سازی، رشد فایل و شکل Plan در مقیاس کوچک ظاهر نمی‌شوند؛ داده و هم‌زمانی آزمایش باید به تولید نزدیک باشد.

چگونه موفقیت RESUMABLE = ON را گزارش می‌کنید؟

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

آیا می‌توان RESUMABLE = ON را در Job نگهداری به‌صورت ثابت قرار داد؟

فقط پس از تعیین شروط نسخه، اندازه، بار، فضای آزاد و سیاست خطا. Job حرفه‌ای باید Idempotent، قابل مشاهده و دارای مسیر توقف ایمن باشد.

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

  • نسخه، Edition، نوع ایندکس و پشتیبانی Syntax کنترل شده است.
  • خط مبنای CPU، I/O، Wait، Blocking، اندازه و مدت ثبت شده است.
  • فضای Data، Log و tempdb و تنظیم Autogrowth کافی است.
  • Query روی داده و هم‌زمانی نماینده تولید آزمایش شده است.
  • شرط توقف، Rollback یا PAUSE و مالک تصمیم مشخص است.
  • خروجی کاتالوگ و DMV پس از اجرا با مقدار هدف مقایسه می‌شود.
  • Job نگهداری، مانیتورینگ و مستندات با تغییر هماهنگ شده‌اند.

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

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

جمع‌بندی

RESUMABLE = ON ابزاری برای تقسیم نگهداری طولانی ایندکس به پنجره‌های زمانی کنترل‌شده و جلوگیری از شروع دوباره کامل کار است، اما مقدار درست آن از داده و اندازه‌گیری به دست می‌آید. Syntax را همراه پیش‌نیازهای نسخه اجرا کنید، هزینه CPU، I/O، Log، tempdb و Lock را ببینید و تغییر را قابل بازگشت نگه دارید.

برای مقایسه این گزینه با ده تنظیم مهم دیگر، مقاله جامع گزینه‌های مهم ایندکس در SQL Server را بخوانید.

 

0 نظر

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر

اطلاعات تماس

  • آدرس:اصفهان-خیابان ام کلثوم غربی - بعد خیابان تخم چی - بیست متر بعد از پیتزا ننه شب - کوچه تعمیر گاه سمار زغالی - پلاک 354 - درب مشکی - طبقه هفتم
  • آدرس ایمیل:najafzade@gmail.com
  • وب سایت:http://www.a00b.com/
  • تلفن ثابت:(+98)9131253620
  • تلفن همراه:09131253620