Explorar guías rápidas · SQL Server
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
- Problema
- Respuesta rápida
- Código / comandos
- 1. Ordenar los candidatos de índices faltantes por beneficio estimado
- 2. Mostrar los detalles sin procesar de índices faltantes de una tabla
- 3. Comparar las recomendaciones de índices faltantes con los índices existentes
- 4. Generar una plantilla CREATE INDEX para revisar
- 5. Comprobar la actividad de escritura en la tabla de destino
- 6. Comprobar cuándo empezó la ventana de observación actual
- Qué significa el resultado
- Qué revisar
- Puntuación alta + muchos seeks
- Muchas recomendaciones para la misma tabla
- Listas INCLUDE grandes
- Columnas de igualdad y desigualdad mezcladas
- La recomendación aparece una sola vez tras un reinicio reciente
- Notas de producción
- Las DMV de índices faltantes son orientativas
- Las recomendaciones son transitorias
- Las recomendaciones pueden solaparse
- Las DMV de índices faltantes no lo dicen todo
- Los permisos varían según la versión y la plataforma
- Crear un índice puede provocar bloqueos o consumir muchos recursos
- Errores comunes
- 1. Convertir cada recomendación en una instrucción CREATE INDEX
- 2. Tomar avg_user_impact como un porcentaje de mejora garantizado
- 3. Ignorar los índices existentes
- 4. Ignorar el costo de escritura
- 5. Crear índices de cobertura muy anchos
- 6. Usar una ventana de observación corta
- 7. Suponer que un índice faltante es la causa raíz
- Siguiente paso
- 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.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.