Ottimizzazione della qualità di rowgroup per gli indici columnstore nel pool SQL dedicatoMaximizing rowgroup quality for columnstore indexes in dedicated SQL pool

La qualità di un gruppo di righe è determinata dal numero di righe nel gruppo.Rowgroup quality is determined by the number of rows in a rowgroup. Aumentando la memoria disponibile è possibile massimizzare il numero di righe che un indice columnstore comprime in ogni rowgroup.Increasing the available memory can maximize the number of rows a columnstore index compresses into each rowgroup. Usare questi metodi per migliorare il tasso di compressione e le prestazioni delle query per gli indici columnstore.Use these methods to improve compression rates and query performance for columnstore indexes.

Perché sono importanti le dimensioni del gruppo di righeWhy the rowgroup size matters

Poiché un indice columnstore analizza una tabella eseguendo la scansione di segmenti di colonna di singoli gruppi di righe, accrescendo al massimo il numero di righe in ogni gruppo di righe le prestazioni delle query migliorano.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 i gruppi di righe hanno un numero elevato di righe, la compressione dei dati migliora, il che significa meno dati da leggere dal disco.When rowgroups have a high number of rows, data compression improves which means there is less data to read from disk.

Per altre informazioni sui gruppi di righe, vedere Descrizione degli indici columnstore.For more information about rowgroups, see Columnstore Indexes Guide.

Dimensioni di destinazione per i gruppi di righeTarget size for rowgroups

Per ottimizzare le prestazioni delle query, l'obiettivo è accrescere al massimo il numero di righe per ogni gruppo di righe in un indice columnstore.For best query performance, the goal is to maximize the number of rows per rowgroup in a columnstore index. Un gruppo di righe può avere un massimo di 1.048.576 righe.A rowgroup can have a maximum of 1,048,576 rows.

È accettabile non avere il numero massimo di righe per gruppo di righe.It's okay to not have the maximum number of rows per rowgroup. Gli indici columnstore ottengono buone prestazioni quando i gruppi di righe hanno almeno 100.000 righe.Columnstore indexes achieve good performance when rowgroups have at least 100,000 rows.

I gruppi di righe possono essere tagliati durante la compressioneRowgroups can get trimmed during compression

Durante un caricamento bulk o la ricompilazione di un indice columnstore, talvolta non è disponibile memoria sufficiente per comprimere tutte le righe per ogni gruppo di righe.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 è presente un numero eccessivo di richieste di memoria, gli indici columnstore tagliano le dimensioni rowgroup in modo che la compressione nel columnstore riescaWhen memory pressure is present, columnstore indexes trim the rowgroup sizes so compression into the columnstore can succeed.

Quando la memoria disponibile non è sufficiente per comprimere almeno 10.000 righe in ogni rowgroup, viene generato un errore.When there is insufficient memory to compress at least 10,000 rows into each rowgroup, an error will be generated.

Per altre informazioni sul caricamento bulk, vedere Caricamento bulk in un indice columnstore cluster.For more information on bulk loading, see Bulk load into a clustered columnstore index.

Come monitorare la qualità di un gruppo di righeHow to monitor rowgroup quality

Il sys.dm_pdw_nodes_db_column_store_row_group_physical_stats DMV (sys.dm_db_column_store_row_group_physical_stats contiene la definizione della vista che corrisponde al database SQL) che espone informazioni utili, ad esempio il numero di righe in RowGroups e il motivo per cui è stato tagliato il taglio.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) that exposes useful information such as number of rows in rowgroups and the reason for trimming, if there was trimming.

Per effettuare una query su questa DMV allo scopo di ottenere informazioni sul trimming di un gruppo di righe, è possibile creare la vista seguente.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;

trim_reason_desc specifica se il gruppo di righe è stato tagliato (trim_reason_desc = NO_TRIM indica che il gruppo di righe è di qualità ottimale e non è stato tagliato).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). I motivi seguenti indicano che il gruppo di righe è stato tagliato prematuramente:The following trim reasons indicate premature trimming of the rowgroup:

  • BULKLOAD: questo motivo viene usato se il batch di righe in ingresso per il caricamento è inferiore a 1 milione di righe.BULKLOAD: This trim reason is used when the incoming batch of rows for the load had less than 1 million rows. Il motore creerà gruppi di righe compressi se devono essere inserite più di 100.000 righe (a differenza dell'inserimento nell'archivio differenziale), ma imposta il motivo per cui il gruppo è stato tagliato su BULKLOAD.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. In questo scenario, prendere in considerazione l'aumento del carico batch per includere più righe.In this scenario, consider increasing your batch load to include more rows. Inoltre, rivalutare lo schema di partizionamento per assicurarsi che non sia troppo granulare perché i gruppi di righe non possono estendersi sui limiti della partizione.Also, reevaluate your partitioning scheme to ensure it's not too granular as row groups can't span partition boundaries.
  • MEMORY_LIMITATION: per creare gruppi di righe con 1 milione di righe, il motore richiede una certa quantità di memoria di lavoro.MEMORY_LIMITATION: To create row groups with 1 million rows, a certain amount of working memory is required by the engine. Se la memoria disponibile nella sessione di caricamento è inferiore alla memoria di lavoro necessaria, i gruppi di righe vengono tagliati in modo prematuro.When available memory of the loading session is less than the required working memory, row groups get prematurely trimmed. Le sezioni seguenti illustrano come stimare la memoria necessaria e allocare ulteriore memoria.The following sections explain how to estimate required memory and allocate more memory.
  • DICTIONARY_SIZE: questo motivo indica che il gruppo di righe è stato tagliato perché era presente almeno una colonna di stringhe con stringhe "wide" e/o a cardinalità elevata.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. Le dimensioni del dizionario sono limitate a 16 MB di memoria e, al raggiungimento di questo limite, il gruppo di righe viene compresso.The dictionary size is limited to 16 MB in memory and once this limit is reached the row group is compressed. Se si verifica questa situazione, valutare l'opportunità di isolare la colonna problematica in una tabella separata.If you do run into this situation, consider isolating the problematic column into a separate table.

Come stimare i requisiti di memoriaHow to estimate memory requirements

La memoria massima necessaria per comprimere un gruppo di righe è circaThe maximum required memory to compress one rowgroup is approximately

  • 72 MB +72 MB +
  • #righe * # colonne * 8 byte +#rows * #columns * 8 bytes +
  • #righe * # stringa breve-colonne * 32 byte +#rows * #short-string-columns * 32 bytes +
  • #colonne stringa lunga * 16 MB per il dizionario di compressione#long-string-columns * 16 MB for compression dictionary

Nota

Le colonne Short String utilizzano i tipi di dati stringa di <= 32 byte e le colonne con stringhe lunghe utilizzano i tipi di dati stringa di > 32 byte.Short-string-columns use string data types of <= 32 bytes and long-string-columns use string data types of > 32 bytes.

Le stringhe lunghe vengono compresse con un metodo di compressione progettato per la compressione del testo.Long strings are compressed with a compression method designed for compressing text. Questo metodo di compressione usa un dizionario per archiviare i modelli di testo.This compression method uses a dictionary to store text patterns. La dimensione massima di un oggetto dictionary è 16 MB.The maximum size of a dictionary is 16 MB. Esiste un solo dizionario per ogni colonna stringa lunga nel gruppo di righe.There is only one dictionary for each long string column in the rowgroup.

Per una descrizione approfondita dei requisiti di memoria columnstore, vedere il video dedicato relativo al ridimensionamento del pool SQL: configurazione e linee guida.For an in-depth discussion of columnstore memory requirements, see the video Dedicated SQL pool scaling: configuration and guidance.

Modi per ridurre i requisiti di memoriaWays to reduce memory requirements

Usare le tecniche seguenti per ridurre i requisiti di memoria per la compressione dei gruppi di righe in indici columnstore.Use the following techniques to reduce the memory requirements for compressing rowgroups into columnstore indexes.

Usare meno colonneUse fewer columns

Se possibile, progettare la tabella con meno colonne.If possible, design the table with fewer columns. Quando un gruppo di righe viene compresso nel columnstore, l'indice columnstore comprime ogni segmento di colonna separatamente.When a rowgroup is compressed into the columnstore, the columnstore index compresses each column segment separately.

Di conseguenza, i requisiti di memoria per comprimere un rowgroup aumentano man mano che aumenta il numero di colonne.As such, the memory requirements to compress a rowgroup increase as the number of columns increases.

Usare meno colonne di stringheUse fewer string columns

Le colonne di dati di tipo stringa richiedono una quantità di memoria maggiore rispetto ai tipi di dati numerici.Columns of string data types require more memory than numeric and date data types. Per ridurre i requisiti di memoria, prendere in considerazione la rimozione delle colonne di tipo stringa dalle tabelle di dati e il loro inserimento in tabelle di dimensioni minori.To reduce memory requirements, consider removing string columns from fact tables and putting them in smaller dimension tables.

Requisiti di memoria aggiuntivi per la compressione di stringhe:Additional memory requirements for string compression:

  • I tipi di dati stringa fino a 32 caratteri possono richiedere 32 byte aggiuntivi per valore.String data types up to 32 characters can require 32 additional bytes per value.
  • I tipi di dati stringa con più di 32 caratteri vengono compressi mediante metodi di dizionario.String data types with more than 32 characters are compressed using dictionary methods. Ogni colonna del gruppo di righe può richiedere fino a 16 MB aggiuntivi per creare il dizionario.Each column in the rowgroup can require up to an additional 16 MB to build the dictionary.

Evitare il partizionamento eccessivoAvoid over-partitioning

Gli indici columnstore creano uno o più gruppi di righe per partizione.Columnstore indexes create one or more rowgroups per partition. Per il pool SQL dedicato in Azure sinapsi Analytics, il numero di partizioni cresce rapidamente perché i dati vengono distribuiti e ogni distribuzione è partizionata.For dedicated SQL pool in Azure Synapse Analytics, the number of partitions grows quickly because the data is distributed and each distribution is partitioned.

Se la tabella ha troppe partizioni, potrebbero esserci abbastanza righe per riempire i gruppi di righe.If the table has too many partitions, there might not be enough rows to fill the rowgroups. La mancanza di righe non crea un numero eccessivo di richieste di memoria durante la compressione.The lack of rows does not create memory pressure during compression. Ma conduce a RowGroups che non raggiungono le migliori prestazioni di query columnstore.But, it leads to rowgroups that do not achieve the best columnstore query performance.

Un altro motivo per evitare l'eccessivo partizionamento è che il caricamento di righe in un indice columnstore in una tabella partizionata comporta un sovraccarico della memoria.Another reason to avoid over-partitioning is there is a memory overhead for loading rows into a columnstore index on a partitioned table.

Durante il caricamento molte partizioni potrebbero ricevere le righe in ingresso, che vengono mantenute in memoria finché ogni partizione dispone di un numero di righe sufficiente da comprimere.During a load, many partitions could receive the incoming rows, which are held in memory until each partition has enough rows to be compressed. Con un numero eccessivo di partizioni vengono create richieste di memoria aggiuntive.Having too many partitions creates additional memory pressure.

Semplificare la query di caricamentoSimplify the load query

Il database condivide la concessione di memoria per una query tra tutti gli operatori della query.The database shares the memory grant for a query among all the operators in the query. Quando una query di caricamento contiene ordinamenti complessi e join, la memoria disponibile per la compressione è ridotta.When a load query has complex sorts and joins, the memory available for compression is reduced.

Progettare la query di caricamento concentrandosi solo sul caricamento.Design the load query to focus only on loading the query. Se è necessario eseguire trasformazioni sui dati, eseguirle separatamente dalla query di caricamento.If you need to run transformations on the data, run them separate from the load query. Ad esempio, collocare temporaneamente i dati in una tabella heap, eseguire le trasformazioni e quindi caricare la tabella di gestione temporanea nell'indice columnstore.For example, stage the data in a heap table, run the transformations, and then load the staging table into the columnstore index.

Suggerimento

È possibile anche caricare prima i dati e poi usare il sistema MPP per trasformarli.You can also load the data first and then use the MPP system to transform the data.

Regolare MAXDOPAdjust MAXDOP

Ogni distribuzione comprime i gruppi di righe nel columnstore in parallelo quando c'è più di un core CPU disponibile per distribuzione.Each distribution compresses rowgroups into the columnstore in parallel when there is more than one CPU core available per distribution.

Il parallelismo richiede risorse di memoria aggiuntive che possono portare a richieste di memoria pesanti e al taglio del gruppo di righe.The parallelism requires additional memory resources, which can lead to memory pressure and rowgroup trimming.

Per ridurre le richieste di memoria, è possibile usare l'hint di query MAXDOP per forzare l'esecuzione seriale dell'operazione di caricamento in ogni distribuzione.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);

Modi per allocare altra memoriaWays to allocate more memory

La dimensione delle DWU e la classe della risorsa utente insieme determinano la quantità di memoria disponibile per una query dell'utente.DWU size and the user resource class together determine how much memory is available for a user query.

Per aumentare la concessione di memoria per una query di caricamento, è possibile aumentare il numero di DWU o aumentare la classe risorsa.To increase the memory grant for a load query, you can either increase the number of DWUs or increase the resource class.

Passaggi successiviNext steps

Per ulteriori modi per migliorare le prestazioni per il pool SQL dedicato, vedere Panoramica delle prestazioni.To find more ways to improve performance for dedicated SQL pool, see the Performance overview.