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

ساخت و نگهداری ایندکس فقط نوشتن CREATE INDEX یا ALTER INDEX REBUILD نیست. انتخاب ONLINE، RESUMABLE، محل مرتب‌سازی، درجه موازی‌سازی، چگالی صفحه، فشرده‌سازی و Granularity قفل تعیین می‌کند عملیات با چه هزینه‌ای روی کاربران، CPU، دیسک، Transaction Log و tempdb اجرا شود. این مقاله یازده گزینه مهم را در یک چارچوب تصمیم‌گیری واحد بررسی می‌کند.

هدف آن است که DBA یا توسعه‌دهنده به جای انتخاب یک Recipe ثابت، مسئله را تشخیص دهد: آیا نگرانی اصلی قطعی سرویس است، پنجره نگهداری کوتاه است، tempdb ظرفیت جداگانه دارد، Page Split زیاد است، داده فشرده‌پذیر است یا Last-page contention و Blocking دیده می‌شود؟ هر پاسخ مسیر متفاوتی می‌سازد.

تمام مثال‌ها برای Microsoft SQL Server نوشته شده‌اند و خروجی نمونه دارند تا شکل نتیجه بدون اجرای فوری روشن باشد. با این حال پشتیبانی دقیق بعضی گزینه‌ها به نسخه، Edition، نوع ایندکس و پلتفرم وابسته است؛ بنابراین فرمان تولیدی را ابتدا در محیط بازیابی‌شده از داده واقعی آزمایش کنید.

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

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

1. ONLINE = ON — ساخت و بازسازی آنلاین ایندکس
2. RESUMABLE = ON — عملیات قابل توقف و ادامه ایندکس
3. SORT_IN_TEMPDB = ON — مرتب‌سازی ساخت ایندکس در tempdb
4. MAXDOP — کنترل موازی‌سازی عملیات ایندکس با MAXDOP
5. FILLFACTOR — تنظیم فضای آزاد صفحات ایندکس با FILLFACTOR
6. PAD_INDEX — اعمال فضای آزاد به سطوح میانی با PAD_INDEX
7. DATA_COMPRESSION — فشرده‌سازی داده و ایندکس با DATA_COMPRESSION
8. WAIT_AT_LOW_PRIORITY — انتظار کم‌اولویت برای قفل عملیات آنلاین
9. OPTIMIZE_FOR_SEQUENTIAL_KEY — بهینه‌سازی درج کلیدهای ترتیبی
10. ALLOW_ROW_LOCKS — کنترل اجازه قفل ردیفی ایندکس
11. ALLOW_PAGE_LOCKS — کنترل اجازه قفل صفحه‌ای ایندکس

ایندکس چگونه ساخته یا بازسازی می‌شود؟

برای ساخت یک ایندکس B-tree، موتور SQL Server ردیف‌های لازم را از Heap یا ایندکس خوشه‌ای می‌خواند، کلیدها را مرتب می‌کند و صفحات سطح برگ و سپس سطوح میانی را می‌سازد. این مسیر به Memory Grant، Worker، فضای داده و گاهی tempdb نیاز دارد و تمام تغییرات لازم باید در Transaction Log ثبت شوند. اندازه کلید، INCLUDEها، تعداد ردیف، نوع داده و بار هم‌زمان، هزینه واقعی را تعیین می‌کنند.

در Rebuild ساختار جدید ساخته و جایگزین ساختار قبلی می‌شود. عملیات آفلاین معمولاً دسترسی محدودتری می‌دهد؛ عملیات آنلاین ساختار Source و Target را هم‌زمان مدیریت می‌کند و تغییرات کاربران را منتقل می‌سازد. Online به معنی بدون قفل یا بدون مصرف منابع نیست: قفل‌های کوتاه Schema در آغاز و پایان و فشار CPU، I/O و Log همچنان وجود دارند.

Reorganize با Rebuild یکی نیست. Reorganize عملیاتی تدریجی روی صفحات موجود است و همه گزینه‌های Rebuild را نمی‌پذیرد. تصمیم نگهداری نیز نباید فقط از درصد Fragmentation بیاید؛ Page Density، اندازه ایندکس، الگوی Scan، Query Store و هزینه واقعی Queryها مهم‌ترند. یک ایندکس کوچک با Fragmentation بالا ممکن است هیچ ارزش عملی برای Rebuild نداشته باشد.

چه منابعی در عملیات ایندکس مصرف می‌شوند؟

• CPU برای مرتب‌سازی، فشرده‌سازی، محاسبه کلیدها و ساخت صفحات مصرف می‌شود؛ MAXDOP سقف موازی‌سازی همان عملیات را کنترل می‌کند.
• I/O برای خواندن Source، نوشتن Target و عملیات tempdb لازم است؛ SORT_IN_TEMPDB مسیر بخشی از این I/O را جابه‌جا می‌کند.
• Transaction Log باید فضای کافی و نرخ Flush مناسب داشته باشد؛ مدل بازیابی و Backup Log روی امکان استفاده مجدد اثر دارند.
• Lock و Latch دو مفهوم متفاوت‌اند. WAIT_AT_LOW_PRIORITY رفتار انتظار Lock را مدیریت می‌کند، در حالی که OPTIMIZE_FOR_SEQUENTIAL_KEY مسئله خاص Latch روی صفحه انتهایی را هدف می‌گیرد.
• Buffer Pool و فضای دیسک از Page Count اثر می‌پذیرند؛ FILLFACTOR پایین فضا را بیشتر و DATA_COMPRESSION معمولاً فضا را کمتر می‌کند.

چارچوب انتخاب گزینه مناسب

گام اول تعریف مسئله با عدد است. برای قطعی سرویس، مدت Blocking و Sessionهای آسیب‌دیده را ثبت کنید. برای Page Split از Extended Events و آمار عملیاتی ایندکس کمک بگیرید. برای فشرده‌سازی، صرفه‌جویی تخمینی و CPU را بسنجید. برای Last-page contention وجود PAGELATCH_EX روی صفحه انتهایی ایندکس ترتیبی را اثبات کنید. بدون این شواهد، تغییر گزینه فقط جابه‌جایی ریسک است.

گام دوم پیش‌بررسی است: نسخه و Edition، اندازه Index و Partition، فضای Data و tempdb، اندازه و نرخ رشد Log، تراکنش‌های طولانی، AG یا Replication، پنجره نگهداری و مجوز ALTER. گام سوم آزمایش روی داده نماینده با هم‌زمانی نزدیک تولید است. گام چهارم انتشار کنترل‌شده با Telemetry و شرط توقف و گام پنجم مقایسه خروجی با خط مبناست.

گزینه‌ها مستقل کامل نیستند. RESUMABLE به عملیات آنلاین وابسته است؛ PAD_INDEX بدون FILLFACTOR معنای عملی کمی دارد؛ WAIT_AT_LOW_PRIORITY زیرمجموعه سیاست Online Lock Wait است؛ SORT_IN_TEMPDB با بعضی عملیات Resumable سازگار نیست؛ و Compression یا Fill Factor می‌توانند Page Count و در نتیجه زمان و منابع Online Rebuild را تغییر دهند.

جدول مقایسه یازده گزینه مهم

ONLINE = ON: ساخت و بازسازی آنلاین ایندکس

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

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

آموزش کامل ONLINE = ON با ده مثال اجرایی، FAQ و نکات Performance

RESUMABLE = ON: عملیات قابل توقف و ادامه ایندکس

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

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

آموزش کامل RESUMABLE = ON با ده مثال اجرایی، FAQ و نکات Performance

SORT_IN_TEMPDB = ON: مرتب‌سازی ساخت ایندکس در tempdb

گزینه SORT_IN_TEMPDB = ON داده‌های میانی مرتب‌سازی را به tempdb منتقل می‌کند و ساختار نهایی ایندکس را در فایل‌گروه مقصد می‌نویسد. این جداسازی می‌تواند الگوی ورودی و خروجی را بهتر کند، اما به فضای کافی و tempdb سالم نیاز دارد.

کاربرد اصلی آن کاهش رقابت I/O میان مرحله مرتب‌سازی و نوشتن ایندکس نهایی در سرورهایی با tempdb جدا و سریع است. مهم‌ترین نکته عملی این است که در tempdb سریع و جدا، جداسازی خواندن و نوشتن می‌تواند ساخت را سریع‌تر کند. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل SORT_IN_TEMPDB = ON با ده مثال اجرایی، FAQ و نکات Performance

MAXDOP: کنترل موازی‌سازی عملیات ایندکس با MAXDOP

گزینه MAXDOP حداکثر تعداد پردازنده‌های منطقی مورد استفاده یک عملیات ساخت یا بازسازی ایندکس را برای همان دستور محدود می‌کند. این مقدار تنظیم سراسری را تغییر نمی‌دهد و راهی برای توازن سرعت نگهداری با ظرفیت باقی‌مانده برای کاربران است.

کاربرد اصلی آن جلوگیری از اشباع CPU یا کوتاه‌کردن پنجره نگهداری با انتخاب درجه موازی‌سازی متناسب است. مهم‌ترین نکته عملی این است که MAXDOP بیشتر لزوماً سریع‌تر نیست و ممکن است به گلوگاه I/O برسد. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل MAXDOP با ده مثال اجرایی، FAQ و نکات Performance

FILLFACTOR: تنظیم فضای آزاد صفحات ایندکس با FILLFACTOR

FILLFACTOR درصد پرشدن صفحات سطح برگ را هنگام ساخت یا بازسازی ایندکس تعیین می‌کند. فضای خالی رزروشده می‌تواند Page Splitهای آینده را برای کلیدهای غیرترتیبی کم کند، اما اندازه ایندکس و تعداد خواندن‌ها را افزایش می‌دهد.

کاربرد اصلی آن ایجاد تعادل میان چگالی صفحه، Page Split و هزینه خواندن در الگوی تغییر واقعی جدول است. مهم‌ترین نکته عملی این است که Fill factor پایین‌تر ایندکس را بزرگ‌تر و Cache را کم‌اثرتر می‌کند. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل FILLFACTOR با ده مثال اجرایی، FAQ و نکات Performance

PAD_INDEX: اعمال فضای آزاد به سطوح میانی با PAD_INDEX

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

کاربرد اصلی آن کاهش Split در صفحات غیر‌برگ برای ایندکس‌های بسیار بزرگ با تغییرات سنگین و الگوی اثبات‌شده است. مهم‌ترین نکته عملی این است که فضای خالی صفحات میانی Fan-out را کاهش می‌دهد و ممکن است عمق را افزایش دهد. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل PAD_INDEX با ده مثال اجرایی، FAQ و نکات Performance

DATA_COMPRESSION: فشرده‌سازی داده و ایندکس با DATA_COMPRESSION

DATA_COMPRESSION نحوه ذخیره صفحات جدول یا ایندکس را با حالت‌های NONE، ROW و PAGE و برای ساختارهای ستونی با حالت‌های مرتبط تعیین می‌کند. فشرده‌سازی فضای دیسک و I/O را کم می‌کند، اما CPU و هزینه نگهداری را تغییر می‌دهد.

کاربرد اصلی آن کاهش اندازه و خواندن فیزیکی برای داده‌های تکراری یا پهن با ارزیابی هزینه CPU است. مهم‌ترین نکته عملی این است که کاهش صفحات معمولاً I/O و مصرف Buffer Pool را بهتر می‌کند. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل DATA_COMPRESSION با ده مثال اجرایی، FAQ و نکات Performance

WAIT_AT_LOW_PRIORITY: انتظار کم‌اولویت برای قفل عملیات آنلاین

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

کاربرد اصلی آن محافظت از دسترس‌پذیری workload هنگام آغاز یا پایان Online Index Rebuild است. مهم‌ترین نکته عملی این است که هدف اصلی کاهش اثر Blocking است، نه سریع‌ترکردن Sort یا Scan. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل WAIT_AT_LOW_PRIORITY با ده مثال اجرایی، FAQ و نکات Performance

OPTIMIZE_FOR_SEQUENTIAL_KEY: بهینه‌سازی درج کلیدهای ترتیبی

OPTIMIZE_FOR_SEQUENTIAL_KEY مکانیزم Flow Control موتور SQL Server را برای کاهش Last-page insert contention در ایندکس‌های B-tree با کلید افزایشی فعال می‌کند. این گزینه برای مشکل PAGELATCH_EX تحت هم‌زمانی بالا طراحی شده و درمان عمومی Fragmentation نیست.

کاربرد اصلی آن بهبود throughput درج هم‌زمان روی آخرین صفحه ایندکس دارای IDENTITY، Sequence یا زمان صعودی است. مهم‌ترین نکته عملی این است که در بار کم ممکن است تفاوتی دیده نشود. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل OPTIMIZE_FOR_SEQUENTIAL_KEY با ده مثال اجرایی، FAQ و نکات Performance

ALLOW_ROW_LOCKS: کنترل اجازه قفل ردیفی ایندکس

ALLOW_ROW_LOCKS مشخص می‌کند موتور اجازه دارد هنگام دسترسی به ایندکس از قفل ردیف یا Key استفاده کند. خاموش‌کردن آن قفل ردیفی را ممنوع می‌کند، اما موتور همچنان بر اساس شرایط می‌تواند از Page یا Table Lock بهره بگیرد.

کاربرد اصلی آن کنترل گزینه‌های Granularity قفل در یک سناریوی خاص پس از تحلیل Blocking و هزینه Lock Manager است. مهم‌ترین نکته عملی این است که قفل ردیفی هم‌زمانی ظریف‌تری می‌دهد اما تعداد Lock را بالا می‌برد. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل ALLOW_ROW_LOCKS با ده مثال اجرایی، FAQ و نکات Performance

ALLOW_PAGE_LOCKS: کنترل اجازه قفل صفحه‌ای ایندکس

ALLOW_PAGE_LOCKS مشخص می‌کند موتور اجازه استفاده از قفل Page هنگام دسترسی به ایندکس را دارد یا نه. خاموش‌کردن این گزینه Page Lock را حذف می‌کند، ولی Table Lock و Lock Escalation را به‌طور مطلق متوقف نمی‌کند و می‌تواند برخی عملیات نگهداری مانند REORGANIZE را محدود کند.

کاربرد اصلی آن آزمایش کنترل‌شده Granularity قفل صفحه در workloadهای خاص با مشاهده دقیق هم‌زمانی است. مهم‌ترین نکته عملی این است که Page Lock میان سربار کم و هم‌زمانی تعادل ایجاد می‌کند. انتخاب مقدار باید با نسخه، نوع ایندکس و ظرفیت سرور هماهنگ شود.

آموزش کامل ALLOW_PAGE_LOCKS با ده مثال اجرایی، FAQ و نکات Performance

سناریوهای تصمیم‌گیری رایج

در سامانه ۲۴ ساعته با ایندکس بزرگ، ترکیب ONLINE، WAIT_AT_LOW_PRIORITY و در صورت پشتیبانی RESUMABLE می‌تواند ریسک پنجره نگهداری را کم کند. MAXDOP برای باقی‌گذاشتن ظرفیت به workload محدود می‌شود و MAX_DURATION پایان پنجره را کنترل می‌کند. با این حال اگر Log یا فضای Target کافی نباشد، همین ترکیب نیز امن نیست.

در Data Warehouse با پنجره Batch، عملیات آفلاین سریع‌تر ممکن است انتخاب بهتری باشد. SORT_IN_TEMPDB زمانی مفید است که tempdb سریع، جدا و دارای فضای کافی باشد. DATA_COMPRESSION برای پارتیشن‌های سرد معمولاً جذاب‌تر است و MAXDOP می‌تواند بر اساس زمان Batch بالاتر انتخاب شود. معیار اصلی پایان قابل پیش‌بینی Batch و هزینه Queryهای تحلیلی است.

در OLTP با GUID تصادفی، FILLFACTOR کنترل‌شده ممکن است Split را کاهش دهد؛ اما مقدار پایین برای همه ایندکس‌ها Cache و I/O را بدتر می‌کند. در کلید IDENTITY افزایشی، Fill Factor پایین درمان Last-page contention نیست و OPTIMIZE_FOR_SEQUENTIAL_KEY پس از اثبات Wait مربوط گزینه مناسب‌تری است.

در مسئله Blocking، ALLOW_ROW_LOCKS یا ALLOW_PAGE_LOCKS راه‌حل جادویی نیستند. خاموش‌کردن یک Granularity می‌تواند موتور را به قفل درشت‌تر یا تعداد بسیار بیشتر قفل سوق دهد. Query، Index Coverage، Isolation Level، طول تراکنش و Lock Escalation باید پیش از تغییر این ویژگی‌ها بررسی شوند.

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

مثال شماره 1: ساخت ایندکس متعادل برای جدول سفارش‌ها

این مثال چند گزینه مکمل را در یک CREATE INDEX کنار هم می‌گذارد: عملیات آنلاین، Sort در tempdb، سقف موازی‌سازی و فضای آزاد کنترل‌شده برای تغییرات آینده.

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_IndexOptions_Orders;
CREATE TABLE dbo.Demo_IndexOptions_Orders
(
    OrderId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    OrderDate datetime2(0) NOT NULL,
    Status tinyint NOT NULL,
    TotalAmount decimal(18,2) NOT NULL
);
INSERT INTO dbo.Demo_IndexOptions_Orders (CustomerId, OrderDate, Status, TotalAmount)
SELECT TOP (2000) 1 + ABS(CHECKSUM(NEWID())) % 500,
       DATEADD(minute, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       ABS(CHECKSUM(NEWID())) % 4, CAST(10 + ABS(CHECKSUM(NEWID())) % 20000 AS decimal(18,2))
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

CREATE INDEX IX_Demo_IndexOptions_Orders_CustomerDate
ON dbo.Demo_IndexOptions_Orders (CustomerId, OrderDate)
INCLUDE (Status, TotalAmount)
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 2, FILLFACTOR = 90);

SELECT name, type_desc, fill_factor
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Demo_IndexOptions_Orders');
DROP TABLE dbo.Demo_IndexOptions_Orders;

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

مثال شماره 2: بازسازی آنلاین و قابل ادامه در پنجره محدود

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

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_IndexOptions_Events;
CREATE TABLE dbo.Demo_IndexOptions_Events
(
    EventId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EventTime datetime2(3) NOT NULL,
    EventType int NOT NULL,
    Payload char(200) NOT NULL DEFAULT 'x'
);
INSERT INTO dbo.Demo_IndexOptions_Events (EventTime, EventType)
SELECT TOP (5000) DATEADD(millisecond, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       ABS(CHECKSUM(NEWID())) % 20
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE INDEX IX_Demo_IndexOptions_Events_Time
ON dbo.Demo_IndexOptions_Events (EventTime, EventType);

ALTER INDEX IX_Demo_IndexOptions_Events_Time ON dbo.Demo_IndexOptions_Events
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 30 MINUTES, MAXDOP = 2);

SELECT name, state_desc, percent_complete
FROM sys.index_resumable_operations
WHERE object_id = OBJECT_ID(N'dbo.Demo_IndexOptions_Events');
DROP TABLE dbo.Demo_IndexOptions_Events;

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

مثال شماره 3: برآورد سود PAGE Compression پیش از Rebuild

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

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_IndexOptions_Archive;
CREATE TABLE dbo.Demo_IndexOptions_Archive
(
    ArchiveId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Category int NOT NULL,
    FixedText char(400) NOT NULL DEFAULT 'Repeated archive value'
);
INSERT INTO dbo.Demo_IndexOptions_Archive (Category)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 10
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE INDEX IX_Demo_IndexOptions_Archive_Category
ON dbo.Demo_IndexOptions_Archive (Category) INCLUDE (FixedText);

EXEC sys.sp_estimate_data_compression_savings
     @schema_name = N'dbo', @object_name = N'Demo_IndexOptions_Archive',
     @index_id = 2, @partition_number = NULL,
     @data_compression = N'PAGE';
DROP TABLE dbo.Demo_IndexOptions_Archive;

اعداد جدول نمایشی‌اند و باید خروجی واقعی همان داده ملاک باشد. برآورد اندازه به‌تنهایی کافی نیست؛ CPU Queryها و زمان Rebuild نیز باید در آزمون بار سنجیده شود.

مثال شماره 4: بهینه‌سازی صف درج با کلید ترتیبی

یک جدول ثبت پیام با کلید IDENTITY ساخته می‌شود و گزینه OPTIMIZE_FOR_SEQUENTIAL_KEY روی ایندکس خوشه‌ای اعمال می‌گردد. اجازه Row و Page Lock نیز صریح ثبت می‌شود.

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_IndexOptions_Queue;
CREATE TABLE dbo.Demo_IndexOptions_Queue
(
    QueueId bigint IDENTITY(1,1) NOT NULL,
    EnqueuedAt datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    Body nvarchar(200) NOT NULL,
    CONSTRAINT PK_Demo_IndexOptions_Queue PRIMARY KEY CLUSTERED (QueueId)
    WITH
    (
        OPTIMIZE_FOR_SEQUENTIAL_KEY = ON,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )
);
INSERT INTO dbo.Demo_IndexOptions_Queue (Body)
SELECT TOP (1000) N'پیام صف' FROM sys.all_objects;

SELECT name, optimize_for_sequential_key, allow_row_locks, allow_page_locks
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Demo_IndexOptions_Queue');
DROP TABLE dbo.Demo_IndexOptions_Queue;

این گزینه فقط وقتی ارزش دارد که Last-page contention با PAGELATCH_EX و تست هم‌زمانی اثبات شده باشد. برای Insert تک‌جلسه‌ای معمولاً تفاوت معناداری دیده نمی‌شود.

مثال شماره 5: پایان کم‌خطر Online Rebuild با WAIT_AT_LOW_PRIORITY

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

USE tempdb;
DROP TABLE IF EXISTS dbo.Demo_IndexOptions_LockWait;
CREATE TABLE dbo.Demo_IndexOptions_LockWait
(
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ChangedAt datetime2(0) NOT NULL,
    Value int NOT NULL
);
INSERT INTO dbo.Demo_IndexOptions_LockWait (ChangedAt, Value)
SELECT TOP (1000) DATEADD(second, -ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSUTCDATETIME()),
       ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_objects;
CREATE INDEX IX_Demo_IndexOptions_LockWait_ChangedAt
ON dbo.Demo_IndexOptions_LockWait (ChangedAt);

ALTER INDEX IX_Demo_IndexOptions_LockWait_ChangedAt ON dbo.Demo_IndexOptions_LockWait
REBUILD WITH
(
    ONLINE = ON
    (
        WAIT_AT_LOW_PRIORITY
        (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)
    ),
    MAXDOP = 2
);
SELECT N'عملیات تکمیل شد یا در صورت عبور از مهلت، فقط خود عملیات لغو می‌شود' AS PolicyResult;
DROP TABLE dbo.Demo_IndexOptions_LockWait;

گزینه BLOCKERS می‌تواند Sessionهای کاربری را خاتمه دهد و برای اجرای خودکار مناسب نیست مگر اینکه دامنه، مجوز و پیامد Rollback به‌طور کامل مدیریت شده باشد.

مثال شماره 6: گزارش یکپارچه ویژگی‌های پایدار ایندکس‌ها

این Query تنظیمات پایدار مانند Fill Factor، Padding، Compression، قفل‌ها و Sequential Key را یکجا نمایش می‌دهد و برای ممیزی پیش از نگهداری مناسب است.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
       OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       i.type_desc,
       i.fill_factor,
       i.is_padded,
       i.allow_row_locks,
       i.allow_page_locks,
       i.optimize_for_sequential_key,
       p.partition_number,
       p.data_compression_desc
FROM sys.indexes AS i
JOIN sys.partitions AS p
  ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.index_id > 0
  AND i.is_hypothetical = 0
ORDER BY SchemaName, TableName, IndexName, p.partition_number;

ONLINE، RESUMABLE، SORT_IN_TEMPDB، MAXDOP و WAIT_AT_LOW_PRIORITY عمدتاً ویژگی اجرای عملیات‌اند و همگی به‌صورت خصوصیت پایدار ایندکس در این گزارش دیده نمی‌شوند؛ تاریخچه اجرای آن‌ها را جداگانه ثبت کنید.

خطاهای رایج در تنظیم گزینه‌های ایندکس

• اجرای یک Script ثابت روی همه پایگاه‌ها بدون کنترل نسخه، Edition، اندازه و نوع ایندکس.
• فرض اینکه ONLINE بدون Lock، RESUMABLE بدون مصرف فضا و Compression بدون هزینه CPU است.
• انتخاب Fill Factor پایین فقط از روی Fragmentation و بدون اندازه‌گیری Page Split و Page Density.
• تنظیم MAXDOP بر اساس تعداد کل Core و بی‌توجهی به NUMA، بار هم‌زمان و Resource Governor.
• استفاده از ABORT_AFTER_WAIT = BLOCKERS بدون تحلیل Rollback تراکنش‌های کاربران.
• رهاکردن عملیات Resumable در حالت PAUSED بدون هشدار، مالک و برنامه RESUME یا ABORT.
• فعال‌کردن SORT_IN_TEMPDB بدون ظرفیت‌سنجی و Autogrowth مناسب فایل‌های tempdb.
• خاموش‌کردن Row یا Page Lock برای درمان Blocking بدون مشاهده قفل واقعی و تست بار.

مانیتورینگ و شاخص‌های ضروری

Snapshot تنها کافی نیست. زمان نمونه‌برداری، راه‌اندازی سرور، پاک‌شدن DMV، بار کاربران و اجرای هم‌زمان Jobها باید کنار خروجی ذخیره شود. برای تغییر تولیدی، داشبوردی که CPU، Log Used، tempdb، Blocking و درصد پیشرفت را هم‌زمان نشان دهد بسیار ارزشمندتر از یک پیام موفقیت DDL است.

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

گزینه‌های مهم ایندکس دقیقاً چه کاری در SQL Server انجام می‌دهد؟

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

چگونه مقدار مناسب برای گزینه‌های مهم ایندکس را انتخاب کنیم؟

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

آیا فعال‌سازی گزینه‌های مهم ایندکس برای هر سامانه تجاری سودمند است؟

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

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

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

گزینه‌های مهم ایندکس چه تفاوتی با تنظیمات نزدیک خود دارد؟

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

برای سفارش بررسی و اجرای گزینه‌های مهم ایندکس چه اطلاعاتی لازم است؟

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

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

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

گزینه‌های مهم ایندکس چه اثری بر Performance دارد؟

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

Best Practice استفاده از گزینه‌های مهم ایندکس چیست؟

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

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

هر گزینه تاریخچه و محدودیت مستقل دارد؛ ONLINE به ویرایش و نوع ایندکس وابسته است، RESUMABLE در نسخه‌های جدیدتر عرضه شده و OPTIMIZE_FOR_SEQUENTIAL_KEY از SQL Server 2019 در دسترس است. مستندات رسمی همان نسخه مقصد معیار نهایی است.

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

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

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

برای اثبات نیاز به گزینه‌های مهم ایندکس کدام شواهد را جمع می‌کنید؟

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

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

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

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

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

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

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

آیا می‌توان گزینه‌های مهم ایندکس را در Job نگهداری به‌صورت ثابت قرار داد؟

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

چک‌لیست اجرای تولیدی

• DDL، اندازه، Partition، وابستگی Constraint و فضای آزاد ثبت شده است.
• نسخه و Edition و محدودیت نوع ایندکس با Syntax انتخابی تطبیق داده شده است.
• خط مبنای Query Store، Wait، CPU، I/O، Log، tempdb و Blocking وجود دارد.
• فرمان در Restore تازه از تولید و با هم‌زمانی نماینده آزمایش شده است.
• MAXDOP، MAX_DURATION، سیاست Low Priority و فضای Autogrowth صریح هستند.
• مسیر PAUSE، RESUME، ABORT یا Rollback و مسئول تصمیم مشخص شده است.
• Telemetry و هشدار عملیات PAUSED، Blocking طولانی و کمبود فضا فعال است.
• پس از اجرا، تنظیم پایدار و معیارهای کارایی با مقدار هدف مقایسه می‌شوند.

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

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

جمع‌بندی و مسیر مطالعه

این یازده گزینه ابزارهایی برای حل مسئله‌های متفاوت‌اند. ONLINE دسترس‌پذیری حین DDL را بهتر می‌کند، RESUMABLE پنجره اجرا را قابل تقسیم می‌سازد، SORT_IN_TEMPDB مسیر Sort را تغییر می‌دهد، MAXDOP CPU را محدود می‌کند، FILLFACTOR و PAD_INDEX چگالی صفحات را شکل می‌دهند، Compression فضای ذخیره را تغییر می‌دهد، Low Priority رفتار انتظار Lock را کنترل می‌کند، Sequential Key رقابت صفحه انتهایی را هدف می‌گیرد و گزینه‌های Row/Page Lock انتخاب‌های Granularity را محدود می‌کنند.

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

• ONLINE = ON؛ ساخت و بازسازی آنلاین ایندکس
• RESUMABLE = ON؛ عملیات قابل توقف و ادامه ایندکس
• SORT_IN_TEMPDB = ON؛ مرتب‌سازی ساخت ایندکس در tempdb
• MAXDOP؛ کنترل موازی‌سازی عملیات ایندکس با MAXDOP
• FILLFACTOR؛ تنظیم فضای آزاد صفحات ایندکس با FILLFACTOR
• PAD_INDEX؛ اعمال فضای آزاد به سطوح میانی با PAD_INDEX
• DATA_COMPRESSION؛ فشرده‌سازی داده و ایندکس با DATA_COMPRESSION
• WAIT_AT_LOW_PRIORITY؛ انتظار کم‌اولویت برای قفل عملیات آنلاین
• OPTIMIZE_FOR_SEQUENTIAL_KEY؛ بهینه‌سازی درج کلیدهای ترتیبی
• ALLOW_ROW_LOCKS؛ کنترل اجازه قفل ردیفی ایندکس
• ALLOW_PAGE_LOCKS؛ کنترل اجازه قفل صفحه‌ای ایندکس