Puesta a punto de los índices en SQL Server

Para tener una base de datos óptima en términos de rendimiento ya sabemos que es imprescindible definir los índices correctamente en todas las tablas. Dichos índices los crearemos con aquellos campos sobre los que vamos a hacer un mayor número de consultas. Los índices pueden ser de uno o varios campos: lo importante es crear los necesarios ya que toda ventaja tiene sus inconvenientes, y uno de ellos es el espacio que ocupan dichos índices en la base de datos, y que además hay que hacerles un mantenimiento. Veamos cómo hacerles "una puesta a punto". Hay disponibles tres funciones y vistas clave, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats y sys.dm_db_index_operational_stats para ayudarnos a comprobar si los índices funcionan tal y como esperamos.

(1) sys.dm_db_index_physical_stats (estudio de la fragmentación).

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID('database_id'')

,OBJECT_ID('nombre_tabla')

,NULL  -- NULL para ver todos los índices (en otro caso introducir el número del índice)

,NULL -- NULL para ver todas las particiones de un índice

,'DETAILED') -- Extraemos toda la información
El examen de los valores de las columnas avg_page_space_used_in_percent y avg_fragmentation_in_percent para la fragmentación de índices que quedan fuera del umbral lógico y de densidad aceptado nos ayudará a determinar qué operación hacer con el índice.

(2) sys.dm_db_index_usage_stats (uso de los índices).

La prioridad, como administradores de la base de datos, es ver qué índices son útiles realmente. Para ver todos los índices de una instancia que no se han utilizado desde la última vez que se reinició el servicio de SQL Server, usaríamos:

SELECT DB_NAME(database_id),OBJECT_NAME([object_id] FROM sys.dm_db_index_usage_stats

WHERE user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 AND system_seeks = 0

AND system_scans = 0 AND system_lookups = 0
Pero ¡ojo!, quizás índices que no salgan en los resultados de esta consulta sí sean muy necesarios, por ejemplo en consultas específicas que se ejecutan periódicamente de forma quincenal, mensual, etc.

(3) sys.dm_db_index_usage_stats (actividad funcional de los índices).

Con esta DMF podremos estudiar la E/S y bloqueos en los índices. Una consulta interesante sería:
SELECT page_latch_wait_count --contador de cerrojos de página
,page_latch_wait_in_ms --tiempos de espera de cerrojos de página
,row_lock_wait_in_ms --tiempos de espera de bloqueos de fila
,page_lock_wait_in_ms --tiempos de espera de bloqueos de página
,row_lock_count --contador bloqueos de fila
,page_lock_count --contador bloqueos de página
,page_io_latch_wait_count --contador de espera E/S
,page_io_latch_wait_in_ms --tiempo de espera E/S
FROM sys.dm_db_index_operational_stats (DB_ID('database_id')
,OBJECT_ID('yourtable')
,NULL -- NULL para ver todos los índices, en otro caso introduce número de índice
,NULL -- NULL para ver todas las particiones de un índice
)

En base a los valores obtenidos podremos detectar índices problemáticos que pueden llegar a causar cuellos de botella imprevistos.