Maximizando a qualidade do grupo de linhas para o columnstoreMaximizing rowgroup quality for columnstore

A qualidade do grupo de linhas é determinada pelo número de linhas em um grupo de linhas.Rowgroup quality is determined by the number of rows in a rowgroup. Aumentar a memória disponível pode maximizar o número de linhas que um índice columnstore compacta em cada rowgroup.Increasing the available memory can maximize the number of rows a columnstore index compresses into each rowgroup. Use estes métodos para melhorar as taxas de compactação e o desempenho da consulta em índices columnstore.Use these methods to improve compression rates and query performance for columnstore indexes.

Por que o tamanho do rowgroup é importanteWhy the rowgroup size matters

Como um índice columnstore examina uma tabela com o exame de segmentos de coluna de rowgroups individuais, maximizar o número de linhas em cada rowgroup melhora o desempenho da consulta.Since a columnstore index scans a table by scanning column segments of individual rowgroups, maximizing the number of rows in each rowgroup enhances query performance. Quando os rowgroups têm um número elevado de linhas, a compactação de dados melhora, o que significa que há menos dados para serem lidos do disco.When rowgroups have a high number of rows, data compression improves which means there is less data to read from disk.

Para obter mais informações sobre rowgroups, consulte Guia de índices Columnstore.For more information about rowgroups, see Columnstore Indexes Guide.

Tamanho de destino para rowgroupsTarget size for rowgroups

Para o melhor desempenho de consulta, o objetivo é maximizar o número de linhas por rowgroup em um índice columnstore.For best query performance, the goal is to maximize the number of rows per rowgroup in a columnstore index. Um rowgroup pode ter, no máximo, 1.048.576 linhas.A rowgroup can have a maximum of 1,048,576 rows. Não é um problema ter o número máximo de linhas por rowgroup.It's okay to not have the maximum number of rows per rowgroup. Os índices Columnstore obtêm um bom desempenho quando os rowgroups têm, pelo menos, 100.000 linhas.Columnstore indexes achieve good performance when rowgroups have at least 100,000 rows.

Os rowgroups podem ser cortados durante a compactaçãoRowgroups can get trimmed during compression

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.During a bulk load or columnstore index rebuild, sometimes there isn't enough memory available to compress all the rows designated for each 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.When there is memory pressure, columnstore indexes trim the rowgroup sizes so compression into the columnstore can succeed.

Quando não há memória suficiente para compactar, pelo menos, 10.000 linhas em cada rowgroup, o SQL Data Warehouse gera um erro.When there is insufficient memory to compress at least 10,000 rows into each rowgroup, SQL Data Warehouse generates an error.

Para obter mais informações sobre o carregamento em massa, consulte Carregamento em massa em um índice columnstore clusterizado.For more information on bulk loading, see Bulk load into a clustered columnstore index.

Como monitorar a qualidade do grupo de linhasHow to monitor rowgroup quality

O DMV sys. dm_pdw_nodes_db_column_store_row_group_physical_stats (Sys. dm_db_column_store_row_group_physical_stats contém a definição de exibição correspondente ao banco de dados SQL para SQL data warehouse) que expõe informações úteis, como o número de linhas em RowGroups e o motivo para aparar se houver corte.The DMV sys.dm_pdw_nodes_db_column_store_row_group_physical_stats (sys.dm_db_column_store_row_group_physical_stats contains the view definition matching SQL DB to SQL Data Warehouse) that exposes useful information such as number of rows in rowgroups and the reason for trimming if there was trimming. Você pode criar a exibição a seguir como uma maneira útil consultar essa DMV para obter informações sobre a fragmentação do grupo de linhas.You can create the following view as a handy way to query this DMV to get information on rowgroup trimming.

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 informa se o grupo de linhas foi cortado (trim_reason_desc = NO_TRIM implica que não houve corte e grupo de linhas é de melhor qualidade).The trim_reason_desc tells whether the rowgroup was trimmed(trim_reason_desc = NO_TRIM implies there was no trimming and row group is of optimal quality). Os motivos de corte a seguir indicam prematuro corte do grupo de linhas:The following trim reasons indicate premature trimming of the 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.BULKLOAD: This trim reason is used when the incoming batch of rows for the load had less than 1 million rows. 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 para CARREGAMENTO EM MASSA.The engine will create compressed row groups if there are greater than 100,000 rows being inserted (as opposed to inserting into the delta store) but sets the trim reason to BULKLOAD. Nesse cenário, considere aumentar a carga do lote para incluir mais linhas.In this scenario, consider increasing your batch load to include more rows. Além disso, reavalie o esquema de particionamento para garantir que não está muito granular, já que os grupos de linhas não podem abranger os limites de partição.Also, reevaluate your partitioning scheme to ensure it is not too granular as row groups cannot span partition boundaries.
  • MEMORY_LIMITATION: Para criar grupos de linhas com 1 milhão de linhas, uma determinada quantidade de memória de trabalho é necessária para o mecanismo.MEMORY_LIMITATION: To create row groups with 1 million rows, a certain amount of working memory is required by the engine. Quando a memória disponível da sessão de carregamento é menor do que a memória necessária do trabalho, grupos de linhas são cortados prematuramente.When available memory of the loading session is less than the required working memory, row groups get prematurely trimmed. As seções a seguir explicam como estimar a memória necessária e alocar mais memória.The following sections explain how to estimate memory required and allocate more memory.
  • 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.DICTIONARY_SIZE: This trim reason indicates that rowgroup trimming occurred because there was at least one string column with wide and/or high cardinality strings. O tamanho do dicionário está limitado a 16 MB de memória e quando esse limite é atingido o grupo de linhas é compactado.The dictionary size is limited to 16 MB in memory and once this limit is reached the row group is compressed. Se você se deparar com essa situação, considere isolar a coluna problemática em uma tabela separada.If you do run into this situation, consider isolating the problematic column into a separate table.

Como estimar os requisitos de memóriaHow to estimate memory requirements

O máximo de memória necessário para compactar um rowgroup é aproximadamenteThe maximum required memory to compress one rowgroup is approximately

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

em que as colunas de cadeia de caracteres curta usam tipos de dados de cadeia de caracteres de <= 32 bytes e as colunas de cadeia de caracteres longa usam tipos de dados de cadeia de caracteres de > 32 bytes.where short-string-columns use string data types of <= 32 bytes and long-string-columns use string data types of > 32 bytes.

As cadeias de caracteres longas são compactadas com um método de compactação projetado para a compactação de texto.Long strings are compressed with a compression method designed for compressing text. Esse método de compactação usa um dicionário para armazenar os padrões de texto.This compression method uses a dictionary to store text patterns. O tamanho máximo de um dicionário é de 16 MB.The maximum size of a dictionary is 16 MB. Há apenas um dicionário para cada coluna de cadeia de caracteres longa no rowgroup.There is only one dictionary for each long string column in the rowgroup.

Para obter uma discussão detalhada sobre os requisitos de memória de columnstore, assista ao vídeo Escala do SQL Data Warehouse do Azure: configuração e diretrizes.For an in-depth discussion of columnstore memory requirements, see the video Azure SQL Data Warehouse scaling: configuration and guidance.

Maneiras de reduzir os requisitos de memóriaWays to reduce memory requirements

Use as técnicas a seguir para reduzir os requisitos de memória para compactar rowgroups em índices columnstore.Use the following techniques to reduce the memory requirements for compressing rowgroups into columnstore indexes.

Usar menos colunasUse fewer columns

Se possível, crie a tabela com menos colunas.If possible, design the table with fewer columns. Quando um rowgroup é compactado no columnstore, o índice columnstore compacta cada segmento de coluna separadamente.When a rowgroup is compressed into the columnstore, the columnstore index compresses each column segment separately. Portanto, os requisitos de memória para compactação de um rowgroup aumentam de acordo com o número de colunas.Therefore the memory requirements to compress a rowgroup increase as the number of columns increases.

Usar menos colunas de cadeia de caracteresUse fewer string columns

As colunas de tipos de dados de cadeia de caracteres exigem mais memória do que os tipos de dados numéricos e de data.Columns of string data types require more memory than numeric and date data types. 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.To reduce memory requirements, consider removing string columns from fact tables and putting them in smaller dimension tables.

Requisitos de memória adicionais para a compactação de cadeia de caracteres:Additional memory requirements for string compression:

  • Tipos de dados de cadeia de caracteres de até 32 caracteres podem exigir 32 bytes adicionais por valor.String data types up to 32 characters can require 32 additional bytes per value.
  • Tipos de dados de cadeia de caracteres com mais de 32 caracteres são compactados usando métodos de dicionário.String data types with more than 32 characters are compressed using dictionary methods. Cada coluna no rowgroup pode exigir até 16 MB adicionais para a criação do dicionário.Each column in the rowgroup can require up to an additional 16 MB to build the dictionary.

Evitar o excesso de particionamentoAvoid over-partitioning

Os índices Columnstore criam um ou mais rowgroups por partição.Columnstore indexes create one or more rowgroups per partition. No SQL Data Warehouse, o número de partições aumenta rapidamente porque os dados são distribuídos e cada distribuição é particionada.In SQL Data Warehouse, the number of partitions grows quickly because the data is distributed and each distribution is partitioned. Se a tabela tiver um número excessivo de partições, talvez não haja linhas suficientes para preencher os rowgroups.If the table has too many partitions, there might not be enough rows to fill the rowgroups. A falta de linhas não cria a pressão de memória durante a compactação, mas leva a rowgroups que não obtêm o melhor desempenho de consulta de columnstore.The lack of rows does not create memory pressure during compression, but it leads to rowgroups that do not achieve the best columnstore query performance.

Outro motivo para evitar o excesso de particionamento é que há uma sobrecarga de memória no carregamento de linhas em um índice columnstore em uma tabela particionada.Another reason to avoid over-partitioning is there is a memory overhead for loading rows into a columnstore index on a partitioned table. 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.During a load, many partitions could receive the incoming rows, which are held in memory until each partition has enough rows to be compressed. Ter um número excessivo de partições cria pressão de memória adicional.Having too many partitions creates additional memory pressure.

Simplificar a consulta de cargaSimplify the load query

O banco de dados compartilha a concessão de memória para uma consulta entre todos os operadores na consulta.The database shares the memory grant for a query among all the operators in the query. Quando uma consulta de carga tem classificações e junções complexas, a memória disponível para compactação é reduzida.When a load query has complex sorts and joins, the memory available for compression is reduced.

Crie a consulta de carga para que ela se concentre apenas no carregamento da consulta.Design the load query to focus only on loading the query. Se você precisar executar transformações nos dados, execute-as separadamente da consulta de carga.If you need to run transformations on the data, run them separate from the load query. Por exemplo, prepare os dados em uma tabela de heap, execute as transformações e carregue a tabela de preparo no índice columnstore.For example, stage the data in a heap table, run the transformations, and then load the staging table into the columnstore index. Você também pode carregar os dados primeiro e usar o sistema MPP para transformar os dados.You can also load the data first and then use the MPP system to transform the data.

Ajustar o MAXDOPAdjust MAXDOP

Cada distribuição compacta rowgroups no columnstore em paralelo quando há mais de um núcleo de CPU disponível por distribuição.Each distribution compresses rowgroups into the columnstore in parallel when there is more than one CPU core available per distribution. O paralelismo exige recursos de memória adicionais, o que pode levar à pressão de memória e ao corte de rowgroup.The parallelism requires additional memory resources, which can lead to memory pressure and rowgroup trimming.

Para reduzir a pressão de memória, use a dica de consulta MAXDOP para forçar a operação de carregamento a ser executada em modo serial em cada distribuição.To reduce memory pressure, you can use the MAXDOP query hint to force the load operation to run in serial mode within each distribution.

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

Maneiras de alocar mais memóriaWays to allocate more memory

O tamanho da DWU e a classe de recurso de usuário em conjunto determinam a quantidade de memória disponível para uma consulta de usuário.DWU size and the user resource class together determine how much memory is available for a user query. 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.To increase the memory grant for a load query, you can either increase the number of DWUs or increase the resource class.

Próximas etapasNext steps

Para encontrar mais maneiras de melhorar o desempenho no SQL Data Warehouse, consulte a Visão geral do desempenho.To find more ways to improve performance in SQL Data Warehouse, see the Performance overview.