Índices columnstore - diretrizes de design

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Banco de dados SQL no Microsoft Fabric

Recomendações gerais para projetar índices columnstore. Algumas boas decisões de design o ajudam a obter a alta compactação de dados e o alto desempenho de consulta que os índices columnstore foram projetados para oferecer.

Prerequisites

Este artigo pressupõe que você esteja familiarizado com a arquitetura columnstore e a terminologia. Para obter mais informações, consulte Índices columnstore: visão geral e Arquitetura de Índice Columnstore.

Conheça seus requisitos de dados

Antes de criar um índice columnstore, entenda o máximo possível sobre seus requisitos de dados. Por exemplo, pense nas respostas para estas perguntas:

• Quão grande é a minha mesa?
• Na maioria das vezes, as minhas consultas realizam análises que verificam grandes intervalos de valores? Índices columnstore são criados para funcionar bem para verificações de grandes intervalos em vez de pesquisar valores específicos.
• Minha carga de trabalho realiza muitas atualizações e exclusões? Índices columnstore funcionam bem quando os dados são estáveis. As consultas devem atualizar e excluir menos de 10% das linhas.
• Tenho tabelas fatorial e de dimensão para um data warehouse?
• Preciso fazer análise em uma carga de trabalho transacional? Se for o caso, consulte as diretrizes de design de columnstore para análise operacional em tempo real.

Você pode não precisar de um índice columnstore. Tabelas rowstore (ou árvore B) com heaps ou índices clusterizados têm melhor desempenho em consultas que buscam nos dados, procurando um valor específico ou para consultas em um pequeno intervalo de valores. Use índices rowstore com cargas de trabalho transacionais, pois eles geralmente tendem a exigir buscas de tabela em vez de verificações de tabela em grandes intervalos.

Escolher o melhor índice columnstore para suas necessidades

Um índice columnstore é clusterizado ou não clusterizado. Um índice columnstore clusterizado pode ter um ou mais índices de árvore B não clusterizados. Os índices columnstore são mais fáceis de experimentar. Se criar uma tabela como um índice columnstore, você pode converter facilmente a tabela de volta para uma tabela rowstore removendo o índice columnstore.

Abaixo, há um resumo das opções e recomendações.

Usar um índice columnstore clusterizado para grandes tabelas de data warehouse

O índice columnstore clusterizado é mais do que um índice, ele é o armazenamento de tabela primário. Ele alcança uma alta compactação de dados e uma melhoria significativa no desempenho de consultas em grandes tabelas de fatos e de dimensões de data warehousing. Índices columnstore clusterizados são mais adequados para consultas de análise em vez de consultas transacionais, já que as consultas de análise tendem a realizar operações em grandes intervalos de valores em vez de pesquisar valores específicos.

Considere usar um índice columnstore clusterizado quando:

• Cada partição tem pelo menos um milhão de linhas. Índices columnstore tiver rowgroups dentro de cada partição. Se a tabela for muito pequena para preencher um rowgroup em cada partição, você não terá os benefícios do desempenho de consultas e da compactação columnstore.
• As consultas realizam principalmente análises em intervalos de valores. Por exemplo, para localizar o valor médio de uma coluna, a consulta precisa examinar todos os valores de coluna. Em seguida, ele agrega os valores somando-os para determinar a média.
• A maioria das inserções se dão em grandes volumes de dados com o mínimo de atualizações e de exclusões. Muitas cargas de trabalho como IOT (Internet das Coisas) inserem grandes volumes de dados com o mínimo de atualizações e exclusões. Essas cargas de trabalho podem se beneficiar da compactação e de ganhos de desempenho de consultas oriundos do uso de um índice columnstore clusterizado.

Não use um índice columnstore clusterizado quando:

• A tabela requer tipos de dados varchar(máx.), nvarchar(máx.) ou varbinary(máx.). Ou, crie o índice columnstore para que ele não inclua essas colunas (Aplica-se a: SQL Server 2016 (13.x) e versões anteriores).
• Os dados da tabela não são permanentes. Considere usar um heap ou uma tabela temporária quando você precisar armazenar e excluir os dados rapidamente.
• A tabela tem menos de um milhão de linhas por partição.
• Mais de 10% das operações na tabela são atualizações e exclusões. Grande número de atualizações e exclusões causam fragmentação. A fragmentação afeta as taxas de compactação e o desempenho da consulta até a execução de uma operação chamada reorganização, que força todos os dados para o columnstore e remove a fragmentação. Para obter mais informações, consulte Minimizing index fragmentation in columnstore indexes (Minimizando a fragmentação de índice nos índices columnstore).

Para obter mais informações, consulte Índices columnstore em data warehousing.

Usar um índice columnstore ordenado para grandes tabelas de data warehouse

Para obter a disponibilidade de índices columnstore ordenados, confira Índices columnstore: visão geral.

Considere usar um índice columnstore ordenado nos seguintes cenários:

• Quando os dados são relativamente estáticos (sem gravações e exclusões frequentes) e a chave de índice columnstore ordenada é estática, os índices columnstore ordenados podem fornecer vantagens significativas de desempenho em relação aos índices columnstore não ordenados ou índices rowstore para cargas de trabalho analíticas.
• Quanto mais distintos forem os valores na primeira coluna da chave do índice columnstore ordenado, melhor poderá ser o ganho de desempenho. Isso se deve à eliminação aprimorada de segmentos para dados de cadeia de caracteres. Para obter mais informações, consulte eliminação de segmento.
• Escolha uma chave de índice columnstore ordenada que seja consultada com frequência e possa se beneficiar da eliminação de segmentos, principalmente a primeira coluna da chave. Os ganhos de desempenho devido à eliminação de segmento em outras colunas na tabela são menos previsíveis.
• Em casos de uso onde apenas os dados analíticos mais recentes devem ser consultados, como nos últimos 15 segundos, os índices columnstore ordenados podem eliminar segmentos de dados mais antigos. A primeira coluna na chave de dados columnstore ordenados deve consistir em dados de data/hora, como uma data/hora de criação ou inserção. A eliminação do segmento seria mais eficaz em um índice columnstore ordenado do que em um índice columnstore não ordenado.
• Considere índices columnstore ordenados em tabelas que contêm chaves com dados GUID, onde o tipo de dados uniqueidentifier agora pode ser usado para eliminação de segmento.

Um índice columnstore ordenado pode não ser tão eficaz nesses cenários:

• Semelhante a outros índices columnstore, uma alta taxa de atividade de inserção pode criar E/S de armazenamento excessiva.
• Para cargas de trabalho em que há muitas operações de gravação, a qualidade da eliminação de segmentos será reduzida ao longo do tempo devido à manutenção do rowgroup pelo motor de tupla. Isso pode ser atenuado pela manutenção regular do índice columnstore com ALTER INDEX REORGANIZE.

Adicionar índices não clusterizados de B-tree para busca eficiente nas tabelas

A partir do SQL Server 2016 (13.x), é possível criar índices B-tree não clusterizados ou índices rowstore como índices secundários em um índice columnstore clusterizado. O índice B-tree não clusterizado é atualizado à medida que ocorrem alterações no índice columnstore. Este é um recurso poderoso que você pode usar a seu favor.

Usando o índice de árvore B secundário, é possível pesquisar com eficiência linhas específicas sem examinar todas as linhas. Outras opções também são disponibilizadas. Por exemplo, é possível impor uma restrição de chave primária ou estrangeira usando uma restrição UNIQUE no índice de árvore B. Como um valor não exclusivo não pode ser inserido no índice B-tree, o SQL Server não pode inserir o valor no columnstore.

Considere usar um índice de árvore b em um índice columnstore para:

• Executar consultas que pesquisam valores específicos ou pequenos intervalos de valores.
• Impor uma restrição como uma chave primária ou restrição de chave estrangeira.
• Realizar operações de atualização e exclusão de maneira eficiente. O índice de árvore B pode localizar rapidamente as linhas específicas para atualizações e exclusões sem examinar toda a tabela ou a partição de uma tabela.
• Você tem armazenamento adicional disponível para armazenar o índice de árvore B.

Usar um índice do tipo columnstore não clusterizado para análise em tempo real

A partir do SQL Server 2016 (13.x), é possível ter um índice columnstore não clusterizado em uma tabela rowstore baseada em disco ou uma tabela OLTP in-memory. Isso possibilita executar a análise em tempo real em uma tabela transacional. Enquanto as transações estão ocorrendo na tabela subjacente, você pode executar análises no índice columnstore. Como uma tabela gerencia os índices, as alterações estão disponíveis em tempo real para índices rowstore e columnstore.

Como um índice columnstore tem uma compactação de dados 10x melhor do que um índice rowstore, ele só precisa de uma pequena quantidade de armazenamento extra. Por exemplo, se a tabela rowstore compactada usa 20 GB, o índice columnstore pode exigir 2 GB adicional. O espaço adicional necessário também depende do número de colunas no índice columnstore não clusterizado.

Considere usar um índice columnstore não clusterizado para:

• Executar análise em tempo real em uma tabela rowstore transacional. É possível substituir os índices de árvore B existentes desenvolvidos para análises por um índice columnstore não clusterizado.

• Acabe com a necessidade de ter um data warehouse separado. Normalmente, as empresas executam transações em uma tabela rowstore e, em seguida, carregam os dados em um data warehouse separado para executar a análise. Para muitas cargas de trabalho, você pode eliminar o processo de carregamento e o data warehouse separado criando um índice columnstore não clusterizado em tabelas transacionais.

O SQL Server 2016 (13.x) oferece várias estratégias para dar a esse cenário um bom desempenho. É fácil experimentá-lo, pois você pode habilitar um índice columnstore não clusterizado sem alterações no aplicativo OLTP.

Para adicionar recursos adicionais de processamento, é possível executar a análise em um secundário legível. Usar um secundário legível separa o processamento da carga de trabalho transacional e a carga de trabalho de análise.

Para obter mais informações, consulte Introdução ao Columnstore para análise operacional em tempo real

Para obter mais informações sobre como escolher o melhor índice columnstore, consulte o blog de Sunil Agarwal Which columnstore index is right for my workload? (Qual índice columnstore é o certo para a minha carga de trabalho?).

Usar partições de tabela para gerenciamento de dados e desempenho de consultas

Os índices columnstore dão suporte ao particionamento, que é uma boa maneira de gerenciar e arquivar dados. O particionamento também melhora o desempenho de consultas limitando operações para uma ou mais partições.

Usar partições para tornar os dados mais fáceis de gerenciar

Para grandes tabelas, a única maneira prática de gerenciar intervalos de dados é usando partições. As vantagens das partições para tabelas de armazenamento em linha também se aplicam a índices de armazenamento em coluna.

Por exemplo, tabelas rowstore e columnstore usam partições para:

• Controlar o tamanho dos backups incrementais. É possível fazer backup de partições para separar grupos de arquivos e marcá-los como somente leitura. Fazendo isso, os backups futuros ignoram os grupos de arquivos somente de leitura.
• Economizar custos de armazenamento movendo uma partição mais antiga para um armazenamento mais barato. Por exemplo, você poderia usar a alternância de partição para mover uma partição para um local de armazenamento mais barato.
• Realizar operações com eficiência, limitando as operações a uma partição. Por exemplo, é possível definir como destino apenas as partições fragmentadas para manutenção de índice.

Além disso, com um índice columnstore, você usa o particionamento para:

• Economizar mais 30% nos custos de armazenamento. Você pode compactar partições mais antigas com as opções de COLUMNSTORE_ARCHIVE compactação. O desempenho da consulta pode ser mais lento, o que pode ser aceitável se a partição for consultada com pouca frequência.

Usar partições para melhorar o desempenho de consultas

Usando partições, é possível limitar suas consultas para examinar somente partições específicas que limita o número de linhas a serem examinadas. Por exemplo, se o índice for particionado por ano e a consulta estiver analisando dados do ano passado, será necessário verificar apenas os dados em uma partição.

Usar menos partições para um índice columnstore

A menos que você tenha um tamanho de dados grande o suficiente, um índice columnstore funciona melhor com partições menores do que a que você poderia usar para um índice rowstore. Se você não tiver pelo menos um milhão de linhas por partição, a maioria de suas linhas poderá ir para o deltastore em que elas não receberão o benefício da compactação de columnstore para o desempenho. Por exemplo, se você carregar um milhão de linhas em uma tabela com 10 partições e cada partição receber 100.000 linhas, todas as linhas vão para os grupos de linhas delta.

Example:

• Carregue 1.000.000 de linhas em uma partição ou em uma tabela não particionada. Você obtém um rowgroup compactado com 1.000.000 de linhas. Isso é ótimo para alta compactação de dados e rápido desempenho de consultas.
• Carregue 1.000.000 de linhas uniformemente em 10 partições. Cada partição recebe 100.000 linhas, o que é menor que o limite mínimo de compactação de columnstore. Como resultado, o índice columnstore poderia ter 10 rowgroups delta com 100.000 linhas em cada. Há maneiras de forçar os rowgroups delta no columnstore. No entanto, se essas forem as únicas linhas no índice columnstore, os rowgroups compactados são muito pequenos para alcançar um melhor desempenho de consultas e de compactação.

Para obter mais informações sobre particionamento, consulte a postagem do blog de Sunil Agarwal, Should I partition my columnstore index? (Devo particionar meu índice columnstore?).

Escolher o método adequado de compactação de dados

O índice columnstore oferece duas opções de compactação de dados: compactação de columnstore e compactação de arquivamento. É possível escolher a opção de compactação quando você cria o índice ou alterá-la posteriormente com ALTER INDEX ... REBUILD.

Usar a compactação de columnstore para alcançar o melhor desempenho de consultas

Normalmente, a compactação de columnstore oferece taxas de compactação 10 vezes melhores em índices rowstore. É o método de compactação padrão para índices columnstore e permite um rápido desempenho de consultas.

Usar a compactação de arquivamento para obter melhor compactação de dados

A compactação de arquivamento foi criada para oferecer máxima compactação quando o desempenho de consultas não é tão importante. Ela alcança taxas de compactação de dados maiores do que a compactação de columnstore, mas ela tem uma desvantagem. Ela leva mais tempo para compactar e descompactar os dados. Portanto, não é adequada para um rápido desempenho de consultas.

Usar otimizações ao converter uma tabela rowstore em um índice columnstore

Se seus dados já estiverem em uma tabela rowstore, será possível usar CREATE COLUMNSTORE INDEX para converter a tabela em um índice columnstore clusterizado. Há algumas otimizações que melhorarão o desempenho de consultas após a conversão da tabela, que serão descritas a seguir.

Use MAXDOP para melhorar a qualidade do rowgroup

É possível configurar o número máximo de processadores para converter um índice heap ou um índice B-tree clusterizado em um índice do tipo columnstore. Para configurar esses processadores, use o MAXDOP (grau máximo de opção de paralelismo).

Se você tiver grandes quantidades de dados, o maxdop 1 pode ser muito lento. Aumentar o MAXDOP para 4 funciona bem. Se isso resultar em alguns rowgroups que não têm o número ideal de linhas, você pode executar ALTER INDEX REORGANIZE para mesclá-los em segundo plano.

Manter a ordem classificada de um índice de árvore b

Como o índice B-tree já armazena linhas em uma ordem classificada, preservar essa ordem quando as linhas são compactadas em um índice columnstore pode melhorar o desempenho das consultas.

O índice columnstore não classifica os dados, mas usa metadados para controlar os valores mínimo e máximo de cada segmento de coluna em cada rowgroup. Ao examinar um intervalo de valores, é possível calcular rapidamente quando ignorar o rowgroup. Quando os dados estão ordenados, mais grupos de linhas podem ser ignorados.

Para preservar a ordem de classificação durante a conversão:

• Use CREATE COLUMNSTORE INDEX com a cláusula DROP_EXISTING. Isso também preserva o nome do índice. Se você tiver scripts que já usam o nome do índice rowstore, não será necessário atualizá-los.

  Este exemplo converte um índice rowstore clusterizado em uma tabela chamada MyFactTable em um índice columnstore clusterizado. O nome do índice, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, permanece o mesmo.

  CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
  ON MyFactTable
  WITH (DROP_EXISTING = ON);
  

Entenda a eliminação de segmentos

Cada rowgroup contém um segmento de coluna para cada coluna na tabela. Cada segmento de coluna é compactado e armazenado em meio físico.

Há metadados com cada segmento para permitir a eliminação rápida de segmentos sem lê-los. 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. Para obter mais informações, consulte eliminação de segmento.

Tarefas relacionadas

Para obter um resumo das tarefas comuns para criar e manter índices columnstore, consulte Tarefas relacionadas.

Conteúdo relacionado

• Novidades nos índices de columnstore
• Índices columnstore em data warehousing
• Índices columnstore - Desempenho da consulta