Mejoras en los índices ColumnStore de SQL 2014


Los índices ColumnStore fueron una novedad en SQL 2012, los mismos fueron pensados y optimizados para los sistemas DW donde con los mismos se podían lograr mejoras en performance significantes (10x por ejemplo).

Pero como toda nueva funcionalidad tenia algunas limitaciones , los que los hemos usados nos habremos topado con el problema de no poder actualizar la tabla que tenia un índice ColumnStore entonces no nos quedaba otra alternativa que aplicar un proceso de disable y luego Rebuild (post insert o actualización de datos) o bien trabajar con particiones y el Swith de las mismas.

SQL 2014 viene con algunas mejoras en relación a este tipo de índices, ahora si podremos insertar y además usarlos como clustered que era otra de las limitaciones de SQL 2012.

En este post haremos un análisis de estas nuevas funcionalidades.

Paso 1: Armado de tabla demo con datos para luego usar ColumnStore Index

SELECT *

INTO DBO.FACTPRODUCTINVENTORYBIG

FROM DBO.FACTPRODUCTINVENTORY

 

ALTER TABLE DBO.FACTPRODUCTINVENTORYBIG

DROP COLUMN MOVEMENTDATE;

GO

INSERT INTO [DBO].[FACTPRODUCTINVENTORYBIG]

           ([PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE])

SELECT       [PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE]

FROM [DBO].[FACTPRODUCTINVENTORY];

GO

INSERT INTO [DBO].[FACTPRODUCTINVENTORYBIG]

           ([PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE])

SELECT       [PRODUCTKEY]

           ,[DATEKEY]

           ,[UNITCOST]

           ,[UNITSIN]

           ,[UNITSOUT]

           ,[UNITSBALANCE]

FROM [DBO].[FACTPRODUCTINVENTORYBIG];

GO 4

 

Paso 2: Creamos un ColumnStore Index clustered

 

CREATE CLUSTERED COLUMNSTORE INDEX ix_cs_MyDWTable_ON dbo.FactProductInventoryBig;

 

Paso 3: Hacemos un insert a la tabla que tiene el ColumnStore Index Smile

 

INSERT INTO [dbo].[FactProductInventoryBig]

           ([ProductKey]

           ,[DateKey]

           ,[UnitCost]

           ,[UnitsIn]

           ,[UnitsOut]

           ,[UnitsBalance])

SELECT top 1        [ProductKey]

           ,[DateKey]

           ,[UnitCost]

           ,[UnitsIn]

           ,[UnitsOut]

           ,[UnitsBalance]

FROM [dbo].[FactProductInventoryBig];

 

Paso 4: Ejecutamos una consulta para observar en el Query Plan (plan de ejecución) el uso del ColumnStoreIndex

 

SELECT  p.EnglishProductName,

             AVG(fpi.UnitCost),

             SUM(fpi.UnitsOut)

FROM   dbo.FactProductInventoryBig as fpi

INNER JOIN dbo.DimProduct as p ON

       fpi.ProductKey = p.ProductKey

INNER JOIN dbo.DimDate as d ON

       fpi.DateKey = d.DateKey

GROUP BY p.EnglishProductName,

             d.WeekNumberOfYear,

             d.CalendarYear

ORDER BY p.EnglishProductName

 

columnStore1

 

Notas adicionales:

Recuerde primero que estos índices fueron pensados para los sistemas DW, luego la mejora de SQL2014 en el uso de los ColumnStoreIndex es muy importante, pero se debe considerar que para que esto funcione se lo debe crear como Clustered (no se le indican columnas) y los Non Clustered quedaron como solo lectura, o sea: si bien se pueden crear (siempre y cuando y exista un Column Store Index Clustered) los mismos son de solo lectura y quedara la tabla imposibilitada de hacer insert, update o delete. Por ejemplo si deseamos crear el siguiente índice (columnsStore Non Clustered) sobre el ejemplo anterior nos dará un error.

            

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_1]

ON dbo.FactProductInventoryBig

(

             ProductKey,

             DateKey,

             UnitCost,

             UnitsIn,

             UnitsOut,

             UnitsBalance

);

 

Msg 35303, Level 16, State 1, Line 131 CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.