مثالهای عملی
مثال 1: ساخت محیط آزمایش و ایجاد ایندکس
در این سناریو، ساخت محیط آزمایش و ایجاد ایندکس برای ایندکس جستوجوی تماممتن بررسی میشود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.
-- پیشنیاز: نیازمند نصب Full-Text Search و Full-Text Catalog
IF OBJECT_ID(N'dbo.IndexLab_FullText', N'U') IS NOT NULL DROP TABLE dbo.IndexLab_FullText;
CREATE TABLE dbo.IndexLab_FullText(Id int NOT NULL, Body nvarchar(max) NOT NULL);
CREATE UNIQUE INDEX UX_IndexLab_FullText_Id ON dbo.IndexLab_FullText(Id);
INSERT dbo.IndexLab_FullText VALUES (1, N'آموزش تخصصی ایندکس در SQL Server');
CREATE FULLTEXT INDEX ON dbo.IndexLab_FullText(Body LANGUAGE 1065) KEY INDEX UX_IndexLab_FullText_Id;
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 1 | شیء نمونه و ایندکس بدون خطا ساخته میشوند. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 2 | نوع، یکتایی و فیلتر ایندکس در Catalog دیده میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText')
ORDER BY i.name, ic.key_ordinal, ic.index_column_id;
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 3 | ترتیب کلیدها و ستونهای غیرکلیدی مشخص میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 4 | تعداد Seek و Scan در کنار هزینه Update قابل مقایسه است. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText') GROUP BY i.name;
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 5 | حجم مصرفی و تعداد ردیف هر ساختار گزارش میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 6 | درصد Fragmentation یا اطلاعات سلامت قابل تصمیمگیری میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 7 | شمار درج، تغییر و Range Scan برای بار واقعی نمایش داده میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 8 | تاریخ آمار و وضعیت فعال بودن ایندکس روشن میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 8 یک بُعد متفاوت از چرخه عمر ایندکس را میسنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.
مثال 9: اجرای سناریوی واقعی و مشاهده پلن
در این سناریو، اجرای سناریوی واقعی و مشاهده پلن برای ایندکس جستوجوی تماممتن بررسی میشود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.
SELECT Id, Body FROM dbo.IndexLab_FullText WHERE CONTAINS(Body, N'"SQL Server"');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 9 | خروجی کسبوکار همراه IO و زمان اجرا قابل ارزیابی است. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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.IndexLab_FullText');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 10 | نسخه موتور و پشتیبانی قابلیت پیش از استقرار تأیید میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی محتوایی با CONTAINS و FREETEXT بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 10 یک بُعد متفاوت از چرخه عمر ایندکس را میسنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.
سؤالات متداول
پرسش 1: ایندکس جستوجوی تماممتن چیست و چه مسئلهای را حل میکند؟
ایندکس جستوجوی تماممتن برای توکنسازی زبانی و جستوجوی واژه، عبارت، صرف و نزدیکی در متن طراحی شده است. ارزش آن با کاهش IO و بهبود Plan سنجیده میشود، نه صرفاً موفق بودن دستور CREATE.
پرسش 2: چگونه تشخیص دهیم Query به ایندکس جستوجوی تماممتن نیاز دارد؟
Actual Plan، Query Store و STATISTICS IO را بررسی کنید. اگر دسترسی پرتکرار، انتخابپذیر و پایدار است، آزمایش کنترلشده ایندکس میتواند تصمیم را تأیید کند.
پرسش 3: آیا ایجاد ایندکس جستوجوی تماممتن هزینه زیرساخت را کم میکند؟
ممکن است زمان CPU و نیاز به ارتقای سختافزار را کاهش دهد، اما فضای دیسک و هزینه نگهداری دارد. محاسبه بازگشت سرمایه باید خواندن و نوشتن را همزمان ببیند.
پرسش 4: برای پروژه سازمانی چه زمانی مشاوره طراحی ایندکس لازم است؟
در بارهای حساس، چندمستاجری، گزارشهای سنگین یا سامانه دارای SLA، بازبینی تخصصی طرح و اجرای Load Test ریسک آزمون مستقیم روی Production را کم میکند.
پرسش 5: ایندکس جستوجوی تماممتن چه تفاوتی با ایندکس غیرخوشهای عمومی دارد؟
تفاوت در معماری، Syntax و الگوی مناسب است. ایندکس جستوجوی تماممتن برای جستوجوی محتوایی با CONTAINS و FREETEXT هدفمند است، در حالی که 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 سازگار است؟
نیازمند نصب Full-Text Search و Full-Text Catalog. پیش از استقرار ProductVersion، Edition، Compatibility Level و محدودیتهای سرویس مقصد را با مستندات همان Build کنترل کنید.