Roberto Carrancio

A lo largo de las pasadas 6 entradas hemos repasado todos los distintos tipos de índices en SQL Server. Sin embargo, hay aspectos que, aunque comentamos por encima en la introducción, no habíamos vuelto a profundizar y son los que vamos a ver. Durante estas líneas vamos a hablar sobre cuándo y cómo SQL Server hace uso de los índices. Además, veremos cómo se van llenando las páginas y cómo podemos influir nosotros en ello, un aspecto que afectará a la fragmentación. Por último aprenderemos cómo crear un plan de mantenimiento que solucione este problema.

Uso de índices y estadísticas

Sea cual sea el índice que creemos, SQL Server generará una estadística sobre él. De esta manera, podrá decidir si es recomendable buscar los registros a través de los niveles del  árbol B, leer directamente el nivel de hoja en busca de los registros o recorrer enteramente la tabla y no usar el índice. 

Cuando ejecutamos una consulta, SQL Server calculará distintos planes de ejecución con todas las opciones de recuperar los datos. En una tabla pequeña de menos de 10.000 páginas es fácil, se va a leer la tabla y no se usarán los índices nonclustered. Cuando tiene más, se usarán las estadísticas de los índices nonclustered para calcular el porcentaje de registros que se van a recuperar. En función de esos datos asignará un coste a cada uno de los planes de ejecución. Con todos los costes calculados se elegirá la mejor opción.

Llenado de índices y tablas

Hemos comentado en varias ocasiones que los datos en SQL se escriben en páginas. Vimos que en las tablas HEAP que los datos se escriben en el primer hueco libre disponible y, a la hora de editar, si el nuevo valor no cabe se mueve al final. Esto generaba punteros de reenvío y fragmentación.

En el caso de los índices clustered y nonclustered los datos se escriben en páginas igualmente. SQL Server intentará escribir el datos en su sitio pero, solo lo hará si hay sitio libre. Si no lo hay, lo escribirá en el espacio libre más próximo. 

Fill Factor

Dado este comportamiento, para reducir al máximo la fragmentación y aprovechar el espacio de manera óptima podemos definir en los índices un nivel de llenado o Fill Factor. Este Fill Factor es un porcentaje que indicará al SQL cuánto espacio de las páginas de los índices se va a llenar de datos (no aplica para tablas HEAP). Por tanto, podremos controlar cuánto espacio se va a dejar libre al final de las páginas.
Podremos definir un nivel genérico en las propiedades de la instancia además de uno particular para cada índice si no queremos que use el genérico. Si no definimos otra cosa el fill factor será del 100%, es decir SQL tratará de llenar completamente las páginas. Esto puede ser una buena idea para índices en campos incrementales que nunca se editan. Sin embargo, si este no es el caso, llenar completamente las páginas nos generará tener que mover los datos o no escribirlos donde toca. En resumen, un mayor consumo de E/S y por tanto peor rendimiento.

Mantenimiento de índices

Acabamos de ver que muy probablemente nuestros índices se van a fragmentar con el uso, así que, como DBAs, es imprescindible que tengamos una buena política de mantenimiento que garantice que se encuentren en óptimas condiciones. Para ello tenemos varias alternativas, reorganizarlos o reconstruirlos. El método elegido dependerá en gran medida de la fragmentación que tenga el índice al momento de optimizarlo. Como norma general, reorganizaremos los índices con menos de un 30% de fragmentación y reconstruiremos los más fragmentados.
Además, tendremos que implementar también un mantenimiento de las estadísticas, como hemos visto son claves para que el motor de bases de datos calcule los costes de los planes de ejecución y decida por cuál de ellos decantarse. La reconstrucción de índices conlleva el mantenimiento de estadísticas, pero la reorganización no, por lo que tendremos que hacerlo por separado. Como os comenté en el artículo de las tareas diarias de un DBA, yo me decanto por la solución de olla hallengren. 

Aunque un mantenimiento de las estadísticas es clave para el rendimiento de nuestra base de datos no tenemos que caer en el error de ejecutarlo con demasiada frecuencia.Cada vez que se actualizan estadísticas, todos los planes de ejecución que tenemos en la caché de SQL Server y que usan esas tablas se van a desechar. Esto provocará que la próxima ejecución de la consulta necesite recompilar el plan, calcular todas las opciones y elegir el de menos coste. Para esto, la solución de ola, tiene un parámetro para solo actualizar las estadísticas que hayan sufrido modificaciones desde la última actualización, lo que liberará a SQL Server de carga de trabajo innecesaria. 

Conclusión

Dado que con este artículo cerramos la serie de índices, permitidme que por una vez me extienda un poco más en la conclusión y recapitulemos todo lo visto. Primero de todo quiero daros las gracias a vosotros, habéis llevado las páginas de esta serie a las primeras posiciones de las estadísticas de visitas del blog en muy pocos días. 

Ahora ya sí, entremos en materia. Empezamos esta serie con una introducción donde vimos las estructura de las tablas en SQL Server y descubrimos la importancia de los índices.

Después analizamos los distintos tipos de índices que podemos usar en SQL Server, desde la ausencia de ellos en una tabla HEAP, índices clustered, nonclustered, columnares y, hasta los índices especiales (únicos, filtrados, espaciales, XML, hash y de texto completo). De todos ellos vimos cómo funcionan, para qué se usan y cuándo son recomendables.Todo este conocimiento adquirido durante los últimos días nos ha traído hasta esta última entrada donde hemos entendido mejor la fragmentación de los índices y cómo combatirla. Además hemos podido comprender la importancia de las estadísticas y su papel clave en el rendimiento de nuestro SQL Server. 

Seguro que los más expertos echaréis en falta algún que otro aspecto relacionado con los índices o que entremos más en detalle sobre alguno de los temas tratados, os prometo que habrá más artículos sobre índices en un futuro aunque sea ya fuera de esta serie. 
Como siempre, tenéis a vuestra disposición la sección de comentarios, mi mail y nuestra cuenta de twitter para cualquier aportación o sugerencia. 
¡Espero que os haya gustado esta serie y hayáis aprendido cosas nuevas!

Contenido de la serie

Introducción a los índices en SQL Server (Índices parte 1)

Estructura física de las tablas y HEAP (Índices parte 2)

Índices Clustered y Primary Keys (Índices parte 3)

Índices Nonclustered (Índices parte 4)

Índices Columnares (Índices parte 5)

Índices especiales (Índices parte 6)

Sexta entrega de esta amplia serie sobre índices donde hemos podido aprender en profundidad todo sobre los principales índices de SQL Server. Antes de entrar en detalle sobre el uso y mantenimiento de los mismos vamos a terminar de repasar rápidamente los tipos de índices especiales.Estos índices especiales son, en su mayoría, específicos para casos concretos de uso y, si bien su uso está mucho menos extendido, como DBAs tenemos que conocer. 

Índices especiales para funcionalidades comunes en SQL Server

Este tipo de índices están pensado para ampliar la funcionalidad de los índices existentes y que hemos repasado en anteriores entradas de esta serie. Hablamos de los índices únicos y los índices filtrados y podríamos decir que existen para complementar al resto de índices.

Índices únicos

La unicidad en una columna es una restricción que se utiliza para mantener la integridad de los datos. Como ya vimos cuando hablamos de las PK, una restricción es una medida lógica que necesita reforzarse con un índice físico. Siempre que queramos crear una restricción de unicidad se creará por defecto un índice nonclustered sobre esa(s) columna(s). También podremos añadir la cláusula UNIQUE al crear un índice clustered o nonclustered.

A la hora de borrarlos, tenemos que tener en cuenta que si la restricción de unicidad se ha creado como restricción (constraint). Si el índice se ha creado automáticamente para reforzar una constraint no podremos usar la sintaxis DROP INDEX y deberemos usar DROP CONSTRAINT. Si hemos creado un índice con la cláusula UNIQUE sí que deberemos usar DROP INDEX.

Índices Filtrados

En ocasiones creamos un índice para mejorar el rendimiento de unas consultas que siempre filtran por los mismos valores de una columna. En estos casos podremos agregar un predicado where a nuestros índices nonclustered (columnstore o árbol B). Esto reducirá el espacio que ocupa nuestro índice y mejorará su rendimiento. 

Tenemos que tener en cuenta que cuanto más lógica añadamos a nuestros índices más le costará a SQL Server mantenerlos. Si el conjunto de resultados resultantes de los filtros del índice, es similar al total de la tabla seguramente no será rentable mantenerlo y deberemos crear un índice de tabla-completa.

Índices especiales para columnas especiales en SQL Server

A lo largo de las distintas versiones de SQL se ha ido añadiendo soporte para información especial. Estos índices que vamos a ver a continuación están pensados para dar soporte a este tipo de información especial.

Índices espaciales

A partir de SQL Server 2008 se introdujeron las columnas GEOGRAPHY y GEOMETRY, para guardar información de coordenadas o geométrica de puntos, líneas y polígonos respectivamente. Estos tipos de datos se conocen como datos espaciales y para ellos se crearon estos tipos de índices. 

Estos índices solo se pueden crear en tablas que tengan PK y en columnas espaciales. 

Índices XML

Como su propio nombre indica los índices XML son índices especiales para columnas de tipo XML en tablas que tengan PK. A poco que conozcáis el estándar XML entenderéis que estas columnas normalmente almacenan grandes cantidades de información de distintos tipos agrupadas en categorías. 

Existen dos tipos de índices XML, los primarios y los secundarios. Crearemos un índice XML primario para indexar las columnas XML. Después de crear el índice primario podremos crear índices XML secundarios para etiquetas concretas dentro de esa columna.

Índices especiales para características especiales de SQL Server

Igual que nos ha pasado en el apartado anterior, SQL ha ido implementando nuevas características para las que ha implementado los índices concretos que vamos a ver ahora. 

Índices hash

Son un tipo de índice especial para tablas optimizadas para memoria. Todas las tablas optimizadas para memoria requieren de un índice de este tipo o un índice nonclustered optimizado para memoria. Sin entrar mucho más en detalle, tenemos que saber que son un tipo de índice que almacena hash de la información ocupando una cantidad de memoria fija.

Índices de texto completo

Este tipo de índices son creados, usados y mantenidos por el motor de SQL Full-text. Permiten a las consultas Full-Text localizar cadenas de texto, para ello dividen las columnas en tokens que luego almacenan en el índice. 

Conclusión

Concluimos con esta sexta entrega el repaso a los distintos tipos de índices que nos podemos encontrar en SQL Server. Hoy hemos aprendido cuáles son los índices especiales que se adaptarán a casuísticas concretas de nuestras instalaciones. Seguid atentos al blog que a partir de mañana empezaremos a ver aspectos más prácticos sobre este mismo tema. Y ya para cerrar lo que os digo siempre, animaos a dejarme vuestras dudas y comentarios aquí debajo o en twitter o por mail.

Contenido de la serie

Introducción a los índices en SQL Server (Índices parte 1)

Estructura física de las tablas y HEAP (Índices parte 2)

Índices Clustered y Primary Keys (Índices parte 3)

Índices Nonclustered (Índices parte 4)

Índices Columnares (Índices parte 5)

Quinta entrega de nuestra serie de índices y aún nos queda mucho que ordenar. Después de haber visto los tipos de índices más comunes, hoy nos vamos a adentrar en los índices columnares, un tipo de índice no tan conocido pero muy usado en entornos OLAP y datawarehouse. Esto no es casualidad, veremos que, por sus características, son ideales para almacenar la información de las tablas de hechos en entornos donde se ejecutan gran cantidad de consultas complejas. 

Estructura de los índices columnares

Como hemos estado viendo hasta ahora los datos en SQL se almacenan en páginas de 8Kb donde vamos a guardar la información fila a fila de nuestra tabla. Cada página contiene N filas completas, es decir todas sus columnas (excepto las grandes que se almacenan en páginas LOB o ROW_OVERFLOW). Con los índices columnares vamos a dejar este paradigma y vamos a ver que, gracias a la tecnología de compresión VertiPaq, la información se almacena por columnas y no por filas. Tampoco vamos a hablar de páginas sino de segmentos. Una columna tendrá uno o más segmentos de datos. Aunque cambiemos el término tenéis que saber que un segmento es una página que almacena datos de una única columna.
Esto nos va a permitir reducir la E/S en gran medida al leer solo algunas columnas de la tabla ya que no vamos a tener que recuperar las filas completas de datos para luego mostrar unas cuantas columnas. Otra de las ventajas que tiene es que permite comprimir los datos. Normalmente los datos de una columna suelen repetirse y los índices columnares se aprovechan de eso escribiendo el dato solo una vez y añadiendo punteros a ese valor el resto de las veces. Esto se traduce en unas 10 veces menos consumo de disco y unas 10 veces mejor rendimiento en consultas pesadas.

Tipos de índices columnares

Al igual que en los índices de árbol B vamos a tener índices columnares clustered y nonclustered. Cada uno de ellos será ideal para un escenario concreto como vamos a ver a continuación. 

Índice columnar clustered

Al igual que con los índices de almacenamiento de filas (o de árbol B) este índice columnar clustered convierte toda nuestra tabla a almacenamiento de columnas. Sin embargo, no todos nuestros datos se almacenan en formato columnar, hasta que no tengamos aproximadamente un millón de filas los datos se almacenarán en un grupo de filas delta. 

Los grupos de filas delta (almacén delta) son estructuras de árbol B especialmente diseñadas para trabajar con almacén de columnas. Cuando un grupo de filas delta llega a 1.048.576 filas pasa de estado OPEN a CLOSED. En ese momento, un proceso en segundo plano llamado motor de tupla comprime el contenido y lo copia al almacén columnar. El grupo de filas en el almacén columnar pasa a estado COMPRESSED y el grupo del almacén delta se marca para borrar, estado TOMBSTONE (muy poético todo).

Hay una excepción a todo esto que hemos visto y son las cargas masivas o bulk insert. Cuando se produce una carga masiva sobre una tabla con un índice columnar clustered las filas van directamente al almacén de columnas. Se van guardando en grupos CLOSED de un millón de filas. Al finalizar, si el último grupo de filas tiene menos del mínimo de filas permitido (102.400) el grupo se moverá a un almacén delta.
Si la carga masiva directamente es inferior a ese valor mínimo se cargará directamente en un almacén delta. 

Índice columnar nonclustered

No nos vamos a extender mucho, un índice columnar nonclustered funciona igual que un índice columnar clustered. La diferencia es que se almacena separado de la tabla y puede contener desde solo una columna hasta todas las de la tabla. 

Podemos crear un índice columnar nonclustered sobre tablas de árbol B (almacenamiento clásico de filas) lo que nos permite aprovecharnos de las ventajas de estos índices sin sus inconvenientes.

Cuándo elegir un índice columnar

Acabo de deciros que los índices columnares tienen inconvenientes, pero ¿cuáles son? Hasta ahora hemos visto que un índice columnar reduce el consumo de disco y mejora el rendimiento en consultas pesadas. Gracias a su estructura están especialmente diseñados para funciones de agregación como sumas, cálculos de valores promedio y en general cualquier operación que implique trabajar con datos de una sola columna. Sin embargo, esto que los hace buenos en entornos OLAP los hace especialmente malos en búsquedas de un valor concreto típicas de un entorno transaccional (OLTP). 

Conclusión

Hemos aprendido qué son y cómo se organizan los índices columnares. También podemos entender cuándo debemos usarlos. Para las tablas de hechos de nuestro datawarehouse lo mejor son los índices columnares clustered mientras que para la mayoría de entornos transaccionales no nos serán de ayuda. Sin embargo, en la vida real es común que los departamentos de análisis, además de trabajar sobre sus entornos OLAP, realicen consultas en caliente sobre entornos OLTP y es donde más partido podremos sacar de los índices columnares nonclustered. También para informes de las propias aplicaciones, no tenemos por qué irnos a informes de departamentos específicos.

Continuamos nuestra serie de Índices en SQL Server. Después de una introducción y de profundizar en las tablas HEAP y los índices clustered ha llegado el turno de los índices nonclustered. Durante este blog vamos a profundizar en su funcionamiento, cuál es la mejor estrategia para crearlos y responderemos a la pregunta de si son buenos o malos para el rendimiento.

Estructura de un índice nonclustered

En la introducción de esta serie comentamos que los índices nonclustered son como una copia de la tabla que solo incluye las columnas que hemos elegido. Profundicemos en esto, un índice nonclustered se crea sobre las mismas páginas de 8Kb que el resto de objetos en SQL Server. En ellas se almacenan las columnas que hemos añadido al índice ordenadas ascendente o descendentemente según elijamos y un puntero hacia la ubicación física del resto de columnas de esas filas. Al igual que pasaba con los índices clustered sobre estas columnas se crea una estructura de árbol B o árbol invertido desde un nivel raíz hasta los datos. Podemos verlo en esta imagen sacada de la documentación oficial de Microsoft:

Cuando se crea un índice nonclustered sobre una tabla HEAP o una vista que no tenga un índice clustered (más sobre vistas indexadas aquí) el nivel de hoja contendrá el RID (identificador de fila) de las filas además de las columnas incluidas en el índice. Si por el contrario se crea sobre una tabla con un índice clustered, el nivel hoja incluirá las columnas del índice clustered. Adicionalmente, solo si el índice clustered no es único, se incluirá también un identificador de fila.

Filtrar por un índice nonclustered

Cuando filtras por una columna incluida en un índice nonclustered SQL Server buscará en ese índice los valores deseados. Independientemente de lo que pase después esto es mucho más rápido que leer la tabla completa en busca de un valor y se reduce en gran medida el consumo de recursos de E/S. Sin embargo aquí pueden pasar dos cosas, puede que nuestro índice incluya todas las columnas que deseamos recuperar en cuyo caso no habrá que leer más o puede que necesitemos más información de la que contiene el índice. En este caso, usará el puntero para saltar directamente a la ubicación de esos datos sin hacer lecturas innecesarias. Cuando un índice incluye todos los campos que requiere la consulta se dice que el índice cubre la consulta y es lo más óptimo en cuanto a rendimiento de lecturas.

Buenas prácticas 

Acabamos de ver que lo mejor para nuestras lecturas será que nuestro índice nonclustered cubra completamente nuestras consultas, sin embargo, todo tiene un pero. A mi me gusta comparar los índices nonclustered con la típica mantita que todos tenemos en el sofá, esa que si te tapas hasta arriba te deja los pies fuera y si te tapas los pies no te arropa hasta arriba. Igual que la mantita, nuestros índices tienen sus cosas malas y para arreglar una cosa empeoran otra. Como hemos comentado, los índices nonclustered son objetos separados de la tabla por lo que cada vez que escribamos (o modifiquemos) un dato en la tabla se tiene que modificar en todos los índices que contengan esa columna. Esto degrada el rendimiento de las escrituras.

Tenemos que tener esto muy en mente a la hora de diseñar los índices nonclustered ya que no nos podemos pasar creando índices nonclustered. Tampoco debemos crear índices nonclustered en columnas que tengan pocas lecturas por cada escritura o incluso más escrituras que lecturas.

Columnas clave o columnas incluidas

Si después de analizar todo esto aún nos cuadra el índice nonclustered, tenemos que tener en cuenta la limitación de 16 claves por índice. Tranquilos si no habéis entendido esto, he metido un concepto nuevo que ahora os explico.

Las columnas que forman parte de un índice nonclustered pueden ser claves o incluidas. Llamamos columnas clave a las columnas que forman parte del índice y por las que vamos a poder filtrar. Sin embargo para poder cubrir más consultas en ocasiones nos interesará añadir otras columnas al índice por las que no se va a filtrar pero si vamos a querer recuperar. Para esto tenemos la opción de incluir columnas. Es importante que no vayamos a filtrar por las columnas incluidas ya que estas no se van a escribir en los niveles raíz e intermedios de nuestro índice nonclustered y solo van a estar en el nivel hoja. Si antes decíamos que un índice nonclustered solo puede tener 16 columnas clave, como columnas incluidas podemos tener hasta 1023. 

¿Qué columnas añado a mi índice?

A estas alturas ya sabemos las implicaciones para el rendimiento de los índices y la teoría, llega el momento de ponernos a crearlo. Hemos visto que no es recomendable crear índices con claves muy largas así que, crearemos las columnas clave que normalmente se usan para filtrar o agrupar los datos (en el where, join, group by o having de nuestras consultas). Si necesitamos agregar alguna consulta más para cubrir completamente las consultas lo haremos dentro de la cláusula INCLUDE.

Conclusión

Hemos visto cómo funcionan los índices nonclustered y como son un arma de doble filo para el rendimiento de nuestra base de datos. Sin haberlo comentado hasta ahora, pero interiorizando los conceptos que hemos aprendido también vamos a ser capaces de entender por qué no es una buena idea añadir todas las columnas de nuestra tabla en un índice nonclustered. Otra enseñanza que nos llevamos es el hecho de qué si un índice nonclustered puede cubrir completamente las consultas hacer select * es una práctica a evitar si queremos mejorar el rendimiento. 

Contenido de la serie

Introducción a los índices en SQL Server (Índices parte 1)

Estructura física de las tablas y HEAP (Índices parte 2)

Índices Clustered y Primary Keys (Índices parte 3)

Índices Columnares (Índices parte 5)

Índices especiales (Índices parte 6)