Sin duda, una de las funcionalidades más interesantes que incorpora SQL Server 2005 son las DMVs (http://msdn2.microsoft.com/es-es/library/ms188754.aspx)Básicamente, exponen información “interna” de la instancia de SQL Server. La información se puede usar para identificar/analizar el comportamiento de SQL Server y actuar en consecuencia: por ejemplo, si una DMV expone que falta un índice, pues vemos que nos cuenta la DMV y analizamos la idoneidad del índice.

La DMV que nos ayuda

SQL Server expone cual es el uso que se hace de los índices de la instalación. La DMV concreta es:

select

  db_name(database_id) database_name,

  object_name (object_id, database_id) object_name,

  index_id, user_seeks, user_scans, user_lookups, user_updates

from sys.dm_db_index_usage_stats

 

Información sobre la DMV (http://msdn2.microsoft.com/es-es/library/ms188755.aspx).

Nota: fíjese que utilizo la función object_name pasando dos argumentos – el identificador del objeto, y el identificador de base de datos: esta es una funcionalidad que aparecerá en el SP2 de SQL Server 2005 y que de momento no está incluida en el producto (http://msdn2.microsoft.com/es-es/library/ms186301.aspx).

Con la consulta anterior, se obtiene el uso que han hecho las conexiones de usuario de los índices de la instancia. RECUERDA que estos contadores se ponen a cero cada vez que se reinicia la instancia de SQL Server.

 

Obtener los índices en la instancia de SQL Server

Ahora que somos capaces de identificar cual es el uso que se hace de los índices habrá que identificar cuáles son los índices no utilizados; para ello podemos utilizar el siguiente artículo para obtener una vista de todos los índices existentes en la instancia de SQL Server: Cómo rellenar una tabla temporal con todas las tablas e índices existentes en una instancia de SQL Server [Versión 2005]: http://solidqualitylearning.com/Blogs/eladio/archive/2007/01/24/3662.aspx

 

La consulta final

Ya sólo quedaría obtener de la tabla ##objetos, las filas que no están en el resultado de la DMV (vamos un NOT EXISTS, o un LEFT JOIN con nulos).

select

  o.database_id, o.object_id, o.index_id

  , o.database_name, o.object_name, o.index_name, o.type_desc

from ##objetos o

left join sys.dm_db_index_usage_stats dmv

  on o.database_id = dmv.database_id

 and o.object_id   = dmv.object_id

 and o.index_id    = dmv.index_id

where

    dmv.database_id IS NULL

 

Ya sólo quedará filtrar para la(s) base(s) de dato(s) que deseas analizar su indexación.

RECUERDA: los valores que devuelve la DMV no persisten cuando la instancia se reinicia.

 

Conclusiones 

En versiones anteriores a SQL Server 2005, no era posible conocer qué índices se utilizaban y cuáles no. Ahora, gracias a las DMVs tenemos acceso a esa información y podemos utilizarlo para mejorar el diseño físico de nuestras bases de datos.

Ahora viene una técnica que quizás suene un poco a locura, pero podría ser la siguiente:

  • Indexar todas las claves ajenas (Foreign Keys),
  • Indexar los grupos de columnas que “consideramos” probables de utilización.
  • Ejecutar pruebas de carga, volumen, funcionales, etc. y utilizar la DMV para identificar qué índices de los creados no se utilizan.
  • Borrar los índices no utilizados durante las pruebas de volumen.

Quizás ahora el problema es ser capaces de crear las pruebas necesarias y adecuadas para probar todas las funcionalidades de la aplicación, aunque eso se los dejamos a las personas encargadas de las pruebas, analistas, arquitectos… en definitiva a los que conocen el uso real que se le va a dar a la aplicación.

¿Qué nos faltaría? Consultar otra DMV para identificar las columnas que el optimizador de SQL Server considera que deberían indexarse… SII, también nos informa de esas cosas… (Será el próximo post J)

Nota: esta técnica no reemplaza a un buen análisis relacional de la aplicación (que incluye diseño físico de base de datos), pero para ser sincero y basándome en mi experiencia, en las aplicaciones de hoy día, las personas involucradas en los proyectos tienen de todo menos tiempo de sobra… así que a intentar optimizar nuestro tiempo…

Eladio Rincón