DMV에서 누락 인덱스 제안 읽기 (주의 사항 포함)

명령어

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+: the query text behind each suggestion:
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;

하는 일

옵티마이저가 컴파일 시 기록한 누락 인덱스 제안을 사용량, 예상 비용, 효과로 점수화하고 CREATE INDEX 초안 문장을 생성한다. 2019+에서는 각 제안 뒤의 쿼리도 보여 준다.

사용 시점

새 애플리케이션 릴리스 후, 읽기 중심 쿼리의 인덱스 필요성을 빠르게 훑어볼 때, 또는 인덱스 설계 검토의 시작 목록으로.

주의

제안을 맹목적으로 적용하지 않는다: 옵티마이저는 기존 인덱스를 무시하고, 거의 중복되는(겹치는) 제안과 지나치게 넓은 INCLUDE 목록을 자주 만들며, 컬럼을 선택도가 아니라 등호/부등호 기준으로 정렬한다. 새 인덱스마다 쓰기와 저장 비용이 들므로 먼저 기존 인덱스 확장을 고려한다. DMV는 재시작, 데이터베이스 오프라인, 테이블의 인덱스 변경 시 초기화된다. 읽기 전용 쿼리이며 안전함.