Ottimizzazione della qualità di un gruppo di righe per columnstoreMaximizing rowgroup quality for columnstore

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. Ridurre i requisiti di memoria o aumentare la memoria disponibile per accrescere al massimo il numero di righe che un indice columnstore comprime in ogni gruppo di righe.Reduce memory requirements or increase the available memory to 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 la memoria disponibile è scarsa, gli indici columnstore riducono le dimensioni del gruppo di righe in modo da consentire la compressione nel columnstore.When there is memory pressure, columnstore indexes trim the rowgroup sizes so compression into the columnstore can succeed.

Quando la memoria è insufficiente per la compressione di almeno 10.000 righe in ogni gruppo di righe, SQL Data Warehouse genera un errore.When there is insufficient memory to compress at least 10,000 rows into each rowgroup, SQL Data Warehouse generates an error.

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

È presente una DMV (sys.dm_pdw_nodes_db_column_store_row_group_physical_stats) che espone informazioni utili come il numero di righe presenti nei gruppi ed eventualmente il motivo per cui un gruppo di righe è stato tagliato.There is a DMV (sys.dm_pdw_nodes_db_column_store_row_group_physical_stats) 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, valutare l'opportunità di ampliare la finestra di caricamento in batch in modo da accumulare più righe.In this scenario, consider increasing your batch load window to accumulate more rows. Rivalutare inoltre lo schema di partizionamento per verificare che non sia troppo granulare e che i gruppi di righe non possano quindi estendersi oltre i limiti della partizione.Also, reevaluate your partitioning scheme to ensure it is not too granular as row groups cannot 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 memoria aggiuntiva.The following sections explain how to estimate memory required 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 *#colonne stringa breve * 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

dove le colonne stringa breve usano tipi di dati stringa < = 32 byte e le colonne stringa lunga usano tipi di dati stringa > 32 byte.where 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 un'analisi approfondita dei requisiti di memoria columnstore, vedere il video Azure SQL Data Warehouse scaling: configuration and guidance (Scalabilità di Azure SQL Data Warehouse: configurazione e linee guida).For an in-depth discussion of columnstore memory requirements, see the video Azure SQL Data Warehouse 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. Pertanto i requisiti di memoria per comprimere un gruppo di righe aumentano con l'aumentare del numero di colonne.Therefore 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. In SQL Data Warehouse il numero di partizioni aumenta rapidamente perché i dati vengono distribuiti e ogni distribuzione è partizionata.In SQL Data Warehouse, 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 richiesta di memoria durante la compressione, ma alcuni gruppi di righe soffriranno di scarse prestazioni delle query 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.

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. È 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.

Ad esempio, sulla DWU 100 un utente nella classe risorsa smallrc può usare 100 MB di memoria per ogni distribuzione.For example, on DWU 100 a user in the smallrc resource class can use 100 MB of memory for each distribution. Per informazioni dettagliate, vedere Gestione della concorrenza e del carico di lavoro in SQL Data Warehouse.For the details, see Concurrency in SQL Data Warehouse.

Si supponga di determinare la necessità di 700 MB di memoria per ottenere le dimensioni di un gruppo di righe di alta qualità.Suppose you determine that you need 700 MB of memory to get high-quality rowgroup sizes. Questi esempi illustrano come eseguire la query di caricamento con memoria sufficiente.These examples show how you can run the load query with enough memory.

  • Usando la DWU 1000 e mediumrc, la concessione di memoria è di 800 MBUsing DWU 1000 and mediumrc, your memory grant is 800 MB
  • Usando la DWU 600 e largerc, la concessione di memoria è di 800 MB.Using DWU 600 and largerc, your memory grant is 800 MB.

Passaggi successiviNext steps

Per trovare altri modi con cui migliorare le prestazioni in SQL Data Warehouse, vedere la sezione Panoramica relativa alle prestazioni.To find more ways to improve performance in SQL Data Warehouse, see the Performance overview.