Índices columnstore – desempenho de consultas

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

Este artigo inclui recomendações para obter o desempenho rápido da consulta com í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. Organizar 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 essa variante de carga de trabalho, a eliminação de rowgroup ocorre automaticamente. No SQL Server 2016 (13.x), você pode descobrir o número de rowgroups ignorado como parte do processamento 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 columnstore clusterizado. 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 na customer coluna. Cada partição contém linhas para cada customer, ordenadas na inserção. Além disso, considere usar o particionamento de tabela se houver necessidade de remover dados mais antigos do columnstore. Alternar e truncar partições que não são necessárias é uma estratégia eficiente para excluir dados sem gerar fragmentação.

  • Evite excluir grandes quantidades de dados. A remoção de 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 rowgroups inteiros, considere usar o particionamento de tabela. Troque as partições que não são mais necessárias e trunce-as, em vez de excluir linhas.

    Observação

    A partir do SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server 2019 (15. Essa tarefa compacta automaticamente grupos de linhas delta OPEN menores que existem há algum tempo, conforme determinado por um limite interno, ou mescla grupos de linhas 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 lide com a tarefa de mesclar rowgroups menores e melhora 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á bastante memória, a criação de um índice columnstore assume a ordem de 1,5 vezes mais longa 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 a tabela tiver mais de um milhão de linhas, mas o SQL Server não puder obter uma concessão de memória grande o suficiente para criar o índice usando MAXDOP, o SQL Server diminuirá MAXDOP automaticamente conforme necessário. Em alguns casos, o DOP deve ser reduzido para um para criar o índice em memória restrita na 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 ColumnStore explicado

Índices columnstore atingem alto desempenho de consultas combinando o processamento no modo de lotes in-memory em alta velocidade com técnicas que reduzem consideravelmente os requisitos de E/S. Como as consultas de análise examinam um grande número de linhas, elas normalmente são associadas à E/S e, portanto, reduzir a 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.

Índices columnstore reduzem a E/S e otimizam as operações in-memory por meio de alta compactação de dados, eliminação de columnstores, eliminação de rowgroups e processamento em lotes.

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.

  • Índices columnstore leem dados compactados do disco, o que significa que menos bytes de dados precisam ser lidos da 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 obter 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 será compactada country-region facilmente e exigirá pouco armazenamento. A compactação linha por linha não é capaz de capitalizar a semelhança de valores de coluna dessa maneira e deve usar mais bytes para compactar os valores na country-region coluna.

Eliminação de colunas

Í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 fisicamente armazenados juntos e não podem ser facilmente separados. O Processador de Consultas precisa ler em uma linha inteira para recuperar valores de coluna específicos, aumentando a E/S porque dados extras são lidos desnecessariamente na memória.

Por exemplo, se uma tabela tiver 50 colunas e a consulta utilizar apenas 5 dessas colunas, o índice columnstore buscará apenas 5 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 rowgroups

Para verificações de tabela completa, um grande percentual dos dados geralmente não corresponde aos critérios de predicado de consulta. Usando metadados, o índice columnstore pode ignorar a leitura nos rowgroups que não contêm dados necessários para o resultado da consulta, tudo isso sem a E/S real. Essa capacidade, chamada de eliminação de rowgroups, reduz ainda mais a E/S para verificações de tabela completas e, portanto, melhora o desempenho de consultas.

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 consulta pode escolher uma verificação de tabela completa. Sem a capacidade de ignorar rowgroups, uma verificação completa da tabela pode ser demorada, especialmente para tabelas grandes.

Quando uma consulta de análise se beneficia de eliminação de rowgroups para uma verificação de tabela completa?

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, embora os índices columnstore não garantam uma ordem classificada, as linhas nessa tabela são carregadas em uma ordem classificada por data. Com o tempo, essa mesa 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. Índices columnstore podem eliminar o acesso aos dados dos 39 trimestres anteriores, apenas observando 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 rowgroups são ignorados em uma verificação de tabela completa?

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 grupo de linhas ou em um grupo de linhas vizinho.

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

Execução em modo de lote

A execução em modo de lote refere-se ao processamento de um conjunto de linhas, normalmente até 900, agrupadas para eficiência de execução. Por exemplo, a consulta SELECT SUM (Sales) FROM SalesData agrega as vendas totais da tabela SalesData. Na execução em modo de lote, o mecanismo de execução de consulta calcula a agregação de 900 valores em grupo. Isso estende aos metadados os custos de acesso e outros tipos de sobrecarga em todas as linhas em um lote, em vez de pagar o custo para cada linha; assim, o caminho do código é reduzido significativamente. O processamento em modo de lote opera em dados compactados quando possível e elimina alguns dos operadores de troca usados pelo processamento em modo de linha, acelerando as consultas de análise em ordens de magnitude.

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 no modo de lote Quando usado SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) e Banco de dados do SQL1 Comentários
Operações DML (insert, delete, update, merge) não não não DML não é uma operação de modo de lote porque ele 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 SCAN Não disponível sim sim Para índices columnstore, podemos enviar por push o predicado por push para o nó SCAN.
Verificação de índice columnstore (não clusterizado) SCAN 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.
computar escalar Uma 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 de agregação 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.
junção de mesclagem não não não
consultas multithread sim sim sim
loops aninhados não não não
consultas de thread único executando no MAXDOP 1 não não sim
consultas de thread único com um plano de consulta serial não não sim
sort Classificar por cláusula em SCAN com índice columnstore. não não sim
classificação superior não não sim
agregações 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)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server

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

Pushdown de agregação

Um caminho de execução normal para a computação de agregação para buscar as linhas qualificadas do nó SCAN e agregar os valores no Modo de Lote. Embora isso ofereça um bom desempenho, a partir do SQL Server 2016 (13.x), a operação de agregação pode ser enviada por push 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 ficar sobre o nó SCAN ou o nó SCAN com GROUP BY.
  • Essa agregação não é uma agregação de distinção.
  • 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;

Aplicação de predicado de cadeia de caracteres

Ao criar um esquema de data warehouse, a modelagem de esquema recomendada é usar um esquema estrela ou floco de neve, consistindo 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 chaves 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)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2016 (13.x)

A aplicação de 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 a aplicação de 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:
    • Nenhuma aplicação de predicado da cadeia de caracteres para rowgroups delta. Não há nenhum dicionário para colunas em rowgroups delta.
    • Não há nenhuma aplicação de predicado de cadeia de caracteres se o dicionário excede 64 KB de entradas.
    • Não há suporte para expressões que avaliam nulos.

Eliminação do segmento

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)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL Server 2022 (16.x)SQL

Depois de atualizar para uma versão do SQL Server que dá suporte à eliminação de segmentos mínimos/máximos de cadeia de caracteres (SQL Server 2022 (16.x) e posterior), o índice columnstore não beneficiará esse recurso até que ele seja recriado usando um REBUILD ou DROP/CREATE.

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

Atualmente, apenas o SQL Server 2022 (16.x) e versões posteriores dão suporte à eliminação de rowgroup de columnstore clusterizados para o prefixo de LIKEpredicados, por exemplo column LIKE 'string%'. A eliminação de segmento não é compatível com o uso sem prefixo de LIKE, como column LIKE '%string'.

Os índices columnstore clusterizados ordenados também se beneficiam da eliminação de segmentos, especialmente para colunas de cadeia de caracteres. Em índices columnstore clusterizados ordenados, a eliminação de segmento na primeira coluna na chave de índice é mais eficaz, porque é classificada. Os ganhos de desempenho devido à eliminação de segmentos em outras colunas da tabela serão menos previsíveis. Para saber mais sobre índices columnstore clusterizados ordenados, veja Usar um índice columnstore clusterizado ordenado para tabelas de data warehouse grandes. 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 saídas como as seguintes para indicar que a eliminação de segmento ocorreu. Os grupos de linhas são compostos de segmentos de coluna, portanto, isso pode indicar a eliminação de segmentos. O exemplo de saída a seguir SET STATISTICS IO de uma consulta, aproximadamente 83% dos dados foram ignorados pela consulta:

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

Conteúdo relacionado