Mejores prácticas para el diseño de índices

Continuamente llegan a nosotros casos de rendimiento en las que de una manera u otra están relacionadas con la gestión de los índices.

En muchos casos TSQL, Jobs, que tardan mucho o TSQL que antes no tardaban nada con pocos registros cada vez la base de datos invierte más tiempo en realizarlos.

En todos ellos, se observa un patrón común con un alto uso de CPU o los contadores de rendimiento de disco inusualmente elevados o bien muchos operativas de table scan.

En este post trataremos de identificar los aspectos relacionados con los índices en el que lo dividiremos en tres partes:

· Fragmentación de los índices, es decir, cada vez que se inserta/actualiza o se borran registros el impacto de cómo está alineado las páginas de datos de índices con los datos.

· Índices que SQL SERVER identifica que podrían ser necesarios crear y que creándolos provocarían un aumento considerable en la mejora del rendimiento del servidor.

· Cómo, cuándo y en qué porcentaje la base de datos están usando estos índices.

Para ello nos apoyaremos en unas vistas dinámicas muy interesantes que nos van a permitir conocer estos datos que tenemos habilitadas en SQL SERVER 2005 y 2008.

Fragmentación de los índices:

Para conocer qué es la fragmentación para ello visitemos la URL: https://technet.microsoft.com/es-es/library/ms189858.aspx

Para conocer cómo se detecta la fragmentación disponemos de dos opciones uno DBCC SHOWCONTIG que aportaba esta información en versiones anteriores o bien la nueva DMV que nos informa con muchos más detalles, el nivel de fragmentación de los índices. Para ello visitaremos la URL: https://technet.microsoft.com/es-es/library/ms188917.aspx

Dentro de la DMV ejecutaremos la siguiente query para observar la fragmentación de los índices:

select OBJECT_NAME([object_id]) as NombreTabla, *

from master.sys.dm_db_index_physical_stats(DB_ID('bbddusuario'),NULL,NULL,NULL,'DETAILED')

order by avg_fragmentation_in_percent desc

En este ejemplo, crearemos una tabla con 3 campos:

· IdElemento que será el campo Identidad y Primary key por defecto.

· Campo1 y Campo2 ambos de nvarchar(10)

Una vez creada la tabla creamos un nuevo índice seleccionado el campo Campo1 dejando la definición del índice de relleno ( fillfactor) por defecto.

Haremos inserciones y borrados masivos durante un tiempo y volveremos a ejecutar la query, obteniendo el siguiente resultado:

En la que podemos identificar la columna avg_fragmentation_in_percent que nos indica por cada tipo de índice el porcentaje de fragmentación. En algunos casos observamos que el porcentaje es muy alto.

Por ello, basta con realizar alguna actividad de reindexación ( reconstrucción ( rebuild ) o reorganización ( reorganize ) ) de los datos para obtener el siguiente resultado y mejorar sensiblemente el resultado de las operaciones de búsqueda. URL: https://msdn.microsoft.com/es-es/library/ms188917.aspx

Otro de las cosas que podemos observar en esta estadística son:

o El nivel de ocupación de cada una de las páginas ( columna avg_page_space_used_in_percent ) con lo cual podemos definir un fill factor o índice de relleno acorde y bajar la fragmentación. URL: https://msdn.microsoft.com/es-es/library/ms191005(v=sql.90).aspx

o La cantidad de registros usados en cada índice ( columna record_count ). En caso de que este índice tenga una cantidad de registros muy cercano a la cantidad de registros de la tabla ( select count(*) from , este índice podría no ser necesario por su nivel de cardianalidad ( número de registros diferentes único que forman el índice ). El motor de optimización de la base de datos podría no utilizar este índice ya que podría considerar que es mejor leer la tabla que usar el índice.

Índices que SQL SERVER identifica que mejorarían el rendimiento ( MISSING INDEXES )

Por otro lado, tenemos los índices que provocarían un aumento considerable en la mejora del rendimiento del servidor.

Para conocer los índices que sería necesarios crear, si ejecutamos la siguiente query podremos encontrar los índices que podría ser necesarios crear para mejorar el rendimiento. En la columna avg_user_impact se podría ver el % de performance. Debemos recordar que crear índices mejora el rendimiento pero obliga a que las escrituras puedan llevar más tiempo al actualizar los datos y que la información que obtenemos se inicializa después de cada reinicio de la instancia de SQL SERVER. URL: https://technet.microsoft.com/es-es/library/ms345434.aspx

SELECT

statement AS [database.scheme.table],

column_id , column_name, column_usage,

migs.user_seeks, migs.user_scans,

migs.last_user_seek, migs.avg_total_user_cost,

migs.avg_user_impact

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON mig.index_handle = mid.index_handle

INNER JOIN sys.dm_db_missing_index_group_stats AS migs

ON mig.index_group_handle=migs.group_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id

Siguiendo con nuestro ejemplo, vamos a realizar un par de consultas en nuestra tabla. Como recordamos nuestra tabla ( Table_1 ) contiene 3 campos, IdElemento como índice primario, Campo1 que está indexado y Campo2 que no está indexado.

Vamos a realizar un par de consultas sobre el campo2 al cercar de los 7 millones de registros que tenemos en la tabla table_1.

Select * from table_1 where campo2 = ‘juan’

Select * from table_1 where campo2 like ‘juan%’

Seguidamente, ejecutaremos la query de obtener los índices que SQL SERVER aconseja crear y obtenemos los siguientes datos.

Como podemos observar las primeras tres filas corresponde a la mejora del filtrado inicial de campo2 = ‘juan’ en las que SQL SERVER nos indica que podríamos mejorar nuestro rendimiento en un 99.91% si creamos un índice en el que Campo2 esté definido en el mismo e incluyamos como campos include del mismo a IdElemento y Campo1.

SQL SERVER recomienda el incluir los campos debido a la query SELECT * en la que se recogen todos los campos. Lógicamente, este índice es para este caso y posiblemente acotar los campos ( sustituir el * por los campos que necesitamos hará que quizás no sea necesario incluir estos campos.

Finalmente, podemos observar que las tres siguientes filas con un porcentaje de cerca del 69% para la query con la clausula LIKE.

INDICES QUE SQL SERVER NO USA EN NUESTRA BASE DE DATOS

Por último, para conocer los índices no usados, si ejecutamos la siguiente query

select i.name, * from sys.dm_db_index_usage_stats s

inner join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

where database_id = DB_ID()  

podremos encontrar los índices que podría ser necesarios crear para mejorar la performance desde el último reinicio de la instancia de SQL SERVER. En la URL https://technet.microsoft.com/es-es/library/ms188755.aspx encontramos información sobre todas sus columnas.

Para ello creamos un índice llamado IX_Table_2 que lo formarán los campos campo1 y campo2 y estarán incluidos el campo de IdElemento.

Realizamos un par de consultas a la tabla incluyendo algunos de estos campos y volveremos a realizar la query para obtener la estadística de los índices.

Podemos observar la columna user_updates o las columnas last_user_seek o las columnas last_user_scan para observar si el índice se está usando.

Como ocurre con los índices que podrían ser necesarios crear, estas estadísticas se inicializan cada vez que se reinicia la instancia.

Jose Manuel Jurado Díaz

Ingeniero de soporte de SQL Server