Usar índices clúster de almacén de columnas

Tareas para usar índices de almacén de columnas agrupados en SQL Server.

Para obtener información general acerca de los índices de almacén de columnas, vea Columnstore Indexes Described.

Para obtener información acerca de los índices clúster de almacén de columnas, vea Using Clustered Columnstore Indexes.

Contenido

• Crear un índice clúster de almacén de columnas

• Quitar un índice clúster de almacén de columnas

• Cargar los datos en un índice clúster de almacén de columnas

• Cambiar los datos de un índice clúster de almacén de columnas

• Recompilación de un índice de almacén de columnas agrupado

• Reorganizar un índice clúster de almacén de columnas

Crear un índice clúster de almacén de columnas

Para crear un índice de almacén de columnas agrupado, primero cree una tabla de almacén de filas como un montón o un índice agrupado y, a continuación, use la instrucción CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) para convertir la tabla en un índice de almacén de columnas agrupado. Si desea que el índice clúster de almacén de columnas tenga el mismo nombre que el índice clúster, use la opción DROP_EXISTING.

En este ejemplo se crea una tabla como un montón y después se convierte en un índice clúster de almacén de columnas denominado cci_Simple. Esto cambia el almacenamiento de la tabla de un almacén de filas a un almacén de columnas.

CREATE TABLE T1(
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;
GO

Para obtener más ejemplos, vea la sección Ejemplos de CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

Quitar un índice clúster de almacén de columnas

Use la instrucción DROP INDEX (Transact-SQL) para quitar un índice de almacén de columnas agrupado. Esta operación quitará el índice y convertirá la tabla de almacén de columnas en un montón de almacenes de filas.

Cargar los datos en un índice clúster de almacén de columnas

Puede agregar datos a un índice clúster de almacén de columnas existente mediante cualquiera de los métodos estándar de carga. Por ejemplo, la herramienta de carga masiva bcp, Integration Services e INSERT ... SELECT puede cargar todos los datos en un índice de almacén de columnas agrupado.

Los índices clúster de almacén de columnas aprovechan el almacén delta para evitar la fragmentación de segmentos de columna en el almacén de columnas.

Carga en una tabla con particiones

Para los datos con particiones, SQL Server primero asigna cada fila a una partición y después realiza operaciones del almacén de columnas con los datos de la partición. Cada partición tiene sus propios grupos de filas y un almacén delta por lo menos.

Escenarios de carga de almacén delta

Las filas se acumulan en el almacén delta hasta que su número alcanza el máximo permitido para formar un grupo de filas. Cuando el almacén delta contiene el número máximo de filas por grupo de filas, SQL Server marca el grupo de filas como "CLOSED". Un proceso en segundo plano, denominado "tupla-mover", busca el grupo de filas CLOSED y se mueve al almacén de columnas, donde el grupo de filas se comprime en segmentos de columna y los segmentos de columna se almacenan en el almacén de columnas.

Para cada índice clúster del almacén de columnas puede haber varios almacenes delta.

• Si un almacén delta está bloqueado, SQL Server intentará obtener un bloqueo en un almacén delta diferente. Si no hay almacenes delta disponibles, SQL Server creará un nuevo almacén delta.

• En una tabla con particiones, puede haber uno o varios almacenes delta para cada partición.

Para los índices clúster de almacén de columnas solo, en los escenarios siguientes se describe cuándo las filas cargadas pasan directamente al almacén de columnas o cuándo van al almacén delta.

En este ejemplo, cada grupo de filas puede tener de 102.400 a 1.048.576 filas.

En el ejemplo siguiente se muestran los resultados de cargar 1.048.577 filas en una partición. Los resultados muestran un grupo de filas COMPRESSED en el almacén de columnas (como segmentos de columna comprimidos) y una fila en el almacén delta.

SELECT * FROM sys.column_store_row_groups;

Cambiar los datos de un índice clúster de almacén de columnas

Los índices clúster de almacén de columnas admiten las operaciones del DML INSERT, UPDATE y DELETE.

Use INSERT (Transact-SQL) para insertar una fila. Se agregará la fila al almacén delta.

Use DELETE (Transact-SQL) para eliminar una fila.

• Si la fila está en el almacén de columnas, SQL Server marca la fila como eliminada lógicamente, pero no reclama el almacenamiento físico de la fila hasta que se vuelva a generar el índice.

• Si la fila está en el almacén delta, SQL Server lógica y físicamente elimina la fila.

Use UPDATE (Transact-SQL) para actualizar una fila.

• Si la fila está en el almacén de columnas, SQL Server marca la fila como eliminada lógicamente y, a continuación, inserta la fila actualizada en el almacén delta.

• Si la fila está en el almacén delta, SQL Server actualiza la fila en el almacén delta.

Volver a generar un índice clúster de almacén de columnas

Use CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) o ALTER INDEX (Transact-SQL) para realizar una recompilación completa de un índice de almacén de columnas agrupado existente. Además, puede usar ALTER INDEX ... REBUILD para recompilar una partición específica.

Proceso de regeneración

Para volver a generar un índice de almacén de columnas agrupado, SQL Server:

• Adquiere un bloqueo exclusivo en la tabla o la partición mientras se produce la regeneración. Los datos están “sin conexión” y no disponibles durante la regeneración.

• Desfragmenta el almacén de columnas físicamente eliminando filas que se han eliminado lógicamente de la tabla; los bytes eliminados se reclaman en los medios físicos.

• Antes de volver a generar el índice, combina los datos del almacén de filas del almacén delta con los datos del almacén de columnas. Una vez finalizada la regeneración, todos los datos se almacenan en el almacén de columnas y el almacén delta se queda vacío.

• Vuelve a comprimir todos los datos del almacén de columnas. Hay dos copias del índice de almacén de columnas mientras se está produciendo la regeneración. Una vez finalizada la recompilación, SQL Server elimina el índice de almacén de columnas original.

Recomendaciones para volver a generar un índice clúster de almacén de columnas

Volver a generar un índice clúster de almacén de columnas es útil para quitar la fragmentación y para mover todas las filas al almacén de columnas. Siga estas recomendaciones:

• Vuelva a generar una partición en lugar de la tabla completa.

  1. Volver a generar la tabla completa tarda mucho si el índice es grande y requiere el espacio en disco suficiente para almacenar una copia adicional del índice durante la regeneración. Por lo general, solo es necesario volver a generar la partición que se ha usado más recientemente.

  2. Para las tablas con particiones, no es necesario que vuelva a generar el índice de almacén de columnas completo, ya que es probable que se produzca la fragmentación solo en las particiones que se han modificado recientemente. Las tablas de hechos y las tablas de dimensiones de gran tamaño normalmente tienen particiones para poder realizar operaciones de copia de seguridad y de administración con los fragmentos de la tabla.

• Vuelva a generar una partición después de haber realizado operaciones DML intensivas.

  La regeneración de una partición desfragmentará la partición y reducirá el almacenamiento de disco. La regeneración eliminará todas las filas del almacén de columnas que están marcadas para eliminación y trasladará todas las filas de almacén delta al almacén de columnas.

• Vuelva a generar una partición después de cargar los datos.

  Esto garantiza que todos los datos se almacenan en el almacén de columnas. Si se producen varias cargas al mismo tiempo, cada partición puede acabar teniendo varios almacenes delta. La regeneración trasladará todas las filas del almacén delta al almacén de columnas.

Reorganizar un índice clúster de almacén de columnas

La reorganización de un índice clúster de almacén de columnas desplaza todos los grupos de columnas marcados como CLOSED al almacén de columnas. Para realizar una reorganización, use ALTER INDEX (Transact-SQL) con la opción REORGANIZE.

No es necesario llevar a cabo la reorganización para mover grupos de filas CLOSED al almacén de columnas. El proceso de tupla motriz encontrará finalmente todos los grupos de filas CLOSED y los moverá. Sin embargo, la tupla motriz es de un solo subproceso y los grupos de filas pueden no moverse lo suficientemente rápido para la carga de trabajo.

Recomendaciones para reorganizar

Cuándo se debe reorganizar un índice clúster de almacén de columnas:

• Reorganice un índice clúster de almacén de columnas después de una o varias cargas de datos para obtener mejoras en el rendimiento de las consultas lo más rápidamente posible. Inicialmente, el proceso de reorganización requerirá recursos de CPU adicionales para comprimir los datos, lo que podría reducir el rendimiento general del sistema. Sin embargo, tan pronto como los datos están comprimidos, el rendimiento de las consultas puede mejorar.