En comparación con los indices agrupados y no agrupados tradicionales de SQL Server a partir de SQL 2012 contamos con una primera versión de los Columnstore Indexes que han ido evolucionando en SQL Server 2014 y finalmente a partir de SQL 2016, los indices columnares presentan una nueva estructura de almacenando por columnas en lugar de filas, basada sobre la tecnología  llamada xVelocity, anteriormente conocido como VertiPaq, que acelerara el procesamiento de análisis de consultas y datos en un data-warehouse, ya que nació en Analysis Services

Pero se debe tener en cuenta que los índices de almacén en columnas aunque son eficaces, no son un vaso de agua en el desierto, ya que hay una serie de limitaciones en ellos. Cuando se usa apropiadamente reducen las entradas y salidas de disco y utilizar menos memoria.

Como mencionamos antes un Columnstore Indexes almacena los datos en un formato por columnas, a diferencia de las estructuras de árbol B tradicionales que se utilizan para los índices de almacenamiento de filas agrupados y no agrupados, que guardan los datos de modo de fila (en filas), abajo una imagen para recordar como es la estructura de filas de un indice clustered o agrupado.

A diferencia de los índices de nivel de fila, un índice de columnas organiza los datos de las columnas individuales que se unen entre sí para formar el índice. Esta estructura puede ofrecer mejoras de rendimiento significativas para las consultas que resumen grandes cantidades de datos, como los utilizados normalmente para la inteligencia empresarial (BI) y almacenamiento de datos.

Estructura del Columnstore Index

En el corazón de este modelo es la estructura columnar que agrupa los datos por columnas en lugar de filas. Para entender mejor cómo funciona esta estructura, vamos a ver una tabla sencilla (Tipo Auto) que almacena los datos relacionados con el automóvil. El siguiente código T-SQL muestra la definición de la tabla:

CREATE TABLE TipoAuto
(   AutoId INT NOT NULL,
   Marca VARCHAR(20) NOT NULL,
   Modelo VARCHAR(20) NOT NULL,
   Color VARCHAR(15) NOT NULL,
   AñoModelo SMALLINT NOT NULL , CONSTRAINT PK_TipoAuto PRIMARY KEY CLUSTERED (AutoId)  )

Esta es una tabla bastante sencilla, sólo unas algunas columnas configuradas con los tipos de dato básicos. Pero tenga en cuenta que la columna de la AutoID se configura como la clave principal e índice agrupado. Como es típico de un índice agrupado, los datos se ordenan físicamente en el disco en páginas de datos, como se ilustra en la Imagen de abajo.

En este caso solo como ejemplo los datos se dividen en tres páginas y cada página con cinco filas, esto es un escenario muy poco probable, ya que cada página normalmente contendrá muchos más datos, pero es solo para fines de demostración. El punto importante a destacar es que cada fila se almacena en una página en su totalidad.

Ahora supongamos que ejecute la siguiente consulta en la tabla TIPOAUTO:

SELECT Marca, AñoModelo FROM TipoAuto;

Cuando el motor de base de datos procesa la consulta, recupera las tres páginas de datos en la memoria, buscará toda la tabla a pesar de que la mayor parte de las columnas no son necesarios. En otras palabras, desperdiciara recursos de entrada y salida y memoria para recuperar datos innecesarios.

Ahora veamos cuando se crea un columnstore index en la tabla. En este caso, hemos incluido todas las columnas  de la tabla en el índice, aunque en realidad probablemente habría que incluir soló algunas columnas. No importa las columnas  que incluimos, se almacenan en el índice, en algo similar a la imagen abajo. Como se puede ver, los datos ya no se almacenan por fila, si no por columna

En el índice de columnas mostrado en la figura, cada columna es su propio segmento. Un segmento puede contener valores de una columna única, que permite que los datos de cada columna para acceder de forma independiente. Sin embargo, una columna puede abarcar varios segmentos, y cada segmento puede ser formado por varias páginas de datos. Los datos se transfieren desde el disco a la memoria por segmento, no por página.

Un segmento es un objeto grande altamente comprimido (LOB) que pueden contener hasta un millón de filas. Los datos dentro del segmento de cada columna coinciden fila por fila para que las filas siempre se pueden montar correctamente. Por ejemplo, la segunda fila en cada segmento en la Imagen de arriba, apuntan al mismo automovil: el azul 2003 Saturn Ion con un IdAuto de 102. Las filas coincidentes en todos los segmentos forman un grupo de filas. Pronto nos ocuparemos de los grupos de filas en más detalle en breve, pero primero volvamos a la declaración SELECT.

Si corremos la instrucción de nuevo, después de la creación de nuestro columnstore index, el procesador de consultas usará el columnstore index, en lugar del índice agrupado. Como resultado, sólo los segmentos asociados con el año, marca y el Modelo serán sacados en la memoria, lo que reduce los recursos necesarios para procesar la consulta. Esto es especialmente importante en las entradas y salidas de disco. A pesar de que hemos visto grandes saltos en el procesamiento y capacidad de memoria, El disco duro y sus entradas y salidas sigue siendo el eslabón más débil de una consulta, pero la estructura de columnas puede ayudar a reducir las E/S de manera significativa.

Por supuesto, los datos de una columna no cabrán siempre en un solo segmento, dada la limitación de un millón de fila. En tales casos, múltiples segmentos se crean para cada columna y se agrupan en múltiples grupos de filas, una para cada conjunto de segmentos.

Cuando un columnstore index se divide en varios grupos o segmentos de filas, cada grupo de filas contiene un conjunto de filas completas. Por ejemplo, la siguiente figura muestra el índice de almacén de columnas ahora dividida en tres grupos de filas. Cada grupo de filas contiene segmentos para cada columna, y juntos los segmentos forman el conjunto completo de filas.

Nótese que la figura de arriba muestra también varios diccionarios, cada uno asociado con una columna específica. Un diccionario codifica los valores de una columna configurada con un tipo de datos de cadena o, en algunos casos, un tipo no-string si la columna contiene unos valores distintos. Aunque no todas las columnas utilizan diccionarios, todas las columnas de cadenas de texto sí.

Cuando se utiliza un diccionario, que almacena valores de datos reales de la columna, y los valores numéricos de referencia se insertan en los segmentos en lugar de esos valores. Esto puede ofrecer una gran ventaja en el rendimiento para las columnas que contienen muchos valores repetidos, pero puede tener un impacto negativo sobre columnas con una gran cantidad de valores únicos. Aun así, una columna de cadena siempre utiliza un diccionario primario e incluso podría utilizar un diccionario secundario.

Funcionamiento del Columnstore Index

Como se señaló anteriormente, una de las mayores ventajas que ofrece un columnstore index es la reducción de Entradas y Salidas de Disco, que puede tener un impacto directo en el rendimiento de las consultas. Sólo para dar una idea de esto, veamos un ejemplo sencillo. La siguiente instrucción SELECT recupera los datos de la tabla FactResellerSales en la base de datos de ejemplo AdventureWorksDW2012 que puede descargarse en: https://msftdbprodsamples.codeplex.com/downloads/get/165405

SELECT ProductKeyM, UnitPrice, CustomerPONumber, OrderDate
FROM FactResellerSales;

La definición de la tabla incluye una clave principal compuesta definida en las columnas SalesOrderNumber y SalesOrderLineNumber, que forman la base del índice agrupado de la tabla. Como resultado, cuando se ejecuta la consulta, el procesador de consultas realiza un recorrido de índice agrupado.

No hay nada demasiado sorprendente aquí. Puesto que la tabla contiene sólo 60.855 filas, la consulta se procesa de forma casi instantánea. Aún así, vale la pena echar un vistazo rápido a los detalles del plan de ejecución, que se muestra en la siguiente figura. En particular se nota el “Estimated I/O Cost” que es de 2,19868. Observe que esto representa una parte significativa de los costos totales de los operadores (Estimated Operator Cost) de 2,26578. El resto de los costos del operador va a la CPU, que está en el puesto solamente 0.0670975.

Ahora vamos a crear un índice de almacén de columnas en nuestra tabla. La siguiente sentencia crea un índice CSI_FactResellerSales con los campos ProductKey, Unitprice, CustomerPONumber y Orderdate:

CREATE NONCLUSTERED COLUMNSTORE INDEX CSI_FactResellerSales
ON dbo.FactResellerSales
(ProductKey, UnitPrice, CustomerPONumber, OrderDate);

Crear de un ColumnStore Index es tan simple como crear cualquier tipo de índice no agrupado. Una vez que creamos el columnStore Index, podemos volver a ejecutar nuestra consulta. La instrucción SELECT una vez más vuelve nuestras filas instantáneamente, por el pequeño grupo de datos que es, pero produce un plan de ejecución diferente, veamos para esto la siguiente figura.

Como se puede ver, esta vez el procesador de consultas realiza un recorrido del Columstore index, en lugar de un recorrido de índice agrupado. Y si revisamos los detalles del plan de ejecución, que sigue abajo, revela una imagen muy diferente de E/S.

Observe que los costos de la CPU son los mismos que para el índice agrupado: 0,0670975. Sin embargo, nuestro operador “Estimated Operator Cost” esta vez es sólo es 0.0931855, en comparación con 2,26578 para el índice agrupado. Esto se debe a nuestro costo de E/S es ahora sólo 0,026088, en lugar del 2,19868 devueltos por la consulta utilizando el índice agrupado.

Este ejemplo es, sin duda, es sencillo, pero demuestra la naturaleza como el índice columnar puede reducir los costos de E/S en sus consultas. Sin embargo, la estructura del índice columnar no es el único aspecto que da lugar a mejoras del rendimiento. La compresión también desempeña un papel integral.

Ya que los datos en un índice de columnas se agrupan por columnas, en lugar de por filas, los datos se pueden comprimir de manera más eficiente que con índices almacenados en filas. Los datos leídos desde una sola columna son más homogéneos que los datos leídos de filas, y cuanto más similares sean los datos, más fácil de comprimir. Además, si se le añade un bajo número de valores distintos y el uso de diccionarios, solo hay ventajas.

La tecnología xVelocity también trae algoritmos de compresión sofisticados que pueden sacar el máximo provecho de la naturaleza columnar de los índices. Y la manera más eficaz de comprimir los datos, ya que más datos cabrán en una sola página y cuantos más datos se puede cargar en la memoria, menos costos de entradas y salidas de disco. Si se tiene en cuenta la naturaleza de las cargas de trabajo de BI, que a menudo implican la agregación de grandes conjuntos de datos, se puede ver la clara ventaja de la estructura de columnas. La necesidad de que el aumento de la potencia de la CPU que requiere dicha agregación puede ser compensado por los ahorros de E / S, lo que ayuda a mejorar el rendimiento de las consultas gigantescas.

Índices Columnares Mejorados en SQL Server 2016

En SQL Server 2016 se introdujeron varias mejoras significativas a los índices columnares, haciendo que sean más eficientes y funcionales. Aquí te explico las mejoras:

1. Actualizaciones en Línea (Online Operations):
   • Antes de SQL Server 2016, las operaciones de actualización (modificaciones) en índices columnares eran más limitadas y menos eficientes. En 2016, se mejoraron las capacidades para realizar actualizaciones en línea, lo que permite modificar los índices columnares sin necesidad de bloquear las tablas, facilitando así la concurrencia y la disponibilidad continua.
2. Soporte para Todas las Columnas (Non-Clustered Columnstore Index):
   • Se añadió la posibilidad de tener índices columnares no agrupados en tablas OLTP (Online Transaction Processing), permitiendo la combinación de índices filares y columnares en la misma tabla. Esto significa que puedes aprovechar los beneficios de almacenamiento comprimido y consultas rápidas de los índices columnares mientras sigues utilizando índices tradicionales para transacciones rápidas.
3. Índices Filtrados (Filtered Non-Clustered Columnstore Index):
   • Los índices columnares no agrupados pueden ser filtrados, lo que permite aplicar el índice solo a un subconjunto de filas basado en una condición específica. Esto ayuda a ahorrar espacio y mejorar el rendimiento de consultas que solo necesitan un segmento de los datos.
4. Mejoras en el Rendimiento de Consultas:
   • Se optimizó el rendimiento de las consultas utilizando índices columnares, especialmente para agregaciones y operaciones de escaneo de grandes volúmenes de datos. Las consultas pueden ejecutarse mucho más rápido debido a la compresión y la forma en que los datos se almacenan y acceden en columnas en lugar de filas.
5. Mejoras en la Compresión:
   • La compresión de datos en índices columnares fue mejorada, resultando en una mayor reducción del tamaño de almacenamiento. Esto permite que más datos puedan caber en la memoria y se reduzca el I/O, lo que mejora significativamente el rendimiento.
6. Soporte para Transacciones y Snapshot Isolation:
   • SQL Server 2016 introdujo mejoras para manejar transacciones con índices columnares, permitiendo el uso de niveles de aislamiento como Snapshot Isolation, lo que facilita la concurrencia sin bloquear operaciones.

Beneficios de las Mejoras en Índices Columnares

• Mayor Velocidad de Consulta: Las consultas analíticas y de agregación se ejecutan mucho más rápido gracias a la forma en que los datos están organizados y comprimidos.
• Menor Espacio de Almacenamiento: Los datos comprimidos ocupan menos espacio, lo que reduce los costos de almacenamiento y mejora el rendimiento.
• Alta Concurrencia: Las operaciones en línea y el soporte para transacciones permiten que múltiples usuarios accedan y modifiquen los datos sin bloquearse entre sí.
• Flexibilidad: La combinación de índices filares y columnares permite obtener lo mejor de ambos mundos en términos de rendimiento de consulta y actualización.

Las mejoras en los índices columnares de SQL Server 2016 hicieron que fueran más rápidos, eficientes y versátiles, permitiendo manejar grandes volúmenes de datos de manera más efectiva. Esto ha hecho que los índices columnares sean una opción ideal para cargas de trabajo analíticas y de grandes volúmenes de datos, proporcionando beneficios significativos en rendimiento y almacenamiento.

En versiones posteriores a SQL Server 2016, se han introducido nuevas mejoras y características adicionales para los índices columnares, continuando con el enfoque en el rendimiento, la eficiencia y la flexibilidad. Aquí te menciono algunas de las mejoras más destacadas en SQL Server 2017, 2019 y 2022:

SQL Server 2017

1. Índices Columnares Agrupados y Compatibilidad con Índices Secundarios:
   • Se mejoró la compatibilidad para permitir la creación de índices secundarios en tablas que ya tienen un índice columnar agrupado. Esto ofrece más flexibilidad en la optimización de consultas y en la estructura de índices.
2. Actualizaciones y Eliminaciones Más Eficientes:
   • Se optimizaron las operaciones DML (Insert, Update, Delete) en índices columnares, haciendo que las modificaciones sean más rápidas y consuman menos recursos.

SQL Server 2019

1. Índices Columnares en Tablas de Almacenamiento de Filas en Memoria (Memory-Optimized Tables):
   • Ahora es posible crear índices columnares en tablas optimizadas para memoria, combinando los beneficios del almacenamiento en memoria y la compresión columnar para mejorar aún más el rendimiento.
2. Instrucción MERGE Mejorada:
   • Se mejoró la instrucción MERGE para trabajar de manera más eficiente con índices columnares, facilitando las operaciones de combinación de datos.
3. Compatibilidad con Resumir Almacenamiento (Hybrid Buffer Pool):
   • Se introdujo la compatibilidad para permitir que los índices columnares residan en el buffer pool híbrido, mejorando el rendimiento al almacenar en caché datos comprimidos en la memoria.

SQL Server 2022

1. Índices Columnares Agrupados en Tablas Particionadas:
   • Se mejoró el soporte para índices columnares en tablas particionadas, permitiendo una administración más eficiente de grandes conjuntos de datos particionados.
2. Actualización y Mantenimiento de Índices Columnares:
   • Se optimizó aún más el mantenimiento y la actualización de índices columnares, reduciendo el costo y la complejidad de mantener estos índices en entornos de producción.
3. Compatibilidad con Always Encrypted:
   • Las consultas sobre datos cifrados utilizando índices columnares pueden beneficiarse de la tecnología de enclaves seguros, mejorando la seguridad sin comprometer el rendimiento.
4. Integración con Azure Synapse Link:
   • Se permite una integración más profunda con Azure Synapse Analytics, facilitando la migración y sincronización de grandes volúmenes de datos entre SQL Server y Synapse Analytics para análisis avanzados.

Los índices columnares son una opción poderosa y eficiente para gestionar y analizar grandes volúmenes de datos en SQL Server.