quinta-feira, 22 de setembro de 2016

Columnstore Index – 2ª Parte: Melhorias no SQL Server 2016



Na 1ª Parte do post sobre Columnstore Index mostrei as principais características deste índice e suas limitações no SQL Server 2014, para acessar utilize o link abaixo:


Nesta 2ª Parte vou mostrar algumas melhorias feitas no Columnstore Index pela Microsoft no SQL Server 2016, veja a lista abaixo:

- Agora uma tabela pode ter Nonclustered Columnstore Index e ser atualizada, nas versões anteriores este índice era somente leitura.

- Nonclustered Columnstore Index pode conter cláusula de filtro.

- Tabelas in-memory podem ter Columnstore Index.

- Tabelas com Clustered Columnstore Index podem ter chave primária e chave estrangeira.


ATUALIZAÇÃO

Utilizando o mesmo Script da 1ª Parte do post no SQL Server 2016, observamos que a limitação de atualização foi retirada.

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

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_MySalesOrderDetail_ColumnStore_ProductID
ON MySalesOrderDetail (ProductID,UnitPrice, OrderQty)
go

INSERT INTO dbo.MySalesOrderDetail
SELECT S1.* FROM AdventureWorks.Sales.SalesOrderDetail S1

No SQL Server 2016 a atualização ocorre sem problema, já no SQL Server 2014 falha com a mensagem de erro abaixo:

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.


COLUMNSTORE INDEX COM FILTRO

Outra novidade no SQL Server 2016 é a possibilidade de definir um Indice Columnstore com filtro, do mesmo modo que fazemos nos índices Nonclustered desde o SQL Server 2008.

Imagine um cenário onde temos uma tabela de vendas com um grande histórico, o Script abaixo cria e inclui na tabela SalesOrderHearder registros de vendas de 1986 até 2007:

CREATE TABLE dbo.SalesOrderHeader(
SalesOrderID int NOT NULL,
OrderDate datetime NOT NULL,
Status tinyint NOT NULL,
OnlineOrderFlag bit NOT NULL,
SalesOrderNumber nvarchar(25) NOT NULL,
CustomerID int NOT NULL,
SalesPersonID int NULL,
TerritoryID int NULL,
SubTotal money NOT NULL,
TaxAmt money NOT NULL,
Freight money NOT NULL,
TotalDue money NOT NULL,
Comment nvarchar(128) NULL)
go

declare @i int = 1

while @i < 20 begin
       insert dbo.SalesOrderHeader
       select SalesOrderID, DATEADD(YY,@i * -1,OrderDate), Status, OnlineOrderFlag,
       SalesOrderNumber, CustomerID, SalesPersonID, TerritoryID, 
       SubTotal, TaxAmt, Freight, TotalDue, Comment
       from AdventureWorks.Sales.SalesOrderHeader

       set @i += 1
end
go

select max(OrderDate),min(OrderDate)
from dbo.SalesOrderHeader
-- Datas de 1986 a 2007

As consultas de dados históricos tendem a ser bem diferentes dos dados atuais, no histórico muitas linhas e várias operações agregadas (GROUP BY), já nos dados recentes poucos registros e consultas mais simples.  A ideia seria criar um índice Columnstore para o histórico, mais eficiente para este cenário, e índice Nonclustered para os dados recentes. Considerei “dados recentes” os dois últimos anos 2006 e 2007, já o histórico até 2005.

-- Indice Columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX IXColumn_SalesOrderHeader_OrderDate
ON dbo.SalesOrderHeader (OrderDate,SalesOrderID,SalesOrderNumber,CustomerID,SubTotal)
WHERE OrderDate < '20060101'

-- Indice Nonclustered
CREATE INDEX IX_SalesOrderHeader_OrderDate
ON dbo.SalesOrderHeader (OrderDate)
INCLUDE (SalesOrderID,SalesOrderNumber,CustomerID,SubTotal)
WHERE OrderDate >= '20060101'

Consultas com datas a partir de 2006 irão utilizar o índice Nonclustered, com datas anteriores a 2006 Columnstore.

-- Utiliza o indice Nonclustered
SELECT CustomerID,sum(SubTotal) CustomerTotal, count(*) OrderQt
FROM dbo.SalesOrderHeader
WHERE OrderDate >= '20060401' AND OrderDate < '20060501'
GROUP BY CustomerID

-- Utiliza o indice Columnstore
SELECT CustomerID,sum(SubTotal) CustomerTotal, count(*) OrderQt
FROM dbo.SalesOrderHeader
WHERE OrderDate >= '20020401' AND OrderDate < '20020501'
GROUP BY CustomerID

O que acontece se o intervalo de datas conter registros de ambos os índices? Vamos analisar o plano de execução abaixo:

-- Utiliza ambos os indices
SELECT CustomerID,sum(SubTotal) CustomerTotal, count(*) OrderQt
FROM dbo.SalesOrderHeader
WHERE OrderDate >= '20050101' AND OrderDate < '20070101'
GROUP BY CustomerID

O Otimizador de Consultas do SQL Server 2016 utiliza ambos os índices e posteriormente concatena o resultado!


CONCLUSÃO

O SQL Server 2016 retirou as principais limitações do Columnstore Index, aumentando os cenários de uso desta funcionalidade.

Até o próximo post.

Saudações Tricolores,
Landry