Compartilhar via

Índices columnstore – Desempenho de consulta

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Banco de Dados SQL no Microsoft Fabric

Este artigo inclui recomendações para alcançar um desempenho rápido em consultas utilizando índices columnstore.

Os índices columnstore podem obter um desempenho até 100 vezes melhor em cargas de trabalho de análise e data warehouse e uma compactação de dados até 10 vezes melhor do que os índices rowstore tradicionais. Essas recomendações ajudam suas consultas a obter o desempenho de consulta rápido que os índices columnstore foram projetados para fornecer.

Recomendações para melhorar o desempenho de consultas

Veja a seguir algumas recomendações para atingir o alto desempenho que os índices columnstore foram projetados para fornecer.

1. Organize os dados para eliminar mais rowgroups de uma verificação de tabela completa

  • Escolha cuidadosamente a ordem de inserção. Geralmente, no data warehouse tradicional, os dados são realmente inseridos na ordem de tempo e a análise é feita na dimensão temporal. Por exemplo, ao analisar vendas por trimestre. Para esse tipo de carga de trabalho, a eliminação do rowgroup ocorre automaticamente. No SQL Server 2016 (13.x), você pode descobrir o número de rowgroups ignorados como parte do processo de consulta.

  • Use um índice clusterizado rowstore. Se o predicado de consulta comum estiver em uma coluna (por exemplo, C1) não relacionada à ordem de inserção, crie um índice clusterizado rowstore na coluna C1. Em seguida, descarte o índice clusterizado rowstore e crie um índice clusterizado columnstore. Se você criar o índice columnstore clusterizado explicitamente usando MAXDOP = 1, o índice columnstore clusterizado resultante será perfeitamente ordenado na coluna C1. Se você especificar MAXDOP = 8, verá sobreposição de valores em oito rowgroups. Para um índice columnstore não clusterizado (NCCI), se a tabela tiver um índice clusterizado rowstore, as linhas já estarão ordenadas pela chave de índice clusterizado. Nesse caso, o índice columnstore não clusterizado também é ordenado automaticamente. Um índice columnstore não mantém inerentemente a ordem das linhas. À medida que novas linhas são inseridas ou linhas mais antigas são atualizadas, talvez seja necessário repetir o processo, pois o desempenho da consulta de análise pode se deteriorar.

  • Implemente o particionamento de tabelas. Você pode particionar o índice columnstore e, em seguida, usar a eliminação de partição para reduzir o número de rowgroups a serem verificados. Por exemplo, uma tabela de fatos armazena compras feitas por clientes. Um padrão de consulta comum é localizar compras trimestrais por customer. Nesse caso, combine a coluna de ordem de inserção com o particionamento em customer coluna. Cada partição contém linhas para cada customer, ordenadas no momento da inserção. Além disso, considere usar o particionamento de tabela se houver necessidade de remover dados mais antigos do columnstore. Substituir e truncar partições que não são necessárias é uma estratégia eficiente para remover dados sem gerar fragmentação.

  • Evite excluir grandes quantidades de dados. Remover linhas compactadas de um rowgroup não é uma operação síncrona. Seria caro descompactar um rowgroup, excluir a linha e depois compactá-la novamente. Portanto, quando você exclui dados de rowgroups compactados, esses rowgroups ainda são verificados, mesmo que retornem menos linhas. Se o número de linhas excluídas para vários rowgroups for grande o suficiente para ser mesclado em menos rowgroups, a reorganização do columnstore aumentará a qualidade do índice e o desempenho da consulta melhorará. Se o processo de exclusão de dados geralmente esvazia grupos de linhas inteiros, considere usar o particionamento de tabela. Alterne partições que não são mais necessárias e trunque-as, em vez de excluir linhas.

    Observação

    A partir do SQL Server 2019 (15.x), o mover de tupla é ajudado por uma tarefa de mesclagem em segundo plano. Essa tarefa compacta automaticamente rowgroups delta OPEN menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla rowgroups COMPRESSED de onde um grande número de linhas foi excluído. Isso melhora a qualidade do índice columnstore ao longo do tempo. Se for necessário excluir grandes quantidades de dados do índice columnstore, considere dividir essa operação em lotes de exclusão menores ao longo do tempo. O envio em lote permite que a tarefa de mesclagem em segundo plano manipule a tarefa de mesclar rowgroups menores e melhore a qualidade do índice. Em seguida, não há necessidade de agendar janelas de manutenção de reorganização de índice após a exclusão de dados. Para obter mais informações sobre os termos e conceitos de Columnstore, veja Visão Geral de Índices Columnstore.

2. Planejar memória suficiente para criar índices columnstore em paralelo

Criar um índice columnstore é, por padrão, uma operação paralela, a menos que a memória seja restrita. Criar o índice em paralelo exige mais memória do que criar o índice em série. Quando há memória ampla, a criação de um índice columnstore assume a ordem de 1,5 vezes mais do que a criação de uma árvore B nas mesmas colunas.

A memória necessária para criar um índice columnstore depende do número de colunas, do número de colunas de cadeia de caracteres, do grau de paralelismo (DOP) e as características dos dados. Por exemplo, se sua tabela tiver menos de um milhão de linhas, o SQL Server usará apenas um thread para criar o índice columnstore.

Se sua tabela tiver mais de um milhão de linhas, mas o SQL Server não conseguir uma concessão de memória grande o suficiente para criar o índice usando MAXDOP, o SQL Server diminuirá automaticamente MAXDOP conforme necessário. Em alguns casos, o DOP deve ser reduzido para um para criar o índice em memória restringida dentro da concessão de memória disponível.

Desde o SQL Server 2016 (13.x), a consulta sempre opera no modo de lote. Em versões anteriores, a execução em lotes só é usada quando o DOP é maior do que um.

Desempenho do ColumnStore Explicado

Os índices columnstore alcançam alto desempenho de consulta combinando processamento de modo em lote na memória de alta velocidade com técnicas que reduzem significativamente os requisitos de E/S. Como as consultas de análise verificam um grande número de linhas, elas normalmente são associadas a E/S e, portanto, a redução de E/S durante a execução da consulta é fundamental para o design de índices columnstore. Depois que os dados são lidos na memória, é fundamental reduzir o número de operações na memória.

Os índices Columnstore reduzem a E/S e otimizam as operações na memória por meio de alta compactação de dados, eliminação de columnstore, eliminação de rowgroup e processamento em lote.

Compactação de dados

Os índices columnstore alcançam uma compactação de dados até 10 vezes maior do que os índices rowstore. Isso reduz significativamente a E/S necessária para executar consultas de análise e, portanto, melhora o desempenho de consultas.

  • Os índices columnstore leem dados compactados do disco, o que significa que menos bytes de dados precisam ser lidos na memória.

  • Os índices columnstore armazenam dados em formato compactado na memória, reduzindo a E/S ao evitar a leitura dos mesmos dados na memória. Por exemplo, com compactação 10 vezes, os índices columnstore podem manter 10 vezes mais dados na memória, em comparação com o armazenamento dos dados em formato não compactado. Com mais dados na memória, é mais provável que o índice columnstore encontre os dados necessários na memória sem incorrer em leituras desnecessárias do disco.

  • Os índices columnstore compactam dados por colunas em vez de compactá-los por linhas, o que alcança altas taxas de compactação e reduz o tamanho dos dados armazenados no disco. Cada coluna é compactada e armazenada de modo independente. Os dados em uma coluna sempre têm o mesmo tipo de dados e tendem a ter valores semelhantes. As técnicas de compactação de dados columnstore são ótimas para alcançar taxas de compactação mais altas quando os valores são semelhantes.

Por exemplo, uma tabela de fatos armazena endereços de clientes e tem uma coluna para country-region. O número total de valores possíveis é inferior a 200. Alguns desses valores são repetidos muitas vezes. Se a tabela de fatos tiver 100 milhões de linhas, a coluna country-region se compacta facilmente e exige pouco armazenamento. A compactação linha por linha não é capaz de capitalizar a similaridade dos valores de coluna dessa forma e deve usar mais bytes para compactar os valores na coluna country-region.

Eliminação de colunas

Os índices Columnstore ignoram a leitura em colunas que não são necessárias para o resultado da consulta. A eliminação de coluna reduz ainda mais a E/S para execução de consulta e, portanto, melhora o desempenho da consulta.

  • A eliminação de colunas é possível porque os dados são organizados e compactados coluna por coluna. Por outro lado, quando os dados são armazenados linha por linha, os valores de coluna em cada linha são armazenados fisicamente juntos e não podem ser facilmente separados. O Processador de Consultas precisa ler uma linha inteira para recuperar valores de colunas específicas, aumentando a entrada/saída porque dados extras são desnecessariamente lidos na memória.

Por exemplo, se uma tabela tiver 50 colunas e a consulta usar apenas cinco dessas colunas, o índice columnstore buscará apenas as cinco colunas do disco. Ele ignora a leitura nas outras 45 colunas, reduzindo a E/S em mais 90%, supondo que todas as colunas sejam de tamanho semelhante. Se os mesmos dados forem armazenados em um rowstore, o processador de consultas precisará ler as 45 colunas restantes.

Eliminação de rowgroup

Para verificações de tabela completas, uma grande porcentagem dos dados geralmente não corresponde aos critérios de predicado de consulta. Usando metadados, o índice columnstore é capaz de pular a leitura dos grupos de linhas que não contêm dados necessários para o resultado da consulta, tudo isso sem necessidade de E/S real. Essa capacidade, chamada de eliminação de rowgroup, reduz a E/S para verificações completas de tabela e, portanto, melhora o desempenho da consulta.

Quando um índice columnstore precisa executar uma verificação de tabela completa?

A partir do SQL Server 2016 (13.x), você pode criar um ou mais índices rowstore não clusterizados regulares, ou árvore B, em um índice columnstore clusterizado. Os índices de árvore B não clusterizados podem acelerar uma consulta que contém um predicado de igualdade ou um predicado com um intervalo de valores pequeno. Para predicados mais complicados, o otimizador de consultas pode optar por uma varredura completa da tabela. Sem a capacidade de ignorar rowgroups, uma verificação completa da tabela pode ser demorada, especialmente para tabelas grandes.

Quando uma consulta analítica se beneficia da eliminação de rowgroups durante uma varredura completa da tabela?

Por exemplo, uma empresa de varejo modela seus dados de vendas usando uma tabela de fatos com índice columnstore clusterizado. Cada nova venda armazena vários atributos da transação, incluindo a data em que um produto é vendido. Curiosamente, mesmo que os índices columnstore não garantam uma ordem classificada, as linhas nesta tabela são carregadas em uma ordem classificada por data. Com o tempo, essa tabela cresce. Embora o negócio varejista possa manter dados de vendas pelos últimos 10 anos, uma consulta de análise talvez precise apenas computar uma agregação do último trimestre. Os índices columnstore podem eliminar o acesso aos dados dos 39 trimestres anteriores apenas examinando os metadados da coluna de data. Isso representa uma redução de 97% na quantidade de dados que são lidos na memória e processados.

Quais grupos de linhas são ignorados em uma varredura completa da tabela?

Para determinar quais grupos de linhas eliminar, o índice columnstore usa metadados para armazenar os valores mínimo e máximo de cada segmento de coluna para cada rowgroup. Quando nenhum dos intervalos de segmentos de coluna atende aos critérios de predicado de consulta, todo o rowgroup é ignorado sem fazer nenhuma E/S real. Isso funciona porque os dados geralmente são carregados em uma ordem classificada. Embora a classificação de linhas não seja garantida, valores de dados semelhantes geralmente estão localizados no mesmo rowgroup ou em um rowgroup vizinho.

Para obter mais informações sobre grupos de linhas, consulte Diretrizes de Design do índice Columnstore.

Execução em modo de lote

Execução em modo de lote processa linhas em grupos, geralmente até 900 por vez, para melhorar a eficiência. Por exemplo, a consulta SELECT SUM(Sales) FROM SalesData calcula o total de vendas da SalesData tabela. No modo de lote, o mecanismo de consulta processa os dados em grupos de 900 linhas. Essa abordagem reduz o custo de acesso a metadados e outros tipos de sobrecarga, distribuindo-os por todas as linhas em um lote, em vez de incorrer na sobrecarga de cada linha. Além disso, o modo em lote funciona com dados compactados quando possível e remove alguns dos operadores de troca usados no modo de linha, acelerando significativamente as consultas analíticas.

Nem todos os operadores de execução de consulta podem ser executados em modo de lote. Por exemplo, operações de linguagem de manipulação de dados (DML), como inserir, excluir ou atualizar, são executadas uma linha por vez. Operadores de modo de lote, como Scan, Join, Aggregate, Sort e outros, podem melhorar o desempenho da consulta. Como o índice columnstore foi introduzido no SQL Server 2012 (11.x), há um esforço contínuo para expandir os operadores que podem ser executados em modo em lote. A tabela a seguir mostra os operadores que são executados no modo de lote de acordo com a versão do produto.

Operadores de modo de lote Quando usado SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) e Banco de Dados SQL1 Comentários
Operações DML (inserir, excluir, atualizar, mesclar) não não não O DML não é uma operação de modo de lote porque não é paralelo. Mesmo quando podemos habilitar o processamento em lotes de modo serial, não vemos ganhos significativos ao permitir que o DML seja processado em modo em lote.
Verificação de índice columnstore VARREDURA Não disponível sim sim Para índices columnstore, podemos enviar o predicado para o nó SCAN.
columnstore index SCAN (não clusterizado) VARREDURA sim sim sim sim
busca de índice Não disponível Não disponível não Executamos uma operação de busca usando um índice de árvore B não clusterizado no modo de linha.
calcular escalar Expressão que é avaliada como um valor escalar. sim sim sim Como todos os operadores de modo de lote, há algumas restrições no tipo de dados.
concatenação UNION e UNION ALL não sim sim
filtro Aplicação de predicados sim sim sim
correspondência de hash Funções agregadas baseadas em hash, junção de hash externa, junção de hash direita, junção de hash esquerda, junção interna direita, junção interna esquerda sim sim sim Restrições de agregação: nenhum mín/máx para cadeias de caracteres. As funções de agregação disponíveis são sum/count/avg/min/max.
Restrições de associação: nenhum tipo incompatível ingressa em tipos não inteiros.
mesclar junção não não não
consultas multi-threaded sim sim sim
loops aninhados não não não
consultas de thread único em execução no MAXDOP 1 não não sim
Consultas de thread único com um plano de consulta serial não não sim
ordenar Ordenar por cláusula em SCAN com índice columnstore. não não sim
classificação superior não não sim
agregados de janela Não disponível Não disponível sim Novo operador no SQL Server 2016 (13.x).

1 Aplica-se ao SQL Server 2016 (13.x), aos níveis Premium e Standard do SQL Database - S3 e superiores, a todos os níveis vCore, e ao Analytics Platform System (PDW).

Para obter mais informações, confira o Guia da Arquitetura de Processamento de Consultas.

Empilhamento agregado

Um caminho de execução normal para computação agregada para buscar as linhas qualificadas do nó SCAN e agregar os valores no Modo de Lote. Embora isso ofereça um bom desempenho, a operação de agregação, a partir do SQL Server 2016 (13.x), pode ser transferida para o nó SCAN. A aplicação agregada melhora o desempenho dos cálculos agregados em ordens de magnitude sobre a execução do Modo de Lote, desde que as seguintes condições sejam atendidas:

  • As agregações são MIN, MAX, SUM, COUNT e COUNT(*).
  • O operador de agregação deve estar no topo do nó SCAN ou do nó SCAN com GROUP BY.
  • Essa agregação não é uma agregação distinta.
  • A coluna de agregação não é uma coluna de cadeia de caracteres.
  • A coluna de agregação não é uma coluna virtual.
  • O tipo de dados de entrada e saída deve ser um dos seguintes e deve caber em 64 bits:
    • tinyint, int, bigint, smallint, bit
    • smallmoney, money, decimal e numérico com precisão <= 18
    • smalldate, date, datetime, datetime2, time

Por exemplo, a aplicação agregada é feita em ambas as consultas a seguir:

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Pushdown de predicado de cadeia de caracteres

Ao projetar um esquema de data warehouse, a modelagem de esquema recomendada é usar o esquema em estrela ou o esquema floco de neve que consiste em uma ou mais tabelas de fatos e muitas tabelas de dimensões.

Dica

A tabela de fatos armazena as transações ou medidas de negócios e a tabela de dimensões armazena as dimensões pelas quais os fatos precisam ser analisados. Para obter mais informações sobre modelagem dimensional, consulte Modelagem dimensional no Microsoft Fabric.

Por exemplo, um fato pode ser um registro que representa uma venda de um produto específico em uma região específica, enquanto a dimensão representa um conjunto de regiões, produtos e assim por diante. As tabelas de fatos e dimensões são conectadas por meio de uma relação de chave primária/estrangeira. As consultas de análise mais comumente usadas unem uma ou mais tabelas de dimensão à tabela de fatos.

Vamos considerar uma tabela de dimensões Products. Uma chave primária típica é ProductCode, comumente representada como string. Para o desempenho de consultas, é uma prática recomendada criar uma chave substituta, normalmente uma coluna inteira, para se referir à linha na tabela de dimensões da tabela de fatos.

O índice columnstore executa consultas de análise com junções e predicados envolvendo chaves numéricas ou baseadas em números inteiros com eficiência. O SQL Server 2016 (13.x) melhorou significativamente o desempenho das consultas de análise com colunas baseadas em cadeia de caracteres, empurrando os predicados com colunas de cadeia de caracteres para o nó SCAN.

O pushdown do predicado de cadeia de caracteres aproveita o dicionário primário/secundário criado para colunas para melhorar o desempenho da consulta. Por exemplo, considere um segmento de coluna de cadeia de caracteres dentro de um rowgroup que consiste em 100 valores de cadeia de caracteres distintos. Cada valor de cadeia de caracteres distinto é referenciado 10.000 vezes em média, supondo um milhão de linhas. Com o pushdown do predicado de cadeia de caracteres, a execução da consulta calcula o predicado em relação aos valores no dicionário. Se o predicado se qualificar, todas as linhas que se referem ao valor do dicionário serão qualificadas automaticamente. Isso melhora o desempenho de duas maneiras:

  • Somente a linha qualificada é retornada, reduzindo o número de linhas que precisam fluir para fora do nó de verificação.
  • O número de comparações de cadeia de caracteres é reduzido. Neste exemplo, apenas 100 comparações de cadeias de caracteres são necessárias, em vez de 1 milhão de comparações. Existem algumas limitações:
    • Nenhum pushdown de predicado de cadeia de caracteres para rowgroups delta. Não há dicionário para colunas em rowgroups delta.
    • Nenhuma aplicação de predicado de cadeia de caracteres se o dicionário exceder as entradas de 64 KB.
    • Não há suporte para expressões que avaliam nulos.

Eliminação de segmentos

As opções de tipo de dados poderão ter um impacto significativo no desempenho da consulta com base em predicados comuns de filtro para consultas no índice columnstore.

Nos dados columnstore, os grupos de linhas são compostos por segmentos de coluna. Há metadados com cada segmento para permitir a eliminação rápida de segmentos sem lê-los. Essa eliminação de segmento se aplica a tipos de dados numéricos, de data e hora e ao tipo de dados datetimeoffset com escala menor ou igual a dois. A partir do SQL Server 2022 (16.x), os recursos de eliminação de segmento se estendem para tipos de dados de cadeia de caracteres, binários e guid e datetimeoffset tipo de dados para escala maior que dois.

Depois de atualizar para uma versão do SQL Server que dá suporte à eliminação de segmento mínima/máxima da cadeia de caracteres (SQL Server 2022 (16.x) e posterior), o índice columnstore não se beneficiará desse recurso até que ele seja recriado usando um ALTER INDEX REBUILD ou CREATE INDEX WITH (DROP_EXISTING = ON).

A eliminação de segmento não se aplica aos tipos de dados LOB, como os comprimentos de tipo de dados (máximo).

Atualmente, somente o SQL Server 2022 (16.x) e posterior dá suporte à eliminação de rowgroup columnstore clusterizado para o prefixo de predicados LIKE, por exemplo, column LIKE 'string%'. A eliminação de segmentos não é compatível com o uso de LIKE sem prefixo, como column LIKE '%string'.

Índices columnstore ordenados também se beneficiam da eliminação de segmentos, especialmente para colunas de cadeia de caracteres. Em índices columnstore ordenados, a eliminação de segmento na primeira coluna na chave de índice é mais eficaz, pois é classificada. Os ganhos de desempenho devido à eliminação de segmento em outras colunas na tabela são menos previsíveis. Para obter mais informações sobre índices columnstore ordenados, consulte Usar um índice columnstore ordenado para tabelas grandes do data warehouse. Para obter a disponibilidade do índice columnstore ordenado, consulte Disponibilidade do índice de coluna ordenada.

Usando a opção SET STATISTICS IO na conexão de consulta, você pode exibir a eliminação de segmentos em ação. Procure uma saída como a seguinte para indicar que ocorreu a eliminação do segmento. Os grupos de linhas são compostos de segmentos de coluna, portanto, isso pode indicar a eliminação de segmentos. A seguir SET STATISTICS IO exemplo de saída de uma consulta, aproximadamente 83% dos dados foram ignorados pela consulta:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Conteúdo relacionado