اندازهی قلم متن
تخمین مدت زمان مطالعهی مطلب:
دو دقیقه
در مطالب قبلی به اختصار در مورد dynamic management views که از SQL server 2005 به بعد ارائه شدهاند مثالهایی کاربردی ارائه گشتند. یکی دیگر از قابلیتهای فوق العاده مهم این DMV ها، پیشنهاد ایجاد ایندکس بر روی جداول است. این پیشنهادات بر اساس آمارهای جمع آوری شده توسط موتور بهینه ساز اجرای کوئریها در اس کیوال سرور به شما ارائه خواهند شد. برای مثال کوئری زیر را در management studio اجر نمائید:
USE master;
SELECT d.database_id,
d.object_id,
d.index_handle,
d.equality_columns,
d.inequality_columns,
d.included_columns,
d.statement AS fully_qualified_object,
gs.*
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats gs
ON gs.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d
ON g.index_handle = d.index_handle
خروجی حاصل لیستی است که بر اساس تفاسیر موتور بهینه ساز اجرای کوئریها بدست آمده است. equality_columns بر اساس حالتهایی مانند table.column = constant_value پیش بینی شدهاست. inequality_columns بر اساس حالتهایی مانند table.column > constant_value و included_columns برای حالتهایی است که میخواهیم ایندکس ایجاد شده محدودیت اندازه 900 بایت را نداشته باشد، یا نوع دادهای مورد استفاده برای مثال nvrachar max و امثال آن باشد (text و ntext مجاز نیست) و مواردی از این دست.
fully_qualified_object هم مشخص میکند که این ایندکس دقیقا باید بر روی چه دیتابیس و جدولی ایجاد شود.
تذکر: این آمارهای جمعآوری شده پس از هر بار ریاستارت سرور، صفر خواهند شد.
اکنون این سؤال مطرح میشود که چگونه از این اطلاعات استفاده کنیم؟
دقیقا بر اساس EQUALITY_COLUMNS ، INEQUALITY_COLUMNS و INCLUDED_COLUMNS گزارش فوق، میتوان به صورت زیر عمل کرد:
CREATE NONCLUSTERED INDEX <unique index name>
ON <FULL_TABLE_NAME> (<EQUALITY_COLUMNS>,<INEQUALITY_COLUMNS>) -- exclude INEQUALITY_COLUMNS if NULL
INCLUDE (<INCLUDED_COLUMNS>); -- exclude INCLUDED_COLUMNS if NULL
SELECT mig.index_group_handle,
mid.index_handle,
migs.avg_total_user_cost AS AvgTotalUserCostThatCouldbeReduced,
migs.avg_user_impact AS AvgPercentageBenefit,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)
+ '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE
WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns
IS NOT NULL THEN ','
ELSE ''
END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS
create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
مزایای ایجاد ایندکسهای صحیح بر اساس نیازهای واقعی کاری:
- سریعتر شدن اجرای کوئریهای جستجو در تعداد رکوردهای بالا
- مرتب سازی سریعتر نتایج (sorting)
- کوئریهایی که بر اساس عبارت GROUP BY ایجاد شدهاند، سریعتر اجرا خواهند شد