Indici columnstore - Prestazioni delle query

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

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

Gli indici columnstore possono ottenere fino a 100 volte migliori prestazioni sui carichi di lavoro di analisi e data warehousing e fino a 10 volte migliori rispetto agli indici rowstore tradizionali. Queste raccomandazioni consentono alle query di ottenere prestazioni di query veloci progettate per gli indici columnstore.

Indicazioni per migliorare le prestazioni delle query

Di seguito sono elencate alcune indicazioni per ottenere prestazioni elevate con gli indici columnstore.

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) diversi rowgroup vengono ignorati durante l'elaborazione della query.

• Usare un indice cluster rowstore. Se il predicato di query comune si trova in una colonna (ad esempio, C1) non correlata all'ordine di inserimento, creare un indice cluster rowstore nella colonna C1. Eliminare quindi l'indice cluster rowstore e creare un indice columnstore cluster. 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 cluster (NCCI), se la tabella ha un indice cluster rowstore, le righe sono già ordinate in base alla chiave di indice cluster. In questo caso, viene ordinato automaticamente anche l'indice columnstore non cluster. 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, combinare la colonna dell'ordine di inserimento con il partizionamento nella customer colonna. 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. Il passaggio e il troncamento delle 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, anziché eliminarle.

  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 in base MAXDOP alle esigenze. In alcuni casi, il valore DOP deve essere ridotto a uno per compilare l'indice sotto memoria vincolata nella 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 usare in questo modo la somiglianza dei valori di colonna e deve usare più byte per comprimere i valori nella country-region colonna.

Eliminazione di colonne

Gli indici columnstore non leggono le colonne non rilevanti ai fini 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 sono archiviati riga per riga, i valori della colonna in ogni riga vengono archiviati fisicamente insieme e non possono essere separati facilmente. 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 ne usa solo 5, l'indice columnstore recupera solo le 5 colonne rilevanti 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 scansioni di tabelle complete, un'alta percentuale di dati spesso non corrisponde ai criteri del predicato della query. Usando i metadati, l'indice columnstore può ignorare la lettura nei rowgroup che non contengono dati necessari per il risultato della query e quindi evitare di eseguire le operazioni I/O corrispondenti. 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ù normali rowstore non cluster o albero B, indici in un indice columnstore cluster. Gli indici albero B non cluster possono velocizzare una query con un predicato di uguaglianza o un predicato con un intervallo di valori limitato. 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 business retail modella i dati di vendita usando una tabella dei fatti con indice columnstore 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

Il termine esecuzione in modalità batch indica l'elaborazione congiunta di un set di righe, generalmente non più di 900, per migliorare l'efficienza di esecuzione. Ad esempio, la query SELECT SUM (Sales) FROM SalesData aggrega le vendite totali della tabella SalesData. Nell'esecuzione in modalità batch, il motore di esecuzione delle query calcola l'aggregato in gruppi di 900 valori. In questo modo, invece di pagare il costo delle singole righe, i metadati, i costi di accesso e altri tipi di costi generali vengono suddivisi su tutte le righe in un batch, riducendo notevolmente il percorso del codice. L'elaborazione in modalità batch opera sui dati compressi quando possibile ed elimina alcuni degli operatori di scambio usati dall'elaborazione in modalità riga, velocizzando le query di analisi in base agli ordini di grandezza.

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.

¹ Si applica a SQL Server 2016 (13.x), database SQL livelli Premium, livelli Standard - S3 e versioni successive e tutti i livelli vCore e il sistema della 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 in base agli 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 essere sopra il nodo SCAN o il nodo SCAN con GROUP BY.
• L'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:

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

Distribuzione del predicato 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.

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 di analisi più diffuse creano un join di una o più tabelle delle dimensioni con la tabella dei fatti.

Ad esempio considerare il caso di una tabella delle dimensioni Products. Una chiave primaria tipica è ProductCode, comunemente rappresentata come stringa. Per le prestazioni delle query, è consigliabile creare una chiave surrogata, in genere una colonna integer , per fare riferimento alla riga nella tabella delle dimensioni della tabella dei fatti.

L'indice columnstore esegue query di analisi con join e predicati che includono chiavi numeriche o basate su integer in modo efficiente. SQL Server 2016 (13.x) ha migliorato in modo significativo le prestazioni delle query di analisi con colonne basate su stringhe, eseguendo il push dei predicati con colonne stringa nel nodo SCAN.

Il pushdown del predicato stringa sfrutta il dizionario primario/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 il pushdown del predicato stringa, l'esecuzione della query calcola il predicato in base 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 è consentita la distribuzione del predicato stringa per i rowgroup 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 basate su predicati di filtro comuni per le query sull'indice columntore.

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 trarrà vantaggio da questa funzionalità finché non viene ricompilata usando o REBUILD/DROPCREATE.

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 per l'uso di non prefisso di LIKE, ad esempio column LIKE '%string'.

Gli indici columnstore cluster ordinati traggono vantaggio anche dall'eliminazione dei segmenti, in particolare per le colonne stringa. Negli indici columnstore cluster 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 saranno meno prevedibili. Per altre informazioni sugli indici columnstore cluster ordinati, vedere Usare un indice columnstore cluster ordinato per tabelle di data warehouse di grandi dimensioni. Per la disponibilità dell'indice columnstore ordinato, vedere Disponibilità dell'indice di colonna ordinata.

Usando l'opzione di connessione querySEt STATISTICS IO, è possibile visualizzare l'eliminazione dei segmenti in azione. Cercare l'output, ad esempio il seguente, per controllare 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. L'esempio di output seguente SET STATISTICS IO di una query, circa l'83% dei dati è stato ignorato dalla query:

...
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 nel data warehousing
• 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)