Índices de columnstore – diretrizes de carregamento de dados

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Banco de dados SQL no Microsoft Fabric

Opções e recomendações para carregar dados em um índice columnstore usando os métodos padrão de carregamento em massa do SQL e de inserção gradual. Carregar dados em um índice columnstore é uma parte essencial de qualquer processo de armazenamento de dados, pois move os dados para o índice para prepará-los para análises.

Não conhece índices columnstore? Consulte Índices columnstore – visão geral e Índices columnstore – arquitetura.

O que é carregamento em massa?

Carregamento em massa refere-se ao modo em que um grande número de linhas é adicionado a um armazenamento de dados. É o modo mais eficaz de mover dados para um índice columnstore, porque funciona em lotes de linhas. O carregamento em massa preenche os grupos de linhas até a capacidade máxima e os compacta diretamente no armazenamento colunar. Somente as linhas no fim de um carregamento que não atingem o mínimo de 102.400 linhas por rowgroup vão para o deltastore.

Para realizar um carregamento em massa, você pode usar o utilitário bcp, o Integration Services ou selecionar linhas de uma tabela de preparação.

Como o diagrama sugere, uma carga em massa:

• Não pré-classifica os dados. Os dados são inseridos em rowgroups na ordem em que são recebidos.
• Se o tamanho do lote for >>= 102400, as linhas serão carregadas diretamente nos grupos de linhas comprimidos. Você deve escolher um tamanho de lote >=102400 para uma importação em massa eficiente, pois isso permite evitar mover linhas de dados para conjuntos de linhas delta antes que as linhas sejam eventualmente movidas para conjuntos de linhas compactados por um thread de plano de fundo, o movedor de tuplas (TM).
• Se o tamanho do lote < 102.400 ou se as linhas restantes forem < 102.400, as linhas serão carregadas em rowgroups delta.

O carregamento em massa tem estas otimizações de desempenho integradas:

• Cargas paralelas: é possível ter várias cargas em massa simultâneas (bcp ou inserção em massa), cada uma carregando um arquivo de dados separado. Ao contrário dos carregamentos em massa em rowstore no SQL Server, não é necessário especificar TABLOCK, porque cada thread de importação em massa carrega dados exclusivamente em rowgroups separados (compactados ou delta), com bloqueio exclusivo em cada um deles.

• Registro em log reduzido: os dados carregados diretamente em grupos de linhas compactados geram uma redução significativa no tamanho do log. Por exemplo, se os dados foram compactados em 10 vezes, o log de transações correspondente é aproximadamente 10 vezes menor sem a necessidade de usar TABLOCK ou o modelo de recuperação Bulk-logged/Simple. Todos os dados que vão para um rowgroup delta são totalmente registrados. Isso inclui qualquer tamanho de lote com menos de 102.400 linhas. A melhor prática é usar o batchsize >= 102400. Como TABLOCK não há necessidade, você pode carregar os dados em paralelo.

• Registro em log mínimo: É possível reduzir ainda mais o registro em log se você atender aos pré-requisitos para o registro em log mínimo. No entanto, ao contrário do carregamento de dados em um rowstore, TABLOCK leva a um X bloqueio (exclusivo) na tabela, em vez de um BU bloqueio (atualização em massa), e, portanto, a carga de dados em paralelo não pode ser feita. Para obter mais informações sobre o bloqueio, confira Bloqueio e controle de versão de linha.

• Otimização de bloqueio: O bloqueio de um grupo de linhas é obtido automaticamente quando se carrega dados em um grupo de linhas compactado. No entanto, ao carregar em lote em um grupo de linhas delta, um X bloqueio é adquirido para o grupo de linhas, mas o Mecanismo de Banco de Dados ainda adquire bloqueios de página e de extensão porque o X bloqueio de grupo de linhas não faz parte da hierarquia de bloqueio.

Se você tiver um índice B-tree não clusterizado em um índice columnstore, não haverá otimização de bloqueio nem de registro em log para o próprio índice, mas as otimizações do índice columnstore clusterizado, conforme descrito anteriormente, são aplicáveis.

Planejar tamanhos de cargas em massa para minimizar os grupos de linhas delta

Os índices columnstore apresentam melhor desempenho quando a maioria das linhas é compactada no columnstore e não fica nos delta rowgroups. É melhor dimensionar suas cargas para que as linhas sejam gravadas diretamente no columnstore e evitem passar pelo deltastore tanto quanto possível.

Esses cenários descrevem quando as linhas carregadas vão diretamente para o columnstore ou quando vão para o deltastore. No exemplo, cada rowgroup pode ter de 102.400 a 1.048.576 linhas por rowgroup. Na prática, o tamanho máximo de um rowgroup poder ser inferior a 1.048.576 linhas quando há pressão de memória.

O exemplo a seguir mostra os resultados do carregamento de 1.048.577 linhas em uma tabela. Os resultados mostram que há um grupo de linhas COMPRESSED no armazenamento colunar (na forma de segmentos de coluna compactados) e 1 linha no deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

Usar uma tabela de preparo para melhorar o desempenho

Se estiver carregando os dados apenas para prepará-los antes de executar mais transformações, carregar a tabela na tabela de heap é muito mais rápido que carregar os dados em uma tabela columnstore clusterizada. Além disso, carregar dados em uma [tabela temporária] [Temporária] também será muito mais rápido do que carregar uma tabela em um armazenamento permanente.

Um padrão comum do carregamento de dados é carregar os dados em uma tabela de preparo, fazer alguma transformação e carregá-la na tabela de destino usando o comando a seguir:

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Esse comando carrega os dados no índice columnstore de forma semelhante ao bcp ou à inserção em massa, mas em um único lote. Se o número de linhas na tabela de preparo < 102.400, as linhas serão carregadas em um rowgroup delta, caso contrário, as linhas serão carregadas diretamente no rowgroup compactado. Uma limitação importante era que essa operação INSERT era de thread único. Para carregar dados em paralelo, era possível criar várias tabelas de preparo ou emitir INSERT/SELECT com intervalos não sobrepostos de linhas da tabela de preparo. Essa limitação não existe no SQL Server 2016 (13.x). O comando a seguir carrega em paralelo os dados da tabela de staging, mas você precisa especificar TABLOCK. Você pode considerar isso contraditório em relação ao que foi dito anteriormente sobre o bulkload, mas a diferença principal é que o carregamento paralelo de dados da tabela de staging é executado na mesma transação.

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Estas são as otimizações disponíveis ao fazer um carregamento em um índice columnstore clusterizado da tabela de preparo:

• Otimização de log: Redução do registro em log quando os dados são carregados em um grupo de linhas compactado.
• Otimização de bloqueio: Ao carregar dados em um grupo de linhas compactado, o X bloqueio no grupo de linhas é obtido. No entanto, ao carregar em lote em um grupo de linhas delta, um X bloqueio é adquirido para o grupo de linhas, mas o Mecanismo de Banco de Dados ainda adquire bloqueios de página e de extensão porque o X bloqueio de grupo de linhas não faz parte da hierarquia de bloqueio.

Se você tiver um ou mais índices não clusterizados, não haverá otimização de bloqueio ou de registro em log para o próprio índice, mas as otimizações no índice columnstore clusterizado, conforme descritas anteriormente, ainda se aplicam.

O que é inserção gradual?

Inserção gradual refere-se à forma como linhas individuais são inseridas no índice columnstore. As inserções graduais usam a INSERT instrução INTO. Com a inserção gradual, todas as linhas são direcionadas ao deltastore. Isso é útil para um número pequeno de linhas, mas não é prático para grandes cargas.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

Depois que o rowgroup contém 1.048.576 linhas, o rowgroup delta é marcado como fechado, mas ainda fica disponível para consultas e operações de atualização/exclusão. Porém, as linhas recém-inseridas vão para um rowgroup deltastore existente ou recém-criado. Há um thread em segundo plano chamado tuple mover (TM) que compacta os grupos de linhas delta fechados periodicamente, aproximadamente a cada cinco minutos. Você pode executar explicitamente o comando a seguir para compactar o rowgroup delta fechado.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

Se você quiser forçar um grupo de linhas delta a ser fechado e comprimido, poderá executar o seguinte comando. Convém executar esse comando se você acabou de carregar as linhas e não espera linhas novas. Ao fechar e compactar explicitamente o rowgroup delta, você poderá salvar mais armazenamento e melhorar o desempenho da consulta analítica. Uma prática recomendada é invocar esse comando se você não espera que novas linhas sejam inseridas.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

Como funciona o carregamento em uma tabela particionada

Para dados particionados, o Mecanismo de Banco de Dados 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 os próprios grupos de linhas e pelo menos um grupo de linhas delta.

Conteúdo relacionado

• Considerações de desempenho de carregamento de dados com índices columnstore clusterizados