SQL Server

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

Dic

2023

Í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

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

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

Dic

2023

Í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.

Contenido de la serie

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

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

Dic

2023

Índices Nonclustered (Índices parte 4)

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

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

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

Dic

2023

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

En esta tercera entrega de nuestra serie de índices vamos a hablar de los índices clustered. Para entender lo que vamos a ver tenemos que tener claro de antemano lo que vimos en la primera y la segunda entrada de esta serie. Si aún no lo has leído es el momento, para aquí mismo y vuelve cuando lo hayas hecho.

Bien, ahora que ya tenemos todos leídas la primera y la segunda entrega vamos a empezar. Analizaremos en profundidad las características de los índices clustered, buenas prácticas y veremos además lo que es una PK.

Índices Clustered en SQL Server

Recordemos lo que comentamos en la introducción, un índice clustered es en realidad toda la tabla ordenada por las columnas que componen el índice. Por este motivo solo puede haber un índice clustered por cada tabla. No tiene más restricciones que esa a menos que se las añadamos nosotros. Admite nulos y duplicados, aunque no es recomendable y luego veremos por qué.

Comparemos con lo que vimos de las tablas HEAP que teníamos un IAM que nos decía dónde empezaba la información. En los índices clustered también tenemos una estructura por encima que nos dice dónde está la información aunque en este caso es una estructura por niveles llamada de árbol invertido o árbol B. En los árboles partimos de un tronco abajo que se va bifurcando en ramas hasta terminar en las hojas arriba, pues nuestro árbol invertido es igual, partimos de un nivel superior que se va separando por niveles hasta terminar en todas las páginas abajo. Sería algo similar a lo que podemos ver en la siguiente imagen:

En este caso tenemos una tabla con un índice clustered por un campo numérico. Nuestro índice entonces crea una estructura de árbol B desde un nivel raíz hasta ir bajando y llegar a las páginas con los datos. Aunque en el ejemplo lo he querido simplificar, lo normal será tener más de un registro en cada una de las páginas finales. Estas páginas son del tipo IN_ROW_DATA y los registros estarán ordenados por el campo numérico del índice. Además, igual que en las tablas HEAP, tendremos también páginas LOB_DATA y ROW_OVERFLOW_DATA para los datos grandes.

Ordenación de los datos

En el párrafo anterior os he mentido un poco, los que teneis un nivel más avanzado os habréis dado cuenta. No me dejéis todavía un comentario negativo por favor, vamos a verlo. Os he dicho que los datos se guardan en las páginas ordenados por el campo que forma parte del índice, esta es la teoría y es como debe de estar pero no siempre es así.
Cuando insertamos un dato SQL se va a posicionar en el sitio donde deberá escribirlo y va a verificar si hay o no espacio, si hay espacio perfecto pero, si no lo escribirá donde pueda. Lo mismo ocurre si actualizamos el campo indexado. Esto se llama fragmentación de los índices y es el motivo por el que nuestros índices necesitan mantenimiento. Podríamos hablar ahora del Fill Factor de los índices y de su mantenimiento pero ambas cosas llegarán más adelante en esta serie. De momento con saber esto nos vale.

Buenas prácticas en los índices clustered

Este problema de la fragmentación es por lo que al principio os decía que no es recomendable crear un índice clustered por campos que admiten nulos o duplicados. Pero además existen otras características que identifican un buen índice clustered, debe ser corto, estático e incremental. Es fácil de explicar, cuanto más corto sea menos nos costará buscar y ordenar. Dado que una actualización nos generará fragmentación también debe de ser algo que no cambie nunca, es decir estático. Que sea incremental se entiende también en clave de evitar la fragmentación pues si siempre aumenta siempre escribiremos después de lo que ya existe.

Índices Primary Key

Cuando hablamos de un índice clustered que no admite duplicados en un campo que no admite nulos muchos de vosotros pensaréis automáticamente en una PK (Primary Key o clave primaria). Esto no es del todo correcto, es cierto que una PK por defecto tiene un índice clustered y que no admite nulos, pero no es lo mismo.

Una PK es el identificador único de los registros de nuestra tabla, por tanto es una restricción lógica en la que una columna no admite valores nulos ni duplicados. Que sea una restricción lógica significa que para poder implementarse a nivel físico necesita de un índice. Mientras no definamos nosotros lo contrario será un índice clustered. Pero puede que a nosotros nos interese que nuestra tabla se ordene por otro campo y crear la PK sobre un índice nonclustered.

Índices Clustered vs Primary Keys

Ahora ya sabemos que un índice clustered no es lo mismo que una PK, la PK es una restricción lógica que debe reforzarse con un índice físico que puede ser clustered o nonclustered. Entonces, la pregunta sería: ¿Cuándo debo usar una PK clustered y cuándo una nonclustered?

Imaginemos que estamos buscando la mejor manera de crear un índice clustered y una PK para nuestra tabla de personas. Tenemos unos datos como nombre, primer apellido, segundo apellido y NIE/DNI. Sabemos que una PK no admite valores nulos ni duplicados y conocemos las buenas prácticas sobre índices clustered. En base a esto decidimos que la PK de nuestra tabla sea el DNI ya que no puede estar vacío y es un valor único, pero ¿es un buen índice clustered? Es corto, una sola columna y de menos de 10 caracteres pero ni es incremental ni es estático (un número NIE cambiará a DNI durante el proceso de residencia de un ciudadano extranjero). Es por esto que no es un buen candidato a índice clustered y lo mejor será crear un campo ID incremental que identifique inequívocamente a las personas de nuestra tabla.

Conclusión

Hoy hemos aprendido aspectos importantísimos sobre los índices clustered y las primary keys. Hemos visto que no siempre son lo mismo y en ocasiones puede interesarnos más una PK con un índice nonclustered. Sin embargo, aún no hemos hablado en profundidad de los índices nonclustered. Eso será en el próximo artículo, estad atentos

Contenido de la serie

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

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

Dic

2023

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

Seguimos con nuestra serie de índices en SQL Server y vamos a profundizar en la estructura de las tablas que vimos ayer. Además vamos a introducir conceptos nuevos como las particiones y las tablas HEAP. Antes de continuar, si aún no lo habéis hecho, os recomiendo leer la primera entrega de esta serie de índices donde hicimos una introducción y aclaramos los conceptos básicos sobre los que vamos a profundizar ahora.

Estructura física de las tablas

Como vimos en la introducción las tablas son en realidad la representación de los datos almacenados en páginas de 8Kb de información. Pero por encima de estas páginas tenemos una estructura lógica que indica dónde se ubican los datos. Del mismo modo, podemos definir particiones, que son divisiones horizontales físicas en función de los valores de una columna.

Como podéis ver en la imagen nuestra tabla, tenga o no particiones, sea HEAP o tenga un índice clustered se compone de páginas del tipo IN_ROW_DATA, LOB_DATA y ROW_OVERFLOW_DATA.

IN_ROW_DATA almacena la mayoría de la información de nuestras filas. Hay tipos de datos especiales que por su gran tamaño no caben en las páginas de datos y se almacenan en LOB_DATA. Por último, cuando definimos una columna que de longitud variable se almacenará en IN_ROW_DATA siempre que sea posible y cuando por tamaño no se pueda irá a una página tipo ROW_OVERFLOW_DATA.

Tablas HEAP

Como vimos, cuando escribimos en una tabla HEAP los datos se escribirán en el primer hueco libre en las páginas ya asignadas. Si no hay hueco disponible se creará una nueva página. Además se asignará un identificador único a cada fila conocido como RID. Aunque no existan índices, SQL necesita localizar donde se encuentran las páginas de una tabla HEAP y para ello hace uso de un IAM o mapa de asignación de índices. El IAM almacena la ubicación de las páginas que componen la tabla pero, no identifica de ninguna manera los RID. Para buscar una fila SQL Server irá al IAM y recorrerá secuencialmente todos los registros de todas las páginas enlazadas al IAM. Como podéis adivinar, esto no es nada bueno en términos de rendimiento, sin embargo no es el peor de los problemas de las tablas HEAP.

Redirección de Punteros

Cuando se modifica un registro de una tabla HEAP es posible que la nueva información no quepa en el espacio que tenía asignada esa fila. En este caso, no se mueve toda la fila a una nueva ubicación sino que ese registro se escribe en otro sitio y en su ubicación original se deja un puntero hacia la nueva ubicación.
Esto complica mucho la lectura, pues para buscar una fila, SQL Server leerá la primera página del IAM que le dirá donde está la primera página de datos. Leerá secuencialmente toda la página de datos hasta que se encuentre termine, irá al IAM a buscar las siguientes páginas y hará lo mismo. Cuando termine con las páginas de datos referenciadas en la primera página del IAM irá a la siguiente página del IAM y habrá lo mismo. Así todas las veces que sea necesario hasta leer completamente la tabla. Por el camino se encontrará punteros que apuntan a páginas LOB, Overflow o simplemente a otras páginas con datos que se han actualizado y no cabían en la ubicación original.

Si esto os parece un problema pensad en los discos duros mecánicos donde una aguja se va moviendo físicamente para posicionarse en la posición correcta del disco y así leer los datos. Una locura, ¿verdad? Es mejor tener nuestras tablas ordenadas y para eso vamos a usar los índices clustered. Pero esto lo veremos en profundidad en el próximo artículo, mientras, podéis dejarme vuestras dudas y comentarios abajo, por Twitter o mail.

Contenido de la serie