تشخيص كمبود ايندكس‌ها در SQL server

در مطالب قبلي به اختصار در مورد 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

خوب، پس از گزارشگيري، ممكن است ليست بلند بالايي تهيه شود. كوئري زير عبارات create index مورد نظر را بر اساس اين قابليت جديد توليد خواهد كرد:

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 ايجاد شده‌اند، سريعتر اجرا خواهند شد