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

Artigo
06/01/2023

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

Por que motivo o tamanho do grupo de linhas é importante

Uma vez que um índice columnstore analisa uma tabela ao analisar segmentos de colunas 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, veja Columnstore Indexes Guide (Guia de Índices columnstore).

Tamanho de destino para grupos de linhas

Para um melhor desempenho de consulta, o objetivo é maximizar o número de linhas por grupo de linhas num í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 conseguem um bom desempenho quando os grupos de linhas têm, pelo menos, 100 000 linhas.

Os grupos de linhas podem ser cortados durante a compressão

Durante uma recompilação de um índice columnstore ou de carga em massa, por vezes não existe memória suficiente disponível para comprimir todas as linhas designadas para cada grupo de linhas. Quando existe pressão de memória, os índices columnstore cortam os tamanhos do grupo de linhas para que a compressão no columnstore possa ser bem-sucedida.

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

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

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 contém a definição de vista correspondente à BD SQL) que expõe informações úteis, como o número de linhas em grupos de linhas e o motivo para aparar se existir aparação. Pode criar a seguinte vista como uma forma útil de consultar este DMV para obter informações sobre a aparação 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 qualidade ideal). As seguintes razões de corte indicam o corte prematuro do grupo de linhas:

BULKLOAD: este motivo de corte é utilizado quando o lote de linhas recebido para a carga tinha menos de 1 milhão de linhas. O motor criará grupos de linhas comprimidos se existirem mais de 100 000 linhas a serem inseridas (em oposição à inserção no arquivo delta), mas define o motivo de corte como BULKLOAD. Neste cenário, considere aumentar a carga do lote para incluir mais linhas. Além disso, reavalie o esquema de criação de partições para garantir que não é demasiado granular, uma vez que os grupos de linhas não podem abranger limites de partição.
MEMORY_LIMITATION: para criar grupos de linhas com um milhão de linhas, é necessária uma determinada quantidade de memória de trabalho pelo motor. Quando a memória disponível da sessão de carregamento é inferior à memória de trabalho necessária, os grupos de linhas são aparados prematuramente. As secções seguintes explicam como estimar a memória necessária e alocar mais memória.
DICTIONARY_SIZE: este motivo de corte indica que ocorreu a aparação do grupo de linhas porque havia, pelo menos, uma coluna de cadeia com cadeias de cardinalidade largas e/ou elevadas. O tamanho do dicionário está limitado a 16 MB na memória e, uma vez atingido este limite, o grupo de linhas é comprimido. Se encontrar esta situação, considere isolar a coluna problemática numa tabela separada.

Como estimar os requisitos de memória

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

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

Nota

Quando as colunas de cadeia curta utilizam tipos de dados de cadeia de carateres de <= 32 bytes e colunas de cadeias de carateres longas utilizam tipos de dados de cadeia de carateres de > 32 bytes.

As cadeias longas são comprimidas com um método de compressão concebido para comprimir texto. Este método de compressão utiliza um dicionário para armazenar padrões de texto. O tamanho máximo de um dicionário é de 16 MB. Existe apenas um dicionário para cada coluna de cadeia longa no grupo de linhas.

Formas de reduzir os requisitos de memória

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

Utilizar menos colunas

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

Utilizar menos colunas de cadeia

As colunas de tipos de dados de cadeias requerem mais memória do que tipos de dados numéricos e de data. Para reduzir os requisitos de memória, considere remover colunas de cadeia de carateres de tabelas de factos e colocá-las em tabelas de dimensão mais pequenas.

Requisitos de memória adicionais para compressão de cadeias:

Os tipos de dados de cadeias até 32 carateres podem exigir 32 bytes adicionais por valor.
Os tipos de dados de cadeia com mais de 32 carateres são comprimidos com métodos de dicionário. Cada coluna no grupo de linhas pode exigir até 16 MB adicionais para criar o dicionário.

Evitar a criação de partições excessivas

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 aumenta rapidamente porque os dados são distribuídos e cada distribuição é particionada. Se a tabela tiver demasiadas partições, poderá 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 compressão, mas leva a grupos de linhas que não conseguem o melhor desempenho de consulta columnstore.

Outra razão para evitar a criação de partições excessivas é que existe uma sobrecarga de memória para carregar linhas para um índice columnstore numa tabela particionada. Durante uma carga, muitas partições podem receber as linhas recebidas, que são mantidas na memória até que cada partição tenha linhas suficientes para serem comprimidas. Ter demasiadas partições cria pressão de memória adicional.

Simplificar a consulta de carga

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

Crie a consulta de carga para se concentrar apenas no carregamento da consulta. Se precisar de executar transformações nos dados, execute-as separadamente da consulta de carregamento. Por exemplo, teste os dados numa tabela de área dinâmica, execute as transformações e, em seguida, carregue a tabela de teste para o índice columnstore.

Ajustar MAXDOP

Cada distribuição comprime grupos de linhas para o columnstore em paralelo quando existe mais do que 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 de grupos de linhas.

Para reduzir a pressão da memória, pode utilizar a sugestão de consulta MAXDOP para forçar a execução da operação de carga no modo de série em cada distribuição.

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

Formas de alocar mais memória

O tamanho da DWU e a classe de recursos do utilizador em conjunto determinam a quantidade de memória disponível para uma consulta de utilizador. Para aumentar a concessão de memória para uma consulta de carga, pode aumentar o número de DWUs ou aumentar a classe de recursos.

Para aumentar as DWUs, veja Como devo proceder para desempenho de dimensionamento?
Para alterar a classe de recursos de uma consulta, veja Alterar um exemplo de classe de recursos de utilizador.

Passos seguintes

Para encontrar mais formas de melhorar o desempenho no SQL do Synapse, veja a Descrição geral do desempenho.

Partilhar via