Índices columnstore – diretrizes de carregamento de dadosColumnstore indexes - Data loading guidance

Aplica-se a:Applies to: simSQL ServerSQL Server (todas as versões compatíveis) yesSQL ServerSQL Server (all supported versions) SimBanco de Dados SQL do AzureAzure SQL DatabaseYesBanco de Dados SQL do AzureAzure SQL Database SimInstância Gerenciada do Azure SQLAzure SQL Managed InstanceYesInstância Gerenciada do Azure SQLAzure SQL Managed Instance simAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics simParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data WarehouseAplica-se a:Applies to: simSQL ServerSQL Server (todas as versões compatíveis) yesSQL ServerSQL Server (all supported versions) SimBanco de Dados SQL do AzureAzure SQL DatabaseYesBanco de Dados SQL do AzureAzure SQL Database SimInstância Gerenciada do Azure SQLAzure SQL Managed InstanceYesInstância Gerenciada do Azure SQLAzure SQL Managed Instance simAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics simParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

Opções e recomendações para carregar dados em um índice columnstore usando o carregamento em massa de SQL padrão e métodos de inserção de fluxo.Options and recommendations for loading data into a columnstore index by using the standard SQL bulk loading and trickle insert methods. Carregar dados em um índice columnstore é uma parte essencial de qualquer processo de data warehousing porque ele move os dados para o índice em preparação para análise.Loading data into a columnstore index is an essential part of any data warehousing process because it moves data into the index in preparation for analytics.

Novato em índices columnstore?New to columnstore indexes? Consulte Índices columnstore – visão geral e Índices columnstore – arquitetura.See Columnstore indexes - overview and Columnstore Index Architecture.

O que é carregamento em massa?What is bulk loading?

Carregamento em massa refere-se ao modo em que um grande número de linhas é adicionado a um armazenamento de dados.Bulk loading refers to the way large numbers of rows are added to a data store. É o modo de melhor desempenho para mover dados para um índice columnstore porque opera em lotes de linhas.It is the most performant way to move data into a columnstore index because it operates on batches of rows. O carregamento em massa preenche rowgroups até a capacidade máxima e compacta-os diretamente no columnstore.Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. Somente as linhas ao final de uma carga que não atende ao mínimo de 102.400 linhas por rowgroup vão para o deltastore.Only rows at the end of a load that don't meet the minimum of 102,400 rows per rowgroup go to the deltastore.

Para executar uma carga em massa, você pode usar o Utilitário bcp, o Integration Services ou selecionar linhas de uma tabela de preparo.To perform a bulk load, you can use bcp Utility, Integration Services, or select rows from a staging table.

Carregamento em um índice columnstore clusterizadoLoading into a clustered columnstore index

Como sugere o diagrama, um carregamento em massa:As the diagram suggests, a bulk load:

  • Não classifica previamente os dados.Does not pre-sort the data. Os dados são inseridos em rowgroups na ordem em que são recebidos.Data is inserted into rowgroups in the order it is received.
  • Se o tamanho do lote for superior ou igual a 102.400, as linhas serão carregadas diretamente em rowgroups compactados.If the batch size is >= 102400, the rows are directly into the compressed rowgroups. É recomendável escolher um tamanho de lote superior ou igual a 102.400 para uma importação em massa eficiente porque você pode evitar mover linhas de dados para um rowgroup delta antes que as linhas sejam finalmente movidas para rowgroups compactados por um thread em segundo plano, o TM (Motor de Tupla).It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, Tuple mover (TM).
  • Se o tamanho do lote for inferior a 102.400 ou se as linhas restantes forem inferiores a 102.400, as linhas serão carregadas em rowgroups delta.If the batch size < 102,400 or if the remaining rows are < 102,400, the rows are loaded into delta rowgroups.

Observação

Em uma tabela rowstore com os dados de um índice columnstore não clusterizado, o SQL ServerSQL Server sempre insere dados na tabela base.On a rowstore table with a nonclustered columnstore index data, SQL ServerSQL Server always inserts data into the base table. Os dados nunca são inseridos diretamente no índice columnstore.The data is never inserted directly into the columnstore index.

O carregamento em tem estas otimizações de desempenho internas:Bulk loading has these built-in performance optimizations:

  • Carregamentos paralelos: Você pode ter várias cargas em massa simultâneas (bcp ou inserção em massa), cada uma carregando um arquivo de dados separado.Parallel loads: You can have multiple concurrent bulk loads (bcp or bulk insert) that are each loading a separate data file. Ao contrário dos carregamentos em massa rowstore no SQL ServerSQL Server, não é necessário especificar TABLOCK porque cada thread de importação em massa carregará dados exclusivamente em um rowgroup separado (rowgroups compactados ou delta) com um bloqueio exclusivo nele.Unlike rowstore bulk loads into SQL ServerSQL Server, you don't need to specify TABLOCK because each bulk import thread will load data exclusively into separate rowgroups (compressed or delta rowgroups) with exclusive lock on it.

  • Registro em log reduzido: Os dados carregados diretamente em grupos de linhas compactadas levam a uma redução significativa no tamanho do log.Reduced Logging: The data that is directly loaded into compressed row groups leads to significant reduction in the size of the log. Por exemplo, se os dados tiverem sido compactados em 10 vezes, o log de transações correspondente será de aproximadamente 10 vezes menor sem a necessidade de um modelo de recuperação TABLOCK ou bulk-logged/simples.For example, if data was compressed 10x, the corresponding transaction log will be roughly 10x smaller without requiring TABLOCK or Bulk-logged/Simple recovery model. Todos os dados que vão para um rowgroup delta são totalmente registrados.Any data that goes to a delta rowgroup is fully logged. Isso inclui qualquer tamanho de lote com menos de 102.400 linhas.This includes any batch sizes that are less than 102,400 rows. A melhor prática é usar o batchsize > = 102400.Best practice is to use batchsize >= 102400. Como não há um TABLOCK necessário, você pode carregar os dados em paralelo.Since there is no TABLOCK required, you can load the data in parallel.

  • Log mínimo: Você poderá obter mais redução no registro em log se seguir os pré-requisitos para o registro em log mínimo.Minimal logging: You can get further reduction in logging if you follow the prerequisites for minimal logging. No entanto, ao contrário do carregamento de dados em um rowstore, o TABLOCK leva a um bloqueio X na tabela em vez de um bloqueio BU (Atualização em Massa) e, portanto, o carregamento de dados paralelo não pode ser feito.However, unlike loading data into a rowstore, TABLOCK leads to an X lock on the table rather than a BU (Bulk Update) lock and therefore parallel data load cannot be done. Para obter mais informações sobre o bloqueio, confira Bloqueio e controle de versão de linha.For more information on locking, see Locking and row versioning.

  • Otimização de bloqueio: O bloqueio X em um grupo de linhas é adquirido automaticamente ao carregar dados em um grupo de linhas compactado.Locking Optimization: The X lock on a row group is automatically acquired when loading data into a compressed row group. No entanto, durante o carregamento em massa em um rowgroup delta, um bloqueio X é adquirido em um rowgroup, mas o SQL ServerSQL Server ainda bloqueia PAGE/EXTENT porque o bloqueio do rowgroup X não faz parte da hierarquia de bloqueios.However, when bulk loading into a delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

Se você tiver um índice de árvore B não clusterizado em um índice columnstore, não haverá otimização de log nem de bloqueio para o índice em si, mas as otimizações no índice columnstore clusterizado, conforme descrito acima, ainda serão aplicáveis.If you have a nonclustered B-tree index on a columnstore index, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are applicable.

Observe que DML (inserir, excluir, atualizar) não é uma operação de modo de lote porque não é paralela.Please note, DML (insert, delete, update) is not a batch mode operation because it is not parallel.

Planejar tamanhos de carga em massa para minimizar os rowgroups deltaPlan bulk load sizes to minimize delta rowgroups

Os índices columnstore têm um desempenho melhor quando a maioria das linhas é compactada no columnstore e não permanece nos rowgroups delta.Columnstore indexes perform best when most of the rows are compressed into the columnstore and not sitting in delta rowgroups. É melhor dimensionar suas cargas de tamanho para que as linhas vão diretamente para o columnstore e ignorem o deltastore o máximo possível.It's best to size your loads so that rows go directly to the columnstore and bypass the deltastore as much as possible.

Esses cenários descrevem quando as linhas carregadas vão diretamente para o columnstore ou quando elas vão para o deltastore.These scenarios describe when loaded rows go directly to the columnstore or when they go to the deltastore. No exemplo, cada rowgroup pode ter de 102.400 a 1.048.576 linhas por rowgroup.In the example, each rowgroup can have 102,400-1,048,576 rows per rowgroup. Na prática, o tamanho máximo de um rowgroup poderá ser inferior a 1.048.576 linhas quando houver pressão de memória.In practice, the maximum size of a rowgroup can be smaller than 1,048,576 rows when there is memory pressure.

Linhas para carregamento em massaRows to Bulk Load Linhas adicionadas ao rowgroup compactadoRows Added to the Compressed Rowgroup Linhas adicionadas ao rowgroup deltaRows Added to the Delta Rowgroup
102.000102,000 00 102.000102,000
145.000145,000 145.000145,000

Tamanho do rowgroup: 145.000Rowgroup size: 145,000
00
1,048,5771,048,577 1.048.5761,048,576

Tamanho do rowgroup: 1.048.576.Rowgroup size: 1,048,576.
11
2,252,1522,252,152 2,252,1522,252,152

Tamanhos dos rowgroups: 1.048.576, 1.048.576, 155.000.Rowgroup sizes: 1,048,576, 1,048,576, 155,000.
00
     

O exemplo a seguir mostra os resultados do carregamento de 1.048.577 linhas em uma tabela.The following example shows the results of loading 1,048,577 rows into a table. Os resultados mostram um rowgroup COMPRESSED no columnstore (como segmentos de coluna compactados) e 1 linha no deltastore.The results show that one COMPRESSED rowgroup in the columnstore (as compressed column segments), and 1 row in the 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  

Rowgroup e deltastore para um carregamento em loteRowgroup and deltastore for a batch load

Usar uma tabela de preparo para melhorar o desempenhoUse a staging table to improve performance

Se você estiver carregando dados apenas para prepará-los antes de executar mais transformações, carregar a tabela na tabela de heap será muito mais rápido do que carregar os dados em uma tabela columnstore clusterizada.If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. 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.In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.

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 seguirA common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

INSERT INTO <columnstore index>  
SELECT <list of columns> 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.This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. Se o número de linhas na tabela de preparo for inferior a 102.400, as linhas serão carregadas em um rowgroup delta, caso contrário, as linhas serão carregadas diretamente no rowgroup compactado.If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. Uma importante limitação era que essa operação INSERT era single-threaded.One key limitation was that this INSERT operation was single threaded. 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.To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from the staging table. Essa limitação não existe no SQL Server 2016 (13.x)SQL Server 2016 (13.x).This limitation goes away with SQL Server 2016 (13.x)SQL Server 2016 (13.x). O comando abaixo carrega os dados da tabela de preparo em paralelo, mas você precisará especificar TABLOCK.The command below loads the data from staging table in parallel but you will need to specify TABLOCK. Você pode achar isso contraditório em relação ao que foi dito anteriormente com carregamento em massa, mas a principal diferença é que o carregamento de dados paralelos da tabela de preparo é executado na mesma transação.You may find this contradictory to what was said earlier with bulkload but the key difference is the parallel data load from the staging table is executed under the same transaction.

INSERT INTO <columnstore index> WITH (TABLOCK) 
SELECT <list of columns> FROM <Staging Table>  

Estas são as otimizações disponíveis ao fazer um carregamento em um índice columnstore clusterizado da tabela de preparo:There are following optimizations available when loading into clustered columnstore index from staging table:

  • Otimização de log: Registro em log reduzido quando os dados são carregados em um rowgroup compactado.Log Optimization: Reduced logging when the data is loaded into compressed rowgroup.
  • Otimização de bloqueio: Ao carregar no rowgroup compactado, o bloqueio X no rowgroup é adquirido.Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. No entanto, com o rowgroup delta, um bloqueio X é adquirido em um rowgroup, mas o SQL ServerSQL Server ainda bloqueia os bloqueios PAGE/EXTENT porque o bloqueio do rowgroup X não faz parte da hierarquia de bloqueios.However, with delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

Se você tiver um ou mais índices não clusterizados, não haverá otimização de log nem de bloqueio para o índice em si, mas as otimizações no índice columnstore clusterizado, conforme descrito acima, permanecerão.If you have one or more nonclustered indexes, there is no locking or logging optimization for the index itself, but the optimizations on the clustered columnstore index as described above are still there.

O que é a inserção de fluxo?What is trickle insert?

Inserção de fluxo refere-se à forma como linhas individuais são movidas para o índice columnstore.Trickle insert refers to the way individual rows move into the columnstore index. As inserções de fluxo usam a instrução INSERT INTO.Trickle inserts use the INSERT INTO statement. Com a inserção de fluxo, todas as linhas vão para o deltastore.With trickle insert, all of the rows go to the deltastore. Isso é útil para um número pequeno de linhas, mas não é prático para grandes cargas.This is useful for small numbers of rows, but not practical for large loads.

INSERT INTO <table-name> VALUES (<set of values>)  

Observação

Os threads simultâneos que usam INSERT INTO para inserir valores em um índice columnstore clusterizado podem inserir linhas no mesmo rowgroup deltastore.Concurrent threads using INSERT INTO to insert values into a clustered columnstore index can insert rows into the same deltastore rowgroup.

Depois que o rowgroup contiver 1.048.576 linhas, o rowgroup delta será marcado como fechado, mas ainda ficará disponível para consultas e operações de atualização/exclusão. No entanto, as linhas recém-inseridas irão para um rowgroup deltastore existente ou recém-criado.Once the rowgroup contains 1,048,576 rows, the delta rowgroup us marked closed but it is still available for queries and update/delete operations but the newly inserted rows go into an existing or newly created deltastore rowgroup. Há um thread em segundo plano, TM (Motor de Tupla) , que compacta os rowgroups delta fechados periodicamente a cada 5 minutos mais ou menos.There is a background thread Tuple Mover (TM) that compresses the closed delta rowgroups periodically every 5 minutes or so. Você pode invocar explicitamente o comando a seguir para compactar o rowgroup delta fechadoYou can explicitly invoke the following command to compress the closed delta rowgroup

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

Se quiser forçar um rowgroup delta a ser fechado e compactado, você poderá executar o comando a seguir.If you want force a delta rowgroup closed and compressed, you can execute the following command. Convém executar esse comando se você tiver terminado de carregar as linhas e não espera linhas novas.You may want run this command if you are done loading the rows and don't expect any new rows. Ao fechar e compactar explicitamente o rowgroup delta, você poderá salvar mais armazenamento e melhorar o desempenho da consulta analítica.By explicitly closing and compressing the delta rowgroup, you can save storage further and improve the analytics query performance. Uma prática recomendada é invocar esse comando se você não espera que novas linhas sejam inseridas.A best practice is to invoke this command if you don't expect new rows to be inserted.

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

Como funciona o carregamento em uma tabela particionadaHow loading into a partitioned table works

Para dados particionados, primeiro o SQL ServerSQL Server atribui cada linha a uma partição e, depois, executa operações columnstore nos dados na partição.For partitioned data, SQL ServerSQL Server first assigns each row to a partition, and then performs columnstore operations on the data within the partition. Cada partição tem seus próprios rowgroups e pelo menos um rowgroup delta.Each partition has its own rowgroups and at least one delta rowgroup.

Próximas etapasNext steps

A postagem de blog agora hospedada no techcommunity, gravado em 03/11/2015: Considerações sobre desempenho de carregamento de dados com índices Columnstore Clusterizados.Blog post now hosted on techcommunity, written 2015-03-11: Data Loading performance considerations with Clustered Columnstore indexes.