مقدمه

نمای ایندکس‌شده یکی از ابزارهای مهم طراحی دسترسی به داده در Microsoft SQL Server است. این ساختار با هدف مادی‌سازی نتیجه View با Unique Clustered Index و SET Optionهای اجباری به کار می‌رود و انتخاب درست آن می‌تواند تعداد خواندن‌های منطقی، زمان CPU و مدت انتظار کاربر را کاهش دهد. با این حال ایندکس رایگان نیست؛ هر ساختار جدید فضا می‌گیرد و هزینه INSERT، UPDATE، DELETE، نگهداری آمار، Backup و عملیات بازسازی را افزایش می‌دهد.

سناریوی شاخص این نوع ایندکس، تجمیع پرهزینه و Joinهای پرتکرار با هزینه نگهداری هنگام DML است. تصمیم حرفه‌ای باید از روی Query واقعی، توزیع داده، نرخ تغییر، Selectivity و Execution Plan گرفته شود؛ نام جذاب یک قابلیت به‌تنهایی دلیل ساخت آن نیست. این راهنما از تعریف و Syntax شروع می‌کند و سپس ساخت، اندازه‌گیری، خطاها، کارایی و استقرار ایمن را با مثال‌های مستقل نشان می‌دهد.

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

تعریف، معماری و محدوده کاربرد

در نمای ایندکس‌شده، موتور پایگاه داده از سازوکاری متناسب با خانواده view استفاده می‌کند. نقش اصلی آن مادی‌سازی نتیجه View با Unique Clustered Index و SET Optionهای اجباری است. نتیجه مطلوب زمانی حاصل می‌شود که Predicate، ترتیب Join، Sort، Group By و ستون‌های خروجی با ساختار ایندکس هماهنگ باشند؛ در غیر این صورت Optimizer ممکن است Scan یا ساختار دیگری را ارزان‌تر تشخیص دهد.

محدوده سازگاری این مقاله چنین است: SQL Server 2005 و نسخه‌های جدیدتر؛ محدودیت‌های تعریف View اعمال می‌شود. هنگام انتقال اسکریپت میان SQL Server، Azure SQL Database و Managed Instance باید Edition، Compatibility Level و قابلیت‌های Preview بررسی شود. هر Syntax نسخه‌محور در محیط آزمایشی اجرا شود و تنها پس از مقایسه Query Store و Plan به Production برسد.

Syntax پایه

CREATE UNIQUE CLUSTERED INDEX CUX_IndexLab_SalesSummary ON dbo.vIndexLab_SalesSummary(CustomerId);

پارامترها و اجزای تصمیم

• کلید یا ستون هدف: باید با فیلتر و ترتیب Queryهای مهم هماهنگ باشد و Selectivity مناسبی داشته باشد.
• نام و مالکیت: نام‌گذاری استاندارد مانند IX یا UX، جدول هدف و هدف کسب‌وکار را قابل تشخیص می‌کند.
• گزینه‌های ساخت: ONLINE، RESUMABLE، MAXDOP، SORT_IN_TEMPDB، فشرده‌سازی یا گزینه تخصصی فقط با بررسی نسخه انتخاب شوند.
• هزینه نگهداری: تعداد Update، حجم Log، Fragmentation، آمار و پنجره Maintenance پیش از ایجاد برآورد شود.
• نوع خروجی: ایندکس مستقیماً داده برنمی‌گرداند؛ اثر آن در Plan، IO، CPU، زمان اجرا و ثبات پاسخ دیده می‌شود.

هیچ Index Hint را صرفاً برای مجبور کردن Optimizer وارد کد نکنید. Hint می‌تواند پس از رشد داده یا ارتقای نسخه به تصمیم نامناسب تبدیل شود. ابتدا علت انتخاب نشدن ایندکس، تبدیل ضمنی، غیر SARGable بودن شرط، آمار قدیمی یا تخمین Cardinality را پیدا کنید.

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

مثال 1: ساخت محیط آزمایش و ایجاد ایندکس

در این سناریو، ساخت محیط آزمایش و ایجاد ایندکس برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

-- پیش‌نیاز: SQL Server 2005 و نسخه‌های جدیدتر؛ محدودیت‌های تعریف View اعمال می‌شود
SET NUMERIC_ROUNDABORT OFF; SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET CONCAT_NULL_YIELDS_NULL ON; SET QUOTED_IDENTIFIER ON; SET ARITHABORT ON;
IF OBJECT_ID(N'dbo.IndexLab_Sales', N'U') IS NULL CREATE TABLE dbo.IndexLab_Sales(CustomerId int NOT NULL, Amount decimal(19,4) NOT NULL);
GO
CREATE OR ALTER VIEW dbo.vIndexLab_SalesSummary WITH SCHEMABINDING AS SELECT CustomerId, COUNT_BIG(*) AS RowCount, SUM(Amount) AS TotalAmount FROM dbo.IndexLab_Sales GROUP BY CustomerId;
GO
CREATE UNIQUE CLUSTERED INDEX CUX_IndexLab_SalesSummary ON dbo.vIndexLab_SalesSummary(CustomerId);

نکته فنی: مثال 1 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 2: بازرسی نوع و ویژگی‌های ایندکس

در این سناریو، بازرسی نوع و ویژگی‌های ایندکس برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SELECT i.name, i.type_desc, i.is_unique, i.has_filter, i.filter_definition
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.vIndexLab_SalesSummary');

نکته فنی: مثال 2 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 3: تحلیل ستون‌های کلیدی و Include

در این سناریو، تحلیل ستون‌های کلیدی و Include برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SELECT i.name, c.name AS column_name, ic.key_ordinal, ic.is_included_column
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
JOIN sys.columns AS c ON c.object_id=ic.object_id AND c.column_id=ic.column_id
WHERE i.object_id=OBJECT_ID(N'dbo.vIndexLab_SalesSummary')
ORDER BY i.name, ic.key_ordinal, ic.index_column_id;

نکته فنی: مثال 3 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 4: اندازه‌گیری Seek، Scan و هزینه DML

در این سناریو، اندازه‌گیری Seek، Scan و هزینه DML برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SELECT i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s ON s.database_id=DB_ID() AND s.object_id=i.object_id AND s.index_id=i.index_id
WHERE i.object_id=OBJECT_ID(N'dbo.vIndexLab_SalesSummary');

نکته فنی: مثال 4 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 5: محاسبه فضا و تعداد ردیف

در این سناریو، محاسبه فضا و تعداد ردیف برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SELECT i.name, SUM(ps.used_page_count)*8.0/1024 AS used_mb, SUM(ps.row_count) AS rows_count
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.object_id=OBJECT_ID(N'dbo.vIndexLab_SalesSummary') GROUP BY i.name;

نکته فنی: مثال 5 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 6: کنترل Fragmentation یا سلامت ساختار

در این سناریو، کنترل Fragmentation یا سلامت ساختار برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SELECT i.name, ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(),i.object_id,i.index_id,NULL,'LIMITED') AS ips
WHERE i.object_id=OBJECT_ID(N'dbo.vIndexLab_SalesSummary');

نکته فنی: مثال 6 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 7: بررسی رفتار عملیاتی در بار واقعی

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

SELECT i.name, ios.leaf_insert_count, ios.leaf_update_count, ios.range_scan_count
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_index_operational_stats(DB_ID(),i.object_id,i.index_id,NULL) AS ios
WHERE i.object_id=OBJECT_ID(N'dbo.vIndexLab_SalesSummary');

نکته فنی: مثال 7 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 8: کنترل آمار، Disable و وضعیت نگهداری

در این سناریو، کنترل آمار، Disable و وضعیت نگهداری برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SELECT i.name, i.is_disabled, i.is_hypothetical, STATS_DATE(i.object_id,i.index_id) AS stats_date
FROM sys.indexes AS i WHERE i.object_id=OBJECT_ID(N'dbo.vIndexLab_SalesSummary');

نکته فنی: مثال 8 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 9: اجرای سناریوی واقعی و مشاهده پلن

در این سناریو، اجرای سناریوی واقعی و مشاهده پلن برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SET STATISTICS IO, TIME ON;
SELECT CustomerId, COUNT_BIG(*) AS cnt, SUM(TotalAmount) AS total
FROM dbo.IndexLab_IndexedView
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
GROUP BY CustomerId;
SET STATISTICS IO, TIME OFF;

نکته فنی: مثال 9 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

مثال 10: کنترل نسخه و چک‌لیست استقرار

در این سناریو، کنترل نسخه و چک‌لیست استقرار برای نمای ایندکس‌شده بررسی می‌شود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.

SELECT SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('Edition') AS edition;
SELECT i.name, i.type_desc FROM sys.indexes AS i WHERE i.object_id=OBJECT_ID(N'dbo.vIndexLab_SalesSummary');

نکته فنی: مثال 10 یک بُعد متفاوت از چرخه عمر ایندکس را می‌سنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.

خطاهای رایج و روش عیب‌یابی

• ساخت نمای ایندکس‌شده بدون مشاهده Query Plan و Baseline باعث Over-indexing و افزایش Write Amplification می‌شود.
• ترتیب نامناسب ستون‌ها یا ناسازگاری نوع داده می‌تواند Seek را به Scan یا Residual Predicate تبدیل کند.
• آمار قدیمی، Parameter Sniffing و توزیع نامتوازن داده ممکن است اثر ایندکس را پنهان یا ناپایدار کنند.
• اجرای Rebuild زمان‌بندی‌نشده می‌تواند Log، TempDB، قفل، CPU و Replica را تحت فشار بگذارد.
• حذف ایندکس بر اساس Usage DMV پس از Restart خطرناک است؛ بازه کامل کسب‌وکار و Jobهای ماهانه باید دیده شود.

برای عیب‌یابی، Actual Execution Plan، SET STATISTICS IO/TIME، Query Store، Wait Statistics و DMVهای مصرف ایندکس را کنار هم بررسی کنید. یک Snapshot منفرد کافی نیست. Plan قبل و بعد، تعداد اجرای Query، اختلاف Logical Read و اثر بر عملیات نوشتن باید ثبت شود.

ملاحظات کارایی و بهترین روش‌ها

برای نمای ایندکس‌شده ابتدا یک فرضیه قابل اندازه‌گیری بنویسید: کدام Query، با چه نرخ اجرا و چه SLA باید بهتر شود؟ سپس تغییر را روی نسخه‌ای واقع‌گرایانه از داده بسازید. Cache گرم و سرد، پارامترهای متنوع و بار همزمان را آزمایش کنید. بهبود یک Query اگر باعث افت شدید ده‌ها عملیات نوشتن شود، موفقیت سامانه نیست.

کلیدهای باریک معمولاً حافظه و IO کمتری مصرف می‌کنند. ستون‌های عریض، مقادیر با تغییر زیاد و ایندکس‌های هم‌پوشان هزینه را بالا می‌برند. Fill Factor نیز نسخه جادویی ندارد؛ برای درج افزایشی معمولاً مقدار پیش‌فرض مناسب است و کاهش آن فقط پس از اثبات Page Split مضر توجیه می‌شود.

در Production، ایجاد یا بازسازی بزرگ را با ظرفیت Transaction Log، TempDB، Availability Group، Replication و پنجره نگهداری هماهنگ کنید. گزینه ONLINE به معنی بدون هزینه یا بدون قفل نیست. قابلیت RESUMABLE می‌تواند کنترل‌پذیری را بهتر کند، اما پشتیبانی Edition و Version باید تأیید شود.

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

• Queryهای هدف و Baseline قبل از تغییر ثبت شده‌اند.
• Syntax و قابلیت در نسخه و Edition مقصد پشتیبانی می‌شود.
• اثر بر خواندن، نوشتن، Log، TempDB و فضای دیسک سنجیده شده است.
• Plan واقعی با پارامترهای نماینده داده بررسی شده است.
• Rollback Script و معیار توقف برای استقرار آماده است.
• مانیتورینگ Query Store و DMVها پس از انتشار زمان‌بندی شده است.

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

پرسش 1: نمای ایندکس‌شده چیست و چه مسئله‌ای را حل می‌کند؟

نمای ایندکس‌شده برای مادی‌سازی نتیجه View با Unique Clustered Index و SET Optionهای اجباری طراحی شده است. ارزش آن با کاهش IO و بهبود Plan سنجیده می‌شود، نه صرفاً موفق بودن دستور CREATE.

پرسش 2: چگونه تشخیص دهیم Query به نمای ایندکس‌شده نیاز دارد؟

Actual Plan، Query Store و STATISTICS IO را بررسی کنید. اگر دسترسی پرتکرار، انتخاب‌پذیر و پایدار است، آزمایش کنترل‌شده ایندکس می‌تواند تصمیم را تأیید کند.

پرسش 3: آیا ایجاد نمای ایندکس‌شده هزینه زیرساخت را کم می‌کند؟

ممکن است زمان CPU و نیاز به ارتقای سخت‌افزار را کاهش دهد، اما فضای دیسک و هزینه نگهداری دارد. محاسبه بازگشت سرمایه باید خواندن و نوشتن را همزمان ببیند.

پرسش 4: برای پروژه سازمانی چه زمانی مشاوره طراحی ایندکس لازم است؟

در بارهای حساس، چندمستاجری، گزارش‌های سنگین یا سامانه دارای SLA، بازبینی تخصصی طرح و اجرای Load Test ریسک آزمون مستقیم روی Production را کم می‌کند.

پرسش 5: نمای ایندکس‌شده چه تفاوتی با ایندکس غیرخوشه‌ای عمومی دارد؟

تفاوت در معماری، Syntax و الگوی مناسب است. نمای ایندکس‌شده برای تجمیع پرهزینه و Joinهای پرتکرار با هزینه نگهداری هنگام DML هدفمند است، در حالی که B+Tree عمومی برای طیف دیگری از Seek و Range Scan مناسب خواهد بود.

پرسش 6: آیا می‌توان اسکریپت استقرار نمای ایندکس‌شده را برای Production آماده کرد؟

بله؛ اسکریپت حرفه‌ای باید Precheck نسخه و فضا، ساخت Online یا مرحله‌ای در صورت پشتیبانی، Validation، مانیتورینگ و Rollback مشخص داشته باشد.

پرسش 7: رایج‌ترین خطای پیاده‌سازی نمای ایندکس‌شده چیست؟

ساخت بر اساس حدس و بدون توجه به Predicate و ترتیب ستون‌ها رایج‌ترین خطاست. نتیجه می‌تواند ایندکس بلااستفاده و کند شدن DML باشد.

پرسش 8: نمای ایندکس‌شده چه اثری بر Performance و عملیات نوشتن دارد؟

در Query مناسب خواندن را سریع می‌کند، ولی هر تغییر داده باید ساختار را هم نگهداری کند. نسبت user_seeks به user_updates همراه اهمیت کسب‌وکار تحلیل شود.

پرسش 9: بهترین روش نگهداری نمای ایندکس‌شده چیست؟

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

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

SQL Server 2005 و نسخه‌های جدیدتر؛ محدودیت‌های تعریف View اعمال می‌شود. پیش از استقرار ProductVersion، Edition، Compatibility Level و محدودیت‌های سرویس مقصد را با مستندات همان Build کنترل کنید.

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

• چرا Optimizer ممکن است با وجود نمای ایندکس‌شده آن را انتخاب نکند؟
• چگونه هزینه Write Amplification ناشی از نمای ایندکس‌شده را اندازه می‌گیرید؟
• چه تفاوتی میان Seek Predicate و Residual Predicate در ارزیابی این ایندکس وجود دارد؟
• برنامه Rollback شما پس از ایجاد یک ایندکس بزرگ چیست؟

پاسخ قوی در مصاحبه فقط تعریف Syntax نیست؛ باید درباره Cardinality، Selectivity، Statistics، IO، Concurrency، Logging و Trade-off میان خواندن و نوشتن استدلال کند.

جمع‌بندی

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

برای مقایسه این ساختار با ۳۴ نوع دیگر، راهنمای کامل انواع ایندکس در SQL Server را مطالعه کنید.