Leer las sugerencias de índices faltantes desde las DMV (con salvedades)

Comando

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;

Qué hace

Puntúa las sugerencias de índices faltantes que el optimizador registró en tiempo de compilación según uso, costo estimado e impacto, y genera un borrador de sentencia CREATE INDEX; en 2019+ también muestra la consulta detrás de cada sugerencia.

Cuándo usarlo

Tras una nueva versión de la aplicación, para una revisión rápida de las necesidades de índices de consultas con mucha lectura o como lista inicial para una revisión del diseño de índices.

Precaución

No aplique las sugerencias a ciegas: el optimizador ignora los índices existentes, a menudo produce sugerencias casi duplicadas (solapadas) y listas INCLUDE demasiado anchas, y ordena las columnas por igualdad/desigualdad en lugar de por selectividad. Cada índice nuevo cuesta escrituras y almacenamiento; considere primero ampliar un índice existente. Las DMV se reinician con un reinicio, con la base de datos offline y con cambios de índices en la tabla. Consulta de solo lectura; segura.