ColumnStore Index Vs Covering Index – O Melhor Índice para seu Dw!

Posted on maio 23, 2013

5


Introdução

O ColumnStore Index é um recurso que já foi abordado diversas vezes em meu blog – https://luanmorenodba.wordpress.com/2012/03/16/entenda-o-columnstore-indexes/ e https://luanmorenodba.wordpress.com/2012/06/05/desvendando-o-columnstore-index/. Esse recurso está totalmente ligado a quantidade de registros que estão presentes na tabela de Fato ou seja, na versão atual do SQL Server temos o mesmo totalmente voltado para DW’s, com isso trabalhamos com ele em gigantescas tabelas para melhorar a performance em consultas contendo agregações, sumarizações, filtros, lembrando que o mesmo está desenhado para ser melhor aplicado em Star-Joins (Esquema Estrela) na modelagem de ambientes OLAP.

Porém nesse anúncio http://www.sqlperformance.com/2013/04/sql-performance/next-ver-announcement temos que o SQL Server 2012 R3 terá o ColumnStore Index como Primary Key, com isso teremos muitas mudanças pode ter certeza e veremos todas elas em detalhes aqui.

O ColumnStore Index

O Intuito do ColumnStore Index é promover um maior processamento de consultas em Data Warehouse’s. A grande vantagem é que, os dados agora são armazenados em forma de colunas, com isso a busca dos mesmos em disco é muito mais eficiente fazendo com que menos leituras físicas sejam realizadas além de possuir novos algoritmos de busca, compressão e paralelismo, mais informações estão no post’s acima.

O Covering Index

O “Cover Index” é um termo utilizado para descrever uma técnica utilizada para a melhora de consultas quando possuímos Key Lookups ou seja, quando a pesquisa em um índice secundário (Non-Clustered) não é satisfeita e o Q.O (Query Optimizer) necessita ir no índice primário (Clustered Index) para buscar o resto das informações necessárias da consulta, um ótimo artigo para leitura – https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

Cenário

Porém em versões anteriores do SQL Server não tinhamos o ColumnStore Index, com isso era utilizado o “Covering Index” para realizar a otimização das consultas, A questão aqui é realmente ver se o ColumStore Index cumpre o que realmente promete, deixando claro que o Cover Index é um ótimo recurso e muito eficiente, não entraremos em detalhe nesse post.

No cenário, possuímos uma tabela de Fato em um Dw chamado dbo.FatoVendasInternet e essa tabela possui 95.432.600 registros. Com isso testaremos a eficiência dos dois índices, e que a disputa começe…

Demonstração e Comparativos

Primeiro iremos realizar a criação dos índices ..

Criação do ColumnStore Index

CREATE NONCLUSTERED COLUMNSTORE INDEX ColumnStoreIndex_FatoVendasInternet

ON FatoVendasInternet

(

    ID ,

    ProductKey ,

    OrderDateKey ,

    CustomerKey ,

    CurrencyKey ,

    SalesTerritoryKey ,

    SalesOrderNumber ,

    OrderQuantity ,

    UnitPrice ,

    TotalProductCost ,

    SalesAmount ,

    Freight ,

    ShipDate

)

go

Criação do “Covering Index”

CREATE NONCLUSTERED INDEX CoveringIndex_FatoVendasInternet

ON FatoVendasInternet(ProductKey, CustomerKey, SalesTerritoryKey, SalesOrderNumber, ShipDate)

INCLUDE (OrderQuantity, UnitPrice, TotalProductCost, SalesAmount, Freight)

go

Após a criação dos índices, iremos habilitar as estatísticas para mensurar as informações que serão disponibilizadas pelo Q.O

SET STATISTICS TIME ON

SET STATISTICS IO ON

Vendo as informações da tabela..

EXEC DwVendas.dbo.sp_help‘FatoVendasInternet’

Capture

(Figura 1 – Índices criados na tabela FatoVendasInternet – ColumStore Index e Covering Index.)

Consulta 1 – Filtros por SalesTerritoryKey, CustomerKey e SalesOrderNumber

Nessa consulta temos o retorno de somente 1 registo ou seja, possuímos uma alta seletividade, comparando os índices temos que….

SELECT ProductKey,

          CustomerKey,

          SalesTerritoryKey,

          SalesOrderNumber,

          MAX(OrderQuantity) AS MaxOrderQuantity,

          MIN(OrderQuantity) AS MinOrderQuantity,

          COUNT(DISTINCT OrderQuantity) AS CountDistinctOrderQuantity,

          AVG(UnitPrice) AS AvgUnitPrice,

          SUM(UnitPrice) AS SumUnitPrice,

          AVG(TotalProductCost) AS AvgTotalProductCost,

          SUM(TotalProductCost) AS SumTotalProductCost,

          AVG(SalesAmount) AS AvgSalesAmount,

          SUM(SalesAmount) AS SumSalesAmount,

          MIN(Freight) AS MinFreight,

          MAX(Freight) AS MaxFreigh

FROM FatoVendasInternet

WHERE SalesTerritoryKey BETWEEN 100 AND 500

       AND CustomerKey BETWEEN 100 AND 875

       AND SalesOrderNumber = 5

GROUP BY ProductKey,

            CustomerKey,

            SalesTerritoryKey,

            SalesOrderNumber

go

SELECT ProductKey,

          CustomerKey,

          SalesTerritoryKey,

          SalesOrderNumber,

          MAX(OrderQuantity) AS MaxOrderQuantity,

          MIN(OrderQuantity) AS MinOrderQuantity,

          COUNT(DISTINCT OrderQuantity) AS CountDistinctOrderQuantity,

          AVG(UnitPrice) AS AvgUnitPrice,

          SUM(UnitPrice) AS SumUnitPrice,

          AVG(TotalProductCost) AS AvgTotalProductCost,

          SUM(TotalProductCost) AS SumTotalProductCost,

          AVG(SalesAmount) AS AvgSalesAmount,

          SUM(SalesAmount) AS SumSalesAmount,

          MIN(Freight) AS MinFreight,

          MAX(Freight) AS MaxFreigh

FROM FatoVendasInternet

WHERE SalesTerritoryKey BETWEEN 100 AND 500

       AND CustomerKey BETWEEN 100 AND 875

       AND SalesOrderNumber = 5

GROUP BY ProductKey,

            CustomerKey,

            SalesTerritoryKey,

            SalesOrderNumber

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

image

(Figura 2 – Quantidade de Leituras Físicas (Disco).)

image

(Figura 3 – Quantidade de Leituras Lógicas (Memória).)

image

image

image

image

(Figura 4 – ColumnStore Index = 9% e Covering Index = 91%.)

Consulta 2 – Filtros por SalesTerritoryKey, CustomerKey, ProductKey e Freight

Nessa consulta temos o retorno de 52 registo ou seja, possuímos ainda uma alta seletividade, comparando os índices temos que….

SELECT ProductKey,

          CustomerKey,

          SalesTerritoryKey,

          SalesOrderNumber,

          MAX(OrderQuantity) AS MaxOrderQuantity,

          MIN(OrderQuantity) AS MinOrderQuantity,

          COUNT(DISTINCT OrderQuantity) AS CountDistinctOrderQuantity,

          AVG(UnitPrice) AS AvgUnitPrice,

          SUM(UnitPrice) AS SumUnitPrice,

          AVG(TotalProductCost) AS AvgTotalProductCost,

          SUM(TotalProductCost) AS SumTotalProductCost,

          AVG(SalesAmount) AS AvgSalesAmount,

          SUM(SalesAmount) AS SumSalesAmount,

          MIN(Freight) AS MinFreight,

          MAX(Freight) AS MaxFreigh

FROM FatoVendasInternet

WHERE SalesTerritoryKey BETWEEN 100 AND 10500

       AND CustomerKey = 6

       AND ProductKey > 200

GROUP BY ProductKey,

            CustomerKey,

            SalesTerritoryKey,

            SalesOrderNumber

HAVING MAX(Freight) BETWEEN 369.4584 AND 487.6408

GO

SELECT ProductKey,

          CustomerKey,

          SalesTerritoryKey,

          SalesOrderNumber,

          MAX(OrderQuantity) AS MaxOrderQuantity,

          MIN(OrderQuantity) AS MinOrderQuantity,

          COUNT(DISTINCT OrderQuantity) AS CountDistinctOrderQuantity,

          AVG(UnitPrice) AS AvgUnitPrice,

          SUM(UnitPrice) AS SumUnitPrice,

          AVG(TotalProductCost) AS AvgTotalProductCost,

          SUM(TotalProductCost) AS SumTotalProductCost,

          AVG(SalesAmount) AS AvgSalesAmount,

          SUM(SalesAmount) AS SumSalesAmount,

          MIN(Freight) AS MinFreight,

          MAX(Freight) AS MaxFreigh

FROM FatoVendasInternet

WHERE SalesTerritoryKey BETWEEN 100 AND 10500

       AND CustomerKey = 6

       AND ProductKey > 200

GROUP BY ProductKey,

            CustomerKey,

            SalesTerritoryKey,

            SalesOrderNumber

HAVING MAX(Freight) BETWEEN 369.4584 AND 487.6408

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

image

(Figura 5 – Quantidade de Leituras Físicas (Disco).)

image

(Figura 6 – Quantidade de Leituras Lógicas (Memória).)

image

image

image

image

(Figura 7 – ColumnStore Index = 10% e Covering Index = 90%.)

Consulta 3 – Filtros por CustomerKey, Freight, OrderQuantity e UnitPrice

Nessa consulta temos o retorno de 109 registo ou seja, possuímos ainda uma alta seletividade novamente, comparando os índices temos que….

SELECT ProductKey,

          CustomerKey,

          SalesTerritoryKey,

          SalesOrderNumber,

          MAX(OrderQuantity) AS MaxOrderQuantity,

          MIN(OrderQuantity) AS MinOrderQuantity,

          COUNT(DISTINCT OrderQuantity) AS CountDistinctOrderQuantity,

          AVG(UnitPrice) AS AvgUnitPrice,

          SUM(UnitPrice) AS SumUnitPrice,

          AVG(TotalProductCost) AS AvgTotalProductCost,

          SUM(TotalProductCost) AS SumTotalProductCost,

          AVG(SalesAmount) AS AvgSalesAmount,

          SUM(SalesAmount) AS SumSalesAmount,

          MIN(Freight) AS MinFreight,

          MAX(Freight) AS MaxFreigh

FROM FatoVendasInternet

WHERE CustomerKey < 100

GROUP BY ProductKey,

            CustomerKey,

            SalesTerritoryKey,

            SalesOrderNumber

HAVING MIN(Freight) > 132.0478

       AND MAX(Freight) < 284.4891

       AND MAX(OrderQuantity) = 22

       AND AVG(UnitPrice) BETWEEN 150.758 AND 757.758

go

SELECT ProductKey,

          CustomerKey,

          SalesTerritoryKey,

          SalesOrderNumber,

          MAX(OrderQuantity) AS MaxOrderQuantity,

          MIN(OrderQuantity) AS MinOrderQuantity,

          COUNT(DISTINCT OrderQuantity) AS CountDistinctOrderQuantity,

          AVG(UnitPrice) AS AvgUnitPrice,

          SUM(UnitPrice) AS SumUnitPrice,

          AVG(TotalProductCost) AS AvgTotalProductCost,

          SUM(TotalProductCost) AS SumTotalProductCost,

          AVG(SalesAmount) AS AvgSalesAmount,

          SUM(SalesAmount) AS SumSalesAmount,

          MIN(Freight) AS MinFreight,

          MAX(Freight) AS MaxFreigh

FROM FatoVendasInternet

WHERE CustomerKey < 100

GROUP BY ProductKey,

            CustomerKey,

            SalesTerritoryKey,

            SalesOrderNumber

HAVING MIN(Freight) > 132.0478

       AND MAX(Freight) < 284.4891

       AND MAX(OrderQuantity) = 22

       AND AVG(UnitPrice) BETWEEN 150.758 AND 757.758

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX) 

image

(Figura 8 – Quantidade de Leituras Físicas (Disco).)

image

(Figura 9 – Quantidade de Leituras Lógicas (Memória).)

image

image

image

image

(Figura 10 – ColumnStore Index = 9% e Covering Index = 91%.)

As Leituras Físicas

Ou seja, tivemos uma quantidade muito pequena de leituras físicas no ìndice ColumnStore, isso porque o dicionário dentro desse índice passa a ser muito efetivo fazendo com que mais registros sejam colocados em páginas, com isso ,o algoritmo de alocação de memória e compressão se beneficia disso.

As Leituras Lógicas

Tivemos uma drástica diminuição de leituras lógicas tambêm, isso se torna possível porque o ColumnStore Index possui um algoritmo para o melhora de armazenamento dos dados em memória. Com isso uma menor quantidade de páginas precisa ser lida, não se esquecendo que isso ocorre porque o índice é dividido primeiramente em RowGroups (Horizontalmente) em partições de 1 em 1 milhão, após isso os dados são particionados novamente e compressos em Segmentos (Verticalmente) e colocados em LOB’s separados.

Os Planos de Execução

Os planos mostram que o ColumnStore Index é muito mais efetivo, lembrando que o mesmo está utilizando em todas as pesquisas o novo modo de execução “Batch Mode” que faz com que a movimentação dos registros do disco para a memória aconteça em Lotes [Chunks] de mil em mil registros.

Conclusão

Como ainda o ColumnStore Index não aceita operações de DML com isso não temos fragmentação, diferente do Covering Index, outro ponto decisivo é que o ColumnStore Index se encaixa em qualquer consulta que seja realizada na tabela porque as colunas só seram acessadas se as mesmas forem solicitadas na pesquisa, diferente do Covering Index que provavelmente terá mais que um para satisfazer as diversas consultas em seu Dw, com isso seu custo de gerenciamento, manutenção e espaço em disco será muito maior

Com isso temos realmente a prova de que o ColumnStore Index cumpre o que promete, ele é sem dúvida o melhor índice para o seu Dw.

Referência

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

http://www.sqlskills.com/blogs/joe/row-and-batch-execution-modes-and-columnstore-indexes/