Ottimizzare la qualità del gruppo di righe per le prestazioni dell'indice columnstore

La qualità del rowgroup è determinata dal numero di righe in un rowgroup. Aumentare la memoria disponibile può massimizzare il numero di righe che un indice columnstore comprime in ciascun rowgroup. Usare questi metodi per migliorare i tassi di compressione e le prestazioni delle query per gli indici columnstore.

Perché le dimensioni del rowgroup sono importanti

Poiché un indice columnstore analizza una tabella analizzando i segmenti di colonna dei singoli rowgroup, ottimizzando il numero di righe in ogni rowgroup migliora le prestazioni delle query. Quando i rowgroup hanno un numero elevato di righe, la compressione dei dati migliora il che significa che sono presenti meno dati da leggere dal disco.

Per altre informazioni sui rowgroup, vedere Guida agli indici columnstore.

Dimensioni di destinazione per i rowgroup

Per ottenere prestazioni ottimali delle query, l'obiettivo è ottimizzare il numero di righe per rowgroup in un indice columnstore. Un rowgroup può avere un massimo di 1.048.576 righe. Va bene non raggiungere il numero massimo di righe per ogni rowgroup. Gli indici columnstore ottengono prestazioni ottimali quando i rowgroup hanno almeno 100.000 righe.

I gruppi di righe possono essere ridotti durante la compressione

Durante il caricamento in blocco o la ricompilazione dell'indice columnstore, non è disponibile memoria sufficiente a volte per comprimere tutte le righe designate per ogni rowgroup. Quando si verifica un utilizzo elevato della memoria, gli indici columnstore tagliano le dimensioni dei rowgroup in modo che la compressione nel columnstore abbia esito positivo.

Quando la memoria non è sufficiente per comprimere almeno 10.000 righe in ogni rowgroup, verrà generato un errore.

Per ulteriori informazioni sul caricamento massivo, vedere Caricamento massivo in un indice columnstore clusterizzato.

Come monitorare la qualità del rowgroup

La vista per la gestione dinamica (DMV) (sys.dm_db_column_store_row_group_physical_stats contiene la definizione della vista corrispondente al database SQL) espone informazioni utili come il numero di righe nei gruppi di righe e il motivo della riduzione se presente. È possibile creare la visualizzazione seguente come un modo pratico per eseguire query su questa DMV per ottenere informazioni sul taglio del rowgroup.

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;

La trim_reason_desc colonna indica se il rowgroup è stato tagliato (trim_reason_desc = NO_TRIM implica che non è presente alcun taglio e il gruppo di righe è di qualità ottimale). I motivi di taglio seguenti indicano un taglio prematuro del rowgroup:

• BULKLOAD: questo motivo di taglio viene usato quando il batch di righe in ingresso per il carico ha meno di 1 milione di righe. Il motore creerà gruppi di righe compressi se vengono inserite più di 100.000 righe (anziché inserire nello store delta) ma imposta la ragione di trimming su BULKLOAD. In questo scenario è consigliabile aumentare il carico batch per includere più righe. Inoltre, rivalutare lo schema di partizionamento per assicurarsi che non sia troppo granulare perché i gruppi di righe non possono estendersi ai limiti delle partizioni.
• MEMORY_LIMITATION: per creare gruppi di righe con 1 milione di righe, è richiesta una determinata quantità di memoria di lavoro dal motore. Quando la memoria disponibile della sessione di caricamento è inferiore alla memoria di lavoro necessaria, i gruppi di righe vengono eliminati prematuramente. Le sezioni seguenti illustrano come stimare la memoria necessaria e allocare più memoria.
• DICTIONARY_SIZE: questo motivo di taglio indica che il taglio del rowgroup si è verificato perché è presente almeno una colonna stringa con stringhe wide e/o con stringhe di cardinalità elevate. Le dimensioni del dizionario sono limitate a 16 MB in memoria e una volta raggiunto questo limite, il gruppo di righe viene compresso. Se si verifica questa situazione, è consigliabile isolare la colonna problematica in una tabella separata.

Come stimare i requisiti di memoria

La memoria massima necessaria per comprimere un rowgroup è, approssimativamente, come indicato di seguito:

• 72 MB +
• #rows * #columns * 8 byte +
• #rows * #short-string-columns * 32 byte +
• #long-string-columns * 16 MB per il dizionario di compressione

Le stringhe lunghe vengono compresse con un metodo di compressione progettato per comprimere il testo. Questo metodo di compressione usa un dizionario per archiviare i modelli di testo. La dimensione massima di un dizionario è 16 MB. Esiste un solo dizionario per ogni colonna a stringa lunga nel rowgroup.

Modi per ridurre i requisiti di memoria

Usare le tecniche seguenti per ridurre i requisiti di memoria per comprimere i rowgroup in indici columnstore.

Usare un minor numero di colonne

Se possibile, progettare la tabella con un minor numero di colonne. Quando un rowgroup viene compresso nel columnstore, l'indice columnstore comprime ogni segmento di colonna separatamente. Pertanto, i requisiti di memoria per comprimere un rowgroup aumentano man mano che aumenta il numero di colonne.

Usare un minor numero di colonne di stringhe

Le colonne di tipi di dati stringa richiedono più memoria rispetto ai tipi di dati numerici e di data. Per ridurre i requisiti di memoria, è consigliabile rimuovere colonne stringa dalle tabelle dei fatti e inserirle in tabelle delle dimensioni più piccole.

Requisiti di memoria aggiuntivi per la compressione di stringhe:

• I tipi di dati stringa fino a 32 caratteri possono richiedere 32 byte aggiuntivi per valore.
• I tipi di dati stringa con più di 32 caratteri vengono compressi usando metodi di dizionario. Ogni colonna del rowgroup può richiedere fino a 16 MB aggiuntivi per compilare il dizionario.

Evitare il partizionamento eccessivo

Gli indici columnstore creano uno o più rowgroup per partizione. Per il data warehousing in Azure Synapse Analytics, il numero di partizioni aumenta rapidamente perché i dati vengono distribuiti e ogni distribuzione viene partizionata. Se la tabella contiene troppi partizioni, potrebbero non esserci righe sufficienti per riempire i rowgroup. L'assenza di righe non esercita pressione sulla memoria durante la compressione, ma porta a gruppi di righe che non raggiungono le migliori prestazioni delle query su colonne.

Un altro motivo per evitare il partizionamento eccessivo è un sovraccarico di memoria per il caricamento di righe in un indice columnstore in una tabella partizionata. Durante un carico, molte partizioni potrebbero ricevere le righe in ingresso, che vengono mantenute in memoria fino a quando ogni partizione non contiene righe sufficienti per essere compresse. La presenza di troppe partizioni crea un utilizzo di memoria aggiuntivo.

Semplificare la query di caricamento

Il database condivide la concessione di memoria per una query tra tutti gli operatori nella query. Quando una query di caricamento dati include ordinamenti e join complessi, la memoria disponibile per la compressione dei dati viene ridotta.

Progettare la query di caricamento per focalizzarsi esclusivamente sul caricamento della query. Se è necessario eseguire trasformazioni sui dati, eseguirle separatamente dalla query di caricamento. Ad esempio, collocare i dati in una tabella heap, eseguire le trasformazioni e quindi caricare la tabella di preparazione nell'indice columnstore.

Regolare MAXDOP

Ogni distribuzione comprime i rowgroup nel columnstore in parallelo quando è disponibile più di un core CPU per ogni distribuzione. Il parallelismo richiede risorse di memoria aggiuntive, che possono causare una pressione di memoria e un taglio di rowgroup.

Per ridurre la pressione della memoria, è possibile usare l'hint per la query MAXDOP per forzare l'esecuzione dell'operazione di caricamento in modalità seriale all'interno di ogni distribuzione.

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

Modi per allocare più memoria

Le dimensioni DWU e la classe di risorse utente determinano insieme la quantità di memoria disponibile per una query utente. Per aumentare la concessione di memoria per una query di caricamento, è possibile aumentare il numero di DWU o aumentare la classe di risorse.

• Per aumentare le DWU, vedere Come si ridimensionano le prestazioni?
• Per modificare la classe di risorse per una query, vedere Modificare un esempio di classe di risorse utente.

Passaggi successivi

Per trovare altri modi per migliorare le prestazioni in Synapse SQL, vedere Panoramica delle prestazioni.