jump to navigation

msSQL – Obtener Información del Uso de Índices

Fuente: http://www.sql-server-performance.com/articles/per/Obtaining_Index_Usage_Information_p1.aspx

Autor : Greg Larsen     

 Cuando se construye una aplicación hay un número de cosas que son importantes. La primera es: Tener un buen diseño de la base de datos. La estructura de una base de datos que sea fácil de usar y de realizar consultas.

 El rendimiento es otra de la cosas. Una medición del rendimiento es qué tan rápidamente su aplicación le permite recuperar y actualizar los datos. Un componente clave de SQL Server, que permite que se devuelvan los datos rápidamente en una consulta, son los índices de las tablas. Usar índices que sean apropiados para la aplicación, le permite al motor de la base de datos minimizar la cantidad de trabajo necesario para obtener los datos.  Contrariamente, si la base de datos tiene demasiados índices, la actualización y la inserción de datos pueden empeorar, ya que SQL Server consumirá ciclos de CPU y de E/S para actualizar todos los índices que sean necesario. En este artículo se discutirá, cómo identificar cuáles índices se usan y cuáles no, además de cómo se utilizarán. Esta información puede utilizarse entonces para optimizar los índices y el diseño de la base de datos.

Como obtener las estadísticas de uso de índices
Con SQL Server 2005 Microsoft introdujo el manejo de vistas dinámicas (DMVs) y de funciones (DMFs).  Estas nuevas DMVs y DMFs permiten acceder a una  gran cantidad de información, sobre cómo el motor de SQL Server utiliza y mejora el rendimiento  de los recursos. Una DMV que provee algunas estadísticas de grandes usos de índices es sys.dm_db_index_usage_stats.

 Como lo sugiere el mismo nombre, estas vistas permiten acceder a las estadísticas de cómo se usan los índices. Las DMVs guardan las estadísticas sobre los índices en toda la base de datos. Los reportes de la DMV tienen un número de contadores para cada índice usado, con el fin de identificar cómo fue usado y las fechas que muestran cuando fue la última vez que se usó un índice. Las estadísticas de uso de índices se pueden usar para determinar, si un índice esta mejorando o empeorando el rendimiento de una aplicación.

 Para poder usar  esta DMV son necesarios permisos de VIEW SERVER STATE. Si un usuario no está habilitado para ejecutar las consultas, debido a problemas de permisos se deberá consultar desde DBA local. En el caso de que un usuario, no tenga permisos entonces el administrador deberá ejecutar las consultas en lugar del usuario.

Como recoge las estadísticas SQL Server para esta DMV?
Cada vez que se ejecuta una sentencia de  T-SQL. SQL Server rastrea el tiempo y cómo se usa un índice. Por cada sentencia ejecutada, SQL Server usa la información del plan de la consulta, incrementa las columnas de los contadores correctos en la DVM sys.dm_db_index_usage_stats

 La información del contador del índice es un contador acumulado basado en: cómo se usó cada índice desde la última vez  que SQL Server fue iniciado, que el índice fue creado, que la base de datos  fue anexada, o que la base de datos se levantó (Si la opción AUTOCLOSE está configurada).  El contenido de los contadores no es el número de entradas/salidas o lecturas de página, sino el número de veces que el índice fue usado. Cuando miramos las estadísticas debemos tener en cuenta el uso de los índices mediante las DMV sys.dm_db_index_usage_stats.  Cuantas más estadísticas SQL Server haya  recolectado, más precisas serán éstas de cómo las aplicaciones aprovechan las ventajas de los índices.

 Identificar los índices que no se usan
Tener índices en una base de datos que no se usan en una aplicación es como tener una casa con ocho habitaciones para solo usar tres de estas. Tener ocho habitaciones puede ser algo lindo, si usted puede disfrutarlas, pero también se estarían pagando y manteniendo cinco habitaciones que no se necesitan. Este costo de mantenimiento adicional es dinero que efectivamente se tirará cada año. Tener índices extra en las tablas y que no se usan, requieren que SQL Server deba mantenerlos, cada vez que ocurra una actualización y consumen espacio adicional si nunca se usan. Por lo tanto, como las habitaciones extra, estos índices extra tendrán el costo de tiempos de respuesta más bajos para cada actualización y tiempos de backups más extensos para almacenar índices que no se usan. ¿Entonces cómo identifico si estos índices no se usan?

 Debajo hay una sentencia T-SQL que usa estos DMV para identificar todos los índices que tenemos, que no se usan para la base de datos AdventureWorks:

 USE AdventureWorks;

go

SELECT o.name Object_Name,

       i.name Index_name,

       i.Type_Desc

 FROM sys.objects AS o

     JOIN sys.indexes AS i

 ON o.object_id = i.object_id

  LEFT OUTER JOIN

  sys.dm_db_index_usage_stats AS s  

 ON i.object_id = s.object_id 

  AND i.index_id = s.index_id

 WHERE  o.type = ‘u’

 — Clustered and Non-Clustered indexes

  AND i.type IN (1, 2)

  — Indexes without stats

  AND (s.index_id IS NULL) OR

  — Indexes that have been updated by not used

      (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );

 Esta sentencia de T-SQL  usa las columnas “user seeks”“user_scans” y/o “user_looksups”  en la sys.dm_index_usage_stats para identificar cuando un índice se utiliza o no. Esta información se une con las tablas de the sys.objects, y sys.indexes  en la base de datos AdventureWorks para identificar la información usada por un índice  para la base de datos  AdventureWorks solamente. Si los contadores mencionados anteriormente tienen cero para un índice en la base de datos, entonces esto significa que no ha sido usado. En caso de que el valor no sea cero entonces es porque ha sido usado.

Capturar los índices que se usan
Alternativamente, es posible capturar los índices que se usan diariamente y almacenarlos en un archivo. Después de algunos meses la información recolectada puede ser analizada para determinar, cuáles índices no han sido usados. Debajo hay otra porción de código  que muestra estos índices que han sido usados:

 USE AdventureWorks;

go

SELECT o.name Object_Name,

       SCHEMA_NAME(o.schema_id) Schema_name,

       i.name Index_name,

       i.Type_Desc,

       s.user_seeks,

       s.user_scans,

       s.user_lookups,

       s.user_updates

 FROM sys.objects AS o

     JOIN sys.indexes AS i

 ON o.object_id = i.object_id

     JOIN

  sys.dm_db_index_usage_stats AS s  

 ON i.object_id = s.object_id 

  AND i.index_id = s.index_id

 WHERE  o.type = ‘u’

 — Clustered and Non-Clustered indexes

  AND i.type IN (1, 2)

 — Indexes that have been updated by not used

  AND(s.user_seeks > 0 or s.user_scans > 0 or s.user_lookups > 0 );

Este es un ejemplo de salida cuando se ejecuta en una instancia de Sql Server:

sql01

 Al examinar esta salida es posible ver cómo ha sido usado cada índice, al mirar la columna “user_…”.  .  La columna “user_seeks”  identifica el número de veces que ha sido usada la operación de búsqueda de un  índice  que recorrer este mismo, para resolver una sentencia de T-SQL.

La columna “user_scan” identifica el número de veces que se realizó la operación de escaneo de un índice. La columna “user_lookups” identifica cuantas veces este índice ha sido actualizado, como consecuencia de la actualización de la tabla en la cual está asociado el índice al ejecutar una sentencia UPDATE.

Otros factores a considerar 
La salida de estas vistas ayuda a determinar cuan valiosos han sido los índices para resolver la consulta. Al usar esta información  podemos tomar la decisión de modificar y/o eliminar algunos índices. Debemos recordar que la información en la sys.dm_db_index_usage_stats sólo contiene estadísticas que han sido tomadas desde que SQL Server fue iniciado, la base de datos fue abierta o se haya creado un índice. Si el sistema no ha sido levantado en un período amplio estas estadísticas potencialmente pueden no ser muy útiles para representar una imagen verdadera de la utilización de los índices. Asegúrese de que las muestras estadísticas sean representativas de las consultas ejecutadas para determinar que actividades de mantenimiento se requieren en base a las salidas de las DMV.

Optimización de los índices
Es importante para un DBA asegurarse que los índices de la base de datos sean útiles.  Al usar la salida de una DMV sys.dm_db_index_usage_stats,  tenemos una  buena imagen de cómo desarrollar los índices que se usarán. Esta información puede utilizarse para mejorar la puesta a punto de los índices y para maximizar el rendimiento de la base de datos, al eliminar índices que producen más overhead que ganancia de rendimiento.


 Conclusión:

Abril 2009

Cabrera Paola (1329-0454)
Matias Omar Gonzalez (3301-2528 )
Puntano Maricel (1328-0030)
Rios Fernando (1330 0333)
Emmanuel Tramaglia(38012517) 
Gricar Francisco (3401-2219)

En este artículo se muestra la importancia que tiene los índices en una aplicación. Como el rendimiento aumenta al tener índices que se usan frecuentemente. Y el efecto contrario que produce en el rendimiento, crear índices que no se usan. SQL Server permite trabajar con las estadísticas de uso de los índices. Y esta es una herramienta muy útil para el administrador ya que le permite tomar decisiones a la hora de crear o borrar índices.

A %d blogueros les gusta esto: