مثالهای عملی
مثال 1: ساخت محیط آزمایش و ایجاد ایندکس
در این سناریو، ساخت محیط آزمایش و ایجاد ایندکس برای ایندکس مکانی بررسی میشود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.
IF OBJECT_ID(N'dbo.IndexLab_Spatial', N'U') IS NOT NULL DROP TABLE dbo.IndexLab_Spatial;
CREATE TABLE dbo.IndexLab_Spatial(Id int NOT NULL PRIMARY KEY, Shape geometry NOT NULL);
INSERT dbo.IndexLab_Spatial VALUES (1, geometry::Point(10, 20, 0));
CREATE SPATIAL INDEX SIX_IndexLab_Shape ON dbo.IndexLab_Spatial(Shape) USING GEOMETRY_AUTO_GRID;
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 1 | شیء نمونه و ایندکس بدون خطا ساخته میشوند. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 2 | نوع، یکتایی و فیلتر ایندکس در Catalog دیده میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial')
ORDER BY i.name, ic.key_ordinal, ic.index_column_id;
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 3 | ترتیب کلیدها و ستونهای غیرکلیدی مشخص میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 4 | تعداد Seek و Scan در کنار هزینه Update قابل مقایسه است. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial') GROUP BY i.name;
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 5 | حجم مصرفی و تعداد ردیف هر ساختار گزارش میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 6 | درصد Fragmentation یا اطلاعات سلامت قابل تصمیمگیری میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 7 | شمار درج، تغییر و Range Scan برای بار واقعی نمایش داده میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 8 | تاریخ آمار و وضعیت فعال بودن ایندکس روشن میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 8 یک بُعد متفاوت از چرخه عمر ایندکس را میسنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.
مثال 9: اجرای سناریوی واقعی و مشاهده پلن
در این سناریو، اجرای سناریوی واقعی و مشاهده پلن برای ایندکس مکانی بررسی میشود. Query را ابتدا روی پایگاه آزمایشی اجرا کنید، خروجی Catalog و Plan را ثبت کنید و سپس نتیجه را با Baseline بدون تغییر مقایسه کنید.
DECLARE @p geometry = geometry::Point(11,21,0);
SELECT Id, Shape.STDistance(@p) AS distance FROM dbo.IndexLab_Spatial WHERE Shape.STDistance(@p) IS NOT NULL;
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 9 | خروجی کسبوکار همراه IO و زمان اجرا قابل ارزیابی است. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 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_Spatial');
| شاخص | خروجی مورد انتظار |
|---|
| نتیجه مثال 10 | نسخه موتور و پشتیبانی قابلیت پیش از استقرار تأیید میشود. |
| کاربرد واقعی | تصمیم درباره جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی بر پایه اندازهگیری و نه حدس |
نکته فنی: مثال 10 یک بُعد متفاوت از چرخه عمر ایندکس را میسنجد. اعداد مطلق میان سرورها قابل مقایسه نیستند؛ تغییر IO، CPU، Duration، Memory Grant و تعداد ردیف تخمینی در همان محیط معیار معتبرتری است.
سؤالات متداول
پرسش 1: ایندکس مکانی چیست و چه مسئلهای را حل میکند؟
ایندکس مکانی برای تسریع فیلتر مکانی روی دادههای geometry و geography با Tessellation طراحی شده است. ارزش آن با کاهش IO و بهبود Plan سنجیده میشود، نه صرفاً موفق بودن دستور CREATE.
پرسش 2: چگونه تشخیص دهیم Query به ایندکس مکانی نیاز دارد؟
Actual Plan، Query Store و STATISTICS IO را بررسی کنید. اگر دسترسی پرتکرار، انتخابپذیر و پایدار است، آزمایش کنترلشده ایندکس میتواند تصمیم را تأیید کند.
پرسش 3: آیا ایجاد ایندکس مکانی هزینه زیرساخت را کم میکند؟
ممکن است زمان CPU و نیاز به ارتقای سختافزار را کاهش دهد، اما فضای دیسک و هزینه نگهداری دارد. محاسبه بازگشت سرمایه باید خواندن و نوشتن را همزمان ببیند.
پرسش 4: برای پروژه سازمانی چه زمانی مشاوره طراحی ایندکس لازم است؟
در بارهای حساس، چندمستاجری، گزارشهای سنگین یا سامانه دارای SLA، بازبینی تخصصی طرح و اجرای Load Test ریسک آزمون مستقیم روی Production را کم میکند.
پرسش 5: ایندکس مکانی چه تفاوتی با ایندکس غیرخوشهای عمومی دارد؟
تفاوت در معماری، Syntax و الگوی مناسب است. ایندکس مکانی برای جستوجوی تقاطع، فاصله، همپوشانی و محدوده جغرافیایی هدفمند است، در حالی که 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 2008 و نسخههای جدیدتر. پیش از استقرار ProductVersion، Edition، Compatibility Level و محدودیتهای سرویس مقصد را با مستندات همان Build کنترل کنید.