Usando índices Columnstore clusterizados

Tarefas para usar índices columnstore clusterizados no SQL Server.

Para obter uma visão geral dos índices columnstore, consulte Columnstore Indexes Described.

Para obter informações sobre índices columnstore clusterizados, consulte Usando índices Columnstore clusterizados.

Conteúdos

• Criar um índice Columnstore clusterizado

• Apagar um Índice Columnstore Clusterizado

• Carregar dados em um índice Columnstore clusterizado

• Alterar dados em um índice Columnstore clusterizado

• Reconstruir um Índice Clusterizado Columnstore

• Reorganizar um índice Columnstore clusterizado

Criar um índice Columnstore clusterizado

Para criar um índice columnstore clusterizado, primeiro crie uma tabela rowstore como um heap ou índice clusterizado e use a instrução CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) para converter a tabela em um índice columnstore clusterizado. Se você quiser que o índice columnstore clusterizado tenha o mesmo nome que o índice clusterizado, use a opção DROP_EXISTING.

Este exemplo cria uma tabela como um heap e a converte em um índice columnstore clusterizado chamado cci_Simple. Isso altera o armazenamento de toda a tabela de rowstore para columnstore.

CREATE TABLE T1(
    ProductKey [int] NOT NULL, 
    OrderDateKey [int] NOT NULL, 
    DueDateKey [int] NOT NULL, 
    ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON T1;
GO

Para obter mais exemplos, consulte a seção Exemplos em CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

Remover um índice Columnstore clusterizado

Use a instrução DROP INDEX (Transact-SQL) para remover um índice columnstore clusterizado. Essa operação removerá o índice e converterá a tabela columnstore em um heap rowstore.

Carregar dados em um índice Columnstore clusterizado

Você pode adicionar dados a um índice columnstore clusterizado existente usando qualquer um dos métodos de carregamento padrão. Por exemplo, a ferramenta de carregamento em massa bcp, o Integration Services e o INSERT... SELECT pode carregar todos os dados em um índice columnstore clusterizado.

Os índices columnstore clusterizados utilizam o deltastore para impedir a fragmentação dos segmentos de coluna dentro do columnstore.

Carregando em uma tabela particionada

Para dados particionados, o SQL Server primeiro atribui cada linha a uma partição e, em seguida, executa operações columnstore nos dados dentro da partição. Cada partição tem seus próprios rowgroups e pelo menos um deltastore.

Os cenários de carregamento da Deltastore

As linhas se acumulam no deltastore até que o número de linhas seja o número máximo de linhas permitidas para um rowgroup. Quando o deltastore contém o número máximo de linhas por rowgroup, o SQL Server marca o rowgroup como "CLOSED". Um processo em segundo plano, chamado de "tupla-mover", localiza o rowgroup CLOSED e se move para o columnstore, onde o rowgroup é compactado em segmentos de coluna e os segmentos de coluna são armazenados no columnstore.

Para cada índice columnstore clusterizado, pode haver vários deltastores.

• Se um deltastore estiver bloqueado, o SQL Server tentará obter um bloqueio em um deltastore diferente. Se não houver deltastores disponíveis, o SQL Server criará um novo deltastore.

• Para uma tabela particionada, pode haver um ou mais deltastores para cada partição.

Somente para índices columnstore clusterizados, os cenários a seguir descrevem quando as linhas carregadas vão diretamente para o columnstore ou quando vão para o deltastore.

No exemplo, cada rowgroup pode ter 102.400-1.048.576 linhas por rowgroup.

O exemplo a seguir mostra os resultados do carregamento de 1.048.577 linhas em uma partição. Os resultados mostram que um rowgroup COMPRESSED no columnstore (como segmentos de coluna compactados) e uma linha no deltastore.

SELECT * FROM sys.column_store_row_groups;

Alterar dados em um índice Columnstore clusterizado

Índices columnstore clusterizados dão suporte a operações DML de inserção, atualização e exclusão.

Use INSERT (Transact-SQL) para inserir uma linha. A linha será adicionada ao deltastore.

Use DELETE (Transact-SQL) para excluir uma linha.

• Se a linha estiver no columnstore, o SQL Server marcará a linha como excluída logicamente, mas não recuperará o armazenamento físico da linha até que o índice seja recriado.

• Se a linha estiver no deltastore, o SQL Server excluirá logicamente e fisicamente a linha.

Use UPDATE (Transact-SQL) para atualizar uma linha.

• Se a linha estiver no columnstore, o SQL Server marcará a linha como excluída logicamente e inserirá a linha atualizada no deltastore.

• Se a linha estiver no deltastore, o SQL Server atualizará a linha no deltastore.

Reconstruir um índice Columnstore clusterizado

Use CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) ou ALTER INDEX (Transact-SQL) para executar uma recompilação completa de um índice columnstore clusterizado existente. Além disso, você pode usar ALTER INDEX ... REBUILD para reconstruir uma partição específica.

Processo de reconstrução

Para que o SQL Server reconstrua um índice columnstore clusterizado:

• Adquire um bloqueio exclusivo na tabela ou partição enquanto a recompilação ocorre. Os dados estão "offline" e não estão disponíveis durante a recompilação.

• Desfragmenta o columnstore excluindo fisicamente as linhas que foram excluídas logicamente da tabela; os bytes excluídos são recuperados na mídia física.

• Mescla os dados do rowstore no deltastore com os dados no columnstore antes de reconstruir o índice. Quando a recompilação for concluída, todos os dados serão armazenados no formato columnstore e o deltastore estará vazio.

• Compacta novamente todos os dados no columnstore. Existem duas cópias do índice columnstore enquanto a recompilação está ocorrendo. Quando a recompilação for concluída, o SQL Server excluirá o índice columnstore original.

Recomendações para reconstruir um índice Columnstore clusterizado

A reconstrução de um índice columnstore clusterizado é útil para remover a fragmentação e mover todas as linhas para o columnstore. Temos as seguintes recomendações:

• Recompile uma partição em vez de toda a tabela.

  1. A recriação de toda a tabela levará muito tempo se o índice for grande e exigir espaço em disco suficiente para armazenar uma cópia adicional do índice durante a recompilação. Normalmente, só é necessário recriar a partição usada mais recentemente.

  2. Para tabelas particionadas, não é necessário recompilar todo o índice columnstore porque a fragmentação provavelmente ocorrerá apenas nas partições que foram modificadas recentemente. Tabelas de fatos e tabelas de dimensões grandes geralmente são particionadas para executar operações de backup e gerenciamento em partes da tabela.

• Recompile uma partição após operações DML pesadas.

  A recriação de uma partição desfragmentará a partição e reduzirá o armazenamento em disco. A recompilação removerá todas as linhas do columnstore marcadas para exclusão e moverá todas as linhas do deltastore para o columnstore.

• Recompile uma partição depois de carregar dados.

  Isso garante que todos os dados sejam armazenados no columnstore (armazenamento em colunas). Se várias cargas ocorrerem ao mesmo tempo, cada partição poderá acabar tendo vários deltastores. A recompilação moverá todas as linhas deltastore para o columnstore.

Reorganizar um índice Columnstore clusterizado

Reorganizar um índice columnstore clusterizado move todos os grupos de linhas fechados para o columnstore. Para executar uma reorganização, use ALTER INDEX (Transact-SQL)com a opção REORGANIZE.

A reorganização não é necessária para mover rowgroups CLOSED para o columnstore. O processo movimentador de tuplas eventualmente encontrará todos os grupos de linhas fechados e os moverá. No entanto, o mover de tupla é unifilamentar e pode não mover grupos de linhas rápido o suficiente para sua carga de trabalho.

Recomendações para reorganização

Quando reorganizar um índice columnstore clusterizado:

• Reorganizar um índice columnstore clusterizado após um ou mais carregamentos de dados para obter melhorias de desempenho em consultas da forma mais rápida possível. A reorganização inicialmente exigirá recursos adicionais da CPU para compactar os dados, o que pode diminuir o desempenho geral do sistema. No entanto, assim que os dados forem compactados, o desempenho da consulta poderá melhorar.