Maximizar a qualidade do grupo de linhas para índices columnstore no conjunto de SQL dedicado

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 das consultas.

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 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 compressão

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

Quando não existe 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

O sys.dm_pdw_nodes_db_column_store_row_group_physical_stats 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, caso exista a limitação.

Pode criar a seguinte vista como uma forma útil de consultar esta DMV para obter informações sobre a limitaçã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;

O trim_reason_desc indica se o grupo de linhas foi cortado (trim_reason_desc = NO_TRIM implica que não houve limitação e o grupo de linhas é de qualidade ideal). Os seguintes motivos de corte indicam a limitação prematura do grupo de linhas:

• BULKLOAD: este motivo de corte é utilizado quando o lote de linhas de entrada para a carga tinha menos de 1 milhão de linhas. O motor criará grupos de linhas comprimidos se forem inseridas mais de 100 000 linhas (por oposição à inserção no arquivo delta), mas define o motivo do 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 1 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 cortados 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 limitaçã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 de memória e, assim que este limite for atingido, 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

Para ver uma estimativa dos requisitos de memória para comprimir um grupo de linhas de tamanho máximo num índice columnstore, considere criar a vista de exemplo dbo.vCS_mon_mem_grant. Esta consulta mostra o tamanho da concessão de memória que um grupo de linhas necessita para compressão no columnstore.

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

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

Nota

As colunas de cadeia curta utilizam tipos de dados de cadeia de carateres = <32 bytes e colunas de cadeia 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.

Como tal, 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 os 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 de carateres:

• Os tipos de dados de cadeia até 32 carateres podem exigir 32 bytes adicionais por valor.
• Os tipos de dados de cadeia com mais de 32 carateres são comprimidos através de 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 o conjunto de SQL dedicado 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. No entanto, leva a grupos de linhas que não conseguem obter o melhor desempenho de consulta columnstore.

Outra razão para evitar a criação de partições excessivas é a sobrecarga de memória para carregar linhas para um índice columnstore numa tabela particionada.

Durante uma carga, muitas partições podem receber as linhas de entrada, 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 para dados, execute as transformações e, em seguida, carregue a tabela de teste para o índice columnstore.

Dica

Também pode carregar os dados primeiro e, em seguida, utilizar o sistema MPP para transformar os dados.

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 à limitação do grupo 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 do conjunto de SQL dedicado, veja Descrição geral do desempenho.