Índices columnares.
Los índices, columnares, surgieron en un proyecto llamado Apollo, este proyecto incluía dos partes:
- Indices columnares – nuevo modo de almacenamiento.
- Un nuevo modo de procesamiento “Batch”.
Los índices columnares nos aportan la capacidad de resolver consultas, muy complejas, de una manera mucho más rápida.
Estos índices, no son ni mas ni menos que un nuevo modelo de índices que se añaden al modelo relacional, con una peculiaridad en su modo de almacenamiento. Hasta ahora la información se almacenaba en páginas de datos, y estas páginas de datos contenían filas con los datos. Ahora ya no es así, ahora el modo de almacenamiento de estos índices es en columnas, más abajo entro en más en detalle cuando quiero decir en “columnas”.
Los índices columnares, traen compresión Vertipaq. Vertipaq surge en SQL Server 2008 R2, con la aparición de Powerpivot y los sistemas de carga en memoria, donde se desarrollaron nuevos algoritmos, los cuales comprimen alrededor de 10 veces los datos, mas abajo tratamos esto mas en detalle… La compresión nativa que teníamos en versiones anteriores a SQL Server no encaja en este nuevo tipo de índices, ya que se quiere manejar grandes volúmenes de datos eficientemente.
Funcionamiento de los índices columnares, principalmente tiene tres conceptos:
- La primera: el modo de almacenamiento, se cambio del modo de almacenamiento de filas por el almacenamiento en columnas. Cuando nosotros lanzamos consultas, normalmente seleccionamos las columnas que deseamos, y este modo de almacenamiento es propicio para ello. Luego hablo mas en detalle de esto.
- La segunda: Vertipaq, algoritmos de compresión que comprimen los datos, mas concretamente las columnas, de una manera bestial. Ganamos reducción de actividad de entrada y salida, es decir, cuando vamos a coger los datos del disco están comprimidos… Y ganamos con una maximización del uso de la memoria, donde estos datos comprimidos en memoria ocupan menos… Aquí tenemos la clave…
- La tercera: el nuevo modo de procesamiento en “Batch”, que viene incluido para este tipo de índices columnares.
Ahora para el almacenamiento, ya no se habla de páginas de datos, se hace en “segmentos”. Segmentos que contienen los valores de las columnas. Un segmento por columna o parte de una columna. Altamente comprimidos con Vertipaq. Cada columna va por su cuenta.
Anteriormente o en versiones anteriores, cuando hacemos una consulta, SQL Server, se va a disco y carga las paginas en memoria, se lee toda la tabla y carga las paginas que necesita en memora… Cada página ocupa 8 k… De esta forma carga información que no es útil en memoria, información que esta dentro de estas paginas… Malgastando la memoria, “un recurso que es tan preciado”… En cambio, el almacenamiento por segmentos en columnas, donde tenemos almacenados los segmentos comprimidos en disco, cuando nos llega la consulta, simplemente va a cargar en memoria los segmentos necesarios de las columnas necesarias, cargando solo en memoria lo que realmente es útil y va a usar…
Modo de procesamiento en “Batch”:
El modo Batch, es un nuevo modo de procesamiento, que viene con los índices columnares, tenemos que cambiar la filosofía que tenemos hasta ahora. En versiones anteriores cuando nosotros procesábamos conjuntos de datos lo hacíamos fila por fila, ahora aparece un nuevo modo Batch, que nos proporciona un procesamiento vectorial, que en lugar de ir fila por fila, nos procesa conjuntos de filas, donde esos conjuntos de filas suelen ser de 1000 filas aproximadamente. Con esto se aprovecha el paralelismo mucho mas, y al procesar por conjuntos de filas, reduce el consumo de CPU, produciendo mejor rendimiento a las consultas. Este modo de procesamiento solo funciona para índices columnares, siempre que usemos otro tipo de índices, procesará registro a registro, no por conjuntos de registros.
El modo Batch, no se utiliza siempre aunque usemos índices columnares, para que use ente modo de procesamiento Batch, nuestra consulta tiene que tener algún JOIN o algún filtro o algún operador de agregación, que lo que haga es reducir un gran conjunto de datos con la agregación, es cuando usará este tipo de procesamiento. También si el conjunto de datos para el cual hemos creado índices columnares, son muy pequeños, (tablas pequeñas), es muy probable que tampoco use este modo de procesamiento. Lo decide el Opitimizador de consultas si utilizarlo o no…
Limitaciones:
Los datos no soportados son:
- Decimal y/o numericos (precision > 18)
- Datetimeoffset (precisión >2)
- Binary, Varbinary
- Image
- Text, ntext.
- Varchar(max), nvarchar(max)
- Hierarchyid
- Timestamp
- Uniqueidentifier
- Sqlvariant
- Xml
Solo podemos tener un índice por tabla, se recomienda crear un índice cluster, por todas las tablas que tengan un tipo de datos compatible, por que el índice columnar aísla cada columna…
No se puede aplicar a vistas indexadas.
No admite compresión nativa de SQL Server, por que ya esta comprimida con Vertipaq.
El índice columnar no puede ser un índice filtrado
El límite de columnas, no puede superar a 1024 columnas.
Cuando creamos un índice columnar en una tabla, esta tabla pasa a convertirse en una tabla de solo lectura. No se podrán realizar inserciones, updates, deletes ni podrás usar operadores tipo Merge...
Aunque existen alternativas, pero a priori no funcionan las modificaciones… Alternativas como eliminar el índice, hacer las inserciones y volver a crear el índice. Esta operativa probablemente no encaje en la lógica de tu aplicación, por lo que en este caso tendríamos que olvidarnos de estos índices columnares.
Otra forma de hacerlo es manteniendo un histórico con la/s tabla/s con índice columnar y una/s tabla/s auxiliar/es sin índice columnar donde cargaremos los datos diarios, entonces sobre las tablas con índice c. y sin índice c. crearemos unas vistas con union sobre las dos tablas, las consultas irán sobre la vista, y tendremos entonces para un mayor volumen de datos índices columnares y para los datos diarios no. Cuando crezca demasiado las tablas auxiliares hay que volcarlas en una ventana de mantenimiento sobre las tablas con índices columnares.
Y una ultima alternativa es utilizando particionado, donde partimos de nuestra tabla principal, con un índice columnar creado, y crearemos una tabla intermedia donde realizaremos las cargas y posteriormente crearemos un índice columnar, para nuestras consultas nos apoyaremos en las tablas intermedias y comono en la tabla principal.
Crear un índice columnar
Para crear un índice columnar lo podemos crear mediante T-SQL:
CREATE NONCLUSTERED COLUMNSTORE INDEX NombreIndice ON NombreTabla ( columna1, columna2, columna n,…);
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
