Índice Columnstore - Herói ou Vilão?

Índice Columnstore – Herói ou Vilão?

Tempo de leitura: 10 minutos

Olá Pessoal,

Espero que estejam todos bem! 😀

No post de hoje vou falar sobre um assunto que sempre gera discussão entre DBAs e desenvolvedores de BI.

Ah, os Columnstore Indexes… Essa funcionalidade que chegou no SQL Server 2012 e desde então vem dividindo opiniões. Uns chamam de revolução, outros de dor de cabeça. Mas afinal, eles são heróis ou vilões na nossa batalha diária para obter mais performance?

Bom, como sempre digo, depende. E hoje vou te explicar quando eles podem salvar o seu dia e quando podem te dar uma bela dor de cabeça.

Ao longo dos anos já vi muita gente implementando Columnstore em lugares que não deveriam, e também vi gente perdendo oportunidades incríveis de melhorar performance por não conhecer essa tecnologia. Para ser bem sincero, eu mesmo já passei por situações onde achei que seria a solução de todos os problemas e acabei me frustrando.

Vamos lá, o objetivo desse post é esclarecer quando usar e quando não usar Columnstore Indexes, com exemplos práticos e scripts que você pode testar no seu ambiente.

O maior conselho que dou é: teste sempre em laboratório primeiro. Não saia implementando em produção sem entender o comportamento no seu cenário específico.

O que são esses tais Columnstore Indexes?

Antes de mais nada, vamos entender o básico. Tradicionalmente, o SQL Server armazena dados em formato “rowstore” (orientado a linhas). Imagine uma tabela como um arquivo Excel: cada linha contém todas as colunas de um registro, uma após a outra.

Já os Columnstore Indexes fazem o contrário: eles armazenam os dados por coluna. É como se você pegasse aquele Excel e, ao invés de ler linha por linha, você lesse coluna por coluna.

Vou dar um exemplo prático para ficar mais claro:

Parece simples, mas essa diferença muda completamente a forma como o SQL Server processa as consultas.

Os Columnstore Indexes não são apenas uma forma diferente de armazenar dados. Eles trazem uma arquitetura completamente nova que você precisa entender:

Rowgroups e Segments

Rowgroup é um grupo de até 1.048.576 linhas (sim, mais de 1 milhão). Cada rowgroup é dividido em segments, onde cada segment representa uma coluna dentro daquele rowgroup.

Existe também o Deltastore, que armazena dados temporários em formato rowstore até ter registros suficientes para formar um rowgroup completo. É como se fosse uma “sala de espera” para os dados.

O processo de compressão

Aqui é onde a coisa fica interessante. Os Columnstore Indexes conseguem compressões de até 10x comparado aos índices tradicionais. Por quê?

Primeiro, numa coluna os dados tendem a ser similares. Se você tem uma coluna de estado civil, vai ter muito “Solteiro”, “Casado”, “Divorciado” repetindo. O SQL Server consegue comprimir isso de forma muito eficiente.

Segundo, ele usa algoritmos específicos como Dictionary encoding e run-length encoding.

Terceiro, valores repetidos são armazenados uma única vez.

Quando eles são heróis?

Data Warehouse e Analytics

Se você trabalha com Data Warehouse, os Columnstore são praticamente obrigatórios. Imagina uma consulta assim:

SELECT
    YEAR(DataVenda) as Ano,
    SUM(ValorVenda) as TotalVendas,
    COUNT(*) as QtdVendas
FROM VendasColumnstore
WHERE DataVenda >= '2020-01-01'
GROUP BY YEAR(DataVenda)

SELECT

YEAR(DataVenda) as Ano,

SUM(ValorVenda) as TotalVendas,

COUNT(*) as QtdVendas

FROM VendasColumnstore

WHERE DataVenda >= '2020-01-01'

GROUP BY YEAR(DataVenda)

Com 200 milhões de registros, numa tabela rowstore isso pode demorar bem mais se comparado ao Columnstore.

Vamos fazer alguns testes

Temos duas tabelas, uma com um índice Columnstore criado e na outra com um índice Rowstore, veja o comparativo abaixo.

ROWSTORE

COLUMNSTORE

Comparativo de Desempenho

O columnstore é uma ferramenta poderosa para melhorar o desempenho de consultas em grandes volumes de dados. Ele é especialmente útil em cenários como queries de agregação (SUM, COUNT, AVG, MIN, MAX), relatórios e Business Intelligence (BI) que fazem full table scan em tabelas gigantes, e compressão de dados, permitindo reduzir significativamente o espaço em disco necessário. Com o columnstore, é possível reduzir drasticamente o tempo de consulta e melhorar a eficiência das suas cargas de dados, tornando-o uma solução ideal para lidar com grandes volumes de dados.

Quando eles são vilões?

OLTP (Transações Frequentes)

Se sua aplicação faz muitos INSERT, UPDATE, DELETE pontuais, esqueça os Columnstore. Eles são péssimos para isso.

Exemplo :

-- Isso pode ser LENTO com Columnstore
UPDATE Clientes
SET Nome ='João Silva'
WHERE ID = 12345

-- Isso pode ser LENTO com Columnstore

UPDATE Clientes

SET Nome ='João Silva'

WHERE ID = 12345

Queries seletivas (busca por chaves)

Procurar um registro específico? Rowstore é muito mais eficiente.

Exemplo :

-- Melhor com rowstore tradicional
SELECT * FROM Pedidos WHERE NumeroPedido = 'PED123456'

1 2	-- Melhor com rowstore tradicional SELECT * FROM Pedidos WHERE NumeroPedido = 'PED123456'

Tabelas pequenas

Columnstore tem overhead. Numa tabela com 10 mil registros, você só vai ter dor de cabeça e não irá mudar em nada o desempenho. O custo-benefício não compensa.

Limitações de tipos de dados

Alguns tipos não são suportados, especialmente em versões mais antigas.

text, ntext, image
varchar(max), nvarchar(max) (em versões antigas)
xml
uniqueidentifier (SQL Server 2012)

Tipos de Columnstore: Clustered vs Nonclustered

Clustered Columnstore Index

Esse substitui completamente a estrutura da tabela. Use em tabelas de fato em DW ou dados históricos. A vantagem é máxima compressão e performance para analytics, mas dificulta operações OLTP.

-- Criando um Clustered Columnstore
CREATE CLUSTERED COLUMNSTORE INDEX CCI_VendasColumnstore
ON VendasColumnstore

-- Criando um Clustered Columnstore

CREATE CLUSTERED COLUMNSTORE INDEX CCI_VendasColumnstore

ON VendasColumnstore

Nonclustered Columnstore Index

Esse é um índice adicional numa tabela rowstore. Use para analytics em tempo real (OLAP). A vantagem é que permite OLTP e Analytics na mesma tabela, mas duplica o armazenamento.

Exemplo :

-- Criando um Nonclustered Columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Vendas
ON Vendas (DataVenda, ValorVenda, IdProduto, IdCliente)

-- Criando um Nonclustered Columnstore

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Vendas

ON Vendas (DataVenda, ValorVenda, IdProduto, IdCliente)

Batch Mode

Uma das grandes vantagens dos Columnstore é o Batch Mode Processing. Ao invés de processar linha por linha, o SQL Server processa cerca de 900 linhas por vez. É como se ao invés de carregar um caminhão caixa por caixa, você carregasse 900 caixas de uma vez.

Segment Elimination

Cada segment armazena metadados com valores mínimo e máximo. Quando você faz uma query com filtro, o SQL Server pode eliminar segments inteiros sem nem lê-los.

Exemplo :

-- Se um segment tem vendas de Jan-2020 a Mar-2020
-- Esta query nem vai ler esse segment:
SELECT DataVenda,
       SUM(ValorVenda)
FROM VendasColumnstore
GROUP BY DataVenda
WHERE DataVenda >= '20250101'
  AND DataVenda < '20250201'
GROUP BY DataVenda

-- Se um segment tem vendas de Jan-2020 a Mar-2020

-- Esta query nem vai ler esse segment:

SELECT DataVenda,

SUM(ValorVenda)

FROM VendasColumnstore

GROUP BY DataVenda

WHERE DataVenda >= '20250101'

AND DataVenda < '20250201'

GROUP BY DataVenda

Isso é uma mágica pura. O SQL Server já sabe que não precisa nem olhar aquele pedaço dos dados.

Mas, nem tudo sâo flores, existem algumas limitaçôes para o Segment Elimination que dependendo de como foi criado o seu índice columnstore pode não funcionar corretamente. Eu estava tendo um pouco de dificuldade para conseguir ver esse recurso funcionando na prática, então li um artigo do Klaus Aschenbrenner que foi bem esclarecedor.

Segue o link do artigo, caso queiram dar uma conferida.

Para este recurso funcionar, foi necessário recriar o índice columnstore com o parâmetro (MAXDOP = 1)

CREATE CLUSTERED COLUMNSTORE INDEX CCI_VendasColumnstore ON VendasColumnstore
WITH (DROP_EXISTING = ON,MAXDOP = 1)

1 2	CREATE CLUSTERED COLUMNSTORE INDEX CCI_VendasColumnstore ON VendasColumnstore WITH (DROP_EXISTING = ON,MAXDOP = 1)

Segundo o Klaus, isso pode acontecer porque quando você cria o índice normalmente no operador do Columnstore Index insert ele é aplicado em paralelo (várias threads ao mesmo tempo) e isso acaba prejudicando a ordem sequencial de inserção. Com o MAX DOP = 1, você obriga o SQL a criar o índice utilizando apenas uma thread e isso faz que os registros sejam inseridos sem perder a sequencia, proporcionando que o Segment Elimination melhor.

Queries úteis

Vou deixar aqui alguns scripts que uso no dia a dia para monitorar columnstore:

Verificando fragmentação

A fragmentação dos índices columnstore pode afetar negativamente o desempenho das consultas e aumentar o uso de recursos, pois o motor de banco de dados precisa realizar mais operações de leitura e processamento para recuperar os dados. Isso pode ocorrer devido a inserções e exclusões frequentes, atualizações de dados e crescimento do índice. Para evitar ou minimizar a fragmentação, é importante monitorar regularmente o nível de fragmentação, reorganizar ou reconstruir os índices columnstore e otimizar as operações de inserção e exclusão, além de usar técnicas de manutenção de índices para manter a eficiência e o desempenho.

Segue um script que pode te ajudar a monitorar a fragmentação dos índices columnstore

SELECT
    i.object_id,
    OBJECT_NAME(i.object_id) AS TableName,
    i.index_id,
    i.name AS IndexName,
    100 * (ISNULL(SUM(rg.deleted_rows),0)) / NULLIF(SUM(rg.total_rows),0) AS FragmentationPercent
FROM sys.indexes i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats rg
    ON i.object_id = rg.object_id AND i.index_id = rg.index_id
WHERE i.type IN (5,6) -- Columnstore indexes
GROUP BY i.object_id, i.index_id, i.name
ORDER BY FragmentationPercent DESC

SELECT

i.object_id,

OBJECT_NAME(i.object_id) AS TableName,

i.index_id,

i.name AS IndexName,

100 * (ISNULL(SUM(rg.deleted_rows),0)) / NULLIF(SUM(rg.total_rows),0) AS FragmentationPercent

FROM sys.indexes i

INNER JOIN sys.dm_db_column_store_row_group_physical_stats rg

ON i.object_id = rg.object_id AND i.index_id = rg.index_id

WHERE i.type IN (5,6) -- Columnstore indexes

GROUP BY i.object_id, i.index_id, i.name

ORDER BY FragmentationPercent DESC

Verificando compressão

Verificar a compressão dos índices columnstore é importante porque ela pode afetar diretamente o desempenho e o armazenamento do banco de dados. Uma boa compressão pode reduzir o tamanho do armazenamento necessário, melhorar o desempenho de consultas e reduzir o uso de recursos, enquanto uma compressão inadequada pode levar a problemas de desempenho e aumento do armazenamento necessário. Além disso, a compressão eficaz pode variar dependendo do tipo de dados e da estrutura do índice.

Segue um script para verificar a compressão dos índices columnstore

SELECT
    t.name AS TableName,
    p.rows AS [RowCount],
    (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
    (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
    p.data_compression_desc
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.type IN (5,6) -- Columnstore
GROUP BY t.name, p.rows,p.data_compression_desc
ORDER BY TotalSpaceMB DESC

SELECT

t.name AS TableName,

p.rows AS [RowCount],

(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,

(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,

p.data_compression_desc

FROM sys.tables t

INNER JOIN sys.indexes i ON t.object_id = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

WHERE i.type IN (5,6) -- Columnstore

GROUP BY t.name, p.rows,p.data_compression_desc

ORDER BY TotalSpaceMB DESC

Forçando compressão do Deltastore

Forçar a compressão do Deltastore pode ser útil para reduzir o tamanho do armazenamento necessário e melhorar o desempenho de consultas, pois o Deltastore é uma estrutura de dados que armazena as alterações nos dados do columnstore. Ao forçar a compressão, você pode garantir que as alterações sejam compactadas e armazenadas de forma eficiente, reduzindo o impacto no desempenho e no armazenamento. No entanto, é importante considerar que a compressão forçada pode ter um custo em termos de recursos de CPU e pode afetar o desempenho de inserção e atualização de dados.

Abaixo um exemplo de como forçar a compressão do Deltastore:

Exemplo :

-- Move dados do deltastore para columnstore
ALTERINDEX CCI_FactVendas ON FactVendas REORGANIZE
WITH (COMPRESS_ALL_ROW_GROUPS = ON)

-- Move dados do deltastore para columnstore

ALTERINDEX CCI_FactVendas ON FactVendas REORGANIZE

WITH (COMPRESS_ALL_ROW_GROUPS = ON)

Limitações que você deve conhecer

SQL Server 2012 (primeira versão)

A primeira versão tinha limitações severas. A tabela ficava read-only com nonclustered columnstore, não suportava clustered columnstore atualizável, e tinha muitas limitações de tipos de dados.

Versões atuais (2016+)

Muito mais flexível. Suporte a updates/deletes, mais tipos de dados suportados, e disponível em todas as edições (não só Enterprise).

A ordem de inserção importa quando usamos índices columnstore?

Dizem que a ordem de inserção importa no columnstore porque ela pode afetar a eficiência da compressão e do armazenamento dos dados. Quando os dados são inseridos em ordem, o columnstore pode aproveitar melhor as oportunidades de compressão, pois os valores semelhantes tendem a ficar juntos, o que pode reduzir o tamanho do armazenamento necessário.

Além disso, a ordem de inserção também pode afetar o desempenho das consultas, pois o columnstore pode utilizar técnicas de eliminação de segmentos (segment elimination) para reduzir a quantidade de dados que precisam ser lidos durante uma consulta. Se os dados estiverem ordenados de acordo com as colunas utilizadas nas consultas, o columnstore pode eliminar mais eficientemente os segmentos que não são relevantes para a consulta.

Mas será que a diferença é tão grande assim na prática?

Vamos aos testes

-- dados ordenados por data
INSERT INTO FatoVendas
SELECT DataVenda,
       ValorVenda,
       IDProduto,
       IdCliente,
       IdRegiao,
       Quantidade,
       DescricaoProduto,
       StatusVenda
FROM VendasColumnstore
ORDER BY DataVenda

-- dados ordenados por data

INSERT INTO FatoVendas

SELECT DataVenda,

ValorVenda,

IDProduto,

IdCliente,

IdRegiao,

Quantidade,

DescricaoProduto,

StatusVenda

FROM VendasColumnstore

ORDER BY DataVenda

-- dados aleatórios
INSERT INTO FatoVendas
SELECT DataVenda,
       ValorVenda,
       IDProduto,
       IdCliente,
       IdRegiao,
       Quantidade,
       DescricaoProduto,
       StatusVenda
FROM VendasColumnstore
ORDER BY New_ID()

-- dados aleatórios

INSERT INTO FatoVendas

SELECT DataVenda,

ValorVenda,

IDProduto,

IdCliente,

IdRegiao,

Quantidade,

DescricaoProduto,

StatusVenda

FROM VendasColumnstore

ORDER BY New_ID()

Comparativo de Desempenho

É, nem tanto.. claro, 19% de CPU é uma singela diferença, mas sendo sincero, acho que não muda tanto o resultado final.

O Particionamento da tabela aliado ao Índex Columnstore pode ajudar na performance?

O particionamento é tipo um superpoder quando se trabalha com columnstore. Ele ajuda a dividir os dados em pedaços menores e mais gerenciáveis, o que torna as consultas mais rápidas e eficientes. Além disso, você pode fazer manutenção e atualizações em apenas uma parte dos dados, em vez de ter que mexer em tudo. Isso é especialmente útil quando se lida com grandes volumes de dados, pois ajuda a reduzir o tempo de consulta e a carga de trabalho no servidor.

Vamos ver na prática uma consulta de vendas dessa nossa tabela de estudos (200 milhões de linhas) buscando dados dos anos de 2020 a 2022.

Exemplo :

SELECT 
    YEAR(DataVenda) AS Ano,
    SUM(ValorVenda) AS FaturamentoTotal
FROM 
    VendasColumnstore
WHERE 
    DataVenda >= '2020-01-01' AND DataVenda < '2023-01-01'
GROUP BY 
    YEAR(DataVenda)

SELECT

YEAR(DataVenda) AS Ano,

SUM(ValorVenda) AS FaturamentoTotal

FROM

VendasColumnstore

WHERE

DataVenda >= '2020-01-01' AND DataVenda < '2023-01-01'

GROUP BY

YEAR(DataVenda)

Performance sem particionamento e sem columnstore

Agora vamos aplicar o particionamento por Data e criar o índice columnstore com a nossa função de particionamento.

-- Combinando particionamento com o columnstore
CREATE PARTITION FUNCTION pf_Data (DATE)
AS RANGE RIGHT FOR VALUES
('2020-01-01', '2021-01-01', '2022-01-01', '2023-01-01', '2025-01-01')

CREATE PARTITION SCHEME ps_Data
AS PARTITION pf_Data
ALL TO ('PRIMARY')

CREATE CLUSTERED COLUMNSTORE INDEX CCI_VendasColumnstore
ON VendasColumnstore
ON ps_Data(DataVenda)

-- Combinando particionamento com o columnstore

CREATE PARTITION FUNCTION pf_Data (DATE)

AS RANGE RIGHT FOR VALUES

('2020-01-01', '2021-01-01', '2022-01-01', '2023-01-01', '2025-01-01')

CREATE PARTITION SCHEME ps_Data

AS PARTITION pf_Data

ALL TO ('PRIMARY')

CREATE CLUSTERED COLUMNSTORE INDEX CCI_VendasColumnstore

ON VendasColumnstore

ON ps_Data(DataVenda)

Performance tabela particionada + índice columnstore

Comparativo de Desempenho

E ai, faz uma boa diferença, né?

Veredicto final: Herói ou vilão? Batman ou Coringa?

A resposta é: DEPENDE DO CONTEXTO.

São heróis quando você trabalha com Data Warehouse (DW), faz muitas queries analíticas, tem tabelas grandes (milhões de registros), precisa de compressão, e usa agregações frequentemente.

São vilões quando sua aplicação é puramente OLTP, faz muitas operações de linha única, tem tabelas pequenas, precisa de updates/deletes frequentes, ou usa tipos de dados não suportados.

Conclusão

Os Columnstore Indexes são como um martelo, excelente para pregar pregos, péssimo para apertar parafusos. A chave é saber quando usar.

Se você está lutando com performance em relatórios, Analytics ou Data Warehouse, os Columnstore podem ser seus heróis. Mas se você tentar forçá-los numa aplicação OLTP, eles rapidamente se tornarão vilões.

A tecnologia evoluiu muito desde 2012. No SQL Server 2019 e versões mais recentes, muitas limitações foram removidas, e até mesmo workloads mistos (HTAP – Hybrid Transactional/Analytical Processing) se tornaram viáveis.

Minha dica final: teste, teste, teste! Cada ambiente é único, e só testando você vai saber se os Columnstore são heróis ou vilões no seu cenário específico.

E você, já teve experiências com Columnstore Indexes? Foram heróis ou vilões no seu cenário?

Conta aí nos comentários!

Bom, é isso, espero que tenham gostado!

Qualquer dúvida deixe os comentários.

Gustavo Larocca

Consultor SQL Server

Referências:

[Microsoft Docs – Columnstore Indexes Overview] – (https://learn.microsoft.com/pt-br/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver17)

[Columnstore Segment Elimination] – (https://www.sqlpassion.at/archive/2017/01/30/columnstore-segment-elimination/)

[Columnstore Index Query Performance] – (https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance?view=sql-server-ver17)

[Alter Index Transact SQL] – (https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17)

[Simple Talk – What Are Columnstore Indexes] – (https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/what-are-columnstore-indexes/)

batch mode Columnstore compression Data Types Datawarehouse deltastore DW fragmentation HTAP Indexing microsoft OLAP OLTP performance Segment Elimination sql server Transactional Operations

Deixe uma respostaCancelar resposta

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Índice Columnstore – Herói ou Vilão?