Entenda o ColumnStore Index!

Introdução

Com a chegada do novo SQL Server 2012 Codename “Denali”, algumas features foram incluídas nesta nova release, um dos grandes melhoramentos da Engine do banco de dados é o novo recurso chamado ColumnStore Indexes.

O grande foco desta feature é possibilitar uma maior performance  de consultas realizadas dentro de um Data Warehouse, ou seja dentro de um sistema OLAP (Online Analytical Processing). O aumento de performance pode ser de 10x a 100x maior.

Para se beneficiar das qualidades desse novo recurso, podemos utilizá-lo preferencialmente em esquemas Star Joins, agregações, filtros, grupos de dados e principalmente tabelas de fatos (Fact Tabe) que utilizem esses recursos descritos acima.

Quando utilizamos o ColumnStore Index ,  a tabela na qual receberá o índice se tornará “Read-Only, sendo assim os dados armazenados neste índice possuirá uma compressão de colunas ao invês de compressão de linhas, com isso temos um grande ganho de performance e armazenamento. Alêm disso, temos um novo modo de execução dentro do QO (Query Optimizer), “batch mode” que pode realizar um processamento de 1.000 linhas enquanto no nossso usual modelo teriamos um  processamento linha-a-linha e dependendo dos fatores e filtros que forem utilizados na consulta, esse índice poderá se beneficiar da nova tecnologia “segment elimination” tendo um algoritmo que pode eliminar os dados que não serão selecionados (segmentados) reduzindo assim grandemente o impacto de I/O.

Row Store x Column Store

Antigamente nas versões anteriores do SQL Server, os dados eram armazendos em modo de linhas, agora com este novo recurso, os dados podem ter um maior aproveitamento, aqui veremos alguns pontos importantes para a implementação deste recurso.

Pontos Positivos:

* Batch Mode = Blocos de 1.000 linhas que são retornados a consulta ao ínves de linha-a-linha.

* Algoritmo de Redução de Custo de I/O, tornando a consulta assim mais eficiente.

* “Segment  Elimination” de acordo com os filtros passados ao QO, possibilitará trazer a consulta mais rápido, isso porque o mecanismo possibilita a quebra da partição em diversas partes selecionando assim os dados de uma forma mais eficiente.

Pontos Negativos:

* A tabela se torna “Read-Only”, não podendo ser permitido operações de DML tais como: INSERT/UPDATE/DELETE e MERGE. Sendo assim só é possivel alterá-la se o ColumnsStore Index for desabilitada ou excluída.

* Algumas operações não são possíveis no novo modo “Batch Mode” como: Outer Joins, Join entre strings, NOT IN, IN, EXISTS e agregações escalares.

* Se houver pressão na memória ou um grande uso de paralelismo, provavelmente o QO utilizirá o modo linha-a-linha para a execução da consulta.

Candidatos à ColumnStore Indexes

* Tabelas contendo milhões a bilhões de registros (Fact Tables).

* Scan x Seek (ColumnStore Indexes não suporta operações de Seek, somente Scan).

* Operações de agregação como SUM(), AVG, joins e filtros utilizados na pesquisa.

Definições do ColumnStore Index

* Só podem ser índices non-clustered e non-unique.

* Não podem ser criados em Views, Indexes Views e Sparse Columns.

* Não podem possuir relacionamento, logo não podem atuar como Primary Key ou Foreign Key.

* Sem conceito da opção INCLUDE na criação do índice non-clustered.

* Sem permissão da utilização do operador Sort ou seja ordernação dos dados ASC ou DESC.

* Varchar(MAX), NVarchar(Max), Lob, FileStream, Numeric e Decimal com precisão >18 e Datetimeoffset >2 não são permitidos.

Demos

Usando o SSMS do SQL Server 2012 e o banco de dados AdventureWorksDW2012 que pode ser baixado aqui –
http://msftdbprodsamples.codeplex.com/releases/view/55330

iremos visualizar como o ColumnStoreIndexes podem no dar um bom ganho de performance.

USE AdventureWorksDW2012

go

 

DBCC FREEPROCCACHE

 

SET STATISTICS TIME ON

SET STATISTICS IO ON

Agora iremos visualizar os índices criados da tabela dbo.FactProductIventory

sp_helpindex‘FactProductInventory’

Realizando a seguinte consulta vemos que:

SELECT DP.EnglishProductName AS NomeProduto,

DP.Color AS Cor,

D.CalendarYear AS Ano,

AVG(F.UnitCost) AS Preco,

D.WeekNumberOfYear AS QtdSemamas,

SUM(F.UnitsOut) AS QtdUnidades

FROM FactProductInventory AS F

INNER JOIN DimProduct AS DP

ON F.ProductKey = DP.ProductKey

INNER JOIN DimDate AS D

ON D.DateKey = F.DateKey

WHERE WeekNumberOfYear BETWEEN 20 AND 50

GROUP BY DP.EnglishProductName, DP.Color, D.WeekNumberOfYear, D.CalendarYear

(58968 row(s) affected)
Table ‘DimProduct’. Scan count 5, logical reads 1804, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DimDate’. Scan count 5, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘FactProductInventory’. Scan count 5, logical reads 6396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Analisando o que foi descrito anteriormente, esse seria um ótimo cenário para que seja implementado o ColumnStore Indexe, isso porque possúimos operações de agregações, agrupamento de dados e ainda esta no caso é tabela de fatos (Fact Table). Olhando um pouco mais atentamente dento do plano de execução gerado, vemos que o operador Hash Match operador esse que é utilizando quando é demandado para o QO operações de agregações, joins e para retirar valores duplicados da consulta, está custando 41% do plano total da consulta acima.

Sendo assim criaremos o ColumnStore Index na tabela dbo.FactProductIventory.

CREATE NONCLUSTERED COLUMNSTORE INDEX CSIidxNCL_FactProductInventory

ON dbo.FactProductInventory

(

     ProductKey,

     DateKey,

     UnitCost,

     UnitsOut

)

Assim temos o novo plano de execução mostrando as seguintes informações:

De fato vemos que o QO utilizou o novo “Mode Batch” para retornar os valores em lotes, sendo assim comparando as consultas utilizando a Hint – OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX), temos:

SELECT DP.EnglishProductName AS NomeProduto,

DP.Color AS Cor,

D.CalendarYear AS Ano,

AVG(F.UnitCost) AS Preco,

D.WeekNumberOfYear AS QtdSemamas,

SUM(F.UnitsOut) AS QtdUnidades

FROM FactProductInventory AS F

INNER JOIN DimProduct AS DP

ON F.ProductKey = DP.ProductKey

INNER JOIN DimDate AS D

ON D.DateKey = F.DateKey

WHERE WeekNumberOfYear BETWEEN 20 AND 50

GROUP BY DP.EnglishProductName, DP.Color, D.WeekNumberOfYear, D.CalendarYear

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

 

SELECT DP.EnglishProductName AS NomeProduto,

DP.Color AS Cor,

D.CalendarYear AS Ano,

AVG(F.UnitCost) AS Preco,

D.WeekNumberOfYear AS QtdSemamas,

SUM(F.UnitsOut) AS QtdUnidades

FROM FactProductInventory AS F

INNER JOIN DimProduct AS DP

ON F.ProductKey = DP.ProductKey

INNER JOIN DimDate AS D

ON D.DateKey = F.DateKey

WHERE WeekNumberOfYear BETWEEN 20 AND 50

GROUP BY DP.EnglishProductName, DP.Color, D.WeekNumberOfYear, D.CalendarYear

Definitivamente vemos um grande ganho de performance entre as duas consultas, porém para realmente termos uma real ideía de ganho efetivo, iremos ver as estatísticas de comparação de tempo e I/O.

Consulta Sem ColunmStore Indexes

(58968 row(s) affected)
Table ‘DimProduct’. Scan count 5, logical reads 1804, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DimDate’. Scan count 5, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘FactProductInventory’. Scan count 5, logical reads 6396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Consulta Com ColumnStore Indexes

(58968 row(s) affected)
Table ‘DimDate’. Scan count 5, logical reads 130, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DimProduct’. Scan count 5, logical reads 1804, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘FactProductInventory’. Scan count 4, logical reads 695, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Obs: Saimos de 6.496 para 695 leituras lógicas no nível folha do índice.

Mais Informações

Como informado anteriormente, quando é criado o ColumnStore Index em uma tabela, essa tabela passa a ser “Read-Only” com isso se tentarmos alterar ou adicionar um novo registro a essa tabela, veremos que:

BEGIN TRANSACTION

SELECT @@TRANCOUNT

UPDATE dbo.FactInternetSales

     SET UnitPrice = ’2500′

WHERE SalesOrderNumber = ‘SO43701′

‘Msg 35330, Level 15, State 1, Line 1
UPDATE statement failed because data cannot be updated in a table with a columnstore index.
Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.’

ROLLBACK TRANSACTION

GO

Desabilitando o índice e tentando novamente….

ALTER INDEX CSIidxNCL_FactInternetSales

ON dbo.FactInternetSales DISABLE

GO

BEGIN TRANSACTION

SELECT @@TRANCOUNT

UPDATE dbo.FactInternetSales

     SET UnitPrice = ’1300′

WHERE SalesOrderNumber = ‘SO43701′

SELECT *

FROM dbo.FactInternetSales

WHERE SalesOrderNumber = ‘SO43701′

–   COMMIT TRANSACTION

ROLLBACKTRANSACTION

Na versão RC0 do SQL Server 2012 Codename Denali, não era possível utilizar a opção REBUILD PARTITION, para poder utilizar novamente o ColumnStore Index na tabela era necessário realizar a exclusão e criação novamente do índice, agora com a versão RTM é possivel realizar o REBUILD.

ALTER INDEX CSIidxNCL_FactInternetSales

ON dbo.FactInternetSales REBUILD PARTITION = ALL

GO

ou se optar poderá excluir e criá-lo novamente

DROP INDEX FactInternetSales.CSIidxNCL_FactInternetSales

CREATE NONCLUSTERED COLUMNSTORE INDEX CSIidxNCL_FactInternetSales

ON dbo.FactInternetSales

(

     ProductKey,

     UnitPrice,

     ProductStandardCost,

     SalesAmount,

     TaxAmt,

     Freight

)

Forçando a utilização do ColumnStore Index, veremos agora que o dado foi atualizado com sucesso.

SELECT*

FROM dbo.FactInternetSales WITH(INDEX(CSIidxNCL_FactInternetSales))

WHERE SalesOrderNumber = ‘SO43701′

Vemos aqui que podemos ter uma grande ganho de perfomance em determinada situação, o importante é sempre analisar bem o cenário no qual você está e nunca se esqueça disso não há a melhor opção, mais sim a que se encaixa melhor em ao seu problema.

Marcado: ColumnStore Indexes, Curiosidades, Novidades SQL Server, SQL Server 2012, Virtual PASS BR

Publicado em: ColumnStore Indexes, SQL Server 2012