Ottimizzazione della qualità dei rowgroup per gli indici columnstore nel pool SQL dedicato

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 gruppi di righe

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, la memoria disponibile potrebbe non essere sufficiente per comprimere tutte le righe designate per ogni gruppo di righe. Quando la memoria disponibile è scarsa, gli indici columnstore troncano le dimensioni del gruppo di righe in modo da consentire la compressione nel columnstore.

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à di un gruppo di righe

La DMV sys.dm_pdw_nodes_db_column_store_row_group_physical_stats (sys.dm_db_column_store_row_group_physical_stats contiene la definizione di vista corrispondente al database SQL) che espone informazioni utili come il numero di righe presenti nei gruppi ed eventualmente il motivo per cui un gruppo di righe è stato troncato.

Per effettuare una query su questa DMV allo scopo di ottenere informazioni sul trimming di un gruppo di righe, è possibile creare la vista seguente.

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;

Il trim_reason_desc indica se il rowgroup è stato tagliato (trim_reason_desc = NO_TRIM implica che non esiste alcun taglio e il gruppo di righe è di qualità ottimale). I motivi seguenti indicano che il gruppo di righe è stato tagliato prematuramente:

• BULKLOAD: questo motivo viene usato se il batch di righe in ingresso per il caricamento è inferiore a 1 milione di righe. 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. In questo scenario, valutare l'opportunità di ampliare il caricamento in batch in modo da 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 indica che il gruppo di righe è stato tagliato perché era presente almeno una colonna di stringhe con stringhe "wide" e/o a cardinalità elevata. 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

Per visualizzare una stima dei requisiti di memoria per comprimere un rowgroup di dimensioni massime in un indice columnstore, è consigliabile creare la vista di esempio dbo.vCS_mon_mem_grant. Questa query mostra la dimensione dell'allocazione di memoria richiesta da un rowgroup per la compressione nel columnstore.

La memoria massima necessaria per comprimere un rowgroup è approssimativamente

• 72 MB +
• #rows * #columns * 8 byte +
• #rows * #short-string-columns * 32 byte +
• #colonne stringa lunga* 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.

Di conseguenza, 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 pool SQL dedicato 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. La mancanza di righe non crea una pressione di memoria durante la compressione. Ma alcuni gruppi di righe soffriranno di scarse prestazioni delle query columnstore.

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 contiene ordinamenti complessi e join, la memoria disponibile per la compressione è ridotta.

Progettare la query di caricamento concentrandosi solo sul caricamento. Se è necessario eseguire trasformazioni sui dati, eseguirle separatamente dalla query di caricamento. Ad esempio, collocare temporaneamente i dati in una tabella heap, eseguire le trasformazioni e quindi caricare la tabella di gestione temporanea 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 per il pool SQL dedicato, vedere Panoramica delle prestazioni.