Que tanto se utilizan los indices en mi base de datos

Imagen de kenneth

Votar: 

Average: 4.9 (25 votes)

Una de las más malas costumbres que tienen los desarrolladores de bases de datos e inclusive DBA's, es creer que entre más índices tenga mi base de datos, más rápidas mis consultas van a ser.

O aún peor, ejecutan una consulta, activan el plan de ejecución, y sin analizar mucho, cuando este les sugiere un índice lo crean sin mucho entender el efecto en la base de datos como tal.

Es importante entender que la sugerencia del plan de ejecución está basada únicamente sobre la consulta que se está ejecutando, no sobre el tráfico y/o estrategias de consolidación de índices.

¿Cuáles son los efectos negativos que puede traer un índice?

  • Las instrucciones de escritura sobre la tabla que involucren las columnas por la cual está definido el índice, van a tener un tiempo de ejecución más lento, en incrementar la duración de los bloqueos sobre los objetos.

  • Dependiendo de la definición del índice, y la cantidad de instrucciones de escritura sobre el mismo, la fragmentación se puede volver un factor de desaprovechamiento de espacio y ampliar las necesidades de administración del mismo.

Acordémonos que no porque SQL Server me permita crear una cantidad muy alta de índices sobre mis tablas, significa que tengo que por fuerza crear más índices en mi base de datos. Es importante el entender que como Administradores, Arquitectos y desarrolladores necesitamos tener una estrategia de consolidación de índices la cual me permita que todas mis consultas utilicen un índice basado en el patrón de acceso a datos esperado (seek/scan) y no que tenga un índice para cada consulta.

Esta planeación de la indexación, muchas veces nos deja como resultado una tabla con 8 columnas y 12 índices. y aquí es donde viene la pregunta importante:

¿Qué tanto se estan utilizando esos índices?

Los índices guardan estadísticas de ejecución de cuantas veces se ha realizado seek, scan, keylookup o updates sobre él, desde la última vez que se reinicio el servidor o la creación del indice (usa la fecha mayor para el cálculo).

Esta información se guarda particularmente en el siguiente DMV, SYS.DM_DB_INDEX_USAGE_STATS  .

Yo en lo personal me preocupo por los seeks y scans que genera un índice, o sea que tanto se usa, y la siguiente consulta me ha resultado muy útil para ver que índices puedo pensar en consolidar y/o eliminar.

  1. USE Basededatos
  2.  
  3. SELECT  t.name AS TablaNombre ,
  4.         t.type_desc AS TablaTipo ,
  5.         i.name AS IndiceNombre ,
  6.         i.type_desc AS IndiceTipo ,
  7.         CASE WHEN i.fill_factor = 0 THEN 100
  8.              ELSE i.fill_factor
  9.         END AS IndiceFillFactor ,
  10.         ( SELECT    MAX(v)
  11.           FROM      ( VALUES ( st.ServerStartTime), ( o.create_date),
  12.                     ( o.modify_date) ) AS VALUE ( v )
  13.           ) AS [EstadisticasDesde] ,
  14.         S.USER_SEEKS ,
  15.         S.USER_SCANS ,
  16.         S.USER_LOOKUPS ,
  17.         S.USER_UPDATES
  18. FROM    sys.tables t
  19.         INNER JOIN sys.indexes I ON I.OBJECT_ID = t.OBJECT_ID
  20.         LEFT OUTER JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S ON I.[OBJECT_ID] = S.[OBJECT_ID]
  21.                                                             AND I.INDEX_ID = S.INDEX_ID
  22.         LEFT OUTER JOIN sys.objects o ON i.name = o.name
  23.         CROSS JOIN ( SELECT login_time AS ServerStartTime
  24.                      FROM   sys.sysprocesses
  25.                      WHERE  spid = 1
  26.                    ) ST
  27.                 WHERE t.name IN ('tabla1', 'tabla2')
  28.                

el siguiente es el resultado que me brinda esta consulta:

TablaNombre TablaTipo IndiceNombre IndiceTipo IndiceFillFactor EstadisticasDesde USER_SEEKS USER_SCANS USER_LOOKUPS USER_UPDATES
Tabla1 USER_TABLE IX_Tabla1_Indice2 NONCLUSTERED 70 8/31/2014 23:29 1511579 59 0 315380
Tabla1 USER_TABLE IX_Tabla1_Indice3 NONCLUSTERED 70 8/31/2014 23:29 187531 31172 0 1685315
Tabla1 USER_TABLE IX_Tabla1_Indice4 NONCLUSTERED 100 8/31/2014 23:29 139868 94121 0 1684976
Tabla2 USER_TABLE PK__Tabla2 CLUSTERED 70 8/31/2014 23:29 7807760 49 8449309 5135525
Tabla2 USER_TABLE IX_ Tabla2_Indice2 NONCLUSTERED 70 8/31/2014 23:29 2168068 0 0 1072220
Tabla2 USER_TABLE IX_ Tabla2_Indice3 NONCLUSTERED 100 8/31/2014 23:29 9467963 0 0 1101808
Tabla2 USER_TABLE IX_ Tabla2_Indice4 NONCLUSTERED 100 8/31/2014 23:29 0 0 0 2194661
Tabla2 USER_TABLE IX_ Tabla2_Indice5 NONCLUSTERED 100 8/31/2014 23:29 32391 0 0 3941803
Tabla2 USER_TABLE IX_ Tabla2_Indice6 NONCLUSTERED 100 8/31/2014 23:29 134644 0 0 2875295
Tabla2 USER_TABLE IX_ Tabla2_Indice7 NONCLUSTERED 100 8/31/2014 23:29 2 2 0 1101808
Tabla1 USER_TABLE PK__Tabla1 CLUSTERED 70 8/31/2014 23:29 14472189 1135 1407188 3928325

Voy a analizar los campos más importantes del resultado de la consulta, en el caso de que requiera expandir el concepto de que es un seek contra un scan o lookup, le sugiero ver este video :

USER_SEEKS: Representa la cantidad de veces que el índice se ha utilizado mediante el patrón de acceso a datos seek, a partir de la fecha definida en el campo EstadisticasDesde.

USER_SCANS: Representa la cantidad de veces que el índice se ha utilizado mediante el patrón de acceso a datos scan, a partir de la fecha definida en el campo EstadisticasDesde.

USER_LOOKUPS: Representa la cantidad de veces que el índice se ha utilizado mediante el patrón de acceso a datos lookup, a partir de la fecha definida en el campo EstadisticasDesde.

USER_UPDATES: Representa las escrituras que el índice ha recibido, esto es básicamente la carga administrativa del índice a partir de operaciones de usuario; desde la fecha definida en el campo EstadisticasDesde.

¿Cómo analizo los resultados para entender la utilización de los índices?

Inicialmente en un índice noncluster se busca que la mayoría de la carga esté dada en seeks, y si estoy consolidando cierto nivel de índices, sería aceptable ver scans.

En la mayoría de los cases los índices cluster y heap generan lookups, y en el caso de los cluster lo ideal es ver seeks a menos que estemos ejecutando consulta fuertes de extracción de datos, lo cual va a generar scans.

Ahora bien, parece nebuloso, pero la verdad es bastante sencillo, veamos los índices IX_ Tabla2_Indice4 y IX_ Tabla2_Indice7 de tabla2, básicamente no se han utilizado para seeks, ni scans, ni lookups, lo que han generado es una carga administrativa alta desde el  de agosto. Esto son características importantes para hacer los índices candidatos fuertes para la eliminación de los mismos. El siguiente paso antes de eliminarlo es ver si hay consulta que lo referencia y realizar pruebas.

Muchas gracias por la atención prestada, y espero que el articulo haya sido de su agrado.