miércoles, 28 de agosto de 2013

Analizar Utilización de Indices - Depurar Indices no Utilizados

Amigos, 

    En más e una oportunidad hemos caído en la cuenta de que nuestros índices representan un valor elevado del espacio en mb que está utilizando una tabla. Este es un signo de que "algo está mal" y que, por consiguiente, es necesario revalidar  los índices creados historicamente sobre la tabla para poder establecer su utilización verdadera y de ese modo llevar adelante una eventual depuración.

  Recordemos como concepto general que los índices bien utlizados aceleran los tiempos de respuesta de una consulta  reduciendo los tiempos de  bloqueo sobre las tablas en las cuales se establecen la peticiónes. Por contrapartida una tabla excesivamente indexada genera una notable pérdida de performance en los procesos de Insert / Update / Delete ya que el motor de nuestra base de datos, dependiendo de la magnitud de estas operaciones, se verá forzado a rehacer los idx para mantenerlos actualizados, generando  una sobrecarga innecesaria en nuestro servidor y tiempos de actualización de data mucho más perezosos.

  Una vez más la gente de Microsoft pensó en nosotros, los sacrificados DBA, y puso a nuestra diposición una vista dinámica que, joineada con otras vistas del sistema, nos proporciona la data necesaria como para realizar esta tarea de análisis.

  Estoy hablando de la vista sys.dm_db_index_usage_stats..
   
  Esta vista acumula, desde el último reinicio del motor de nuestra base de datos, un contador para cada uno de los índices, y para los siguientes eventos, (relacionados diréctamente con la utilización de los indices como producto de la ejecucion de las queries por nosotros definidas ) a saber:

       user_seeks --  cantidad de veces que se utilizó el índice en búsquedas directas
     user_scans -- cantidad de veces que  scanearon en base al idx
       user_lookups -- cantidad de lookups 

   Estos tres eventos nos hablan de la utilización efectiva que han tenidos nuestros índices. 

   Pero para que nuestro estudio sea realmente efectivo, Microsoft nos propociona un cuarto campo vital para determinar el "costo/beneficio" de tener un idx activo en nuestra base de datos hablo del contador:

     user_updates -- este contador nos habla de la cantidad de veces que el sql server tuvo que hacer una tarea de mantenimiento sobre el índice (que puede ser la recontrucción total o parcial del mismo), como consecuencia de operaciones de Insert - Update - Delete.


    Ahora bien, tenemos estos datos valiosísimos... Cómo establecer si un índice justifica su existencia?

     A - Si el idx tiene valores elevados en cualquier de los 3 primeros campos detallados no debería ser deprecado

     B -  Si el idx no presenta valores significativos de utilización y a la vez  requiere de frecuentes tareas de mantenimiento por parte del sistema (contador user_updates con cifras elevadas), debería de eliminado.
  
   C - Si el idx presenta valores intermedios de utilización y mantenimiento, pues nada mejor que apelar al conocimiento que uno tiene sobre el sistema y la base de datos para de ese modo determinar la acción a seguir. Es decir, si vale la pena o no mentener el idx.


 Ahora les dejo la query que armé en base a la vista, linkeando la misma con otras vistas del sistema, para que puedan ya mismo empezar a revalidar sus indices.

  
------------------------------------------------------------------------------------------
-- Detalle:  Estudiar la utilización de los idx de una base de datos           
-- Vista base: sys.dm_db_index_usage_stats                                        - Autor: Gustavo Herrera para "SQL Server Para Todos"                   --------------------------------------------------------------------------------------------

Use NombreBaseDatos
SELECT 
DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,
si.name AS IndexName,
sc.Name AS ColumnName,
sis.user_seeks,  
sis.user_scans,  
sis.user_lookups, 
sis.user_updates 
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si  on sis.object_id = si.object_id and sis.index_id = si.Index_id
INNER JOIN sys.index_columns sic on sis.object_id = sic.object_id and sic.Index_id = si.Index_id
INNER JOIN sys.columns sc on sis.object_id = sc.object_id and sic.Column_id = sc.Column_id
INNER JOIN  sys.objects o on si.object_id = o.object_id
WHERE sis.database_id = DB_ID('NombreBaseDatos') and o.type = 'U' ;
go

       Quedo a disposición de uds, hasta la próxima !

6 comentarios:

  1. Muy buena información, me fue de mucha utilidad.

    Mucha gracias.

    ResponderEliminar
  2. Muchas gracias por la información le hice unas variaciones para convertirlo en dinámico y ejecutarlo en todas las bases de datos de mi instancia

    ResponderEliminar
  3. Excelente articulo...

    ResponderEliminar
  4. Excelente articulo, me fue de mucha utilidad, creo que mejor explicado no puede estar, solo como comentario seria genial tener como una tabla de criterios para poder decidir si se procede a la eliminación.

    ResponderEliminar