Explorar guías rápidas · SQL Server

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

Comprobar el uso de índices

Revisa la actividad de lectura y escritura de los índices de SQL Server, identifica los índices de poco valor y decide cuáles merecen un análisis más profundo antes de eliminar ninguno.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Mostrar la actividad de lectura y escritura de cada índice normal
  5. 2. Encontrar índices con escrituras pero sin lecturas de usuario observadas
  6. 3. Encontrar índices con muchas escrituras en relación con las lecturas
  7. 4. Mostrar la última lectura y escritura observadas
  8. 5. Comprobar desde cuándo existe la ventana de observación actual de la DMV
  9. 6. Añadir el tamaño del índice a la vista de uso
  10. 7. Mostrar las columnas indexadas antes de tomar una decisión
  11. Qué significa el resultado
  12. Qué revisar
  13. Cero lecturas + muchas escrituras
  14. Muy pocas lecturas + muchísimas actualizaciones
  15. Muchos scans
  16. Muchos lookups
  17. Índice grande + poca actividad de lectura
  18. Índices similares en la misma tabla
  19. Notas de producción
  20. Restablecimiento de las estadísticas de uso
  21. user_updates cuenta operaciones, no filas modificadas
  22. Que falte la fila en la DMV no demuestra que el índice no se use nunca
  23. Los permisos necesarios varían
  24. Los índices optimizados para memoria usan otras DMV
  25. El número de usos no es el valor de negocio
  26. Errores comunes
  27. 1. Eliminar todos los índices con cero lecturas
  28. 2. Ignorar la hora de reinicio del servidor
  29. 3. Tratar los scans como algo malo
  30. 4. Usar user_updates como recuento de filas modificadas
  31. 5. Eliminar índices de clave primaria o de restricción única
  32. 6. Evaluar un índice de forma aislada
  33. 7. Usar las estadísticas de uso como monitoreo histórico
  34. Siguiente paso
  35. Para profundizar

Problema

Con el tiempo, SQL Server puede acumular índices.

Algunos se usan mucho. Algunos existen para un informe mensual concreto. Algunos dan soporte a restricciones. Otros quizá ya no ayuden a ninguna carga de trabajo importante, pero siguen sumando costo de almacenamiento, de memoria y de mantenimiento en las escrituras.

El objetivo no es encontrar índices para eliminarlos automáticamente.

El objetivo es responder:

  • ¿Qué índices se usan para seeks, scans o lookups?
  • ¿Qué índices se mantienen debido a escrituras?
  • ¿Qué índices parecen sin usar durante el periodo observado?
  • ¿Cuándo se usó un índice por última vez?
  • ¿Es costoso mantener un índice en relación con el beneficio de lectura que aporta?
  • ¿Qué índices merecen una revisión más profunda?

Respuesta rápida

Empieza por sys.dm_db_index_usage_stats, combinada con las vistas de catálogo para que los índices sin fila en la DMV también aparezcan.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,

    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,

    COALESCE(us.user_seeks, 0)
      + COALESCE(us.user_scans, 0)
      + COALESCE(us.user_lookups, 0) AS total_user_reads,

    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    total_user_reads ASC,
    user_updates DESC,
    s.name,
    t.name,
    i.index_id;

Trata el resultado como evidencia para investigar, no como un script de eliminación.

Código / comandos

1. Mostrar la actividad de lectura y escritura de cada índice normal

Esta es la consulta de inventario predeterminada.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,
    i.type_desc,

    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,

    COALESCE(us.user_seeks, 0)
      + COALESCE(us.user_scans, 0)
      + COALESCE(us.user_lookups, 0) AS total_user_reads,

    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    total_user_reads DESC,
    user_updates DESC;

El LEFT JOIN es importante.

Un índice que no se ha usado desde que se restablecieron las estadísticas de uso puede no tener fila en sys.dm_db_index_usage_stats. Un inner join ocultaría precisamente los índices que quizá quieras investigar.


2. Encontrar índices con escrituras pero sin lecturas de usuario observadas

Usa esta consulta como lista de candidatos, nunca como lista de eliminación automática.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,
    i.type_desc,

    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,

    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 1
    AND i.is_hypothetical = 0
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND COALESCE(us.user_seeks, 0) = 0
    AND COALESCE(us.user_scans, 0) = 0
    AND COALESCE(us.user_lookups, 0) = 0
    AND COALESCE(us.user_updates, 0) > 0
ORDER BY
    user_updates DESC;

¿Por qué index_id > 1?

Así te centras en los índices rowstore no agrupados y evitas tratar un índice agrupado como si fuera un índice secundario opcional.

Aun así, debes revisar la definición del índice, el historial de la carga de trabajo, las dependencias y el uso de negocio antes de plantearte eliminarlo.


3. Encontrar índices con muchas escrituras en relación con las lecturas

Esta consulta puede revelar índices con un costo de mantenimiento alto en comparación con la actividad de lectura de usuario observada.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,

    COALESCE(us.user_seeks, 0)
      + COALESCE(us.user_scans, 0)
      + COALESCE(us.user_lookups, 0) AS total_user_reads,

    COALESCE(us.user_updates, 0) AS user_updates,

    CAST(
        COALESCE(us.user_updates, 0) * 1.0
        / NULLIF(
            COALESCE(us.user_seeks, 0)
              + COALESCE(us.user_scans, 0)
              + COALESCE(us.user_lookups, 0),
            0
        )
        AS decimal(18,2)
    ) AS writes_per_read
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.is_ms_shipped = 0
    AND i.index_id > 1
    AND i.is_hypothetical = 0
ORDER BY
    writes_per_read DESC,
    user_updates DESC;

Una proporción alta no es necesariamente mala.

Un índice que usa una consulta crítica una vez por hora puede valer mucho más que un índice leído miles de veces por una carga de trabajo sin importancia.


4. Mostrar la última lectura y escritura observadas

Úsala cuando intentes determinar si un índice es estacional, periódico o potencialmente obsoleto.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,

    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    COALESCE(
        us.last_user_seek,
        us.last_user_scan,
        us.last_user_lookup,
        us.last_user_update
    ) ASC;

Una marca de tiempo de último uso NULL no demuestra que un índice nunca se haya usado.

Significa que no se ha registrado el uso correspondiente en la ventana de observación actual.


5. Comprobar desde cuándo existe la ventana de observación actual de la DMV

Los contadores de uso solo tienen sentido si sabes cuándo pudieron haberse restablecido.

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Los contadores de sys.dm_db_index_usage_stats se restablecen cuando se inicia el motor de base de datos de SQL Server.

Las filas de una base de datos también pueden desaparecer cuando la base de datos se desasocia o se cierra, por ejemplo con AUTO_CLOSE.

Si la instancia se reinició ayer, las conclusiones del tipo «índice sin usar» son débiles.


6. Añadir el tamaño del índice a la vista de uso

Un índice grande, con muchas escrituras y que rara vez se lee puede merecer más atención que un índice diminuto.

WITH index_size AS
(
    SELECT
        ps.object_id,
        ps.index_id,
        SUM(ps.used_page_count) AS used_pages
    FROM sys.dm_db_partition_stats AS ps
    GROUP BY
        ps.object_id,
        ps.index_id
)
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,

    CAST(COALESCE(sz.used_pages, 0) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,

    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 index_size AS sz
    ON sz.object_id = i.object_id
   AND sz.index_id = i.index_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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    used_mb DESC;

Usa el tamaño junto con el uso, no en su lugar.


7. Mostrar las columnas indexadas antes de tomar una decisión

Antes de eliminar o consolidar nada, revisa la definición.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.index_id,
    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.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
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
    t.is_ms_shipped = 0
    AND i.name = N'YourIndexName'
    AND t.object_id = OBJECT_ID(N'dbo.YourTable')
ORDER BY
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

Dos índices con nombres parecidos pueden dar servicio a cargas de trabajo distintas por el orden de las claves, las columnas INCLUDE, los filtros o la unicidad.

Qué significa el resultado

Campo Por qué importa
user_seeks Número de seeks de consultas de usuario registrados sobre el índice.
user_scans Número de scans de consultas de usuario registrados sobre el índice. Un scan no es necesariamente malo.
user_lookups Actividad de bookmark/key lookup que implica la estructura del índice.
user_updates Número de operaciones de escritura de usuario que requirieron mantenimiento del índice. Es un recuento de operaciones, no de filas modificadas.
last_user_seek Último seek de usuario registrado durante la ventana de observación actual de la DMV.
last_user_scan Último scan de usuario registrado.
last_user_lookup Último lookup de usuario registrado.
last_user_update Último evento de mantenimiento por escritura de usuario registrado.
total_user_reads Cálculo de conveniencia: seeks + scans + lookups.
writes_per_read Solo una proporción para priorizar. Ayuda a destacar índices con mucho mantenimiento, pero no es una regla para eliminarlos.
used_mb Espacio de almacenamiento que ocupa actualmente el índice.

Qué revisar

Cero lecturas + muchas escrituras

Es un buen candidato a revisión si la ventana de observación es lo bastante larga.

Antes de actuar, verifica que:

  • SQL Server no se haya reiniciado recientemente
  • la base de datos no se haya restaurado, desasociado ni cerrado recientemente
  • el índice no lo use una carga de trabajo mensual, trimestral o de cierre de año
  • no sea necesario para la unicidad ni para la semántica de una restricción
  • ninguna consulta importante dependa de él fuera del periodo observado

Muy pocas lecturas + muchísimas actualizaciones

Esto puede indicar sobrecarga de mantenimiento con poco beneficio de lectura observado.

Prioriza los índices grandes en tablas con muchas escrituras para una revisión más profunda.

Muchos scans

No califiques el índice como malo porque tenga muchos scans.

Un scan puede ser correcto para:

  • consultas analíticas grandes
  • predicados poco selectivos
  • tablas o índices pequeños
  • acceso ordenado
  • patrones de acceso cubiertos

Revisa los planes de ejecución que lo usan.

Muchos lookups

Los key/bookmark lookups frecuentes pueden indicar que un índice no agrupado acerca a SQL Server a las filas necesarias, pero no cubre las columnas solicitadas.

Posible siguiente paso:

  • revisar la consulta
  • examinar el costo de los lookups en el plan de ejecución
  • valorar si tiene sentido añadir columnas INCLUDE

No añadas a ciegas al índice todas las columnas de los lookups.

Índice grande + poca actividad de lectura

Esta combinación merece atención porque afecta a:

  • el almacenamiento
  • el buffer pool
  • el tamaño de las copias de seguridad
  • el mantenimiento de índices
  • el costo de las escrituras

Aun así, valida la carga de trabajo de negocio antes de eliminarlo.

Índices similares en la misma tabla

Puede haber solapamiento cuando los índices comparten:

  • las mismas claves iniciales
  • columnas INCLUDE similares
  • filtros similares

Los contadores de uso por sí solos no pueden decirte si dos índices se pueden consolidar de forma segura.

Compara las definiciones y los planes de consulta reales.

Notas de producción

Restablecimiento de las estadísticas de uso

sys.dm_db_index_usage_stats no es un historial permanente.

Sus contadores se vuelven a inicializar cuando se inicia el motor de base de datos.

Las filas también pueden desaparecer cuando una base de datos se desasocia o se cierra.

Registra siempre el inicio de la ventana de observación antes de calificar un índice como sin usar.

user_updates cuenta operaciones, no filas modificadas

Una instrucción que actualiza 1 fila incrementa el contador una vez.

Una instrucción que actualiza 1.000.000 de filas también lo incrementa una sola vez.

No interpretes user_updates como volumen de filas modificadas.

Que falte la fila en la DMV no demuestra que el índice no se use nunca

Significa que no existe ninguna fila de uso en la ventana de recopilación actual.

Puede deberse a que el índice no se usa, pero también puede significar:

  • un reinicio reciente
  • una creación reciente del índice
  • una carga de trabajo poco frecuente
  • un evento del ciclo de vida de la base de datos

Los permisos necesarios varían

En SQL Server y SQL Managed Instance, el acceso amplio a sys.dm_db_index_usage_stats requiere visibilidad del estado del servidor.

Para SQL Server 2022 y versiones posteriores, Microsoft documenta VIEW SERVER PERFORMANCE STATE.

Las versiones anteriores de SQL Server usan VIEW SERVER STATE.

Azure SQL Database tiene un comportamiento de permisos específico según la base de datos y el nivel de servicio.

Los índices optimizados para memoria usan otras DMV

sys.dm_db_index_usage_stats no es el lugar adecuado para evaluar índices optimizados para memoria.

Usa las DMV de estadísticas de índices XTP correspondientes para cargas de trabajo optimizadas para memoria.

El número de usos no es el valor de negocio

Una consulta que se ejecuta una vez al día puede ser crítica para el negocio.

No optimices únicamente en función de la frecuencia.

Errores comunes

1. Eliminar todos los índices con cero lecturas

Es la interpretación más peligrosa de la DMV.

Cero lecturas significa cero lecturas observadas en la ventana de estadísticas actual, no «este índice no tiene valor».

2. Ignorar la hora de reinicio del servidor

Un índice puede parecer sin usar simplemente porque los contadores se restablecieron hace poco.

Establece siempre la ventana de observación.

3. Tratar los scans como algo malo

Un scan puede ser el método de acceso óptimo.

Evalúa la consulta, la selectividad, el ancho del índice, el número de filas y el plan de ejecución.

4. Usar user_updates como recuento de filas modificadas

Cuenta operaciones, no filas afectadas.

No derives de él el volumen de escritura.

5. Eliminar índices de clave primaria o de restricción única

Los índices que respaldan restricciones forman parte de la semántica de integridad de los datos.

No los trates como índices de rendimiento opcionales.

6. Evaluar un índice de forma aislada

Un índice puede:

  • dar soporte a una consulta crítica
  • solaparse con otro índice
  • proporcionar ordenación
  • garantizar la unicidad
  • reducir los bloqueos al acortar las lecturas
  • aumentar el costo de las escrituras

Revisa en conjunto la carga de trabajo y los índices vecinos.

7. Usar las estadísticas de uso como monitoreo histórico

La DMV solo es acumulativa dentro de su vida útil actual.

Si necesitas evidencia duradera, guarda periódicamente instantáneas de las estadísticas de uso en tu propio almacén de monitoreo.

Siguiente paso

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

  • Cero lecturas + escrituras significativas + ventana de observación larga → revisa la definición del índice, las dependencias, Query Store y la carga de trabajo de negocio antes de plantearte eliminarlo.
  • Muchas lecturas + muchas escrituras → probablemente es valioso; ajústalo solo si el tamaño o el costo de mantenimiento son un problema.
  • Muchos lookups → revisa los planes de ejecución y evalúa una estrategia de índice cubierto.
  • Muchos scans → revisa la selectividad de la carga de trabajo y los planes; no des por hecho que el índice es malo.
  • Índice grande + pocas lecturas → compara el uso, el tamaño, el solapamiento y el costo de mantenimiento.
  • Varios índices similares → compara el orden de las claves, las columnas INCLUDE, los filtros y el uso en los planes para una posible consolidación.
  • La ventana de observación es corta → reúne más evidencia antes de cambiar nada.

← Todas las guías rápidas