Columnstore indexes - orientação de design

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

Recomendações de alto nível para conceber índices columnstore. Algumas boas decisões de design ajudam você a alcançar a alta compactação de dados e o desempenho de consulta que os índices columnstore foram projetados para fornecer.

Prerequisites

Este artigo pressupõe que o leitor esteja familiarizado com a arquitetura e a terminologia de armazenamento em coluna. Para obter mais informações, consulte Columnstore indexes: Overview e Columnstore Index Architecture.

Conheça os 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 a estas perguntas:

• Qual é o tamanho da minha mesa?
• As minhas consultas executam principalmente análises que varrem grandes intervalos de valores? Os índices Columnstore são concebidos para funcionar eficientemente em varreduras de grandes intervalos, em vez de pesquisar por valores específicos.
• Minha carga de trabalho executa muitas atualizações e exclusões? Os índices Columnstore funcionam bem quando os dados são estáveis. As consultas deverão atualizar e apagar menos de 10% das linhas.
• Tenho tabelas de factos e dimensões para um armazém de dados?
• Preciso executar análises em uma carga de trabalho transacional? Em caso afirmativo, consulte as diretrizes de design do columnstore para análise operacional em tempo real.

Talvez você não precise de um índice columnstore. As tabelas de armazenamento de linhas (ou de árvore B) com heaps ou índices agrupados têm melhor desempenho em consultas que procuram um valor específico nos dados ou em consultas dentro de um pequeno intervalo de valores. Use índices de rowstore com cargas de trabalho transacionais, pois tendem a exigir principalmente consultas de tabela em vez de varreduras de tabela de grande alcance.

Escolha o melhor índice de armazenamento em colunas para as suas necessidades

Um índice columnstore é clusterizado ou não clusterizado. Um índice columnstore clusterizado pode incluir um ou mais índices B-tree não clusterizados. Os índices Columnstore são fáceis de testar. Se criar uma tabela como um índice columnstore, poderá facilmente convertê-la novamente numa tabela rowstore eliminando o índice columnstore.

Aqui está um resumo das opções e recomendações.

Usar um índice de armazenamento de colunas clusterizado para grandes tabelas de armazém de dados.

O índice columnstore clusterizado é mais do que um índice; é o principal armazenamento de tabela. Esta tecnologia proporciona alta compactação dos dados e melhora significativamente o desempenho das consultas em grandes tabelas de factos e dimensões de data warehouse. Os índices columnstore agrupados são mais adequados para consultas de análise em vez de consultas transacionais, uma vez que as consultas de análise tendem a executar operações em grandes intervalos de valores em vez de procurar valores específicos.

Considere o uso de um índice columnstore clusterizado quando:

• Cada partição tem pelo menos um milhão de linhas. Os índices Columnstore têm grupos de linhas dentro de cada partição. Se a tabela for muito pequena para preencher um grupo de linhas dentro de cada partição, é possível que não se obtenham os benefícios da compressão de armazenamento em coluna e do desempenho da consulta.
• As consultas executam principalmente análises em intervalos de valores. Por exemplo, para encontrar o valor médio de uma coluna, a consulta precisa verificar todos os valores de coluna. Em seguida, agrega os valores somando-os para determinar a média.
• A maioria das inserções de dados ocorre em grandes volumes de dados, com atualizações e eliminações mínimas. Muitas cargas de trabalho, como a Internet das Coisas (IOT), inserem grandes volumes de dados com atualizações e exclusões mínimas. Essas cargas de trabalho podem beneficiar dos ganhos de desempenho em compressão e consulta proporcionados pela utilização de um índice columnstore clusterizado.

Não use um índice columnstore clusterizado quando:

• A tabela requer o tipo de dados varchar(max), nvarchar(max)ou varbinary(max). 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 o uso de um heap ou tabela temporária caso necessite de armazenar e eliminar 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. Um grande número de atualizações e exclusões causa fragmentação. A fragmentação afeta as taxas de compressão e o desempenho das consultas até que se execute uma operação chamada reorganizar, que força todos os dados no armazenamento em colunas e elimina a fragmentação. Para obter mais informações, consulte Minimizando a fragmentação do índice no columnstore index.

Para obter mais informações, consulte índices Columnstore em armazenamento de dados.

Usar um índice columnstore ordenado para tabelas grandes de armazém de dados

Para obter informações sobre a disponibilidade de índices columnstore ordenados, consulte Columnstore indexes: Overview.

Considere o uso de um índice columnstore ordenado nos seguintes cenários:

• Quando os dados são relativamente estáticos (sem gravações e eliminações frequentes) e a chave do índice columnstore ordenada é estática, os índices columnstore ordenados podem oferecer vantagens significativas de desempenho em relação aos índices columnstore não ordenados ou aos índices de linhas para cargas de trabalho analíticas.
• Quanto mais valores distintos houver na primeira coluna da chave de índice columnstore ordenada, maiores poderão ser os ganhos em desempenho. Isso se deve à melhor eliminação de segmentos para dados de cadeia de caracteres. Para mais informações, consulte eliminação de segmento.
• Escolha uma chave de índice columnstore ordenada que seja frequentemente consultada e que se possa beneficiar da eliminação de segmentos, dando especial atenção à primeira coluna da chave. Os ganhos de desempenho devido à eliminação de segmentos em outras colunas da tabela são menos previsíveis.
• Casos de uso em que apenas os dados analíticos mais recentes devem ser consultados, como os dos últimos 15 segundos, podem tirar proveito de índices columnstore ordenados para eliminar segmentos de dados mais antigos. A primeira coluna da chave dos dados columnstore ordenados deve conter dados de data/hora, como a data/hora de inserção ou criaçã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, nos quais o tipo de dados uniqueidentifier agora pode ser utilizado para eliminação de segmento.

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

• Semelhante a outros índices columnstore, uma frequência elevada de inserções poderá criar E/S de armazenamento excessivas.
• Para cargas de trabalho que envolvem muitas operações de escrita, a qualidade da eliminação de segmentos será reduzida ao longo do tempo devido à gestão do grupo de linhas pelo movedor de tuplos. Isso pode ser atenuado pela manutenção regular do índice columnstore com ALTER INDEX REORGANIZE.

Para pesquisas eficientes em tabelas, adicione índices não-agrupados de B-tree.

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

Usando o índice secundário de árvore B, pode-se consultar linhas específicas de forma eficiente sem verificar todas as linhas. Outras opções também ficam disponíveis. Por exemplo, você pode impor uma restrição de chave primária ou estrangeira usando uma restrição UNIQUE no índice da árvore B. Como um valor não exclusivo não consegue ser inserido no índice B-tree, o SQL Server não pode inserir o valor na columnstore.

Considere a utilização de um índice B-tree num índice columnstore para:

• Execute consultas que pesquisem valores específicos ou pequenos intervalos de valores.
• Imponha uma restrição, como uma restrição de chave primária ou de chave estrangeira.
• Execute operações de atualização e exclusão com eficiência. O índice da árvore B é capaz de localizar rapidamente as linhas específicas para atualizações e exclusões sem verificar a tabela completa ou a partição de uma tabela.
• Você tem armazenamento adicional disponível para armazenar o índice B-tree.

Usar um índice de armazenamento de colunas não clusterizado para análise em tempo real

A partir do SQL Server 2016 (13.x), pode-se ter um índice columnstore não-clusterizado em uma tabela rowstore baseada em disco ou em uma tabela OLTP na memória. Isso torna possível 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 ambos os índices, as alterações estão disponíveis em tempo real para os índices rowstore e columnstore.

Como um índice columnstore alcança 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 tiver 20 GB, o índice columnstore poderá exigir 2 GB adicionais. O espaço adicional necessário também depende do número de colunas no índice columnstore não clusterizado.

Considere o uso de um índice columnstore não clusterizado para:

• Executar análises em tempo real numa tabela rowstore transacional. Você pode substituir índices de árvore B existentes projetados para análise por um índice columnstore não clusterizado.

• Elimine a necessidade de um armazém de dados separado. Tradicionalmente, as empresas executam transações em uma tabela rowstore e, em seguida, carregam os dados em um armazém de dados separado para executar análises. Para muitas cargas de trabalho, pode-se 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 tornar esse cenário eficiente. É fácil experimentá-lo, pois você pode habilitar um índice columnstore não clusterizado sem alterações no seu aplicativo OLTP.

Para adicionar recursos de processamento adicionais, você pode executar a análise em um secundário legível. O uso de um secundário legível separa o processamento da carga de trabalho transacional da 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 Qual índice columnstore é adequado para minha carga de trabalho?.

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

Os índices Columnstore suportam particionamento, que é uma boa maneira de gerenciar e arquivar dados. O particionamento também melhora o desempenho da consulta, limitando as operações a uma ou mais partições.

Use partições para facilitar o gerenciamento de dados

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

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

• Controle o tamanho dos backups incrementais. Você pode fazer backup de partições para separar grupos de arquivos e, em seguida, marcá-los como somente leitura. Ao fazer isso, os backups futuros ignoram os grupos de arquivos somente leitura.
• Economize custos de armazenamento movendo uma partição mais antiga para um armazenamento mais barato. Por exemplo, você pode usar a comutação de partição para mover uma partição para um local de armazenamento mais barato.
• Execute operações de forma eficiente limitando-as a uma partição. Por exemplo, você pode direcionar apenas as partições fragmentadas para manutenção de índice.

Além disso, com um índice columnstore, utiliza-se o particionamento para:

• Poupe 30% adicionais em custos de armazenamento. Pode comprimir as partições mais antigas com as opções de compressão COLUMNSTORE_ARCHIVE. 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 da consulta

Usando partições, você pode limitar suas consultas para verificar apenas partições específicas, o que limita o número de linhas a serem verificadas. Por exemplo, se o índice for particionado por ano e a consulta estiver analisando dados do ano passado, ele só precisará digitalizar 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 tem melhor desempenho com menos partições do que o que você pode usar para um índice de armazenamento de linhas. Se não tiveres pelo menos um milhão de linhas por partição, a maioria das tuas linhas pode ir para o armazenamento delta onde elas não recebem o benefício de desempenho da compactação em armazenamento em colunas. Por exemplo, se forem carregadas um milhão de linhas para uma tabela com 10 partições e cada partição receber 100.000 linhas, todas as linhas serão direcionadas para os grupos de linhas delta.

Example:

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

Para obter mais informações sobre particionamento, consulte a postagem do blog de Sunil Agarwal, Devo particionar meu índice columnstore?.

Escolha o método de compressão de dados apropriado

O índice de armazenamento em colunas oferece duas opções para compressão de dados: compressão de armazenamento em colunas e compressão de arquivo. Você pode escolher a opção de compactação ao criar o índice, ou mudá-la mais tarde com ALTER INDEX ... RECONSTRUIR.

Utilize a compactação columnstore para garantir o melhor desempenho da consulta

A compactação do armazenamento em colunas normalmente alcança taxas de compactação 10x melhores do que os índices de armazenamento em linhas. É o método de compressão padrão para índices columnstore e permite um desempenho de consulta rápido.

Use a compactação de arquivo para melhor compactação de dados

A compactação de arquivo foi projetada para máxima compressão quando o desempenho das consultas não é prioritário. Esta técnica alcança taxas de compressão de dados mais altas do que a compressão em colunas, mas tem um preço. Demora mais tempo a comprimir e descomprimir os dados, pelo que não é adequado para um desempenho de consulta rápido.

Recomenda-se usar otimizações ao converter uma tabela rowstore num índice columnstore

Se seus dados já estiverem em uma tabela de armazenamento de linhas, você poderá usar CREATE COLUMNSTORE INDEX para converter a tabela em um índice columnstore clusterizado. Há algumas otimizações que melhorarão o desempenho da consulta após a conversão da tabela, descritas a seguir.

Utilize o MAXDOP para melhorar a qualidade do conjunto de linhas

Você pode configurar o número máximo de processadores para converter um índice em heap ou uma árvore B clusterizada em um índice em formato columnstore. Para configurar os processadores, use a opção de grau máximo de paralelismo (MAXDOP).

Se você tiver grandes quantidades de dados, o MAXDOP 1 pode ser muito lento. Aumentar MAXDOP para 4 funciona bem. Se isso resultar em alguns grupos de linhas 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 ordenada de um índice de árvore B

Como o índice B-tree já armazena linhas em uma ordem ordenada, preservar essa ordem quando as linhas são compactadas no índice columnstore pode melhorar o desempenho da consulta.

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 grupo de linhas. Ao analisar um intervalo de valores, é capaz de calcular rapidamente quando deve ignorar o grupo de linhas. Quando os dados são ordenados, mais grupos de linhas podem ser ignorados.

Para preservar a ordem ordenada durante a conversão:

• Utilize 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 de armazenamento de linhas, não será necessário atualizá-los.

  Este exemplo converte um índice de armazenamento de linhas 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);
  

Compreender a eliminação de segmentos

Cada grupo de linhas contém um segmento de coluna para cada coluna da tabela. Cada segmento de coluna é compactado em conjunto e armazenado em mídia física.

Há metadados com cada segmento para permitir a eliminação rápida de segmentos sem lê-los. As opções de tipo de dados podem ter um impacto significativo no desempenho da consulta com base em predicados de filtro comuns para consultas no índice columnstore. Para 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 columnstore
• Índices de armazenamento em colunas em armazenagem de dados
• Índices de Columnstore - Desempenho das consultas