Maximizar a qualidade dos grupos de linhas para melhorar o desempenho do índice columnstore

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

Por que o tamanho do grupo de linhas (rowgroup) é importante

Como um índice columnstore verifica uma tabela com a varredura de segmentos de coluna de rowgroups individuais, maximizar o número de linhas em cada rowgroup aprimora o desempenho da consulta. Quando os rowgroups têm um alto número de linhas, a compactação de dados melhora, o que significa que há menos dados a serem lidos do disco.

Para obter mais informações sobre rowgroups, consulte Guia de Índices de Columnstore.

Tamanho alvo para grupos de linhas

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

Os rowgroups podem ser cortados durante a compactação

Durante um carregamento em massa ou uma recompilação de índices columnstore, às vezes não há memória suficiente disponível para compactar todas as linhas designadas para cada rowgroup. Quando há pressão de memória, os índices columnstore cortam o tamanho do rowgroup para que a compactação no columnstore possa ser bem-sucedida.

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

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

Como monitorar a qualidade do rowgroup

A DMV (exibição de gerenciamento dinâmico) (sys.dm_db_column_store_row_group_physical_stats contém a definição da exibição correspondente SQL DB) que expõe informações úteis, como o número de linhas em rowgroups e o motivo para aparar se houver corte. Você pode criar a exibição a seguir como uma forma útil consultar essa DMV para obter informações sobre a fragmentação do rowgroup.

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 rowgroup foi cortado (trim_reason_desc = NO_TRIM implica que não houve corte e o grupo de linhas é de qualidade ideal). Os seguintes motivos de corte indicam um corte prematuro do rowgroup:

• CARREGAMENTO EM MASSA: esse motivo de corte é usado quando o lote de entrada de linhas para a carga tinha menos de 1 milhão de linhas. O mecanismo criará grupos de linhas compactado se houver mais que 100.000 linhas sendo inseridas (em vez de inserir no repositório delta), mas define o motivo do corte como CARREGAMENTO EM MASSA. Nesse cenário, considere aumentar a carga de 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 limites de partição.
• MEMORY_LIMITATION: Para criar grupos de linhas com 1 milhão de linhas, uma determinada quantidade de memória de trabalho é exigida pelo mecanismo. Quando a memória disponível da sessão de carregamento é menor 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: este motivo do corte indica que a fragmentação do grupo de linhas ocorreu devido a pelo menos uma coluna de cadeia de caracteres com cadeias de caracteres ampla e/ou de alta cardinalidade. O tamanho do dicionário é limitado a 16 MB na memória e, depois que esse limite é atingido, o grupo de linhas é compactado. Se você encontrar 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 compactar um rowgroup é, aproximadamente, da seguinte maneira:

• 72 MB +
• #rows * #columns * 8 bytes +
• #rows * #short-string-columns * 32 bytes +
• #colunas de cadeia de caracteres longa * 16 MB para o dicionário de compactação

As cadeias de caracteres longas são compactadas com um método de compactação projetado para a compactação de 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 rowgroup.

Maneiras de reduzir os requisitos de memória

Use as técnicas a seguir para reduzir os requisitos de memória para compactar rowgroups em índices columnstore.

Usar menos colunas

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

Usar menos colunas de cadeia de caracteres

Colunas de tipos de dados de texto exigem mais memória do que tipos de dados numéricos ou de data. Para reduzir os requisitos de memória, considere remover as colunas de cadeia de caracteres de tabelas de fatos e colocá-las em tabelas de dimensão menores.

Requisitos de memória adicionais para compactaçã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 rowgroup pode exigir até 16 MB adicionais para criar o dicionário.

Evitar o excesso de particionamento

Os índices Columnstore criam um ou mais grupos de linhas por partição. Para data warehousing no Azure Synapse Analytics, o número de partições aumenta rapidamente porque os dados são distribuídos e cada distribuição é particionada. Se a tabela tiver muitas partições, talvez não haja linhas suficientes para preencher os rowgroups. 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 em armazenamento por colunas.

Outro motivo para evitar o particionamento excessivo é que há uma sobrecarga de memória para carregar linhas em um índice columnstore em uma tabela particionada. Durante o carregamento, várias partições poderão receber as linhas de entrada, que são mantidas na memória até que cada partição tenha linhas suficientes para ser compactada. Ter muitas partições cria pressão de memória adicional.

Simplificar 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 classificações e junções complexas, a memória disponível para compactaçã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 separadas da consulta de carga. Por exemplo, prepare os dados em uma tabela de heap, execute as transformações e carregue a tabela de preparo no índice columnstore.

Ajustar MAXDOP

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

Para reduzir a pressão de memória, você pode usar a dica de consulta MAXDOP para forçar a operação de carga a ser executada no modo serial em 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 do DWU e a classe de recurso do usuário juntos determinam a quantidade de memória disponível para uma consulta de usuário. Para aumentar a concessão de memória para uma consulta de carga, você pode aumentar o número de DWUs ou aumentar a classe de recurso.

• Para aumentar os DWUs, veja Como faço para dimensionar o desempenho?
• Para alterar a classe de recurso de uma consulta, consulte Alterar um exemplo de classe de recurso de usuário.

Próximas etapas

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