Fala galera, o ColumnStore Index é um novo formato de índice que foi lançado junto ao SQL Server 2012, este índice usa um padrão de compressão de dados proprietário da Microsoft e altera o formato de armazenamento dos dados nas páginas do índice. A primeira vez que um registro é inserido no índice, ele registra o dado bruto, qualquer outra aparição deste mesmo dado dentro do índice, o SQL faz um apontamento de memória para o primeiro registro, diminuindo significativamente o tamanho da página com os índices.
Este novo formato de índice não chegou para substituir os já convencionais e úteis Clustered e Non-Clustered, ele vem para atender um outro cenário. O armazenamento do ColumnStore Index altera a escrita dos dados do índice que estamos acostumados a ver em um padrão linear (como a figura abaixo).
Para um formato colunar – por isso o nome ColumnStore – armazenando todos os registros da coluna em uma mesma página. Uma representação visula seria como a imagem abaixo:
Os ganhos de performance com o uso correto do ColumnStore Index varia entre 10 e 100X. Podendo, em alguns casos reais que já presenciei, chegar a retornos 400X mais rápidos. Porém o ColumnStore Index não é só maravilhas. A utilização deste índice em uma tabela a transforma em Read Only, impedindo manutenção nos dados já existentes. Este cenário de dados como somente leitura nos remete à ambientes de Data Warehouse, onde a informação armazenada sobre manutenção incremental em determinados momentos do ciclo. Em alguns casos sendo incrementado somente uma vez por noite, em outros cenários somente uma atualização semanal, em um terceiro podendo ser uma vez por mês. Isso varia de acordo com a necessidade da área de negócios.
Agora, se a tabela está em um formato Read Only, como podemos inserir dados incrementais nela???
Pensando sobre como implementar estes incrementos, vem à mente 3 possibilidades. Podemos desabilitar, inserir e reabilitar o índice, remover, inserir e recriar o índice ou então trabalhar com Particionamento de Tabelas, onde temos as partições com o índice e uma tabela onde serão inseridos os dados.
Para colocar em comparação estes três cenários, montei um ambiente de teste com aproximadamente 35Milhões de linhas e fiz comparativo entre eles.
Veja o comparativo abaixo entre Logical Read dos três cenários, e também o tempo necessário para realizar cada atividade.
O teste consistiu em criar uma tabela com 33milhões de registros e aplicar o ColumnStore Index nesta tabela. Em seguida, adicionar mais 1.6Milhões de linhas… Realizamos o teste no mesmo ambiente 2 vezes, e tiramos a média tanto de Logical Reads quanto de Elapsed Time do processo.
Os resultados provam que a melhor solução, disparada, é a utilização de Partition Table. A alguns meses eu havia escrito uma série com 7 posts sobre o assunto, e acredito que podem ser aproveitados para o entendimento do que fiz neste exemplo.
Partition Table (Particionamento de Tabelas) – Parte#1
Partition Table (Cenários/Benefícios) – Parte#2
Partition Table (Definições/Terminologias) – Parte#3
Partition Table (Criando Filegroup) – Parte#4
Partition Table (Criando Partition Function) – Parte#5
Partition Table (Criando o Partition Scheme) – Parte#6
Partition Table (Criando a tabela) – Parte#7
Caso alguém queira simular o processo que utilizei, segue abaixo a criação do ambiente e a população das tabelas com poucos dados. É claro que para você simular a mesma coisa que fiz aqui, você precisa adaptar este código abaixo para seu cenário.
/*******************************************/
/*********** CRIAÇÃO DO AMBIENTE ***********/
/*******************************************/
CREATE DATABASE ngrSolutionsDW
GO
USE ngrSolutionsDW
GO
/*******************************************/
/*********** LIMPEZA DO AMBIENTE ***********/
/*******************************************/
USE ngrSolutionsDW
GO
DROP SEQUENCE seq_Codigo
GO
DROP TABLE tabelaProducao
GO
DROP TABLE tabelaProducao_v2
GO
DROP PARTITION SCHEME ps_DataAtualizacao
GO
DROP PARTITION FUNCTION pf_DataAtualizacao
GO
/*******************************************/
/********** POPULAÇÃO DO AMBIENTE **********/
/*******************************************/
CREATE TABLE tabelaProducao(
id INT NOT NULL
, idOrigem INT NOT NULL
, nome VARCHAR(20) NOT NULL
, endereco VARCHAR(30) NOT NULL
, dataCadastro date NOT NULL
, dataAtualizacao date NOT NULL )
ON [PRIMARY]
GO
ALTER TABLE tabelaProducao WITH CHECK ADD
CONSTRAINT [validarCodigo_V1_Check] CHECK
(dataAtualizacao >= '2010-01-01' and dataAtualizacao < '2013-03-01')
GO
ALTER TABLE tabelaProducao CHECK CONSTRAINT [validarCodigo_V1_Check]
GO
CREATE SEQUENCE seq_Codigo AS INT
INCREMENT BY 1
minvalue 1
maxvalue 10000
GO
/* 1K registros de SETEMBRO 2012 */
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
dateadd(month,-5, convert(datetime, getdate())),
dateadd(month,-5, convert(datetime, getdate())))
go 1000
/* 1K registros de OUTUBRO 2012 */
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
dateadd(month,-4, convert(datetime, getdate())),
dateadd(month,-4, convert(datetime, getdate())))
go 1000
/* 1K registros de NOVEMBRO 2012 */
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
dateadd(month,-3, convert(datetime, getdate())),
dateadd(month,-3, convert(datetime, getdate())))
go 1000
/* 1K registros de DEZEMBRO 2012 */
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
dateadd(month,-2, convert(datetime, getdate())),
dateadd(month,-2, convert(datetime, getdate())))
go 1000
/* 1K registros de JANEIRO 2013 */
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
dateadd(month,-1, convert(datetime, getdate())),
dateadd(month,-1, convert(datetime, getdate())))
go 1000
/* 500 registros de FEVEREIRO 2013 */
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
getdate(), getdate())
go 500
SET STATISTICS IO ON; SET STATISTICS TIME ON
GO
/**************************************************************************/
/**************************** DISABLE / REBUILD ***************************/
/**************************************************************************/
/*******************************************/
/************ CRIAÇÃO DO INDEX *************/
/*******************************************/
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_csi_tabelaProducao]
ON tabelaProducao ( id, nome, endereco, dataCadastro, dataAtualizacao )
GO
/*******************************************/
/******** INSERIR NA TABELA DE PROD ********/
/*******************************************/
/* +1 em FEVEREIRO 2013 */
-- Forçar o erro por causa que a tabela está com ColumnStore Index
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
getdate(), getdate())
/*******************************************/
/************ DISABLE / REBUILD ************/
/*******************************************/
ALTER INDEX idx_csi_tabelaProducao ON tabelaProducao DISABLE
GO
ALTER INDEX idx_csi_tabelaProducao ON tabelaProducao REBUILD
GO
/*******************************************/
/************ POPULAR A TABELA *************/
/*******************************************/
ALTER INDEX idx_csi_tabelaProducao ON tabelaProducao DISABLE
GO
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
dateadd(month,-4, convert(datetime, getdate())), getdate())
go 1000
ALTER INDEX idx_csi_tabelaProducao ON tabelaProducao REBUILD
GO
/**************************************************************************/
/****************************** DROP / CREATE *****************************/
/**************************************************************************/
/*******************************************/
/************ CRIAÇÃO DO INDEX *************/
/*******************************************/
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_csi_tabelaProducao]
ON tabelaProducao ( id, nome, endereco, dataCadastro, dataAtualizacao )
GO
/*******************************************/
/******** INSERIR NA TABELA DE PROD ********/
/*******************************************/
/* +1 em FEVEREIRO 2013 */
-- Forçar o erro por causa que a tabela está com ColumnStore Index
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
getdate(), getdate())
/*******************************************/
/************** DROP / CREATE **************/
/*******************************************/
DROP INDEX [idx_csi_tabelaProducao] ON tabelaProducao
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_csi_tabelaProducao]
ON tabelaProducao ( id, nome, endereco, dataCadastro, dataAtualizacao )
GO
/*******************************************/
/************ POPULAR A TABELA *************/
/*******************************************/
DROP INDEX [idx_csi_tabelaProducao] ON tabelaProducao
GO
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
dateadd(month,-4, convert(datetime, getdate())), getdate())
go 1000
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_csi_tabelaProducao]
ON tabelaProducao ( id, nome, endereco, dataCadastro, dataAtualizacao )
GO
/**************************************************************************/
/***************************** PARTITION TABLE ****************************/
/**************************************************************************/
/*******************************************/
/********** CRIAÇÃO DAS PARTIÇÕES **********/
/*******************************************/
CREATE PARTITION FUNCTION [pf_DataAtualizacao](date) AS RANGE RIGHT
FOR VALUES ('2012-09-01','2012-10-01','2012-11-01',
'2012-12-01','2013-01-01','2013-02-01','2013-03-01')
GO
CREATE PARTITION SCHEME [ps_DataAtualizacao]
AS PARTITION [pf_DataAtualizacao] ALL TO ([PRIMARY])
GO
/*******************************************/
/************ CRIAÇÃO DOS INDEX ************/
/*******************************************/
CREATE CLUSTERED INDEX [idx_DataCodigo] ON tabelaProducao(DataAtualizacao)
ON ps_DataAtualizacao(dataAtualizacao)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_csi_tabelaProducao]
ON tabelaProducao ( id, nome, endereco, dataCadastro, dataAtualizacao )
GO
/*******************************************/
/******** INSERIR NA TABELA DE PROD ********/
/*******************************************/
/* +1 em FEVEREIRO 2013 */
-- Forçar o erro por causa que a tabela está com ColumnStore Index
insert into tabelaProducao(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
getdate(), getdate())
/*******************************************/
/******** CRIAÇÃO DA SEGUNDA TABELA ********/
/*******************************************/
CREATE TABLE tabelaProducao_V2 (id INT NOT NULL, idOrigem int NOT NULL,
nome VARCHAR(20) NOT NULL, endereco VARCHAR(30) NOT NULL,
dataCadastro date NOT NULL, dataAtualizacao date NOT NULL)
GO
ALTER TABLE tabelaProducao_V2 WITH CHECK ADD
CONSTRAINT[validarCodigo_V2_Check] CHECK
(dataAtualizacao >= '2013-02-01' and dataAtualizacao < '2013-03-01')
GO
/*******************************************/
/************ CRIAÇÃO DOS INDEX ************/
/*******************************************/
CREATE CLUSTERED INDEX [idx_DataCodigo]
ON tabelaProducao_v2(DataAtualizacao) ON [PRIMARY]
GO
/*******************************************/
/********* MOVIMENTAÇÃO DOS DADOS **********/
/*******************************************/
-- Conta os registros
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao
GO
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao_v2
GO
-- Movimenta os dados
ALTER TABLE tabelaProducao SWITCH PARTITION 7 TO tabelaProducao_v2
GO
-- Conta os registros
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao
GO
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao_v2
GO
/* Fevereiro na TABELA 2 */
insert into tabelaProducao_V2(id, idOrigem, nome,
endereco, dataCadastro, dataAtualizacao)
values (next value for seq_Codigo, convert(int,rand()*100)+1,
substring(convert(varchar(40),newid()),1,20),
substring(convert(varchar(40),newid()),1,30),
getdate(), getdate())
go 10
-- Conta os registros
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao
GO
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao_v2
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_csi_tabelaProducao]
ON tabelaProducao_v2 ( id, nome, endereco, dataCadastro, dataAtualizacao )
GO
ALTER TABLE tabelaProducao_V2 switch to tabelaProducao partition 7
GO
-- Conta os registros
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao
GO
SELECT COUNT(0) [TOTAL], min(dataAtualizacao) [MENOR],
max(dataAtualizacao) [MAIOR] FROM tabelaProducao_v2
GO
Bom divertimento em seus testes. Espero ver nos comentários sua conclusão sobre este comparativo.
