Usando índices columnstore clusterizados

Tarefas para usar índices columnstore clusterizados em SQL Server.

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

Para obter informações sobre índices columnstore clusterizados, consulte Using Clustered Columnstore Indexes.

Sumário

• Criar um índice columnstore clusterizado

• Descartar um índice columnstore clusterizado

• Carregar dados em um índice columnstore clusterizado

• Alterar dados em um índice columnstore clusterizado

• Recompilar um índice Columnstore clusterizado

• Reorganizar um índice columnstore clusterizado

Criar um índice columnstore clusterizado

Para criar um índice columnstore clusterizado, primeiro crie uma tabela rowstore como um índice clusterizado ou heap e, em seguida, use a instrução CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL) para converter a tabela em um índice columnstore clusterizado. Se você desejar 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, depois, converte-o em um índice columnstore clusterizado chamado o cci_Simple. Isso altera o armazenamento da tabela inteira, de rowstore a 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).

Descartar um índice columnstore clusterizado

Use a instrução DROP INDEX (Transact-SQL) para remover um índice columnstore clusterizado. Essa operação irá 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, Integration Services e INSERT... SELECT pode carregar todos os dados em um índice columnstore clusterizado.

Os índices columnstore clusterizados aproveitam o deltastore a fim de evitar a fragmentação de segmentos de colunas no columnstore.

Carregando em uma tabela particionada

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

Cenários de carregamento de deltastore

As linhas são acumuladas no deltastore até que o número de linhas atinja o número máximo de linhas permitido para um rowgroup. Quando o deltastore contém o número máximo de linhas por rowgroup, SQL Server marca o rowgroup como "CLOSED". Um processo em segundo plano, chamado "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, podem existir vários deltastores.

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

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

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

No exemplo, cada rowgroup pode ter de 102.400 a 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 um rowgroup COMPRESSED no columnstore (como segmentos de coluna compactados) e 1 linha no deltastore.

SELECT * FROM sys.column_store_row_groups;

Alterar dados em um índice columnstore clusterizado

Os índices columnstore clusterizados oferecem 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, 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, SQL Server excluirá logicamente e fisicamente a linha.

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

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

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

Recriar 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 recompilar uma partição específica.

Processo de recriação

Para recompilar um índice columnstore clusterizado, SQL Server:

• Obtenha um bloqueio exclusivo na tabela ou na partição durante a recompilação. Os dados ficam "offline" e não disponíveis enquanto você recompila.

• 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 do columnstore antes da recriação do índice. Quando a recriação é concluída, todos os dados são armazenados no formato columnstore, e o deltastore está vazio.

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

Recomendações para recriar um índice columnstore clusterizado

Recriar um índice columnstore clusterizado é útil para remover a fragmentação, e para mover todas as linhas para o columnstore. Nossas recomendações:

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

  1. Recriar a tabela inteira é uma tarefa demorada se o índice é grande, e isso exige espaço em disco suficiente para armazenar uma cópia adicional do índice durante a recriação. Geralmente, é necessário recriar apenas a partição mais recentemente usada.

  2. Em tabelas particionadas, não é necessário recriar todo o índice columnstore, pois é provável que a fragmentação ocorra apenas nas partições que foram modificadas recentemente. As tabelas de fatos e de dimensões grandes geralmente são particionadas para executar operações de backup e gerenciamento em partes da tabela.

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

  Recriar uma partição desfragmentará a partição e reduzirá o armazenamento em disco. Essa recriação excluirá todas as linhas do columnstore que são marcadas para exclusão, e moverá todas as linhas do deltastore para o columnstore.

• Recriar uma partição após carregar dados.

  Isso garante que todos dados sejam armazenados no columnstore. Se vários carregamentos ocorrerem ao mesmo tempo, cada partição poderá acabar tendo vários deltastores. A recriação moverá todas as linhas do deltastore para o columnstore.

Reorganizar um índice columnstore clusterizado

A reorganização de um índice columnstore clusterizado move todos os rowgroups CLOSED 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 Tuple Mover acabará encontrando todos os rowgroups FECHADOS e os moverá. No entanto, o Tuple Mover tem thread único e não pode mover rowgroups com rapidez suficiente para a carga de trabalho.

Recomendações para reorganizar

Quando reorganizar um índice columnstore clusterizado:

• Reorganize um índice columnstore clusterizado depois que um ou mais carregamentos de dados atingir os benefícios de desempenho de consulta o mais rapidamente possível. Inicialmente, a reorganização exigirá recursos de CPU adicionais para compactar os dados, o que pode reduzir o desempenho geral do sistema. No entanto, assim que os dados forem compactados, o desempenho de consulta poderá aumentar.