Columnstore indexes - query performance

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. These recommendations help your queries achieve the fast query performance that columnstore indexes are designed to provide.

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.

• Use a rowstore clustered index. If the common query predicate is on a column (for example, C1) unrelated to the insert order, create a rowstore clustered index on column 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 this case, the nonclustered columnstore index is also automatically ordered. 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. A common query pattern is to find quarterly purchases by customer. In this case, combine the insert order column with partitioning on customer column. 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

  Starting with SQL Server 2019 (15.x), the tuple-mover is helped by a background merge task. This task automatically compresses smaller OPEN delta rowgroups that have existed for some time, as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted. 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. For more information about columnstore terms and concepts, see Columnstore indexes: overview.

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. For example, if your table has fewer than one million rows, Database Engine uses only one thread to create the columnstore index.

If your table has more than one million rows, but Database Engine can't get a large enough memory grant to create the index using MAXDOP, Database Engine automatically decreases MAXDOP as needed. 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.

Columnstore performance explained

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

Columnstore indexes skip reading in columns that aren't referenced by the 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.

Rowgroup elimination

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?

For example, a retail business models their sales data using a fact table with clustered columnstore index. 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.

For more information about rowgroups, see Columnstore index design guidelines.

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. The following table shows the operators that run in batch mode according to the product version.

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

Aggregate pushdown

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(*).
• Aggregate operator must be on top of SCAN node or SCAN node with 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

For example, aggregate pushdown is done in both of the following queries:

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

String predicate pushdown

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. Most commonly used analytics queries join one or more dimension tables with the fact table.

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.

The columnstore index runs analytics queries with joins and predicates involving numeric or integer-based keys efficiently. 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.

String predicate pushdown leverages the primary/secondary dictionary created for columns to improve the query performance. 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. With string predicate pushdown, the query execution computes the predicate against the values in the dictionary. 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:
  • No string predicate pushdown for delta rowgroups. Non esiste un dizionario per le colonne nei rowgroup delta.
  • No string predicate pushdown if dictionary exceeds 64-KB entries.
  • 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).

Segment elimination doesn't apply to LOB data types, such as the (max) data type lengths.

Currently, only SQL Server 2022 (16.x) and later supports clustered columnstore rowgroup elimination for the prefix of LIKE predicates, for example column LIKE 'string%'. Segment elimination isn't supported for non-prefix use of LIKE, such as column LIKE '%string'.

Ordered columnstore indexes also benefit from segment elimination, especially for string columns. 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. For ordered columnstore index availability, see Ordered column index availability.

Usando l'opzione di connessione querySEt STATISTICS IO, è possibile visualizzare l'eliminazione dei segmenti in azione. Look for output such as the following to indicate that segment elimination has occurred. 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

...
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
• Columnstore indexes in data warehousing
• Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre il consumo di risorse
• Columnstore Index Architecture
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)