Categoría: Sin categorizar

por Miguel Ángel Motos 2019-03-26

Ahorra espacio con la Compresión de Index ColumnStore en SQL Server

Vamos a realizar un pequeño estudio y comparación de la compresión de los índices orientados en columnas para SQL Server (Index ColumnStore).

Estos índices pueden llegar a ser hasta 10 veces más rápidos que los índices tradicionales orientados a filas (Index RowStore) y, hablando del tamaño que ocupan, la compresión de datos puede ser hasta 7 veces más pequeño en relación a los datos sin comprimir.

1.- Primer análisis.

Al centrarse en un tipo de almacenamiento por columnas, muchos de los datos de las columnas son repetidos, dando lugar a un índice de compresión muy alto. De hecho, los índices de almacenamiento basado en columnas tienen por defecto aplicada una compresión, esto hace que las consultas sobre las tablas vayan más rápido al necesitar menos memoria para ejecutarlas.

Un problema que podemos encontrar es un mayor número de bloqueos al tratar de realizar las funciones INSERT, UPDATE y DELETE en las tablas con ColumnStore Index. De ahí que se deban usar dichos índices en tablas del tipo OLAP y no OLTP como como vimos en la entrada anterior.

2.- Escenario real.

Para realizar las siguientes pruebas, vamos a utilizar la base de datos ContosoBi, descargable desde aquí. Una vez descargada, vamos a ejecutar el siguiente comando para ver cuánto espacio ocupa la tabla dbo.FactSales.

exec sp_spaceused '[dbo].[FactSales]', true;

El resultado es el siguiente:

Bien, ahora vamos a crear el Clustered Columnstore Index:

create clustered columnstore index [PK_FactSales_SalesKey]
on dbo.[FactSales]
with ( DROP_EXISTING = ON );

¿Qué ha pasado?

Msg 1907, Level 16, State 1, Line 1
Cannot recreate index ‘PK_FactSales_SalesKey’. The new index definition does not match the constraint being enforced by the existing index.

Sencillo, la creación del índice ha fallado ya que para ello, debemos eliminar todas las Foreign Keys(FK) y la Primary Key(PK) de la tabla. Para ello usaremos el siguiente script:

ALTER TABLE [dbo].[FactSales] DROP CONSTRAINT [FK_FactSales_DimCurrency]
GO
ALTER TABLE [dbo].[FactSales] DROP CONSTRAINT [FK_FactSales_DimChannel]
GO
ALTER TABLE [dbo].[FactSales] DROP CONSTRAINT [FK_FactSales_DimDate]
GO
ALTER TABLE [dbo].[FactSales] DROP CONSTRAINT [FK_FactSales_DimProduct]
GO
ALTER TABLE [dbo].[FactSales] DROP CONSTRAINT [FK_FactSales_DimPromotion]
GO
ALTER TABLE [dbo].[FactSales] DROP CONSTRAINT [FK_FactSales_DimStore]
GO

ALTER TABLE [dbo].[FactSales] DROP CONSTRAINT [PK_FactSales_FactSalesKey] WITH ( ONLINE = OFF )
GO

Una vez borradas, procedemos de nuevo a crear el Clustered ColumnStore Index sobre la tabla FactSales.

Antes de nada, durante la creación del índice hay que tener en cuenta que se pueden ocasionar bloqueos, los cuales ralentizarían las consultas paralelas lanzadas contra esa tabla. Recomendamos arduamente realizar la creación de índices  en horario de menor actividad en el servidor para minimizar los riesgos.

Ahora sí, teniendo en cuenta los posibles bloqueos y estando en una franja horaria de menor actividad en el servidor, creemos el índice:

create clustered columnstore index [PK_FactSales_SalesKey]
on dbo.[FactSales];
GO

Con esto ya tenemos nuestro índice basado en columnas creado.

Veamos ahora cuánto espacio ocupa con el mismo comando usado al principio:

exec sp_spaceused '[dbo].[FactSales]', true;

¡Impresionante! la tabla ha pasado de ocupar 151368KB a ocupar 46856KB. Estamos hablando de una reducción de un 66% aproximadamente.

3.- Un poco más allá.

No contentos con la compresión que nos viene dada con el tipo de índice basado en columnas, vamos realizar una compresión más grande. Este tipo de compresión se llama ” columnstore archive” y, sobre todo, sirve para tablas que los datos se insertan, todavía, con menor frecuencia. Un ejemplo de una tabla apta para que podamos realizar el tipo de compresión “archive” sería un histórico de ventas, ya que los datos en ellas se podrían insertar 1 vez a la semana.

En nuestro caso, vamos a realizar este tipo de compresión sobre la misma tabla [dbo].[FactSales], así podremos ver la diferencia entre la compresión por defecto del índice ColumnStore y la compresión Archive. Ejecutamos el siguiente comando:

alter index all on dbo.[FactSales]
rebuild with ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Una vez establecida la compresión Archive del índice ColumnStore, de nuevo vamos a comprobar el espacio ocupado de la tabla:

exec sp_spaceused '[dbo].[FactSales]', true;

Vemos que el tamaño de la tabla ha pasado de 46856KB a 42056KB. No parece un gran cambio, aún así, si pensamos en una tabla extremadamente grande, esta compresión sería significativa.

En la siguiente entrada sobre índices ColumnStore hablaremos sobre el funcionamiento interno de éstos y cómo funcionan los almacenes delta.

Si quieres que ayudemos a tu negocio o empresa contacta con nosotros en info@aleson-itc.com o llámanos al +34 962 681 242