Indici Columnstore Descrizione

L'indice columnstore in memoria di SQL Server archivia e gestisce i dati usando l'archiviazione dei dati basata su colonne e l'elaborazione di query basate su colonne. Gli indici columnstore funzionano bene per i flussi di lavoro di archiviazione dati che eseguono principalmente caricamenti in blocco e query di sola lettura. Usare l'indice columnstore per ottenere fino a 10 volte i miglioramenti delle prestazioni delle query rispetto all'archiviazione tradizionale orientata alle righe e fino a 7 volte la compressione dei dati rispetto alle dimensioni dei dati non compressi.

Contenuto

• Nozioni di base

• al caricamento dei dati

• Suggerimenti sulle prestazioni

• Attività e argomenti correlati

Nozioni di base

Un indice columnstore è una tecnologia per l'archiviazione, il recupero e la gestione dei dati usando un formato di dati a colonne, denominato columnstore. SQL Server supporta sia gli indici columnstore clusterizzati che quelli non clusterizzati. Entrambi usano la stessa tecnologia columnstore in memoria, ma presentano differenze nello scopo e nelle funzionalità supportate.

Vantaggi

Gli indici columnstore funzionano bene per la maggior parte delle query di sola lettura che eseguono analisi su set di dati di grandi dimensioni. Spesso si tratta di query per i carichi di lavoro di data warehousing. Gli indici columnstore offrono notevoli miglioramenti delle prestazioni per le query che usano analisi di tabelle complete e non sono particolarmente adatti per le query che cercano nei dati un determinato valore.

Vantaggi dell'indice columnstore:

• Le colonne hanno spesso dati simili, con tassi di compressione elevati.

• Le frequenze di compressione elevate migliorano le prestazioni delle query utilizzando un footprint di memoria più piccolo. A sua volta, le prestazioni delle query possono migliorare perché SQL Server può eseguire più operazioni di query e dati in memoria.

• È stato aggiunto a SQL Server un nuovo meccanismo di esecuzione di query denominato esecuzione in modalità batch che riduce l'utilizzo della CPU di una grande quantità. L'esecuzione in modalità batch è strettamente integrata e ottimizzata per il formato di archiviazione columnstore. L'esecuzione in modalità batch è talvolta nota come esecuzione vettoriale o vettorializzata.

• Le query spesso selezionano solo alcune colonne di una tabella, riducendo il totale delle operazioni di I/O su un supporto fisico.

Versioni Columnstore

SQL Server 2012, SQL Server 2012 Parallel Data Warehouse e SQL Server 2014 usano tutti gli indici columnstore per accelerare le query comuni del data warehouse. SQL Server 2012 ha introdotto due nuove funzionalità: un indice columnstore non cluster e una funzionalità di esecuzione di query basata su vettori che elabora i dati in unità denominate "batch". SQL Server 2014 include le funzionalità degli indici columnstore cluster aggiornabili di SQL Server 2012.

Caratteristiche chiave

In SQL Server, un indice columnstore cluster:

• È disponibile nelle edizioni Enterprise, Developer e Evaluation.

• Aggiornabile.

• Metodo di archiviazione primario per l'intera tabella.

• Nessuna colonna chiave. Tutte le colonne sono colonne incluse.

• È l'unico indice della tabella. Non può essere combinato con altri indici.

• Può essere configurato per utilizzare la compressione columnstore o la compressione columnstore archivio.

• Non archivia fisicamente le colonne in un ordine ordinato. Archivia invece i dati per migliorare la compressione e le prestazioni.

In SQL Server, un indice columnstore non clusterizzato:

• Può indicizzare un sottoinsieme di colonne nell'indice clusterizzato o nell'heap. Ad esempio, può indicizzare le colonne usate di frequente.

• Richiede spazio di archiviazione aggiuntivo per archiviare una copia delle colonne nell'indice.

• Viene aggiornato ricompilando l'indice o spostando le partizioni all'interno e all'esterno. Non è aggiornabile usando le operazioni DML, ad esempio inserimento, aggiornamento ed eliminazione.

• Può essere combinato con altri indici nella tabella.

• Può essere configurato per l'uso della compressione dell'archivio columnstore o columnstore.

• Non archivia fisicamente le colonne in un ordine ordinato. Archivia invece i dati per migliorare la compressione e le prestazioni. L'ordinamento preliminare dei dati prima di creare il columnstore index non è obbligatorio, ma può migliorare la compressione del columnstore.

Concetti e termini chiave

I seguenti concetti e termini chiave sono associati agli indici columnstore.

indice columnstore Un indice columnstore è una tecnologia per l'archiviazione, il recupero e la gestione dei dati tramite un formato di dati a colonne, denominato columnstore. SQL Server supporta gli indici columnstore clusterizzati e non clusterizzati. Entrambi usano la stessa tecnologia columnstore in memoria, ma presentano differenze nello scopo e nelle funzionalità supportate.

columnstore Un columnstore è costituito da dati organizzati logicamente come tabella con righe e colonne e archiviati fisicamente in un formato di dati a colonne.

rowstore Un rowstore è costituito da dati organizzati logicamente come tabella con righe e colonne e quindi archiviati fisicamente in un formato di dati a livello di riga. Questo è stato il modo tradizionale per archiviare i dati delle tabelle relazionali.

rowgroup e segmenti di colonna Per prestazioni elevate e frequenze di compressione elevate, l'indice columnstore suddivide la tabella in gruppi di righe, denominati gruppi di righe e quindi comprime ogni gruppo di righe in modo a livello di colonna. Il numero di righe nel gruppo di righe deve essere sufficientemente grande per migliorare i tassi di compressione e sufficientemente piccolo da trarre vantaggio dalle operazioni in memoria.

gruppo di righe Un rowgroup è un gruppo di righe compresse contemporaneamente in formato columnstore.

segmento di colonna Un segmento di colonna è una colonna di dati dall'interno del rowgroup.

• Un rowgroup contiene in genere il numero massimo di righe per rowgroup, ovvero 1.048.576 righe.

• Ogni rowgroup contiene un segmento di colonna per ogni colonna della tabella.

• Ogni segmento di colonna è compresso e archiviato su un supporto fisico.

Indice columnstore non raggruppato Un indice columnstore non raggruppato è un indice columnstore di sola lettura creato in un indice raggruppato esistente o in una tabella heap. Contiene una copia di un sottoinsieme di colonne, comprese tutte le colonne nella tabella. La tabella è di sola lettura quando contiene un indice columnstore non clusterizzato.

Un indice columnstore non cluster consente di disporre di un indice columnstore per l'esecuzione di query di analisi durante l'esecuzione di operazioni di sola lettura nella tabella originale.

Un indice columnstore clusterizzato è l'archiviazione fisica per l'intera tabella ed è l'unico indice per la tabella. L'indice cluster è aggiornabile. È possibile eseguire operazioni di inserimento, eliminazione e aggiornamento sull'indice ed è possibile caricare in blocco i dati nell'indice.

Per ridurre la frammentazione dei segmenti di colonna e migliorare le prestazioni, l'indice columnstore potrebbe archiviare temporaneamente alcuni dati in una tabella rowstore, denominata deltastore, oltre a un albero B di ID per le righe eliminate. Le operazioni deltastore sono gestite dietro le quinte. Per restituire i risultati della query corretti, l'indice columnstore clusterizzato combina i risultati della query dal columnstore e dal deltastore.

deltastore Usato solo con indici columnstore cluster, un deltastore è una tabella rowstore che archivia le righe fino a quando il numero di righe è sufficientemente grande da essere spostato nel columnstore. Un deltastore viene utilizzato con indici columnstore clusterizzati per migliorare le prestazioni durante il caricamento e altre operazioni DML.

Durante un caricamento massivo, la maggior parte delle righe viene indirizzata direttamente al columnstore senza passare per il deltastore. Alcune righe nel caricamento in blocco alla fine potrebbero essere troppo poche per soddisfare la dimensione minima di un gruppo di righe, ovvero 102.400 righe. In questo caso, le righe finali passano al deltastore anziché al columnstore. Per i carichi in blocco di piccole dimensioni, con meno di 102.400 righe, tutte le righe passano direttamente al deltastore.

Quando l'archivio differenziale raggiunge il numero massimo di righe, viene chiuso. Un processo di spostamento delle tuple verifica la presenza di gruppi di righe chiusi. Quando trova il rowgroup chiuso, lo comprime e lo archivia nel columnstore.

Caricamento di dati

Caricamento di dati in un indice columnstore non cluster

Per caricare i dati in un indice columnstore non cluster, caricare prima i dati in una tabella rowstore tradizionale archiviata come indice heap o cluster e quindi creare l'indice columnstore non cluster con CREATE COLUMNSTORE INDEX (Transact-SQL).

Una tabella con un indice columnstore non clusterizzato è di sola lettura fino a quando l'indice non viene rimosso o disabilitato. Per aggiornare la tabella e l'indice columnstore nonclustered, è possibile scambiare le partizioni. È anche possibile disabilitare l'indice, aggiornare la tabella e quindi ricostruire l'indice.

Per altre informazioni, vedere Uso di indici columnstore non cluster

Caricamento di dati in un indice columnstore raggruppato

Come suggerisce il diagramma, per caricare i dati in un indice columnstore clusterizzato, SQL Server:

1. Inserisce i rowgroup di dimensioni massime direttamente nel columnstore. Quando i dati vengono caricati, SQL Server assegna le righe di dati in un ordine di arrivo all'interno di un gruppo di righe aperto.

2. Per ogni rowgroup, dopo aver raggiunto le dimensioni massime, SQL Server:

   1. Contrassegna il rowgroup come CLOSED.

   2. Ignora il deltastore.

   3. Comprimi ogni segmento di colonna nel gruppo di righe utilizzando la compressione colonnare.

   4. Archivia fisicamente ogni segmento di colonna compresso nel columnstore.

3. Inserisce le righe rimanenti nel columnstore o nel deltastore come indicato di seguito:

   1. Se il numero di righe soddisfa i requisiti minimi per ogni rowgroup, le righe vengono aggiunte al columnstore.

   2. Se il numero di righe è minore delle righe minime per ogni rowgroup, le righe vengono aggiunte al deltastore.

Per ulteriori informazioni sulle attività e i processi deltastore, vedere Uso di indici columnstore con cluster

Suggerimenti per incrementare le prestazioni

Pianificare una quantità sufficiente di memoria 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 userà 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 ridurrà automaticamente MAXDOP in base alle esigenze per adattarsi alla concessione di memoria disponibile. In alcuni casi, è necessario ridurre il grado di parallelismo a uno per compilare l'indice nella memoria vincolata.

Attività e argomenti correlati

Indici columnstore non clusterizzati

Per le attività comuni, vedere Uso degli indici Columnstore non clusterizzati.

• CREATE COLUMNSTORE INDEX (Transact-SQL)

• ALTER INDEX (Transact-SQL) usando REBUILD.

• DROP INDEX (Transact-SQL)

Indici columnstore clusterizzati

Per le attività comuni, vedere Uso di indici columnstore cluster.

• CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL)

• ALTER INDEX (Transact-SQL) usando REBUILD o REORGANIZE.

• DROP INDEX (Transact-SQL)

• INSERT (Transact-SQL)

• UPDATE (Transact-SQL)

• DELETE (Transact-SQL)

Metadati

Tutte le colonne di un indice columnstore vengono archiviate nei metadati come colonne incluse. L'indice columnstore non include colonne chiave.

• sys.indexes (Transact-SQL)

• sys.index_columns (Transact-SQL)

• sys.partitions (Transact-SQL)

• sys.column_store_segments (Transact-SQL)

• sys.column_store_dictionaries (Transact-SQL)

• sys.column_store_row_groups (Transact-SQL)