Columnstore Index – 1ª Parte

Indice Columnstore está disponível desde o SQL Server 2012, e vem recebendo melhorias a cada versão.  Foi desenvolvido com uma tecnologia chamada xVelocity, que utiliza a memória para organizar os dados por coluna, no lugar da organização por linhas dos índices regulares em árvore B.  O engine xVelocity possui duas implementações no SQL Server:

1) xVelocity in-memory analytics engine: índice do Analysis Service Tabular para análise de dados em Business Intelligence, chamado inicialmente de Vertipaq.

2) xVelocity memory optimized ColumnStore index: mesma tecnologia usada no Analysis Service, disponibiliza índice em coluna (Column Store Index) no SQL Server Engine.

Neste post vou mostrar a segunda implementação do xVelocity acima, Column Store Index.

O SQL Server 2012 foi a primeira versão com Column Store Index, porém existia uma limitação importante, tabela com este índice não poderia receber atualização.  Já no SQL Server 2014 podemos atualizar tabelas com Clustered Column Store Index, porém com baixo desempenho nas atualizações.  Tabelas com poucas atualizações ou que recebem cargas periódicas de dados, são as ideais para uso destes índices.

Abaixo link com as diferenças entre as versões do SQL Server:

https://msdn.microsoft.com/en-us/library/dn934994.aspx

O objetivo principal deste índice é proporcionar melhora no desempenho de algumas consultas, comparando com os índices regulares do SQL Server em árvore B. As consultas com maior probabilidade de ganho de desempenho são aquelas similares as utilizadas em Data Warehouse, com grande volume de linhas de retorno e/ou com operações agregadas (GROUP BY).

Uma observação importante, quando se pretende utilizar este recurso, é que este tipo de índice utiliza a memória do servidor, apesar da eficiente compactação dos dados o consumo de memória aumenta.

O script abaixo cria uma tabela a partir do banco AdventureWorks.

USE tempdb

go

-- Cria Tabela e importa do banco AdventureWorks

CREATE TABLE dbo.MySalesOrderDetail(

SalesOrderID int NOT NULL,

SalesOrderDetailID int NOT NULL,

CarrierTrackingNumber nvarchar(25) NULL,

OrderQty smallint NOT NULL,

ProductID int NOT NULL,

SpecialOfferID int NOT NULL,

UnitPrice money NOT NULL,

UnitPriceDiscount money NOT NULL,

LineTotal numeric(38, 6) NOT NULL,

rowguid uniqueidentifier NOT NULL,

ModifiedDate datetime NOT NULL)

go

CREATE INDEX NCL_MySalesOrderDetail ON dbo.MySalesOrderDetail (ProductID)

INCLUDE (UnitPrice, OrderQty)

go

-- Importa linhas do banco AdventureWorks

-- ATENÇÃO: esta Query poe levar até 10 minutos

INSERT INTO dbo.MySalesOrderDetail

SELECT S1.* FROM AdventureWorks.Sales.SalesOrderDetail S1

go 100

Vamos executar agora uma consulta na tabela acima, que será resolvida utilizando índice Nonclustered regular do SQL Server.

SET STATISTICS IO ON

go

SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,

SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty

FROM dbo.MySalesOrderDetail

GROUP BY ProductID ORDER BY ProductID

GO

-- Table 'MySalesOrderDetail'. Scan count 3, logical reads 42201

Agora vou criar um Índice Columnstore na tabela e executar a mesma consulta:

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_MySalesOrderDetail_ColumnStore

ON MySalesOrderDetail (UnitPrice, OrderQty, ProductID)

go

SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,

SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty

FROM [dbo].[MySalesOrderDetail]

GROUP BY ProductID

ORDER BY ProductID

-- Table 'MySalesOrderDetail'. Scan count 2, logical reads 540

Comparem ambas as execuções!  O volume de OI reduziu drasticamente de 42.201 páginas lidas para 540 com Columnstore Index!

ATUALIZANDO OS DADOS

O ganho de desempenho observado na consulta contrasta com a limitação na atualização da tabela, veja o que acontece quando tentamos incluir linhas na tabela com Columnstore Index no SQL Server 2014:

INSERT INTO dbo.MySalesOrderDetail

SELECT S1.* FROM AdventureWorks2012.Sales.SalesOrderDetail S1

Msg 35330, Level 15, State 1, Line 25

INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

No SQL Server 2014 tabelas com Columnstore Index não podem sofrer atualização, tem que excluir o índice, atualizar e depois recriar.

No próximo post vou mostrar as melhorias no SQL Server 2016.

Saudações Tricolores,

Landry