Partilhar via

Maximizar a qualidade do grupo de linhas para o desempenho do índice columnstore

A qualidade do grupo de linhas é determinada pelo número de linhas em um grupo de linhas. Aumentar a memória disponível pode maximizar o número de linhas que um índice de columnstore comprime em cada grupo de linhas. Utilize estes métodos para melhorar as taxas de compactação e o desempenho das consultas para índices columnstore.

Por que o tamanho do grupo de linhas é importante

Como um índice columnstore verifica uma tabela examinando segmentos de coluna de grupos de linhas individuais, maximizar o número de linhas em cada grupo de linhas melhora o desempenho da consulta. Quando os grupos de linhas têm um número elevado de linhas, a compressão de dados melhora, o que significa que há menos dados para ler a partir do disco.

Para obter mais informações sobre grupos de linhas, consulte Columnstore Indexes Guide.

Tamanho de destino para grupos de linhas

Para obter o melhor desempenho da consulta, o objetivo é maximizar o número de linhas por grupo de linhas em um índice columnstore. Um grupo de linhas pode ter um máximo de 1.048.576 linhas. Não há problema em não ter o número máximo de linhas por grupo de linhas. Os índices Columnstore alcançam um bom desempenho quando os grupos de linhas têm pelo menos 100.000 linhas.

Os grupos de linhas podem ser cortados durante a compactação

Durante um carregamento em massa ou reconstrução de índice (columnstore), às vezes não há memória suficiente disponível para compactar todas as linhas designadas para cada grupo de linhas. Quando há pressão de memória, os índices columnstore ajustam os tamanhos dos grupos de linhas para que a compactação no columnstore tenha sucesso.

Quando não houver memória suficiente para compactar pelo menos 10.000 linhas em cada grupo de linhas, um erro será gerado.

Para obter mais informações sobre carregamento em massa, consulte Carregamento em massa em um índice columnstore clusterizado.

Como monitorizar a qualidade do grupo de linhas

A vista de gestão dinâmica (DMV) (sys.dm_db_column_store_row_group_physical_stats, que corresponde à definição da vista no Banco de Dados SQL) expõe informações úteis, como o número de linhas nos grupos de linhas e o motivo da redução, caso tenha ocorrido. Você pode criar a exibição a seguir como uma maneira prática de consultar esse Detran para obter informações sobre o corte de grupos de linhas.

CREATE VIEW dbo.vCS_rg_physical_stats
AS
WITH cte
AS
(
select   tb.[name]                    AS [logical_table_name]
,        rg.[row_group_id]            AS [row_group_id]
,        rg.[state]                   AS [state]
,        rg.[state_desc]              AS [state_desc]
,        rg.[total_rows]              AS [total_rows]
,        rg.[trim_reason_desc]        AS trim_reason_desc
,        mp.[physical_name]           AS physical_name
FROM    sys.[schemas] sm
JOIN    sys.[tables] tb               ON  sm.[schema_id]          = tb.[schema_id]
JOIN    sys.[pdw_table_mappings] mp   ON  tb.[object_id]          = mp.[object_id]
JOIN    sys.[pdw_nodes_tables] nt     ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg      ON  rg.[object_id]     = nt.[object_id]
                                                                            AND rg.[pdw_node_id]   = nt.[pdw_node_id]
                                        AND rg.[distribution_id]    = nt.[distribution_id]
)
SELECT *
FROM cte;

A trim_reason_desc coluna indica se o grupo de linhas foi cortado (trim_reason_desc = NO_TRIM implica que não houve corte e o grupo de linhas é de ótima qualidade). As seguintes razões de corte indicam corte prematuro do grupo de linhas:

  • BULKLOAD: Esta razão de ajuste é usada quando o lote de entrada de linhas para a carga tinha menos de 1 milhão de linhas. O mecanismo criará grupos de linhas compactadas se houver mais de 100.000 linhas a serem inseridas (ao contrário de inserir no armazenamento delta), mas define o motivo de ajuste como BULKLOAD. Nesse cenário, considere aumentar a carga em lote para incluir mais linhas. Além disso, reavalie seu esquema de particionamento para garantir que ele não seja muito granular, pois os grupos de linhas não podem ultrapassar os limites da partição.
  • MEMORY_LIMITATION: Para criar grupos de linhas com 1 milhão de linhas, é necessária uma certa quantidade de memória de trabalho para o mecanismo. Quando a memória disponível da sessão de carregamento é menor do que a memória de trabalho necessária, os grupos de linhas são cortados prematuramente. As seções a seguir explicam como estimar a memória necessária e alocar mais memória.
  • DICTIONARY_SIZE: Esse motivo de corte indica que o corte de grupo de linhas ocorreu porque havia pelo menos uma coluna de cadeia de caracteres com cadeias de cardinalidade largas e/ou altas. O tamanho do dicionário é limitado a 16 MB de memória e, uma vez atingido este limite, o grupo de linhas é comprimido. Se você se deparar com essa situação, considere isolar a coluna problemática em uma tabela separada.

Como estimar os requisitos de memória

A memória máxima necessária para comprimir um grupo de linhas é, aproximadamente, a seguinte:

  • 72 MB +
  • #rows * #columns * 8 bytes +
  • #rows * #short-string-columns * 32 bytes +
  • #long-string-columns * 16 MB para o dicionário de compressão

Observação

Onde as colunas de strings curtas usam tipos de dados string de < = 32 bytes e as colunas de strings longas usam tipos de dados string de 32 bytes.

Strings longas são compactadas com um método de compressão projetado para compactar texto. Esse método de compactação usa um dicionário para armazenar padrões de texto. O tamanho máximo de um dicionário é de 16 MB. Há apenas um dicionário para cada coluna de cadeia de caracteres longa no grupo de linhas.

Formas de reduzir os requisitos de memória

Utilize as técnicas seguintes para reduzir os requisitos de memória para compactar grupos de linhas em índices de armazenamento em colunas.

Usar menos colunas

Se possível, projete a tabela com menos colunas. Quando um grupo de linhas é compactado no columnstore, o índice columnstore comprime cada segmento de coluna separadamente. Portanto, os requisitos de memória para compactar um grupo de linhas aumentam à medida que o número de colunas aumenta.

Usar menos colunas de cadeia de caracteres

As colunas de tipos de dados do tipo strings requerem mais memória do que os tipos de dados numéricos e de data. Para reduzir os requisitos de memória, considere remover colunas de cadeia de caracteres de tabelas de fatos e colocá-las em tabelas de dimensões menores.

Requisitos de memória adicionais para compressão de cadeia de caracteres:

  • Tipos de dados de cadeia de caracteres de até 32 caracteres podem exigir 32 bytes adicionais por valor.
  • Tipos de dados de cadeia de caracteres com mais de 32 caracteres são compactados usando métodos de dicionário. Cada coluna no grupo de linhas pode exigir até 16 MB adicionais para criar o dicionário.

Evite o particionamento excessivo

Os índices Columnstore criam um ou mais grupos de linhas por partição. Para armazenamento de dados no Azure Synapse Analytics, o número de partições cresce rapidamente porque os dados são distribuídos e cada distribuição é particionada. Se a tabela tiver muitas partições, pode não haver linhas suficientes para preencher os grupos de linhas. A falta de linhas não cria pressão de memória durante a compactação, mas leva a grupos de linhas que não alcançam o melhor desempenho de consulta columnstore.

Outra razão para evitar o particionamento excessivo é que há uma sobrecarga de memória para carregar linhas em um índice columnstore em uma tabela particionada. Durante uma carga, várias partições podem receber as linhas que chegam, mantendo-as na memória até que cada partição tenha um número suficiente de linhas para ser compactada. Ter muitas partições cria pressão adicional de memória.

Simplifique a consulta de carga

O banco de dados compartilha a concessão de memória para uma consulta entre todos os operadores na consulta. Quando uma consulta de carga tem ordenações e junções complexas, a memória disponível para compressão é reduzida.

Projete a consulta de carga para se concentrar apenas no carregamento da consulta. Se você precisar executar transformações nos dados, execute-as separadamente da consulta de carregamento. Por exemplo, prepare os dados numa tabela heap, execute as transformações e carregue a tabela de estágio no índice columnstore.

Ajustar MAXDOP

Cada distribuição compacta grupos de linhas no columnstore em paralelo quando há mais de um núcleo de CPU disponível por distribuição. O paralelismo requer recursos de memória adicionais, o que pode levar à pressão da memória e ao corte do grupo de linhas.

Para reduzir a pressão da memória, você pode usar a dica de consulta MAXDOP para forçar a operação de carga a ser executada no modo serial dentro de cada distribuição.

CREATE TABLE MyFactSalesQuota
WITH (DISTRIBUTION = ROUND_ROBIN)
AS SELECT * FROM FactSalesQuota
OPTION (MAXDOP 1);

Maneiras de alocar mais memória

O tamanho da DWU e a classe de recurso do usuário juntos determinam a quantidade de memória disponível para uma consulta do usuário. Para aumentar a concessão de memória para uma consulta de carregamento, você pode aumentar o número de DWUs ou aumentar a classe de recurso.

Próximos passos

Para encontrar mais maneiras de melhorar o desempenho no Synapse SQL, consulte a Visão geral de desempenho.

  • Last updated on