Columnstore Indexes Described
O SQL Server índice columnstore na memória armazena e gerencia dados usando o armazenamento de dados baseado em coluna e o processamento de consulta baseado em coluna. Os índices columnstore funcionam bem para as cargas de trabalho de data warehouse que executam principalmente carregamentos em massa e consultas somente leitura. Use o índice columnstore para obter um ganho de desempenho de consulta até 10 vezes maior sobre o armazenamento tradicional orientado por linha e de compactação de dados até 7 vezes maior sobre o tamanho dos dados não compactados.
Observação
Exibimos o índice columnstore clusterizado como o padrão para armazenar grandes tabelas de fatos de data warehouse e esperamos que ele seja usado na maioria dos cenários de data warehouse. Uma vez que o índice columnstore clusterizado é atualizável, a carga de trabalho pode executar um grande número de operações de inserção, atualização e exclusão.
Conteúdo
Noções básicas
Um columnstore index é uma tecnologia para armazenamento, recuperação e gerenciamento de dados usando um formato de dados colunar, chamado columnstore. SQL Server dá suporte a índices columnstore clusterizados e não clusterizados. Ambos usam a mesma tecnologia de columnstore na memória, mas possuem diferenças na finalidade e nos recursos aos quais oferecem suporte.
Benefícios
Os índices columnstore funcionam bem na maioria das consultas somente leitura que executam a análise em grandes conjuntos de dados. Geralmente, essas são consultas sobre cargas de trabalho de data warehousing. Os índices columnstore permitem ganhos de desempenho alto para consultas que usam digitalizações completas de tabela e não são apropriados para consultas que buscam nos dados, procurando um valor específico.
Benefícios do índice columnstore:
As colunas muitas vezes têm dados semelhantes, o que resulta em altas taxas de compactação.
As altas taxas de compactação melhoram o desempenho da consulta usando um menor volume na memória. Por sua vez, o desempenho da consulta pode melhorar porque SQL Server pode executar mais operações de consulta e dados na memória.
Um novo mecanismo de execução de consulta chamado execução do modo em lotes foi adicionado ao SQL Server, o que reduz bastante o uso da CPU. A execução do modo em lotes é integrada ao formato de armazenamento columnstore e otimizado com base nele. A execução do modo em lote às vezes é conhecida como execução baseada em vetor ou vetorizado.
Muitas vezes, as consultas selecionam apenas algumas colunas de uma tabela, o que reduz a E/S total da mídia física.
Versões de columnstore
O SQL Server 2012, o SQL Server 2012 Parallel Data Warehouse e o SQL Server 2014 usam índices columnstore para acelerar consultas comuns de data warehouse. SQL Server 2012 introduziu dois novos recursos: um índice columnstore não clusterizado e uma funcionalidade de execução de consulta baseada em vetor que processa dados em unidades chamadas "lotes". SQL Server 2014 tem os recursos de SQL Server 2012 mais índices columnstore clusterizados atualizáveis.
Principais características
| Aplica-se a: SQL Server 2014 a SQL Server 2019 (15.x). |
Em SQL Server, um índice columnstore clusterizado:
Está disponível nas edições Enterprise, Developer e Evaluation.
É atualizável.
É o método de armazenamento primário para toda a tabela.
Não tem colunas de chave. Todas as colunas são colunas incluídas.
É o único índice na tabela. Não pode ser combinado com nenhum outro índice.
Pode ser configurado para usar a columnstore ou compressão de arquivamento columnstore.
Não armazena fisicamente colunas em uma ordem classificada. Em vez disso, ele armazena dados para aprimorar a compactação e o desempenho.
| Aplica-se a: SQL Server 2012 a SQL Server 2019 (15.x). |
Em SQL Server, um índice columnstore não clusterizado:
Pode indexar um subconjunto das colunas no índice clusterizado ou heap. Por exemplo, pode indexar as colunas frequentemente usadas.
Exige armazenamento extra para armazenar uma cópia das colunas no índice.
É atualizado recompilando o índice ou alternando partições para dentro e para fora. Ele não é atualizável usando as operações DML, como inserir, atualizar e excluir.
Pode ser combinado com outros índices na tabela.
Pode ser configurado para usar a columnstore ou compressão de arquivamento columnstore.
Não armazena fisicamente colunas em uma ordem classificada. Em vez disso, ele armazena dados para aprimorar a compactação e o desempenho. A pré-classificação de dados antes da criação do índice columnstore não é necessária, mas pode melhorar a compactação columnstore.
Conceitos e termos principais
Os termos e conceitos principais a seguir estão associados aos índices columnstore.
índice columnstore Um índice columnstore é uma tecnologia para armazenar, recuperar e gerenciar dados usando um formato de dados columnar, chamado columnstore. SQL Server dá suporte a índices columnstore clusterizados e não clusterizados. Ambos usam a mesma tecnologia de columnstore na memória, mas possuem diferenças na finalidade e nos recursos aos quais oferecem suporte.
columnstore Um columnstore são dados organizados logicamente como uma tabela com linhas e colunas e armazenados fisicamente em um formato de dados em termos de coluna.
rowstore Um rowstore são dados que são logicamente organizados como uma tabela com linhas e colunas e, em seguida, armazenados fisicamente em um formato de dados em linha. Esse tem sido o modo tradicional de armazenar dados da tabela relacional.
rowgroups e segmentos de coluna Para altas taxas de desempenho e alta compactação, o índice columnstore fatia a tabela em grupos de linhas, chamados grupos de linhas e compacta cada grupo de linhas de maneira em termos de coluna. O número de linhas no grupo de linhas deve ser grande o suficiente para melhorar as taxas de compactação e pequeno o suficiente para se beneficiar com as operações na memória.
grupo de linhas Um rowgroup é um grupo de linhas que são compactadas no formato columnstore ao mesmo tempo.
segmento de coluna Um segmento de coluna é uma coluna de dados de dentro do rowgroup.
Um grupo de linhas normalmente contém o número máximo de linhas por grupo de linhas que é 1.048.576 linhas.
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.
índice columnstore não clusterizado Um índice columnstore não clusterizado é um índice somente leitura criado em um índice clusterizado existente ou tabela de heap. Contém uma cópia de um subconjunto de colunas, até e incluindo todas as colunas na tabela. A tabela é somente leitura enquanto contém um índice columnstore não clusterizado.
Um índice columnstore não clusterizado fornece uma maneira de ter um índice columnstore para execução de consultas de análise e, ao mesmo tempo, executar operações somente leitura na tabela original.
índice columnstore clusterizado Um índice columnstore clusterizado é o armazenamento físico de toda a tabela e é o único índice para a tabela. O índice clusterizado é atualizável. Você pode executar operações de inserção, exclusão e atualização no índice e pode carregar dados em massa no índice.
índice
Para reduzir a fragmentação dos segmentos de coluna e melhorar o desempenho, o índice columnstore pode armazenar alguns dados temporariamente em uma tabela rowstore, chamada deltastore, mais uma árvore B de IDs para as linhas excluídas. As operações de deltastore são tratadas em segundo plano. Para retornar os resultados corretos da consulta, o índice columnstore clusterizado combina os resultados da consulta de columnstore e deltastore.
deltastore Usado apenas com índices columnstore clusterizados, um deltastore é uma tabela rowstore que armazena linhas até que o número de linhas seja grande o suficiente para ser movido para o columnstore. Um deltastore é usado com índices columnstore clusterizados para melhorar o desempenho do carregamento e de outras operações DML.
Durante o carregamento em massa grande, a maioria das linhas vai diretamente para o columnstore sem passar pelo deltastore. No fim do carregamento em massa, o número de linhas pode ser muito pouco para atender ao tamanho mínimo de um rowgroup, que é de 102.400 linhas. Quando isso ocorre, as linhas finais vão para o deltastore, e não para o columnstore. Para carregamento em massa pequeno, menos de 102.400 linhas, todas as linhas vão diretamente para o deltastore.
Quando o deltastore atinge o número máximo de linhas, ele é fechado. Um processo de movimentação de tupla verifica grupos de linhas fechados. Quando encontra o grupo de linhas fechado, compacta-o e armazena-o no columnstore.
Carregando dados
Carregando dados em um índice columnstore não clusterizado
Para carregar dados em um índice columnstore não clusterizado, primeiro carregue os dados em uma tabela rowstore tradicional armazenada como um índice clusterizado ou heap e, em seguida, crie o índice columnstore não clusterizado com CREATE COLUMNSTORE INDEX (Transact-SQL).
Uma tabela com um índice columnstore não clusterizado é somente leitura até que o índice seja removido ou desabilitado. Para atualizar a tabela e o índice columnstore não clusterizado, você pode alternar as partições para dentro e para fora. Você também pode desabilitar o índice, atualizar a tabela e recompilar o índice.
Para obter mais informações, consulte Using Nonclustered Columnstore Indexes
Carregando dados em um índice columnstore clusterizado
Como o diagrama sugere, para carregar dados em um índice columnstore clusterizado, SQL Server:
Insere rowgroups de tamanho máximo diretamente no columnstore. À medida que os dados são carregados, SQL Server atribui as linhas de dados em uma ordem de primeiro saque por ordem de primeiro serviço em um rowgroup aberto.
Para cada rowgroup, depois de atingir o tamanho máximo, SQL Server:
Marca o rowgroup como CLOSED.
Ignora o deltastore.
Compacta cada segmento de coluna com o rowgroup com a compactação columnstore.
Armazena fisicamente cada segmento de coluna compactado no columnstore.
Insere as linhas restantes no columnstore ou deltastore, como se segue:
Se o número de linhas atender ao requisito de linhas mínimas por rowgroup, as linhas serão adicionadas ao columnstore.
Se o número de linhas for menor que o mínimo de linhas por rowgroup, as linhas serão adicionadas ao deltastore.
Para obter mais informações sobre tarefas e processos de deltastore, consulte Using Clustered Columnstore Indexes
Dicas de desempenho
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 a tabela tiver menos de um milhão de linhas, o SQL Server usará apenas um thread para criar o índice columnstore.
Se a sua tabela tiver mais de um milhão de linhas, mas o SQL Server não puder obter uma quantidade de memória suficiente para criar o índice usando MAXDOP, o SQL Server diminuirá automaticamente MAXDOP conforme o necessário de acordo com a quantidade de memória disponível. Em alguns casos, o DOP deve ser diminuído para um para criar o índice na memória restrita.
Tarefas e tópicos relacionados
Índices columnstore não clusterizados
Para tarefas comuns, consulte Using Nonclustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) com REBUILD.
Índices columnstore clusterizados
Para tarefas comuns, consulte Using Clustered Columnstore Indexes.
ALTER INDEX (Transact-SQL) com REBUILD ou REORGANIZE.
Metadados
Todas as colunas em um índice columnstore são armazenadas nos metadados como colunas incluídas. O índice columnstore não tem colunas de chave.