Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Base de dados SQL no Microsoft Fabric
Opções e recomendações para carregar dados em um índice de colunas usando os métodos padrão de carregamento em massa SQL e inserção gradual. Carregar dados num índice columnstore é uma parte essencial de qualquer processo de armazenamento de dados porque move dados para o índice em preparação para a análise.
Novo nos índices columnstore? Consulte Columnstore indexes - overview e Columnstore Index Architecture.
O que é o carregamento a granel?
O carregamento em massa refere-se à forma como um grande número de linhas é adicionado a um armazenamento de dados. É a maneira mais eficiente de mover dados para um índice columnstore porque ele opera em lotes de linhas. O carregamento em massa preenche os grupos de linhas até a capacidade máxima e os compacta diretamente no columnstore. Somente as linhas no fim de um carregamento que não atendem ao mínimo de 102,400 linhas por grupo de linhas vão para o deltastore.
Para executar uma carga em massa, você pode usar o Utilitário bcp, Integration Services ou selecionar linhas de uma tabela de preparo.
Como o diagrama sugere, uma carga em massa:
- Não pré-classifica os dados. Os dados são inseridos em grupos de linhas na ordem em que são recebidos.
- Se o tamanho do lote for >= 102400, as linhas serão carregadas diretamente nos grupos de linhas compactados. Você deve optar por um tamanho de lote >= 102400 para uma importação em massa eficiente, pois isso permite evitar que as linhas de dados sejam movidas para grupos de linhas delta antes de serem transferidas para grupos de linhas compactados por um processo em segundo plano, o tuple mover (TM).
- Se o tamanho < do lote for 102.400 ou se as linhas restantes forem < 102.400, as linhas serão carregadas em grupos de linhas delta.
Note
Em uma tabela rowstore com dados de índice columnstore não clusterizados, o SQL Server sempre insere dados na tabela base. Os dados nunca são inseridos diretamente no índice de columnstore.
O carregamento em massa tem estas otimizações de desempenho integradas:
Cargas paralelas: Você pode ter várias cargas em massa simultâneas (bcp ou inserção em massa) que estão carregando um arquivo de dados separado. Ao contrário das cargas em massa no armazenamento de linhas do SQL Server, não é necessário especificar
TABLOCKporque cada thread de importação em massa carrega dados exclusivamente em grupos de linhas separados (grupos de linhas compactados ou delta) com um bloqueio exclusivo nesses grupos.Redução de logging: Os dados que são carregados diretamente em grupos de linhas compactadas levam a uma redução significativa no tamanho do arquivo de log. Por exemplo, se os dados foram compactados em 10x, o log de transações correspondente é aproximadamente 10x menor sem ser necessário o uso dos modelos de recuperação em massa ou simples. Todos os dados que vão para um grupo de linhas delta são integralmente registados. Isso inclui qualquer tamanho de lote inferior a 102.400 linhas. A prática recomendada é usar batchsize >= 102400. Como não
TABLOCKé necessário, você pode carregar os dados em paralelo.Registo mínimo: Você pode obter mais redução no registro se seguir os pré-requisitos para o registro mínimo. No entanto, ao contrário de carregar dados em um rowstore, o
TABLOCKleva a um bloqueioX(exclusivo) na tabela em vez de um bloqueioBU(atualização em massa) e, portanto, não é possível realizar o carregamento de dados em paralelo. Para obter mais informações sobre bloqueio, consulte Bloqueio e versionamento de linha.Otimização de bloqueio: O
Xbloqueio num grupo de linhas é adquirido automaticamente ao carregar dados num grupo de linhas compactadas. No entanto, ao carregar em massa num grupo de linhas delta, umXbloqueio é adquirido para o grupo de linhas, mas o Mecanismo de Banco de Dados ainda adquire bloqueios de página e extensão porque oXbloqueio de grupo de linhas não faz parte da hierarquia de bloqueio.
Se você tiver um índice de árvore B não clusterizado em um índice columnstore, não haverá otimização de bloqueio ou registro em log para o índice em si, mas as otimizações no índice columnstore clusterizado, conforme descrito anteriormente, serão aplicáveis.
Planear tamanhos de carregamentos em massa para minimizar grupos de linhas de delta
Os índices Columnstore têm melhor desempenho quando a maioria das linhas é compactada no columnstore e não está em grupos de linhas delta. É melhor dimensionar suas cargas para que as linhas vão diretamente para o columnstore e ignorem o 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 grupo de linhas pode ter de 102.400 a 1.048.576 linhas por grupo de linhas. Na prática, o tamanho máximo de um grupo de linhas pode ser menor que 1.048.576 linhas quando há pressão de memória.
| Linhas para carregamento em massa | Linhas adicionadas ao grupo compactado de linhas | Linhas adicionadas ao grupo de linhas delta |
|---|---|---|
| 102,000 | 0 | 102,000 |
| 145,000 | 145,000 Tamanho do grupo de linhas: 145.000 |
0 |
| 1,048,577 | 1,048,576 Tamanho do grupo de linhas: 1.048.576. |
1 |
| 2,252,152 | 2,252,152 Tamanhos do grupo de linhas: 1.048.576, 1.048.576, 155.000. |
0 |
O exemplo a seguir mostra os resultados do carregamento de 1.048.577 linhas em uma tabela. Os resultados mostram que um grupo de linhas COMPRESSED no columnstore (como 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 estágio para melhorar o desempenho
Se estiveres a carregar dados apenas para os preparar antes de realizar mais transformações, carregar a tabela para uma tabela heap é muito mais rápido do que carregar os dados numa tabela columnstore clusterizada. Além disso, carregar dados em uma [tabela temporária][Temporária] também carregará muito mais rápido do que carregar uma tabela para armazenamento permanente.
Um padrão comum para carregamento de dados é carregar os dados em uma tabela de preparo, fazer alguma transformação e, em seguida, carregá-los na tabela de destino usando o seguinte comando:
INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]
Este comando carrega os dados no índice columnstore de maneira semelhante ao bcp ou inserção em massa, mas em um único lote. Se o número de linhas na tabela < de preparo 102400, as linhas são carregadas em um grupo de linhas delta, caso contrário, as linhas são carregadas diretamente no grupo de linhas compactado. Uma limitação importante era que essa INSERT operação era de thread único. Para carregar dados em paralelo, pode criar várias tabelas de estágio ou emitir INSERT/SELECT com intervalos de linhas não sobrepostos da tabela de estágio. Essa limitação desaparece com o SQL Server 2016 (13.x). O comando a seguir carrega os dados da tabela de preparo em paralelo, mas você deve especificar TABLOCK. Você pode achar isto contraditório em relação ao que foi dito anteriormente sobre o carregamento em massa, mas a principal diferença é que a carga de dados paralela da tabela de preparação ocorre na mesma transação.
INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]
Estão disponíveis as seguintes otimizações ao carregar num índice columnstore clusterizado a partir de uma tabela de preparação:
- Otimização de logs: Registro em log reduzido 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
Xbloqueio no grupo de linhas é adquirido. No entanto, ao carregar em massa num grupo de linhas delta, umXbloqueio é adquirido para o grupo de linhas, mas o Mecanismo de Banco de Dados ainda adquire bloqueios de página e extensão porque oXbloqueio 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 registro em log para o índice em si, mas as otimizações no índice columnstore clusterizado, conforme descrito anteriormente, ainda estarão lá.
O que é trickle insert?
A inserção por gotejamento refere-se à forma como as linhas individuais são movidas para o índice columnstore. As inserções por gotejamento usam a instrução INSERT INTO. Com a inserção por gotejamento, todas as linhas vão para o deltastore. Isso é útil para um pequeno número de linhas, mas não é prático para grandes volumes de dados.
INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)
Note
Threads simultâneos usando INSERT INTO para inserir valores em um índice columnstore clusterizado podem inserir linhas no mesmo grupo de linhas deltastore.
Quando o grupo de linhas contém 1.048.576 linhas, o grupo de linhas delta us marcou fechado, mas ainda está disponível para consultas e operações de atualização/exclusão, mas as linhas recém-inseridas vão para um grupo de linhas deltastore existente ou recém-criado. Há um thread em segundo plano chamado tuple mover (TM) que comprime os grupos de linhas delta fechados periodicamente a cada 5 minutos ou mais. Você pode invocar explicitamente o seguinte comando para compactar o grupo de linhas delta fechado.
ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE
Se você quiser forçar um grupo de linhas delta fechado e compactado, você pode executar o seguinte comando. Você pode querer executar este comando se tiver terminado de carregar as linhas e não esperar novas linhas. Ao fechar e compactar explicitamente o grupo de linhas delta, você pode economizar ainda mais o armazenamento e melhorar o desempenho da consulta de análise. 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 seus próprios grupos de linhas e pelo menos um grupo de linhas delta.