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.
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:
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’
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.
Tor, D.WeekNumberOfYear, D.CalendarYear
(58968 row(s) affected)
Table ‘DimProduct’. Scan count 5, logical reads 1804, physical reads 0, read-ahead reads 0, able ‘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 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:
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
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.
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'