Indici columnstore - Prestazioni delle query

Si applica a:SQL ServerAzure SQL DatabaseIstanza gestita di Azure SQLAzure Synapse AnalyticsAnalytics Platform System (PDW)Database SQL in Microsoft Fabric

Questo articolo include raccomandazioni per ottenere prestazioni di query veloci con indici columnstore.

Gli indici columnstore possono ottenere un miglioramento delle prestazioni fino a 100 volte sui carichi di lavoro di analisi e data warehousing, e ottenere una compressione dei dati fino a 10 volte migliore rispetto agli indici basati su riga tradizionali. Queste raccomandazioni aiutano le vostre query a raggiungere le elevate performance che gli indici columnstore sono progettati per fornire.

Indicazioni per migliorare le prestazioni delle query

Di seguito sono riportate alcune raccomandazioni per raggiungere le elevate prestazioni che gli indici columnstore sono progettati per offrire.

1. Organizzare i dati per eliminare più rowgroup da una scansione di tabella completa

• Scegliere con attenzione l'ordine di inserimento. In genere in un data warehouse tradizionale i dati vengono inseriti in ordine temporale e le analisi vengono eseguite in una dimensione temporale, come nel caso delle analisi delle vendite per trimestre. Per questo tipo di carico di lavoro, l'eliminazione del rowgroup viene eseguita automaticamente. In SQL Server 2016 (13.x), puoi determinare il numero di rowgroup ignorati come parte dell'elaborazione della query.

• Usare un indice clusterizzato rowstore. Se il predicato di query comune si trova in una colonna (ad esempio, C1) non correlata all'ordine di inserimento, creare un indice clusterizzato rowstore nella colonna C1. Eliminare prima l'indice clusterizzato rowstore, quindi creare un indice clusterizzato columnstore. Se si crea l'indice columnstore cluster in modo esplicito usando MAXDOP = 1, l'indice columnstore cluster risultante viene ordinato perfettamente nella colonna C1. Se si specifica MAXDOP = 8, viene visualizzata la sovrapposizione dei valori tra otto rowgroup. Per un indice columnstore non clusterizzato (NCCI), se la tabella ha un indice clusterizzato rowstore, le righe sono già ordinate in base alla chiave dell'indice clusterizzato. In questo caso, anche l'indice columnstore non cluster viene ordinato automaticamente. Un indice columnstore non mantiene intrinsecamente l'ordine delle righe. Man mano che vengono inserite nuove righe o vengono aggiornate righe precedenti, potrebbe essere necessario ripetere il processo perché le prestazioni delle query di analisi potrebbero peggiorare.

• Implementare il partizionamento delle tabelle. È possibile partizionare l'indice columnstore, quindi usare l'eliminazione della partizione per ridurre il numero di rowgroup da analizzare. Ad esempio, una tabella dei fatti archivia gli acquisti effettuati dai clienti. Un modello di query comune consiste nel trovare acquisti trimestrali da customer. In questo caso, combina la colonna dell'ordine di inserimento con il partizionamento sulla colonna customer. Ogni partizione contiene righe per ogni customeroggetto , ordinato al momento dell'inserimento. Prendere in considerazione anche l'uso del partizionamento delle tabelle se è necessario rimuovere i dati meno recenti dal columnstore. Sostituire e troncare le partizioni non necessarie è una strategia efficiente per eliminare i dati senza generare frammentazione.

• Evitare di eliminare quantità elevate di dati. La rimozione di righe compresse da un rowgroup non è un'operazione sincrona. Sarebbe costoso decomprimere un rowgroup, eliminare la riga e quindi ricomprimerla. Pertanto, quando si eliminano dati da rowgroup compressi, questi rowgroup vengono ancora analizzati, anche se restituiscono meno righe. Se il numero di righe eliminate per più rowgroup è sufficientemente grande da essere unito in meno rowgroup, la riorganizzazione del columnstore aumenta la qualità dell'indice e le prestazioni delle query migliorano. Se il processo di eliminazione dei dati svuota in genere interi rowgroup, è consigliabile usare il partizionamento delle tabelle. Disattivare le partizioni che non sono più necessarie e troncarle, invece di eliminare righe.

  Nota

  A partire da SQL Server 2019 (15.x), lo spostamento delle tuple è aiutato da un'attività di unione in background. Questa attività comprime automaticamente i rowgroup differenziali OPEN più piccoli esistenti per un certo periodo di tempo, come determinato da una soglia interna o unisce rowgroup COMPRESSED da cui è stato eliminato un numero elevato di righe. In questo modo viene migliorata la qualità dell'indice columnstore nel tempo. Se è necessario eliminare grandi quantità di dati dall'indice columnstore, è consigliabile suddividere l'operazione in batch di eliminazione più piccoli nel tempo. L'invio in batch consente all'attività di unione in background di gestire l'attività di unione di rowgroup più piccoli e migliora la qualità dell'indice. Non è quindi necessario pianificare le finestre di manutenzione della riorganizzazione degli indici dopo l'eliminazione dei dati. Per altre informazioni sui termini e sui concetti dei columnstore, vedere Indici columnstore: Panoramica

2. Pianificare una quantità di memoria sufficiente per creare indici columnstore in parallelo

Per impostazione predefinita, la creazione di un indice columnstore è un'operazione parallela, a meno che la memoria non sia vincolata. La creazione dell'indice in parallelo richiede più memoria rispetto alla creazione dell'indice in modo seriale. Se si dispone di un'ampia quantità di memoria, la creazione di un indice columnstore richiede un tempo di circa 1,5 volte superiore rispetto alla compilazione di un albero B nelle stesse colonne.

La memoria richiesta per la creazione di un indice columnstore dipende dal numero di colonne, dal numero di colonne stringa, dal grado di parallelismo e dalle caratteristiche dei dati. Ad esempio, se la tabella contiene meno di un milione di righe, SQL Server usa un solo thread per creare l'indice columnstore.

Se la tabella contiene più di un milione di righe, ma SQL Server non può ottenere una concessione di memoria sufficiente per creare l'indice usando MAXDOP, SQL Server diminuisce automaticamente MAXDOP in base alle esigenze. In alcuni casi, il valore DOP deve essere ridotto a uno per costruire l'indice con memoria limitata nel contesto di una concessione di memoria disponibile.

A partire da SQL Server 2016 (13.x), la query funziona sempre in modalità batch. Nelle versioni precedenti l'esecuzione batch viene usata solo quando DOP è maggiore di uno.

Spiegazione delle prestazioni columnstore

Gli indici columnstore ottengono prestazioni di query ottimali combinando l'elaborazione in memoria in modalità batch ad alta velocità con tecniche che riducono significativamente i requisiti I/O. Poiché le query di analisi analizzano un numero elevato di righe, sono in genere associate a I/O e pertanto la riduzione delle operazioni di I/O durante l'esecuzione delle query è fondamentale per la progettazione di indici columnstore. Quando i dati vengono letti in memoria, è fondamentale ridurre il numero di operazioni in memoria.

Gli indici columnstore riducono le operazioni I/O e ottimizzano le operazioni in memoria grazie all'elevata compressione dei dati, all'eliminazione di columnstore, all'eliminazione di rowgroup e all'elaborazione batch.

Compressione dei dati

Gli indici columnstore raggiungono fino a 10 volte maggiore la compressione dei dati rispetto agli indici rowstore. Ciò riduce significativamente le operazioni I/O richieste per eseguire query di analisi e di conseguenza migliora le prestazioni delle query.

• Gli indici columnstore leggono i dati compressi dal disco, quindi il numero di byte che deve essere letto nella memoria risulta ridotto.

• Gli indici columnstore archiviano i dati in formato compresso in memoria, riducendo le operazioni di I/O evitando di leggere gli stessi dati in memoria. Ad esempio, con una compressione di 10 volte, gli indici columnstore possono mantenere 10 volte più dati in memoria, rispetto all'archiviazione dei dati in formato non compresso. Con più dati in memoria, è più probabile che l'indice columnstore trovi i dati necessari in memoria senza incorrere in letture non necessarie dal disco.

• Gli indici columnstore comprimono i dati per colonne anziché per righe. Questo consente di ottenere percentuali di compressione maggiori e di ridurre le dimensioni dei dati archiviati su disco. Ogni colonna viene compressa e archiviata in modo indipendente. I dati all'interno di una colonna hanno sempre lo stesso tipo di dati e tendono ad avere valori simili. Le tecniche di compressione dei dati columnstore sono ideali per ottenere frequenze di compressione più elevate quando i valori sono simili.

Ad esempio, una tabella dei fatti archivia gli indirizzi dei clienti e ha una colonna per country-region. Il numero totale di valori possibili è inferiore a 200. Alcuni di questi valori vengono ripetuti più volte. Se la tabella dei fatti contiene 100 milioni di righe, la country-region colonna comprime facilmente e richiede poco spazio di archiviazione. La compressione riga per riga non è in grado di capitalizzare in questo modo la somiglianza dei valori di colonna e deve usare più byte per comprimere i valori nella colonna country-region.

Eliminazione di colonne

Gli indici columnstore evitano di leggere le colonne che non sono necessarie per il risultato della query. L'eliminazione delle colonne riduce ulteriormente l'I/O per l'esecuzione delle query e pertanto migliora le prestazioni delle query.

• L'eliminazione di colonne è possibile perché i dati sono organizzati e compressi colonna per colonna. Al contrario, quando i dati vengono archiviati riga per riga, i valori di colonna in ogni riga vengono archiviati fisicamente insieme e non possono essere facilmente separati. Query Processor deve leggere in un'intera riga per recuperare valori di colonna specifici, aumentando le operazioni di I/O perché i dati aggiuntivi vengono letti in memoria inutilmente.

Ad esempio, se una tabella contiene 50 colonne e la query usa solo cinque di queste colonne, l'indice columnstore recupera solo le cinque colonne dal disco. Ignora la lettura nelle altre 45 colonne, riducendo le operazioni di I/O di un altro 90%, presupponendo che tutte le colonne siano di dimensioni simili. Se gli stessi dati vengono archiviati in un rowstore, Query Processor deve leggere le 45 colonne rimanenti.

Eliminazione di rowgroup

Per le analisi complete delle tabelle, una percentuale elevata dei dati in genere non corrisponde ai criteri del predicato di query. Usando i metadati, l'indice columnstore è in grado di ignorare la lettura nei rowgroup che non contengono dati necessari per il risultato della query, senza operazioni di I/O effettive. Questa capacità, denominata eliminazione di rowgroup, riduce le operazioni I/O per le scansioni di tabella complete e di conseguenza migliora le prestazioni delle query.

Quando è necessario che un indice columnstore esegua una scansione di tabella completa?

A partire da SQL Server 2016 (13.x), è possibile creare uno o più indici rowstore non clusterizzato normali o indici B-tree su un indice columnstore con cluster. Gli indici B-tree non clusterizzati possono velocizzare una query con un predicato di uguaglianza o un predicato con un intervallo di valori ristretto. Per i predicati più complessi, Query Optimizer potrebbe optare per una scansione di tabella completa. Senza la possibilità di ignorare i rowgroup, un'analisi completa della tabella può richiedere molto tempo, soprattutto per tabelle di grandi dimensioni.

In che occasioni una query di analisi trae vantaggio dall'eliminazione di rowgroup per una scansione di tabella completa?

Ad esempio, un'azienda al dettaglio modella i dati di vendita usando una tabella dei fatti con un indice columnstore con cluster. Ogni nuova vendita archivia vari attributi della transazione, inclusa la data di vendita di un prodotto. È interessante notare che, anche se gli indici columnstore non garantiscono un ordine ordinato, le righe di questa tabella vengono caricate in un ordine ordinato in data. Nel corso del tempo questa tabella cresce. Anche se l'azienda di vendita al dettaglio conserva i dati di vendita degli ultimi 10 anni, per una query di analisi potrebbe essere necessario solo il calcolo di un'aggregazione dell'ultimo trimestre. Gli indici columnstore possono eliminare l'accesso ai dati dei 39 trimestri precedenti semplicemente esaminando i metadati per la colonna della data. Si tratta di una riduzione del 97% della quantità di dati letti in memoria ed elaborati.

Quali rowgroup vengono ignorati in una scansione di tabella completa?

Per determinare quali rowgroup eliminare, l'indice columnstore usa i metadati per archiviare i valori minimi e massimi di ogni segmento di colonna per ogni rowgroup. Quando nessuno degli intervalli di segmenti di colonna soddisfa i criteri del predicato di query, l'intero rowgroup viene ignorato senza eseguire operazioni di I/O effettive. Ciò funziona perché i dati vengono in genere caricati in un ordine ordinato. Anche se l'ordinamento delle righe non è garantito, i valori di dati simili si trovano spesso all'interno dello stesso rowgroup o di un rowgroup adiacente.

Per altre informazioni sui rowgroup, vedere Linee guida per la progettazione degli indici columnstore.

Esecuzione in modalità batch

L'esecuzione in modalità batch elabora righe in gruppi, in genere fino a 900 alla volta, per migliorare l'efficienza. Ad esempio, la query SELECT SUM(Sales) FROM SalesData calcola le vendite totali della SalesData tabella. In modalità batch, il motore di query elabora i dati in gruppi di 900 righe. Questo approccio riduce i costi di accesso ai metadati e altri tipi di overhead distribuindoli in tutte le righe di un batch, anziché incorrere nell'overhead per ogni riga. Inoltre, la modalità batch funziona con i dati compressi quando possibile e rimuove alcuni degli operatori di scambio usati in modalità riga, velocizzando notevolmente le query analitiche.

Non tutti gli operatori di esecuzione delle query possono essere eseguiti in modalità batch. Ad esempio, le operazioni DML (Data Manipulation Language), ad esempio insert, delete o update, vengono eseguite una riga alla volta. L'operatore in modalità batch, ad esempio Scan, Join, Aggregate, Sort e altri, può migliorare le prestazioni delle query. Poiché l'indice columnstore è stato introdotto in SQL Server 2012 (11.x), si sta lavorando costantemente per aumentare gli operatori che possono essere eseguiti in modalità batch. La tabella seguente illustra gli operatori eseguiti in modalità batch in base alla versione del prodotto.

Operatori in modalità batch	Quando si usa	SQL Server 2012 (11.x)	SQL Server 2014 (12.x)	SQL Server 2016 (13.x) e Database SQL1	Commenti
Operazioni DML (inserimento, eliminazione, aggiornamento, fusione)		no	no	no	DML non è un'operazione in modalità batch perché non è parallela. Anche quando si abilita l'elaborazione batch in modalità seriale e si consente l'elaborazione in modalità batch di DML, non si rilevano vantaggi significativi.
scansione dell'indice columnstore	SCAN	Non disponibile	sì	sì	Per gli indici columnstore, possiamo trasferire il predicato al nodo SCAN.
Scansione dell'indice columnstore (non clusterizzato)	SCAN	sì	sì	sì	sì
ricerca dell'indice		Non disponibile	Non disponibile	no	In modalità riga, eseguiamo un'operazione di ricerca con un indice albero B non clusterizzato.
calcolo scalare	Espressione che restituisce un valore scalare.	sì	sì	sì	Analogamente a tutti gli operatori in modalità batch, esistono alcune restrizioni sul tipo di dati.
Concatenazione	UNION e UNION ALL	no	sì	sì
filtro	Applicare i predicati	sì	sì	sì
Hash Match	Funzioni di aggregazione basate su hash, join hash esterno, join hash destro, join hash sinistro, join interno destro, join interno sinistro	sì	sì	sì	Restrizioni per l'aggregazione: nessun valore min e max per le stringhe. Le funzioni di aggregazione disponibili sono sum/count/avg/min/max. Restrizioni per il join: nessun join di tipi non corrispondenti per tipi non interi.
Unione di Merge		no	no	no
Query multithreading		sì	sì	sì
cicli annidati		no	no	no
Query a thread singolo in esecuzione in MAXDOP 1		no	no	sì
Query a thread singolo con un piano di query seriale		no	no	sì
ordinamento	Ordinare per clausola in SCAN con l'indice columnstore.	no	no	sì
Ordinamento Principale		no	no	sì
aggregati finestra		Non disponibile	Non disponibile	sì	Nuovo operatore in SQL Server 2016 (13.x).

¹ Si applica a SQL Server 2016 (13.x), SQL Database livelli Premium, livelli Standard (S3 e versioni successive), tutti i livelli vCore e al sistema di piattaforma di analisi (PDW).

Per altre informazioni, vedere Guida sull'architettura di elaborazione delle query.

Pushdown dell'aggregazione

Un percorso di esecuzione normale per il calcolo di aggregazione che consente di recuperare le righe idonee dal nodo SCAN e aggregare i valori in modalità batch. Anche se offre prestazioni ottimali, a partire da SQL Server 2016 (13.x), l'operazione di aggregazione può essere inserita nel nodo SCAN. Il pushdown aggregato migliora le prestazioni dei calcoli aggregati di ordini di grandezza rispetto all'esecuzione in modalità Batch, a condizione che siano soddisfatte le condizioni seguenti.

• Le funzioni di aggregazione sono MIN, MAX, SUM, COUNT e COUNT(*).
• L'operatore di aggregazione deve trovarsi sopra un nodo SCAN o su un nodo SCAN con GROUP BY.
• Questa aggregazione non è un'aggregazione distinta.
• La colonna di aggregazione non è una colonna stringa.
• La colonna di aggregazione non è una colonna virtuale.
• Il tipo di dati di input e output deve essere uno dei seguenti e deve essere compreso tra 64 bit:
  • tinyint, int, bigint, smallint, bit
  • smallmoney, money, decimal e numeric con precisione <= 18
  • smalldate, date, datetime, datetime2, time

Ad esempio, il pushdown aggregato viene eseguito in entrambe le query seguenti:

SQL

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Pushdown del predicato della stringa

Quando si progetta uno schema del data warehouse, la modellazione consigliata è l'uso di uno schema star o snowflake costituito da una o più tabelle dei fatti e da molte tabelle delle dimensioni.

Suggerimento

La tabella dei fatti archivia le misure o le transazioni aziendali e la tabella delle dimensioni archivia le dimensioni di cui analizzare i fatti. Per altre informazioni sulla modellazione dimensionale, vedere Modellazione dimensionale in Microsoft Fabric.

Ad esempio, un fatto può essere un record che rappresenta la vendita di un certo prodotto in un'area specifica, mentre la dimensione rappresenta un set di regioni, prodotti e così via. Le tabelle dei fatti e delle dimensioni sono connesse da una relazione di tipo chiave primaria/chiave esterna. Le query analitiche più diffuse eseguono un join di una o più tabelle delle dimensioni con la tabella dei fatti.

Consideriamo una tabella delle dimensioni Products. Una chiave primaria tipica è ProductCode, comunemente rappresentata come stringa. Per migliorare le prestazioni delle query, è consigliabile creare una chiave surrogata, in genere una colonna intera, per fare riferimento alla riga nella tabella delle dimensioni a partire dalla tabella dei fatti.

L'indice columnstore esegue query analitiche con join e predicati che coinvolgono chiavi numeriche o basate su numeri interi in modo efficiente. SQL Server 2016 (13.x) ha migliorato in modo significativo le prestazioni delle query analitiche con colonne basate su stringhe, spostando i predicati con colonne su stringhe al nodo SCAN.

Il pushdown del predicato sulle stringhe sfrutta il dizionario primario e secondario creato per le colonne per migliorare le prestazioni delle query. Si consideri, ad esempio, un segmento di colonna stringa all'interno di un rowgroup costituito da 100 valori di stringa distinti. A ogni valore di stringa distinto viene fatto riferimento 10.000 volte in media, presupponendo un milione di righe. Con l'ottimizzazione del predicato di stringa, l'esecuzione della query computa il predicato rispetto ai valori nel dizionario. Se il predicato viene qualificato, tutte le righe che fanno riferimento al valore del dizionario vengono qualificate automaticamente. Questo migliora le prestazioni in due modi:

• Viene restituita solo la riga qualificata riducendo il numero di righe che devono essere propagate all'esterno del nodo di analisi.
• Il numero di confronti tra stringhe viene ridotto. In questo esempio sono necessari solo 100 confronti di stringhe invece di un milione. Esistono alcune limitazioni:
  • Non è previsto il pushdown del predicato stringa per i gruppi di righe delta. Non esiste un dizionario per le colonne nei rowgroup delta.
  • Non è consentito il pushdown del predicato stringa se le voci del dizionario superano i 64 KB.
  • L'espressione che valuta i valori Null non è supportata.

Eliminazione dei segmenti

Le scelte del tipo di dati possono avere un impatto significativo sulle prestazioni delle query, in quanto influenzano i predicati di filtro comuni per le query sull'indice colonnare.

Nei dati columnstore i gruppi di righe sono costituiti da segmenti di colonna. Esistono metadati con ogni segmento che consentono l'eliminazione rapida dei segmenti senza leggerli. Questa eliminazione di segmento si applica ai tipi di dati numerici, di data e ora e al tipo di dati datetimeoffset con scala minore o uguale a due. A partire da SQL Server 2022 (16.x), le funzionalità di eliminazione dei segmenti si estendono ai tipi di dati string, binary, guid e al tipo di dati datetimeoffset per la scala maggiore di due.

Dopo l'aggiornamento a una versione di SQL Server che supporta l'eliminazione di segmenti min/max stringa (SQL Server 2022 (16.x) e versioni successive, l'indice columnstore non trae vantaggio da questa funzionalità fino a quando non viene ricompilato usando un ALTER INDEX REBUILD o CREATE INDEX WITH (DROP_EXISTING = ON).

L'eliminazione dei segmenti non si applica ai tipi di dati LOB, ad esempio le lunghezze dei tipi di dati (max).

Al momento, solo SQL Server 2022 (16.x) e versioni successive supportano l'eliminazione del rowgroup columnstore cluster per il prefisso dei predicati LIKE, ad esempio column LIKE 'string%'. L'eliminazione dei segmenti non è supportata quando si utilizza LIKE, ad esempio column LIKE '%string'.

Indici columnstore ordinati beneficiano anche dell'eliminazione dei segmenti, in particolare per le colonne di tipo stringa. Negli indici columnstore ordinati, l'eliminazione dei segmenti nella prima colonna nella chiave di indice è più efficace, perché è ordinata. I miglioramenti delle prestazioni dovuti all'eliminazione dei segmenti in altre colonne della tabella sono meno prevedibili. Per altre informazioni sugli indici columnstore ordinati, vedere Usare un indice columnstore ordinato per tabelle di data warehouse di grandi dimensioni. Per la disponibilità dell'indice columnstore ordinato, vedere Disponibilità dell'indice columnstore ordinato.

Usando l'opzione di connessione querySEt STATISTICS IO, è possibile visualizzare l'eliminazione dei segmenti in azione. Cercare un output come il seguente per accertarsi che si è verificata l'eliminazione del segmento. I gruppi di righe sono costituiti da segmenti di colonna, pertanto ciò potrebbe indicare l'eliminazione dei segmenti. Circa l'83% dei dati è stato ignorato dalla query nel seguente esempio di output di una query: SET STATISTICS IO

Output

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Contenuto correlato

• Linee guida per la progettazione di indici columnstore
• Indici columnstore - Linee guida per il caricamento di dati
• Introduzione a columnstore per l'analisi operativa in tempo reale
• Indici columnstore nell'archiviazione dei dati
• Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre il consumo di risorse
• Architettura degli indici columnstore
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)