Columnstore Indexes Described
El SQL Server almacén de columnas en memoria almacena y administra los datos mediante el almacenamiento de datos basado en columnas y el procesamiento de consultas basado en columnas. Los índices de almacén de columnas funcionan correctamente para las cargas de trabajo de almacenamiento de datos que ejecutan principalmente cargas masivas y consultas de solo lectura. Use el índice de almacén de columnas para aumentar hasta en diez veces el rendimiento de las consultas en relación con el almacenamiento tradicional orientado a filas, y hasta en siete veces la compresión de datos en relación con el tamaño de los datos sin comprimir.
Nota:
Consideramos que el índice clúster de almacén de columnas es el estándar para el almacenamiento de grandes tablas de hechos de almacenamiento de datos, y esperamos que se use en la mayoría de los escenarios de almacenamiento de datos. Puesto que el índice de almacén de columnas clúster es actualizable, la carga de trabajo puede realizar un gran número operaciones de inserción, actualización y eliminación.
Contenido
Aspectos básicos
Un columnstore index es una tecnología de almacenamiento, recuperación y administración de datos que emplea un formato de datos en columnas denominado almacén de columnas. SQL Server admite índices de almacén de columnas agrupados y no agrupados. Ambos usan la misma tecnología de almacén de columnas en memoria, pero tienen algunas diferencias en cuanto a su propósito y las funciones que admiten.
Ventajas
Los índices de almacén de columnas funcionan correctamente con la mayoría de las consultas de solo lectura que realizan análisis en conjuntos de datos grandes. A menudo, se trata de consultas de cargas de trabajo de almacenamiento de datos. Los índices de almacén de columnas ofrecen alto rendimiento para las consultas que usan recorridos de tabla completos y no son adecuados para las consultas que buscan un valor concreto en los datos.
Ventajas de los índices de almacén de columnas:
Las columnas a menudo tienen datos similares, lo que da como resultado factores de compresión altos.
Los factores de compresión altos mejoran el rendimiento de las consultas mediante la utilización de una superficie en memoria menor. A su vez, el rendimiento de las consultas puede mejorar porque SQL Server puede realizar más operaciones de consulta y datos en memoria.
Se ha agregado a SQL Server un nuevo mecanismo de ejecución de consultas denominado ejecución en modo por lotes que reduce considerablemente el uso de CPU. La ejecución en modo por lotes está estrechamente integrada con el formato de almacenamiento de almacén de columnas y optimizada alrededor del mismo. La ejecución en modo por lotes se conoce en ocasiones como ejecución basada en vectores o vectorizada.
Con frecuencia, las consultas seleccionan únicamente unas pocas columnas de una tabla, lo que reduce la E/S total desde los medios físicos.
Versiones de almacén de columnas
SQL Server 2012, Almacenamiento de datos paralelos de SQL Server 2012 y SQL Server 2014 emplean índices de almacén de columnas para acelerar las consultas comunes de almacenamiento de datos. SQL Server 2012 introdujo dos nuevas características: un índice de almacén de columnas no agrupado y una funcionalidad de ejecución de consultas basada en vectores que procesa los datos en unidades denominadas "lotes". SQL Server 2014 tiene las características de SQL Server 2012 más índices de almacén de columnas agrupados actualizables.
Características clave
| Se aplica a: SQL Server 2014 a SQL Server 2019 (15.x). |
En SQL Server, un índice de almacén de columnas agrupado:
Está disponible en las ediciones Enterprise, Developer y Evaluation.
Es actualizable.
Es el método de almacenamiento principal de toda la tabla.
No tiene ninguna columna de clave. Todas las columnas son columnas incluidas.
Es el único índice de la tabla. No se puede combinar con ningún otro índice.
Se puede configurar para usar almacén de columnas o compresión de archivo de almacén de columnas.
No almacena físicamente las columnas de forma ordenada. En su lugar, almacena los datos para mejorar la compresión y el rendimiento.
| Se aplica a: SQL Server 2012 a SQL Server 2019 (15.x). |
En SQL Server, un índice de almacén de columnas no agrupado:
Puede indizar un subconjunto de columnas del índice clúster o el montón. Por ejemplo, puede indizar las columnas usadas con frecuencia.
Necesita almacenamiento adicional para almacenar una copia de las columnas en el índice.
Se actualiza recompilando el índice o cambiando las particiones dentro y fuera. No se puede actualizar mediante las operaciones DML, como insertar, actualizar y eliminar.
Se puede combinar con otros índices de la tabla.
Se puede configurar para usar almacén de columnas o compresión de archivo de almacén de columnas.
No almacena físicamente las columnas de forma ordenada. En su lugar, almacena los datos para mejorar la compresión y el rendimiento. No es necesario ordenar los datos antes de crear el índice de almacén de columnas, pero si se hace puede mejorar la compresión de almacén de columnas.
Conceptos y términos clave
Los términos y conceptos clave siguientes están asociados a los índices de almacén de columnas.
índice de almacén de columnas Un índice de almacén de columnas es una tecnología para almacenar, recuperar y administrar datos mediante un formato de datos en columnas, denominado almacén de columnas. SQL Server admite índices de almacén de columnas agrupados y no agrupados. Ambos usan la misma tecnología de almacén de columnas en memoria, pero tienen algunas diferencias en cuanto a su propósito y las funciones que admiten.
almacén de columnas Un almacén de columnas es datos organizados lógicamente como una tabla con filas y columnas, y almacenados físicamente en un formato de datos de columna.
almacén de filas Un almacén de filas es datos que se organizan lógicamente como una tabla con filas y columnas y, a continuación, se almacenan físicamente en un formato de datos de fila. Esta ha sido la forma tradicional de almacenar los datos de una tabla relacional.
grupos de filas y segmentos de columna Para tasas de alto rendimiento y alta compresión, el índice de almacén de columnas segmenta la tabla en grupos de filas, denominados grupos de filas y, a continuación, comprime cada grupo de filas de una manera de columna. El número de filas del grupo de filas debe ser suficientemente grande como para mejorar las tasas de compresión y suficientemente pequeño como para beneficiarse de las operaciones en memoria.
grupo de filas Un grupo de filas es un grupo de filas que se comprimen en formato de almacén de columnas al mismo tiempo.
segmento de columna Un segmento de columna es una columna de datos desde dentro del grupo de filas.
Un grupo de filas suele contener el número máximo de filas por grupo, que es 1.048.576 filas.
Cada grupo de filas contiene un segmento de cada columna de la tabla.
Cada sector de columna se comprime junto y se almacena en un medio físico.
índice de almacén de columnas no agrupado Un índice de almacén de columnas no clúster es un índice de solo lectura creado en un índice agrupado existente o en una tabla de montón. Contiene una copia de un subconjunto de columnas, hasta incluir todas las columnas de la tabla. La tabla es de solo lectura mientras contiene un índice de almacén de columnas no agrupado.
Un índice no clúster de almacén de columnas proporciona una manera de tener un índice de almacén de columnas para ejecutar consultas de análisis al tiempo que se realizan operaciones de solo lectura en la tabla original.
índice de almacén de columnas agrupado Un índice de almacén de columnas agrupado es el almacenamiento físico de toda la tabla y es el único índice de la tabla. El índice clúster se puede actualizar. Puede realizar operaciones de inserción, eliminación y actualización del índice y puede cargar datos de forma masiva en el índice.
Para reducir la fragmentación de los segmentos de columna y mejorar el rendimiento, el índice de almacén de columnas puede almacenar temporalmente algunos datos en una tabla de almacén de filas, denominada almacén delta, así como un árbol B de los identificadores de las filas eliminadas. Las operaciones del almacén delta se administran en segundo plano. Para devolver los resultados correctos de la consulta, el índice clúster de almacén de columnas combina los resultados de la consulta tanto del almacén de columnas como del almacén delta.
almacén delta Usado solo con índices de almacén de columnas agrupados, un almacén delta es una tabla de almacén de filas que almacena filas hasta que el número de filas es lo suficientemente grande como para moverse al almacén de columnas. Un almacén delta se usa con índices clúster de almacén de columnas para mejorar el rendimiento de carga y de otras operaciones DML.
Durante una gran carga masiva, la mayoría de las filas van directamente al almacén de columnas sin pasar por el almacén delta. Al final de la carga masiva, podría quedar un número de filas menor que el tamaño mínimo de un grupo de filas, que es 102.400 filas. Cuando esto sucede, las últimas filas van al almacén delta en lugar de al almacén de columnas. En el caso de cargas masivas pequeñas con menos de 102.400 filas, todas las filas van directamente al almacén delta.
Cuando el almacén delta alcanza el número máximo de filas, se cierra. Un proceso de tupla motriz comprueba si hay grupos de filas cerrados. Cuando encuentra el grupo de filas cerrado, lo comprime y lo almacena en el almacén de columnas.
Cargar datos
Cargar datos en un índice no clúster de almacén de columnas
Para cargar datos en un índice de almacén de columnas no agrupado, primero cargue los datos en una tabla de almacén de filas tradicional almacenada como un montón o un índice agrupado y, a continuación, cree el índice de almacén de columnas no agrupado con CREATE COLUMNSTORE INDEX (Transact-SQL).
Una tabla con un índice no clúster de almacén de columnas es de solo lectura hasta que se quita o se deshabilita el índice. Para actualizar la tabla y el índice de almacén de columnas no clúster, puede cambiar las particiones dentro y fuera. También puede deshabilitar el índice, actualizar la tabla y, a continuación, volver a generar el índice.
Para obtener más información, vea Using Nonclustered Columnstore Indexes
Cargar datos en un índice clúster de almacén de columnas
de
Como sugiere el diagrama, para cargar datos en un índice de almacén de columnas agrupado, SQL Server:
Inserta grupos de filas con el tamaño máximo directamente en el almacén de columnas. A medida que se cargan los datos, SQL Server asigna las filas de datos en un orden de primera entrega en un grupo de filas abierto.
Para cada grupo de filas, después de alcanzar el tamaño máximo, SQL Server:
Lo marca como CLOSED.
Omite el almacén delta.
Comprime cada segmento de columna del grupo de filas mediante la compresión Columnstore.
Almacena físicamente cada segmento de columna comprimido en el almacén de columnas.
Inserta las filas restantes en el almacén de columnas o en el almacén delta del modo siguiente:
Si el número de filas coincide con el número mínimo de filas necesario para formar un grupo de filas, las filas se agregan al almacén de columnas.
Si el número de filas es menor que el número mínimo de filas necesario para formar un grupo de filas, las filas se agregan al almacén delta.
Para obtener más información acerca de las tareas y los procesos de almacén delta, vea Using Clustered Columnstore Indexes.
Sugerencias para mejorar el rendimiento
Planear para que haya suficiente memoria para crear índices de almacén de columnas en paralelo
La creación un índice de almacén de columnas es de forma predeterminada una operación paralela a menos que se restrinja la memoria. Crear el índice en paralelo requiere más memoria que crear el índice en serie. Cuando hay suficiente memoria, la creación de un índice de almacén de columnas tarda aproximadamente 1,5 más que generar un árbol B en las mismas columnas.
La memoria necesaria para crear un índice de almacén de columnas depende del número de columnas, el número de columnas de cadena, el grado de paralelismo (DOP) y las características de los datos. Por ejemplo, si la tabla tiene menos de un millón de filas, SQL Server solo usará un subproceso para crear el índice de almacén de columnas.
Si la tabla tiene más de un millón de filas pero SQL Server no puede obtener una concesión de memoria suficiente para crear el índice mediante MAXDOP, SQL Server reducirá automáticamente el valor de MAXDOP según sea necesario para ajustarse a la concesión de memoria disponible. En algunos casos, se debe reducir a uno el DOP para generar el índice cuando la memoria está restringida.
Temas y tareas relacionados
Índices no clúster de almacén de columnas
Para las tareas comunes, vea Using Nonclustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) con REBUILD.
Índices clúster de almacén de columnas
Para las tareas comunes, vea Using Clustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) con REBUILD o REORGANIZE.
Metadatos
Todas las columnas de un índice de almacén de columnas se almacenan en los metadatos como columnas incluidas. El índice de almacén de columnas no tiene columnas de clave.