Compartilhar via

Entendendo o Column Store Index no SQL Server 2012 (pt-BR)

  • Artigo

  

Introdução

Com a chegada do novo SQL Server 2012 Codename “Denali”, algumas features foram incluídas nesta nova release, sendo um dos grandes melhoramentos da Engine do banco de dados é um novo recurso denominado 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 do novo recurso, podemos utilizá-lo preferencialmente em esquemas Star Joins (Modelo Estrela), agregações, filtros, grupos de dados e principalmente tabelas de fatos (Fact Table) 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ão 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), o “batch mode”, que pode realizar um processamento de 1.000 linhas enquanto no nossso modelo convencional 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.

http://luanmorenodba.files.wordpress.com/2012/03/image_thumb12.png?w=612&h=357

http://luanmorenodba.files.wordpress.com/2012/03/des_thumb.jpg?w=363&h=268

***Pontos Positivos:

  • Batch Mode = Blocos de 1.000 linhas que são retornados a consulta ao invés 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 nos 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’

http://luanmorenodba.files.wordpress.com/2012/03/image_thumb13.png?w=576&h=65

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.

http://luanmorenodba.files.wordpress.com/2012/03/image6_thumb.png?w=1153&h=174

Analisando o que foi descrito anteriormente, esse seria um ótimo cenário para que seja implementado o ColumnStore Index, pois no plano de execução existe operações de agregações, agrupamento de dados além de envolver uma tabela com grande número de registros, a tabela fato  (Fact Table). Olhando um pouco mais atentamente para o plano de execução gerado, observa-se que o operador **Hash Match **é utilizado quando é requerido ao QO (Query Optimizer) operações de agregações, joins e para retirar valores duplicados da consulta, o que, neste caso ilustrado acima, está custando 41% do plano total da consulta.

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:

http://luanmorenodba.files.wordpress.com/2012/03/image_thumb15.png?w=319&h=67

http://luanmorenodba.files.wordpress.com/2012/03/image_thumb16.png?w=460&h=363

De fato vemos que o QO utilizou o novo “Batch Mode” 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

http://luanmorenodba.files.wordpress.com/2012/03/image_thumb17.png?w=1007&h=216

Definitivamente observa-se um grande ganho de performance entre as duas consultas, porém para realmente termos uma real idéia de ganho efetivo, é necessário também observar 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: Houve, no exemplo acima, uma diferença considerável 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'

Para maiores informações verifique também as informações da documentação oficial:

Índices Columnstore: http://msdn.microsoft.com/pt-br/library/gg492088.aspx