Eksik index önerilerini DMV'lerden oku (uyarılarıyla)

Komut

USE [Musteri];
SELECT TOP (25)
       mid.statement                                   AS table_name,
       mid.equality_columns, mid.inequality_columns, mid.included_columns,
       migs.user_seeks, migs.user_scans,
       CAST(migs.avg_total_user_cost AS DECIMAL(18,2)) AS avg_query_cost,
       migs.avg_user_impact                            AS est_impact_pct,
       CAST(migs.user_seeks * migs.avg_total_user_cost * migs.avg_user_impact / 100.0 AS DECIMAL(18,0)) AS score,
       migs.last_user_seek,
       N'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + N'_missing_'
         + CAST(mid.index_handle AS NVARCHAR(10)) + N'] ON ' + mid.statement + N' ('
         + ISNULL(mid.equality_columns, N'')
         + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
         + ISNULL(mid.inequality_columns, N'') + N')'
         + ISNULL(N' INCLUDE (' + mid.included_columns + N')', N'') AS create_stmt_draft
FROM   sys.dm_db_missing_index_details mid
  JOIN sys.dm_db_missing_index_groups mig       ON mig.index_handle = mid.index_handle
  JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
WHERE  mid.database_id = DB_ID()
ORDER BY score DESC;

-- 2019+: oneriyi doguran sorgu metni:
SELECT TOP (25) mid.statement AS table_name, misq.group_handle, misq.user_seeks,
       SUBSTRING(st.text, misq.last_statement_start_offset / 2 + 1, 300) AS statement_text
FROM   sys.dm_db_missing_index_group_stats_query misq
  JOIN sys.dm_db_missing_index_groups mig  ON mig.index_group_handle = misq.group_handle
  JOIN sys.dm_db_missing_index_details mid ON mid.index_handle = mig.index_handle
  CROSS APPLY sys.dm_exec_sql_text(misq.last_sql_handle) st
WHERE  mid.database_id = DB_ID()
ORDER BY misq.user_seeks DESC;

Ne yapar?

Optimizer'ın derleme sırasında kaydettiği eksik index önerilerini kullanım sayısı, tahmini maliyet ve etkiye göre puanlar, taslak CREATE INDEX ifadesi üretir; 2019+ ile öneriyi doğuran sorguyu da gösterir.

Ne zaman kullanılır?

Yeni uygulama sürümü sonrası, yüksek okuma yapan sorguların index ihtiyacını hızlı tarama için, index tasarımı gözden geçirmesinde başlangıç listesi olarak.

Dikkat

Öneriler körü körüne uygulanmamalı: optimizer mevcut index'lere bakmadan önerir, sık sık neredeyse aynı (overlapping) öneriler ve gereksiz geniş INCLUDE listeleri üretir, sütun sırasını seçicilik değil eşitlik/eşitsizlik ayrımına göre verir. Her yeni index yazma maliyeti ve depolama getirir; önce mevcut index'i genişletmeyi düşün. DMV restart, DB offline ve tablo üzerindeki index değişikliğinde sıfırlanır. Sadece sorgu; risksiz.