Índices en SQL Server: tenerlos no significa que mejoren el rendimiento

Os dejo una consulta para ver los índices de una base de datos o filtrarlos por tablas.

USE Pruebas;
GO

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id)        AS table_name,
    i.name                          AS index_name,
    i.index_id,
    i.type_desc                     AS index_type,        -- HEAP / CLUSTERED / NONCLUSTERED
    i.is_primary_key,
    i.is_unique,
    i.is_disabled,
    -- Reads: how the index is actually used by queries
    ISNULL(us.user_seeks,   0)      AS user_seeks,
    ISNULL(us.user_scans,   0)      AS user_scans,
    ISNULL(us.user_lookups, 0)      AS user_lookups,
    -- Writes: maintenance cost the index imposes on INSERT/UPDATE/DELETE
    ISNULL(us.user_updates, 0)      AS user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
       ON  us.object_id   = i.object_id
       AND us.index_id    = i.index_id
       AND us.database_id = DB_ID()   -- usage stats are per-database
/*WHERE i.object_id IN
      (
          OBJECT_ID(N'dbo.Tabla1),
          OBJECT_ID(N'dbo.Tabla2)
      ) -- And i.is_disabled = 1*/
ORDER BY
    table_name,
    user_seeks + user_scans + user_lookups ASC;   -- least used first within each table
GO

Como podeis ver esto

/*WHERE i.object_id IN
      (
          OBJECT_ID(N'dbo.Tabla1),
          OBJECT_ID(N'dbo.Tabla2)
      ) -- And i.is_disabled = 1*/

Esta comentado, si lo descomentáis se puede filtrar por tablas y por índices deshabilitados

Esta consulta muestra un gráfico como el siguiente que te da mucha información sobre si un índice funciona, no funciona o solo hace daño.



Si pulsáis en la imagen se ve en grande y aquí hay que fijarse en las cuatro columnas siguientes:

• user_seeks: indica cuántas operaciones han accedido al índice buscando una clave concreta o un rango. Lo normal es que sea eficiente, pero un seek no garantiza que el índice sea bueno ni que la consulta sea rápida: podría leer muchas filas o provocar miles de Key Lookup.
• user_scans: indica cuántas operaciones han recorrido total o parcialmente un índice o una tabla. No siempre es malo: si la consulta necesita un porcentaje elevado de los registros, un scan puede ser más eficiente. Pero es malo cuando recorren millones de filas para devolver un registro o muy pocas.
• user_lookups: indica accesos adicionales para recuperar la fila completa o columnas que faltan, el proceso es:
  
  • SQL Server realizar un Index Seek sobre un índice nonclustered.
  • El índice nonclustered no contiene todas las columnas requeridas por la consulta.
  • SQL Server accede al índice clustered mediante un Key Lookup, o al heap mediante un RID Lookup.
  • Este acceso puede repetirse por cada fila obtenida del índice nonclustered.
  • El contador user_lookups aparece normalmente en el índice clustered o en el heap, no en el índice nonclustered que inició la búsqueda.
  • Puede evitarse incluyendo las columnas necesarias con INCLUDE, pero solo después de comprobar que el coste de los lookups justifica ampliar el índice.
• user_updates: indica las operaciones INSERT, UPDATE o DELETE que han requerido mantener el índice. No representa necesariamente el número de filas modificadas. Si un índice tiene muchos updates y no tiene busquedas es un mal indice que solo perjudica.
• Si todos los campos de fecha de uso están en NULL: significa que no se ha registrado el uso del índice durante el periodo cubierto por las estadísticas. Este periodo es desde el último reinicio.

Los datos de sys.dm_db_index_usage_stats se reinician, entre otros casos, cuando se reinicia el servicio de SQL Server. Por lo que para tomar una muestra de estos índices que sea válida al menos requiere un par de semanas de utilización sin reinicios.

Un índice con muchos user_updates y sin seeks, scans ni lookups es un candidato para revisión o deshabilitarlos.

Antes de borrar los índices yo personalmente los deshabilito para probar que realmente es correcto.

ALTER INDEX [IND_UPDATE_DB_PDM_HOUR2] ON [dbo].[TABLA1] DISABLE;

Al cabo de un tiempo si todo va bien los borro.

Los índices son un mundo que hacen que tus consultas vuelen o vayan a pedales.

No se soluciona poniendo más índices sino analizando la base de datos, las consultas, los índices existentes durante un periodo de tiempo. Un exceso de índices también puede ser perjudicial.

Recordar que las prisas son malas consejeras y en este caso también.


... y esto es todo amig@s!!! hasta la próxima ...
Saludos
Alex
:-)
/