Índices columnstore – diretrizes de design

  • 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)

Recomendações de alto nível para criação de í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.

Pré-requisitos

Este artigo pressupõe que você esteja familiarizado com a terminologia e a arquitetura columnstore. 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 tabela?
  • 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 estão estáveis. As consultas devem atualizar e excluir menos de 10% das linhas.
  • Eu tenho tabelas de fatos e de dimensões para um data warehouse?
  • É necessário executar análise em uma carga de trabalho transacional? Se for o caso, veja as diretrizes de design columnstore para análises operacionais em tempo real.

Talvez não seja necessário 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 você criar uma tabela como um índice columnstore, será possível converter facilmente a tabela de volta para uma tabela rowstore removendo o índice columnstore.

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

Opção columnstore Recomendações para quando usar Compactação
Índice columnstore clusterizado Use para:

1) Carga de trabalho de data warehouse tradicional com uma estrela ou esquema floco de neve

2) Cargas de trabalho IOT (Internet das Coisas) que inserem grandes volumes de dados com o mínimo de atualizações e exclusões.		 Média de 10 vezes
Índice columnstore clusterizado ordenado Use quando um índice columnstore clusterizado é consultado por meio de uma única coluna de predicado ordenado ou conjunto de colunas. Essa diretriz é semelhante à escolha da(s) coluna(s) de chave para um índice clusterizado de rowstore, embora os rowgroups subjacentes compactados se comportem de maneira diferente. Para obter mais informações, consulte CREATE COLUMNSTORE INDEX e Ajuste de desempenho com índices columnstore clusterizados ordenados. Média de 10 vezes
Índices de árvore B não clusterizados em um índice columnstore clusterizado Use para:

1. Impor uma chave primária e restrições de chaves estrangeiras em um índice columnstore clusterizado.

2. Acelerar consultas que pesquisam valores específicos ou pequenos intervalos de valores.

3. Acelerar atualizações e exclusões de linhas específicas.		 Em média, 10x mais um pouco de armazenamento adicional para os NCIs.
Índice columnstore não clusterizado em um índice de heap ou árvore B baseado em disco Use para:

1) Uma carga de trabalho OLTP que tem algumas consultas de análise. É possível remover índices de árvore B criados para análise e substituí-los por um índice columnstore não clusterizado.

2) Muitas cargas de trabalho OLTP tradicionais que realizam operações ETL (Extração, Transformação e Carregamento) para mover dados para um data warehouse separado. É possível eliminar a ETL e um data warehouse separado, criando um índice columnstore não clusterizado em algumas tabelas OLTP.		 O NCCI é um índice adicional que requer, em média, 10% a mais de armazenamento.
Índice columnstore em uma tabela na memória As mesmas recomendações que o índice columnstore não clusterizado em uma tabela baseada em disco, a menos que a tabela base seja uma tabela na memória. O índice columnstore é um índice adicional.

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 warehouse. Í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 tiver 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 da compactação columnstore e do desempenho de consultas.
  • 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 exigir 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 uma tabela de heap ou temporária quando você precisar armazenar e excluir os dados rapidamente.
  • A tabela tiver 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 de consultas até você executar uma operação chamada reorganizar 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 no data warehouse.

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

Para obter a disponibilidade de índice columnstore ordenado, consulte Índices columnstore: visão geral#ordered-columnstore-index-availability).

Considere o uso de um índice columnstore clusterizado 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 clusterizada ordenada é estática, os índices columnstore clusterizados ordenados podem fornecer vantagens significativas de desempenho em relação a índices columnstore clusterizados não ordenados ou índices clusterizados de rowstore para cargas de trabalho analíticas.
  • Quanto mais valores distintos na primeira coluna da chave de índice columnstore clusterizada ordenada, melhores serão os ganhos de desempenho para índices columnstore clusterizados ordenados. Isso se deve à eliminação aprimorada de segmentos para dados de cadeia de caracteres. Para obter mais informações, veja eliminação de segmento.
  • Escolha uma chave de índice columnstore clusterizada ordenada que será consultada com frequência e pode se beneficiar da eliminação de segmento, especialmente a primeira coluna da chave. Os ganhos de desempenho devido à eliminação de segmentos em outras colunas da tabela serão menos previsíveis.
  • Casos de uso em que apenas os dados analíticos mais recentes devem ser consultados, por exemplo, os últimos 15 segundos, índices columnstore clusterizados ordenados podem fornecer eliminação de segmento para dados mais antigos. A primeira coluna na chave dos dados columnsore clusterizados ordenados deve ser os dados de data/hora, como uma data/hora inserida ou criada. A eliminação de segmento seria mais eficaz em um índice columnstore clusterizado ordenado do que em um índice columnstore clusterizado não ordenado.
  • Considere índices columnstore clusterizados 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 clusterizado ordenado pode não ser tão eficaz nestes 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 tuplas. Isso pode ser atenuado pela manutenção regular do índice columnstore com ALTER INDEX REORGANIZE.

Adicionar índices de árvore B não clusterizados para buscas de tabela eficientes

A partir do SQL Server 2016 (13.x), é possível criar índices árvore B não clusterizados ou índices rowstore como índices secundários em um índice columnstore clusterizado. O índice de árvore B não clusterizado é atualizado conforme ocorrem alterações no índice columnstore. Esse é um recurso avançado que você pode 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 poderá ser inserido no índice de árvore B, o SQL Server não pode inserir o valor no columnstore.

Considere a possibilidade de 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 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, é possível executar a análise 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 talvez requeira um adicional de 2 GB. 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, é possível 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. É muito fácil experimentá-lo, pois é possível habilitar um índice columnstore não clusterizado sem alterações para seu 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 de partições para tabelas rowstore também se aplicam a índices columnstore.

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

  • Controlar o tamanho de 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 ignorarão os grupos de arquivos somente 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, use 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. Os dados serão mais lentos para desempenho de consultas. Isso será aceitável se a partição for consultada raramente.

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 columnstore. 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 irão para rowgroups delta.

Exemplo:

  • 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 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 serão muito pequenos para se ter melhores desempenho de consultas e 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 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 columnstore para obter melhor desempenho de consultas

Normalmente, a compactação columnstore oferece taxas de compactação 10x 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 oferece taxas de compactação de dados maiores do que a compactação columnstore, mas ela tem um porém. 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 rowgroup

É possível configurar o número máximo de processadores para converter um índice heap ou de árvore B clusterizado em um índice 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 provavelmente 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 de classificação de um índice de árvore B

Como o índice de árvore B já armazena linhas em uma ordem de classificação, preservar essa ordem quando as linhas são compactadas em um índice columnstore pode melhorar o desempenho de 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 rowgroups 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 junto 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, veja eliminação de segmento.

Essas tarefas são destinadas a criar e a manter índices columnstore.

Tarefa Artigos de referência Observações
Crie uma tabela como columnstore. CREATE TABLE (Transact-SQL) Começando com o SQL Server 2016 (13.x), você pode criar a tabela como um índice columnstore clusterizado. Não é preciso criar primeiro uma tabela rowstore e convertê-la em columnstore.
Crie uma tabela de memória com um índice columnstore. CREATE TABLE (Transact-SQL) A partir do SQL Server 2016 (13.x), você pode criar uma tabela com otimização de memória com um índice columnstore. O índice columnstore também pode ser adicionado após a criação da tabela, usando a sintaxe ALTER TABLE ADD INDEX.
Converta uma tabela rowstore em columnstore. CREATE COLUMNSTORE INDEX (Transact-SQL) Converta um heap ou árvore B existente em columnstore. Exemplos mostram como lidar com os índices existentes e o nome do índice ao realizar essa conversão.
Converta uma tabela columnstore em rowstore. CREATE CLUSTERED INDEX (Transact-SQL) ou Converter uma tabela columnstore novamente em um heap rowstore Geralmente, essa conversão não é necessária, mas pode haver ocasiões em que você precisa realizá-la. Exemplos mostram como converter um columnstore em uma pilha ou um índice clusterizado.
Crie um índice columnstore em uma tabela rowstore. CREATE COLUMNSTORE INDEX (Transact-SQL) Uma tabela rowstore pode ter um índice columnstore. Começando com o SQL Server 2016 (13.x), o índice columnstore pode ter uma condição filtrada. Exemplos mostram a sintaxe básica.
Crie índices de alto desempenho para análises operacionais. Introdução ao Columnstore para análise operacional em tempo real Descreve como criar índices de árvore B e columnstore complementares, para que as consultas OLTP usem índices de árvore B e as consultas de análise usem índices columnstore.
Crie índices columnstore de alto desempenho para data warehouse. Índices columnstore no data warehouse Descreve como usar índices de árvore B em tabelas columnstore para criar consultas de data warehouse de alto desempenho.
Use um índice de árvore B para impor uma restrição de chave primária em um índice columnstore. Índices columnstore no data warehouse Mostra como combinar índices columnstore e de árvore B para impor restrições de chave primária no índice columnstore.
Remover um índice columnstore DROP INDEX (Transact-SQL) A remoção de um índice columnstore usa a sintaxe DROP INDEX padrão usada pelos índices de árvore B. Remover um índice columnstore clusterizado converterá a tabela columnstore em uma pilha.
Excluir uma linha de um índice columnstore DELETE (Transact-SQL) Use DELETE (Transact-SQL) para excluir uma linha.

Linha columnstore: o SQL Server marca a linha como excluída logicamente, mas não recupera o armazenamento físico da linha até que o índice seja recompilado.

Linha deltastore: o SQL Server exclui a linha lógica e fisicamente.
Atualizar uma linha no índice columnstore UPDATE (Transact-SQL) Use UPDATE (Transact-SQL) para atualizar uma linha.

Linha columnstore: o SQL Server marca a linha como excluída logicamente e insere a linha atualizada no deltastore.

Linha deltastore: o SQL Server atualiza a linha no deltastore.
Força todas as linhas no deltastore a ir para o columnstore. ALTER INDEX (Transact-SQL) ... REBUILD

Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos		 ALTER INDEX com a opção REBUILD força todas as linhas a ir para o columnstore.
Desfragmentar um índice columnstore ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE desfragmenta os índices columnstore online.
Mescle tabelas com índices columnstore. MERGE (Transact-SQL)

Conteúdo relacionado

Para criar um índice columnstore vazio para:

Para obter mais informações sobre como converter um índice de heap ou árvore B rowstore existente em um índice columnstore clusterizado ou criar um índice columnstore não clusterizado, veja CREATE COLUMNSTORE INDEX (Transact-SQL).