Explorar guías rápidas · SQL Server

Tecnología: SQL ServerCategoría: IndexaciónNivel: IntermedioActualizado

Encontrar índices faltantes

Usa las DMV de índices faltantes de SQL Server como señal de priorización, ordena los candidatos según su impacto estimado y valida cada recomendación antes de crear un índice.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Ordenar los candidatos de índices faltantes por beneficio estimado
  5. 2. Mostrar los detalles sin procesar de índices faltantes de una tabla
  6. 3. Comparar las recomendaciones de índices faltantes con los índices existentes
  7. 4. Generar una plantilla CREATE INDEX para revisar
  8. 5. Comprobar la actividad de escritura en la tabla de destino
  9. 6. Comprobar cuándo empezó la ventana de observación actual
  10. Qué significa el resultado
  11. Qué revisar
  12. Puntuación alta + muchos seeks
  13. Muchas recomendaciones para la misma tabla
  14. Listas INCLUDE grandes
  15. Columnas de igualdad y desigualdad mezcladas
  16. La recomendación aparece una sola vez tras un reinicio reciente
  17. Notas de producción
  18. Las DMV de índices faltantes son orientativas
  19. Las recomendaciones son transitorias
  20. Las recomendaciones pueden solaparse
  21. Las DMV de índices faltantes no lo dicen todo
  22. Los permisos varían según la versión y la plataforma
  23. Crear un índice puede provocar bloqueos o consumir muchos recursos
  24. Errores comunes
  25. 1. Convertir cada recomendación en una instrucción CREATE INDEX
  26. 2. Tomar avg_user_impact como un porcentaje de mejora garantizado
  27. 3. Ignorar los índices existentes
  28. 4. Ignorar el costo de escritura
  29. 5. Crear índices de cobertura muy anchos
  30. 6. Usar una ventana de observación corta
  31. 7. Suponer que un índice faltante es la causa raíz
  32. Siguiente paso
  33. Para profundizar

Problema

SQL Server puede registrar sugerencias de índices faltantes cuando el optimizador estima que otro índice podría reducir el costo de una consulta.

Estas DMV son útiles, pero son recomendaciones, no instrucciones de diseño.

El objetivo es responder:

  • ¿Qué sugerencias de índices faltantes tienen el mayor valor estimado?
  • ¿Qué consultas o tablas las están generando?
  • ¿Varias sugerencias piden en realidad casi el mismo índice?
  • ¿Algún índice existente ya cubre la mayor parte de la recomendación?
  • ¿Cuál es el beneficio probable en lectura frente al costo en escritura y almacenamiento?

Respuesta rápida

Empieza haciendo un join de las DMV de índices faltantes y ordena los candidatos usando la señal de beneficio estimado por el optimizador.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,

    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,

    CAST(
        migs.user_seeks
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        AS decimal(18,2)
    ) AS estimated_improvement_score,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    estimated_improvement_score DESC;

Usa esto para priorizar la revisión. No conviertas la salida directamente en instrucciones CREATE INDEX sin revisar los índices existentes, la importancia de la carga de trabajo, el orden de las claves y el costo de escritura.

Código / comandos

1. Ordenar los candidatos de índices faltantes por beneficio estimado

Esta es la mejor consulta para una primera pasada.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,

    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,

    migs.avg_total_user_cost,
    migs.avg_user_impact,

    CAST(
        (migs.user_seeks + migs.user_scans)
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        AS decimal(18,2)
    ) AS estimated_improvement_score,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    estimated_improvement_score DESC;

La puntuación es una heurística de priorización basada en el costo y el impacto estimados por el optimizador.

No es una mejora de rendimiento garantizada.


2. Mostrar los detalles sin procesar de índices faltantes de una tabla

Usa esto cuando una tabla aparezca repetidamente en el resultado ordenado.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    mid.index_handle,
    mid.database_id,
    mid.object_id,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    mid.statement
FROM sys.dm_db_missing_index_details AS mid
WHERE
    mid.database_id = DB_ID()
    AND mid.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
ORDER BY
    mid.index_handle;

Si ves varias filas similares, consolídalas mentalmente antes de diseñar nada.

Por ejemplo, estas sugerencias pueden solaparse mucho:

(A, B) INCLUDE (C)
(A, B) INCLUDE (C, D)
(A, B, E) INCLUDE (C)

Crear las tres rara vez es la respuesta correcta.


3. Comparar las recomendaciones de índices faltantes con los índices existentes

Antes de crear nada, revisa las definiciones de índices actuales.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    i.index_id,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.has_filter,
    i.filter_definition,
    ic.key_ordinal,
    ic.is_included_column,
    c.name AS column_name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE
    i.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
    AND i.is_hypothetical = 0
ORDER BY
    i.index_id,
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

Lo que buscas es:

  • las mismas columnas iniciales en la clave
  • conjuntos INCLUDE casi duplicados
  • la misma lógica de filtro
  • índices que podrían ampliarse en lugar de duplicarse
  • semántica de unicidad o de restricciones que sea relevante

4. Generar una plantilla CREATE INDEX para revisar

Esto produce un borrador, no un script de despliegue automático.

SELECT TOP (50)
    'CREATE INDEX IX_'
    + REPLACE(
        REPLACE(
            OBJECT_NAME(mid.object_id, mid.database_id),
            ']',
            ''
        ),
        ' ',
        '_'
    )
    + '_MissingIndex'
    + CHAR(13) + CHAR(10)
    + 'ON '
    + QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id))
    + '.'
    + QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id))
    + ' ('
    + COALESCE(mid.equality_columns, '')
    + CASE
        WHEN mid.equality_columns IS NOT NULL
         AND mid.inequality_columns IS NOT NULL
            THEN ', '
        ELSE ''
      END
    + COALESCE(mid.inequality_columns, '')
    + ')'
    + CASE
        WHEN mid.included_columns IS NOT NULL
            THEN ' INCLUDE (' + mid.included_columns + ')'
        ELSE ''
      END
    + ';' AS create_index_draft,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    (
        migs.user_seeks
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
    ) DESC;

Revisa manualmente el SQL generado.

Los cambios habituales antes del despliegue pueden incluir:

  • elegir un nombre de índice real
  • eliminar columnas INCLUDE redundantes
  • cambiar el orden de las claves
  • combinar varias recomendaciones similares
  • considerar un índice filtrado
  • decidir no crear el índice en absoluto

5. Comprobar la actividad de escritura en la tabla de destino

Un índice optimizado para lectura puede perjudicar a una tabla que se modifica mucho.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE
    t.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
    AND i.is_hypothetical = 0
ORDER BY
    i.index_id;

Si la tabla tiene mucha escritura, cada índice que añadas tiene un costo.


6. Comprobar cuándo empezó la ventana de observación actual

Las recomendaciones de índices faltantes son transitorias.

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Si la instancia se reinició recientemente, las DMV de índices faltantes solo representan la carga de trabajo desde ese reinicio.

Esa ventana puede ser demasiado corta para tomar una decisión de diseño.

Qué significa el resultado

Campo Por qué importa
user_seeks Número de búsquedas (seeks) de usuario que podrían haberse beneficiado de la recomendación.
user_scans Número de exploraciones (scans) de usuario asociadas a la recomendación.
avg_total_user_cost Costo promedio, estimado por el optimizador, de las consultas de usuario asociadas al grupo.
avg_user_impact Estimación del optimizador de cuánto podría reducir el índice el costo de la consulta, expresada como porcentaje.
estimated_improvement_score Heurística de priorización que combina frecuencia, costo e impacto estimado. No es una garantía.
equality_columns Columnas referenciadas por predicados de igualdad como =.
inequality_columns Columnas referenciadas por predicados de tipo rango como >, < o BETWEEN.
included_columns Columnas no clave que el optimizador sugiere cubrir en el índice.
unique_compiles Número de compilaciones/recompilaciones que contribuyeron al grupo de recomendaciones.
last_user_seek Última búsqueda (seek) observada asociada a la recomendación.

Qué revisar

Puntuación alta + muchos seeks

Es un buen candidato para una revisión más profunda.

Valida que:

  • la consulta sea importante para el negocio
  • la carga de trabajo sea representativa
  • ningún índice existente cubra ya la mayor parte del patrón
  • el nuevo índice no genere un costo de escritura excesivo

Muchas recomendaciones para la misma tabla

Esto suele indicar que la salida sin procesar de las DMV necesita consolidarse.

Busca columnas iniciales repetidas y conjuntos INCLUDE que se solapen.

Un número menor de índices bien diseñados suele ser mejor que implementar cada recomendación al pie de la letra.

Listas INCLUDE grandes

Ten cuidado.

Un índice de cobertura muy ancho puede:

  • consumir mucho almacenamiento
  • aumentar la presión sobre el buffer pool
  • aumentar el costo de escritura
  • alargar el tiempo de mantenimiento

Cubre solo lo que tenga un beneficio defendible para la carga de trabajo.

Columnas de igualdad y desigualdad mezcladas

Un punto de partida habitual es:

  • primero las columnas de igualdad
  • después las columnas de desigualdad

Pero no trates el texto que devuelve la DMV como un diseño perfecto del orden de las claves.

La selectividad de las columnas, las necesidades de ordenación, los patrones de join, los índices existentes y la forma de la carga de trabajo siguen importando.

La recomendación aparece una sola vez tras un reinicio reciente

Evidencia débil.

Recopila datos durante una ventana de observación más larga o valida con Query Store y los planes de ejecución.

Notas de producción

Las DMV de índices faltantes son orientativas

El optimizador registra lo que cree que podría ayudar en un problema de optimización concreto.

No tiene en cuenta tu cartera completa de índices, la carga de escritura, el presupuesto de almacenamiento, la estrategia de mantenimiento ni el resto de consultas sobre la tabla.

Las recomendaciones son transitorias

La información de índices faltantes puede borrarse con eventos como un reinicio de SQL Server.

No trates las DMV como un historial duradero.

Si quieres analizar tendencias, guarda instantáneas periódicas en tu propio almacén de monitoreo.

Las recomendaciones pueden solaparse

Una misma carga de trabajo subyacente puede generar varias sugerencias de índices similares.

Implementar cada una a ciegas puede provocar una proliferación de índices.

Compara siempre:

  • las columnas de clave
  • el orden de las claves
  • las columnas INCLUDE
  • los filtros
  • la unicidad
  • los índices existentes
  • la importancia de la carga de trabajo

Las DMV de índices faltantes no lo dicen todo

No sustituyen:

  • el análisis de planes de ejecución
  • Query Store
  • las estadísticas de uso de índices
  • el análisis del tamaño de las tablas
  • el análisis del volumen de escritura
  • las pruebas con carga de trabajo real

Los permisos varían según la versión y la plataforma

El acceso a las DMV de índices faltantes depende de la versión y la plataforma de SQL Server.

En las versiones más recientes de SQL Server se usan permisos de estado de rendimiento a nivel de servidor para tener visibilidad amplia de las DMV; las versiones anteriores suelen depender de VIEW SERVER STATE.

Azure SQL Database tiene sus propias reglas de ámbito y permisos.

Crear un índice puede provocar bloqueos o consumir muchos recursos

En una tabla de producción grande, crear un índice puede requerir una cantidad considerable de:

  • CPU
  • E/S
  • registro de transacciones
  • memoria
  • tempdb
  • actividad de bloqueos/metadatos

Usa las opciones online/reanudables (resumable) solo cuando tu edición/versión de SQL Server las admita y encajen en tu plan operativo.

Errores comunes

1. Convertir cada recomendación en una instrucción CREATE INDEX

Este es el error más común.

Las DMV de índices faltantes generan sugerencias específicas de una carga de trabajo, no un diseño de índices optimizado globalmente.

2. Tomar avg_user_impact como un porcentaje de mejora garantizado

Es una estimación del optimizador.

No significa que la consulta vaya a ser literalmente un 80 % más rápida.

3. Ignorar los índices existentes

Una recomendación puede parecerse mucho a un índice que ya tienes.

A veces la respuesta correcta es:

  • modificar un índice existente
  • combinar dos índices
  • ajustar las columnas INCLUDE
  • no hacer nada

4. Ignorar el costo de escritura

Cada índice no clúster debe mantenerse durante los cambios de datos que le afectan.

El beneficio en lectura debe justificar ese costo continuo.

5. Crear índices de cobertura muy anchos

Más columnas INCLUDE pueden reducir los lookups, pero los índices anchos pueden resultar caros en almacenamiento, memoria, escrituras y mantenimiento.

6. Usar una ventana de observación corta

Un reinicio puede hacer que las DMV parezcan limpias y enfocadas simplemente porque solo contienen la carga de trabajo reciente.

No rediseñes la indexación a partir de unos pocos minutos u horas de datos, a menos que el propio incidente justifique ese alcance tan acotado.

7. Suponer que un índice faltante es la causa raíz

Una consulta lenta también puede deberse a:

  • malas estimaciones de cardinalidad
  • bloqueos
  • estadísticas obsoletas o inadecuadas
  • sensibilidad a parámetros
  • presión de memoria
  • una consulta mal planteada
  • presión sobre tempdb
  • contención de recursos en todo el servidor

Trata la evidencia de índices faltantes como una señal más.

Siguiente paso

Usa el patrón de la recomendación para decidir qué investigar a continuación.

  • Candidato de alto valor + ningún índice existente similar → revisa el plan de ejecución y prueba un índice diseñado con cuidado.
  • Varias recomendaciones similares → consolídalas antes de crear nada.
  • Lista INCLUDE ancha → identifica qué columnas necesitan realmente cobertura.
  • Tabla con mucha escritura → sopesa el beneficio en lectura frente al costo de actualización.
  • Reinicio reciente de SQL Server → recopila más evidencia antes de hacer cambios estructurales.
  • La recomendación apunta a una única consulta problemática → valida con Query Store y el plan de ejecución real.
  • Hay un índice existente parecido → considera modificarlo/consolidarlo en lugar de añadir otro índice.
  • No hay candidatos de índices faltantes convincentes → continúa con un diagnóstico de rendimiento más amplio en lugar de forzar una solución basada en índices.

← Todas las guías rápidas